环境：
oracle 10g R2
Select Count(*) From t_Ho_Order_Statistics --2032946
Select Count(*) From t_Ho_Order_Info --2032946
其他都是小的维度表
统计信息已经检查过了，差不多10天前的（不过我10天前跑过这个SQL,出来的执行计划一样），
这里，这里就把注意力集中在两个大表连接的问题上，
后面的谓词过滤，这个SQL是程序里面拼出来的，每次谓词过滤条件可能不同。
文章主要是指出hints的基本用法，还有使用hints的误区--<1>
下面这条SQL要运行5分钟左右，出来的结果为0
原SQL和执行计划
SQL> explain plan for
Select Count(*)
From (Select o.Order_Id,
o.Order_Version,
o.Guest_Name,
o.Order_Status_Id,
o.Order_Flow_Id,
o.Order_Occupt_Type_Id,
o.Pymt_Means_Id,
o.Contact_Name,
o.Reserv_Time,
h.Hotel_Name,
h.Address,
Arrival_Time,
Checkout_Time,
o.Use_Rule,
o.Guaranty_Type_Id,
Gt.Guaranty_Type_Desc,
o.Hbeorder_Id,
o.Order_Occupt_Type_Id Producttype,
o.Order_Type_Rule,
o.Hotel_Belong
From t_Ho_Order_Info o
Left Join t_Sd_Guaranty_Type Gt
On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id
Inner Join t_Hp_Hotel h
On o.Hotel_Id = h.Hotel_Id
Inner Join t_Ho_Order_Statistics Os
On Os.Order_Id = o.Order_Id
Inner Join t_s_Employee e
On e.Employee_Id = o.Employee_Id
Where 1 = 1
And o.Guest_Name Like '%王明%'
And o.Reserv_Time >=
To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')
And o.Reserv_Time <=
To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0
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
Select Count(*)
From (Select /*+ leading(o,Os) use_nl(Os,o) */
o.Order_Id,
o.Order_Version,
o.Guest_Name,
o.Order_Status_Id,
o.Order_Flow_Id,
o.Order_Occupt_Type_Id,
o.Pymt_Means_Id,
o.Contact_Name,
o.Reserv_Time,
h.Hotel_Name,
h.Address,
Arrival_Time,
Checkout_Time,
o.Use_Rule,
o.Guaranty_Type_Id,
Gt.Guaranty_Type_Desc,
o.Hbeorder_Id,
o.Order_Occupt_Type_Id Producttype,
o.Order_Type_Rule,
o.Hotel_Belong
From t_Ho_Order_Info o
Left Join t_Sd_Guaranty_Type Gt
On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id
Inner Join t_Hp_Hotel h
On o.Hotel_Id = h.Hotel_Id
Inner Join t_Ho_Order_Statistics Os
On Os.Order_Id = o.Order_Id
Inner Join t_s_Employee e
On e.Employee_Id = o.Employee_Id
Where 1 = 1
And o.Guest_Name Like '%王明%'
And o.Reserv_Time >=
To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')
And o.Reserv_Time <=
To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0
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
注意：这里如果单写use_nl(Os,o)并不是指Os表和o表做嵌套循环，而是指Os会用嵌套循环，o表也会用嵌套循环，加了
leading之后才是把他们两个连到一起来了。
Reference上的解释：
Multi-table hints are like single-table hints, except that the hint can specify
one or more tables or views. LEADING is an example of a multi-table hint. Note
that USE_NL(table1 table2) is not considered a multi-table hint because it is
actually a shortcut for USE_NL(table1) and USE_NL(table2).
the LEADING hint specifies the exact join order to be used; the join methods to
be used on the different tables are also specified.
--<3>
通过加hints让其走hash,差不多40秒左右跑完
SQL> explain plan for
Select Count(*)
From (Select /*+ use_hash(Os,o) */
o.Order_Id,
o.Order_Version,
o.Guest_Name,
o.Order_Status_Id,
o.Order_Flow_Id,
o.Order_Occupt_Type_Id,
o.Pymt_Means_Id,
o.Contact_Name,
o.Reserv_Time,
h.Hotel_Name,
h.Address,
Arrival_Time,
Checkout_Time,
o.Use_Rule,
o.Guaranty_Type_Id,
Gt.Guaranty_Type_Desc,
o.Hbeorder_Id,
o.Order_Occupt_Type_Id Producttype,
o.Order_Type_Rule,
o.Hotel_Belong
From t_Ho_Order_Info o
Inner Join t_Sd_Guaranty_Type Gt
On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id
Inner Join t_Hp_Hotel h
On o.Hotel_Id = h.Hotel_Id
Inner Join t_Ho_Order_Statistics Os
On Os.Order_Id = o.Order_Id
Inner Join t_s_Employee e
On e.Employee_Id = o.Employee_Id
Where 1 = 1
And o.Guest_Name Like '%王明%'
And o.Reserv_Time >=
To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')
And o.Reserv_Time <=
To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0
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)*/，它也选择了走hash,且执行计划和上面
一模一样。
直接加leading,直接加leading的意思就是表名让他们两个表
相连接，没有指定是Hash还是嵌套的话oracle会自行选择。
--<4>
直接加rule,12秒出结果
SQL> explain plan for
Select /*+ RULE */
Count(*)
From (Select o.Order_Id,
o.Order_Version,
o.Guest_Name,
o.Order_Status_Id,
o.Order_Flow_Id,
o.Order_Occupt_Type_Id,
o.Pymt_Means_Id,
o.Contact_Name,
o.Reserv_Time,
h.Hotel_Name,
h.Address,
Arrival_Time,
Checkout_Time,
o.Use_Rule,
o.Guaranty_Type_Id,
Gt.Guaranty_Type_Desc,
o.Hbeorder_Id,
o.Order_Occupt_Type_Id Producttype,
o.Order_Type_Rule,
o.Hotel_Belong
From t_Ho_Order_Info o
Inner Join t_Sd_Guaranty_Type Gt
On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id
Inner Join t_Hp_Hotel h
On o.Hotel_Id = h.Hotel_Id
Inner Join t_Ho_Order_Statistics Os
On Os.Order_Id = o.Order_Id
Inner Join t_s_Employee e
On e.Employee_Id = o.Employee_Id
Where 1 = 1
And o.Guest_Name Like '%王明%'
And o.Reserv_Time >=
To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')
And o.Reserv_Time <=
To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0
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
------------------------------------
SQL>analyze index IDX_RESERV_TIME validate Structure
SQL>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一下
SQL>alter index IDX_RESERV_TIME rebuild Online
SQL>analyze index IDX_RESERV_TIME validate Structure
SQL>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
SQL>exec dbms_stats.gather_index_stats('gc','IDX_RESERV_TIME');
------------------------------------
将索引做了调整后，发现原SQL仍然是原来那种效果，没有改进
看来还是集群因子的问题
--<5>
根据rule的思路，我们直接强制走索引IDX_RESERV_TIME，0.3秒出结果
SQL> explain plan for
Select Count(*)
From (Select /*+ index(o IDX_RESERV_TIME)*/
o.Order_Id,
o.Order_Version,
o.Guest_Name,
o.Order_Status_Id,
o.Order_Flow_Id,
o.Order_Occupt_Type_Id,
o.Pymt_Means_Id,
o.Contact_Name,
o.Reserv_Time,
h.Hotel_Name,
h.Address,
Arrival_Time,
Checkout_Time,
o.Use_Rule,
o.Guaranty_Type_Id,
Gt.Guaranty_Type_Desc,
o.Hbeorder_Id,
o.Order_Occupt_Type_Id Producttype,
o.Order_Type_Rule,
o.Hotel_Belong
From t_Ho_Order_Info o
Left Join t_Sd_Guaranty_Type Gt
On o.Guaranty_Type_Id = Gt.Guaranty_Type_Id
Inner Join t_Hp_Hotel h
On o.Hotel_Id = h.Hotel_Id
Inner Join t_Ho_Order_Statistics Os
On Os.Order_Id = o.Order_Id
Inner Join t_s_Employee e
On e.Employee_Id = o.Employee_Id
Where 1 = 1
And o.Guest_Name Like '%王明%'
And o.Reserv_Time >=
To_Date('2012-1-10 0:00:00', 'yyyy-mm-ddhh24-mi-ss')
And o.Reserv_Time <=
To_Date('2012-5-24 19:00:00', 'yyyy-mm-ddhh24-mi-ss')) T0
;
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
扫一扫
1268
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
