常用的几种Hints优化一条SQL
来源：互联网 发布：net和java有什么区别 编辑：程序博客网 时间：2024/05/29 17:50
环境： oracle 10g R2 Select Count(*) From t_Ho_Order_Statistics --2032946Select Count(*) From t_Ho_Order_Info --2032946其他都是小的维度表统计信息已经检查过了，差不多10天前的（不过我10天前跑过这个SQL,出来的执行计划一样），这里，这里就把注意力集中在两个大表连接的问题上，后面的谓词过滤，这个SQL是程序里面拼出来的，每次谓词过滤条件可能不同。
文章主要是指出hints的基本用法，还有使用hints的误区
--<1>下面这条SQL要运行5分钟左右，出来的结果为0 原SQL和执行计划SQL> explain plan forSelect 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')) T0Explained 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 specifyone or more tables or views. LEADING is an example of a multi-table hint. Notethat 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')) T0Explained 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')) T0Explained 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 forSelect 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- 常用的几种Hints优化一条SQL
- 工作中常用的几种sql优化技巧
- 利用ordered hints优化SQL
- SQL语句中的优化提示Hints的总结
- SQL语句中的优化提示Hints的总结
- 一条Mysql上的Sql优化经历
- 一条SQL语句的优化过程
- 一条SQL语句的优化学习
- 记一条子查询的SQL优化
- 一条经典的SQL语句优化实例
- Mysql的一条SQL优化（一）
- Mysql的一条SQL优化（二）
- Oracle下一条SQL语句的优化
- 一条相关子查询的SQL优化
- 几种常用的优化方法
- 【转】几种常用的优化方法
- 浅谈SQL Server 2008中的Hints（提示）-三种不同类型的联接提示（Join Hints）、查询提示（Query Hints）、表提示（Table Hints）
- 浅谈SQL Server 2008中的Hints（提示）-三种不同类型的联接提示（Join Hints）、查询提示（Query Hints）、表提示（Table Hints）
- Android中除了利用VideoView、Mediaplayer播放视频文件外，还可以用发送Intent来调用视频播放模块。
- [菜鸟专供|高手勿入] 快速判断质数
- Android实现文件下载
- android opengl es 绘制位图字体
- android UI 设计注意事项
- 常用的几种Hints优化一条SQL
- ASP.NET 中 “返回上一页”的实现方法小结
- ssh信任关系建立
- 动态库(.so)
- 为什么要使用接口 ？ 研发的那些事3--接口之本
- ios中UIImageView展现图片的不同处理方式
- ADT-20 问题 android support library
- 多队列网卡简介
- float数据 与 double数据相乘时，结果转成int时会出现错误
