oracle 10g R2
加各种hints优化一条SQL：
Select Count(*) From t_Ho_Order_Statistics --2032946
Select Count(*) From t_Ho_Order_Info --2032946
其他都是小的维度表
统计信息已经检查过了，差不多10天前的（不过我10天前跑过这个SQL,出来的执行计划一样），
这里，这里就把注意力集中在两个大表连接的问题上，
后面的谓词过滤，这个SQL是程序里面拼出来的，每次谓词过滤条件可能不同。
=========== =========== =========== =========== =========== =========== =========== ===========
--<1>
下面这条SQL要运行5分钟左右，出来的结果为0
原SQL和执行计划
SQL> explain plan for
2
2 Select Count(*)
3 From (Select o.Order_Id,
4 o.Order_Version,
5 o.Guest_Name,
6 o.Order_Status_Id,
7 o.Order_Flow_Id,
8 o.Order_Occupt_Type_Id,
9 o.Pymt_Means_Id,
10 o.Contact_Name,
11 o.Reserv_Time,
12 h.Hotel_Name,
13 h.Address,
14 Arrival_Time,
15 Checkout_Time,
16 o.Use_Rule,
17 o.Guaranty_Type_Id,
18 Gt.Guaranty_Type_Desc,
19 o.Hbeorder_Id,
20 o.Order_Occupt_Type_Id Producttype,
21 o.Order_Type_Rule,
22 o.Hotel_Belong
23 From t_Ho_Order_Info o
24 Left Join t_Sd_Guaranty_Type Gt
25 On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id
26 Inner Join t_Hp_Hotel h
27 On o.Hotel_Id = h.Hotel_Id
28 Inner Join t_Ho_Order_Statistics Os
29 On Os.Order_Id = o.Order_Id
30 Inner Join t_s_Employee e
31 On e.Employee_Id = o.Employee_Id
32 Where 1 = 1
33 And o.Guest_Name Like '%王明%'
34 And o.Reserv_Time >=
35 To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')
36 And o.Reserv_Time <=
37 To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0
38 ;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1815111547
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 11175 (38)| 00:02:15 |
| 1 | SORT AGGREGATE | | 1 | 49 | | |
| 2 | NESTED LOOPS OUTER | | 3430 | 164K| 11175 (38)| 00:02:15 |
| 3 | NESTED LOOPS | | 3430 | 154K| 11174 (38)| 00:02:15 |
| 4 | INDEX FAST FULL SCAN | HOBASEINFO_STATINFO_FK | 2016K| 11M| 1609 (7)| 00:00:20 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T_HO_ORDER_INFO | 1 | 40 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_T_HO_ORDER_INFO | 1 | | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_T_SD_GUARANTY_TYPE | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("O"."RESERV_TIME">=TO_DATE('2012-01-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"O"."GUEST_NAME" LIKE U'%\738B\634D\660E%' AND "O"."RESERV_TIME"<=TO_DATE('2012-05-24 19:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "O"."EMPLOYEE_ID" IS NOT NULL)
6 - access("OS"."ORDER_ID"="O"."ORDER_ID")
7 - access("O"."GUARANTY_TYPE_ID"="GT"."GUARANTY_TYPE_ID"(+))
23 rows selected
注意：HOBASEINFO_STATINFO_FK为表t_Ho_Order_Statistics上的索引，问题比较明显，
嵌套循环驱动表出来了2064384行，下面虽然是通过索引唯一扫描访问t_ho_order_info
但是这个操作的次数实在是太多了，肯定慢。
----------------------------------------------------------------------------
=========== =========== =========== =========== =========== =========== =========== ===========
--<2>
还是让它走嵌套循环，换个驱动表，48秒左右跑完
SQL> explain plan for
2
2 Select Count(*)
3 From (Select /*+ leading(o,Os) use_nl(Os,o) */
4 o.Order_Id,
5 o.Order_Version,
6 o.Guest_Name,
7 o.Order_Status_Id,
8 o.Order_Flow_Id,
9 o.Order_Occupt_Type_Id,
10 o.Pymt_Means_Id,
11 o.Contact_Name,
12 o.Reserv_Time,
13 h.Hotel_Name,
14 h.Address,
15 Arrival_Time,
16 Checkout_Time,
17 o.Use_Rule,
18 o.Guaranty_Type_Id,
19 Gt.Guaranty_Type_Desc,
20 o.Hbeorder_Id,
21 o.Order_Occupt_Type_Id Producttype,
22 o.Order_Type_Rule,
23 o.Hotel_Belong
24 From t_Ho_Order_Info o
25 Left Join t_Sd_Guaranty_Type Gt
26 On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id
27 Inner Join t_Hp_Hotel h
28 On o.Hotel_Id = h.Hotel_Id
29 Inner Join t_Ho_Order_Statistics Os
30 On Os.Order_Id = o.Order_Id
31 Inner Join t_s_Employee e
32 On e.Employee_Id = o.Employee_Id
33 Where 1 = 1
34 And o.Guest_Name Like '%王明%'
35 And o.Reserv_Time >=
36 To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')
37 And o.Reserv_Time <=
38 To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0
39 ;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3583136125
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 19342 (4)| 00:03:53 |
| 1 | SORT AGGREGATE | | 1 | 49 | | |
| 2 | NESTED LOOPS OUTER | | 3430 | 164K| 19342 (4)| 00:03:53 |
| 3 | NESTED LOOPS | | 3430 | 154K| 19341 (4)| 00:03:53 |
|* 4 | TABLE ACCESS FULL| T_HO_ORDER_INFO | 2962 | 115K| 17262 (5)| 00:03:28 |
|* 5 | INDEX RANGE SCAN | HOBASEINFO_STATINFO_FK | 1 | 6 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_T_SD_GUARANTY_TYPE | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("O"."RESERV_TIME">=TO_DATE('2012-01-10 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "O"."GUEST_NAME" LIKE U'%\738B\634D\660E%' AND
"O"."RESERV_TIME"<=TO_DATE('2012-05-24 19:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"O"."EMPLOYEE_ID" IS NOT NULL)
5 - access("OS"."ORDER_ID"="O"."ORDER_ID")
6 - access("O"."GUARANTY_TYPE_ID"="GT"."GUARANTY_TYPE_ID"(+))
23 rows selected
=========== =========== =========== =========== =========== =========== =========== ===========
--<3>
通过加hints让其走hash,差不多40秒左右跑完
SQL> explain plan for
2
2 Select Count(*)
3 From (Select /*+ use_hash(Os,o) */
4 o.Order_Id,
5 o.Order_Version,
6 o.Guest_Name,
7 o.Order_Status_Id,
8 o.Order_Flow_Id,
9 o.Order_Occupt_Type_Id,
10 o.Pymt_Means_Id,
11 o.Contact_Name,
12 o.Reserv_Time,
13 h.Hotel_Name,
14 h.Address,
15 Arrival_Time,
16 Checkout_Time,
17 o.Use_Rule,
18 o.Guaranty_Type_Id,
19 Gt.Guaranty_Type_Desc,
20 o.Hbeorder_Id,
21 o.Order_Occupt_Type_Id Producttype,
22 o.Order_Type_Rule,
23 o.Hotel_Belong
24 From t_Ho_Order_Info o
25 Inner Join t_Sd_Guaranty_Type Gt
26 On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id
27 Inner Join t_Hp_Hotel h
28 On o.Hotel_Id = h.Hotel_Id
29 Inner Join t_Ho_Order_Statistics Os
30 On Os.Order_Id = o.Order_Id
31 Inner Join t_s_Employee e
32 On e.Employee_Id = o.Employee_Id
33 Where 1 = 1
34 And o.Guest_Name Like '%王明%'
35 And o.Reserv_Time >=
36 To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')
37 And o.Reserv_Time <=
38 To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0
39 ;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3811067636
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 18941 (5)| 00:03:48 |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | HASH JOIN | | 3430 | 154K| 18941 (5)| 00:03:48 |
|* 3 | TABLE ACCESS FULL | T_HO_ORDER_INFO | 2962 | 115K| 17262 (5)| 00:03:28 |
| 4 | INDEX FAST FULL SCAN| HOBASEINFO_STATINFO_FK | 2016K| 11M| 1609 (7)| 00:00:20 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OS"."ORDER_ID"="O"."ORDER_ID")
3 - filter("O"."RESERV_TIME">=TO_DATE('2012-01-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND "O"."GUEST_NAME" LIKE U'%\738B\634D\660E%' AND
"O"."RESERV_TIME"<=TO_DATE('2012-05-24 19:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"O"."EMPLOYEE_ID" IS NOT NULL AND "O"."GUARANTY_TYPE_ID" IS NOT NULL)
20 rows selected
-----
注意：这里将hints的写法单单改成/*+ leading(o,OS)*/，直接加leading,它也选择了走hash,且执行计划和上面
一模一样。
=========== =========== =========== =========== =========== =========== =========== ===========
--<4>
直接加rule,12秒出结果
SQL> explain plan for
2 Select /*+ RULE */ Count(*)
3 From (Select o.Order_Id,
4 o.Order_Version,
5 o.Guest_Name,
6 o.Order_Status_Id,
7 o.Order_Flow_Id,
8 o.Order_Occupt_Type_Id,
9 o.Pymt_Means_Id,
10 o.Contact_Name,
11 o.Reserv_Time,
12 h.Hotel_Name,
13 h.Address,
14 Arrival_Time,
15 Checkout_Time,
16 o.Use_Rule,
17 o.Guaranty_Type_Id,
18 Gt.Guaranty_Type_Desc,
19 o.Hbeorder_Id,
20 o.Order_Occupt_Type_Id Producttype,
21 o.Order_Type_Rule,
22 o.Hotel_Belong
23 From t_Ho_Order_Info o
24 Inner Join t_Sd_Guaranty_Type Gt
25 On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id
26 Inner Join t_Hp_Hotel h
27 On o.Hotel_Id = h.Hotel_Id
28 Inner Join t_Ho_Order_Statistics Os
29 On Os.Order_Id = o.Order_Id
30 Inner Join t_s_Employee e
31 On e.Employee_Id = o.Employee_Id
32 Where 1 = 1
33 And o.Guest_Name Like '%王明%'
34 And o.Reserv_Time >=
35 To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')
36 And o.Reserv_Time <=
37 To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0
38 ;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4158354473
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
|* 3 | TABLE ACCESS BY INDEX ROWID| T_HO_ORDER_INFO |
|* 4 | INDEX RANGE SCAN | IDX_RESERV_TIME |
|* 5 | INDEX RANGE SCAN | HOBASEINFO_STATINFO_FK |
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("O"."GUARANTY_TYPE_ID" IS NOT NULL AND "O"."EMPLOYEE_ID"
IS NOT NULL AND "O"."GUEST_NAME" LIKE U'%\738B\634D\660E%')
4 - access("O"."RESERV_TIME">=TO_DATE('2012-01-10 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "O"."RESERV_TIME"<=TO_DATE('2012-05-24
19:00:00', 'yyyy-mm-dd hh24:mi:ss'))
5 - access("OS"."ORDER_ID"="O"."ORDER_ID")
Note
-----
- rule based optimizer used (consider using cbo)
26 rows selected
=========== =========== =========== =========== =========== =========== =========== ===========
注意：加了rule之后，是通过索引范围扫描访问了t_ho_order_info,Reserv_Time列上
的索引IDX_RESERV_TIME起效果了？关注一下这个索引。
--先前原始SQL走t_ho_order_info上面的PK_T_HO_ORDER_INFO，是因为嵌套循环可以通过等值条件使用索引，而非使用到了谓词过滤条件上面的索引列
-----------------------------------
Select c.Table_Name 表名,
a.Index_Name 索引名,
a.index_type 索引类型,
b.Column_Name 列名,
a.Distinct_Keys 列中不同的值,
a.Clustering_Factor As 集群因子,
a.Last_Analyzed As 索引上次统计信息收集时间,
c.Last_Analyzed As 表上次统计信息收集时间
From User_Indexes a, User_Ind_Columns b, User_Tables c
Where a.Index_Name = b.Index_Name
And a.Table_Name = c.Table_Name
And a.Index_Name = Upper('IDX_RESERV_TIME');
表名 索引名 索引类型 列名 列中不同的值 集群因子 索引上次统计信息收集时间 表上次统计信息收集时间
T_HO_ORDER_INFO IDX_RESERV_TIME NORMAL RESERV_TIME 1763955 1699935 2012/3/28 17:17:05 2012/3/28 17:16:21
------------------------------------
analyze index IDX_RESERV_TIME validate Structure
select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;
LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
2064512 39225728 31495 598405
碎片貌似有点多
在线rebuild一下
alter index IDX_RESERV_TIME rebuild Online
analyze index IDX_RESERV_TIME validate Structure
select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;
LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
2033136 38629584 0 0
exec dbms_stats.gather_index_stats('gc','IDX_RESERV_TIME');
------------------------------------
将索引做了调整后，发现原SQL仍然是原来那种效果，没有改进
为什么没有使用IDX_RESERV_TIME？查看上面的索引状态，跟集群因子有较大关系
=========== =========== =========== =========== =========== =========== =========== ===========
--<5>
根据rule的思路，我们直接强制走索引IDX_RESERV_TIME，0.3秒出结果
SQL> explain plan for
2
2 Select Count(*)
3 From (Select /*+ index(o IDX_RESERV_TIME)*/
4 o.Order_Id,
5 o.Order_Version,
6 o.Guest_Name,
7 o.Order_Status_Id,
8 o.Order_Flow_Id,
9 o.Order_Occupt_Type_Id,
10 o.Pymt_Means_Id,
11 o.Contact_Name,
12 o.Reserv_Time,
13 h.Hotel_Name,
14 h.Address,
15 Arrival_Time,
16 Checkout_Time,
17 o.Use_Rule,
18 o.Guaranty_Type_Id,
19 Gt.Guaranty_Type_Desc,
20 o.Hbeorder_Id,
21 o.Order_Occupt_Type_Id Producttype,
22 o.Order_Type_Rule,
23 o.Hotel_Belong
24 From t_Ho_Order_Info o
25 Left Join t_Sd_Guaranty_Type Gt
26 On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id
27 Inner Join t_Hp_Hotel h
28 On o.Hotel_Id = h.Hotel_Id
29 Inner Join t_Ho_Order_Statistics Os
30 On Os.Order_Id = o.Order_Id
31 Inner Join t_s_Employee e
32 On e.Employee_Id = o.Employee_Id
33 Where 1 = 1
34 And o.Guest_Name Like '%王明%'
35 And o.Reserv_Time >=
36 To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')
37 And o.Reserv_Time <=
38 To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0
39 ;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3515346792
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 19878 (2)| 00:03:59 |
| 1 | SORT AGGREGATE | | 1 | 49 | | |
|* 2 | HASH JOIN | | 3430 | 164K| 19878 (2)| 00:03:59 |
| 3 | NESTED LOOPS OUTER | | 2962 | 124K| 18199 (1)| 00:03:39 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T_HO_ORDER_INFO | 2962 | 115K| 18198 (1)| 00:03:39 |
|* 5 | INDEX RANGE SCAN | IDX_RESERV_TIME | 63952 | | 62 (4)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_T_SD_GUARANTY_TYPE | 1 | 3 | 1 (0)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | HOBASEINFO_STATINFO_FK | 2016K| 11M| 1609 (7)| 00:00:20 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OS"."ORDER_ID"="O"."ORDER_ID")
4 - filter("O"."GUEST_NAME" LIKE U'%\738B\634D\660E%' AND "O"."EMPLOYEE_ID" IS NOT NULL)
5 - access("O"."RESERV_TIME">=TO_DATE('2012-01-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"O"."RESERV_TIME"<=TO_DATE('2012-05-24 19:00:00', 'yyyy-mm-dd hh24:mi:ss'))
6 - access("O"."GUARANTY_TYPE_ID"="GT"."GUARANTY_TYPE_ID"(+))
23 rows selected