SQL優化--用各種hints優化一條SQL
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10678398/viewspace-721283/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- [20181114]一條sql語句的優化.txtSQL優化
- 通過新增條件優化SQL優化SQL
- MySQL SQL優化案例(一)MySql優化
- sql優化之邏輯優化SQL優化
- 資料庫優化 - SQL優化資料庫優化SQL
- SQL SERVER優化SQLServer優化
- SQL優化指南SQL優化
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- 詳解SQL效能優化十條經驗SQL優化
- Oracle SQL效能優化的40條軍規OracleSQL優化
- SQL優化參考SQL優化
- sql優化專題SQL優化
- SQL語句優化SQL優化
- SQL效能優化技巧SQL優化
- MySQL-SQL優化MySql優化
- 慢Sql優化思路SQL優化
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- SQL優化案例-使用with as優化Subquery Unnesting（七）SQL優化
- MySQL之SQL優化詳解（一）MySql優化
- 資料庫優化SQL資料庫優化SQL
- MYSQL SQL語句優化MySql優化
- SQL優化之limit 1SQL優化MIT
- 優化sql查詢速度優化SQL
- (轉)SQL 優化原則SQL優化
- SQL優化的方法論SQL優化
- 史上最全SQL優化方案SQL優化
- MySQL之SQL優化技巧MySql優化
- [20201224]sql優化困惑.txtSQL優化
- sql語句效能優化SQL優化
- 對一條基於分割槽的簡單SQL的優化SQL優化
- SQL精華總結索引型別優化SQL優化事務大表優化思維導圖❤️SQL索引型別優化
- SQL優化（3）（延續前一節）SQL優化
- Oracle 某行系統SQL優化案例(一)OracleSQL優化
- Mysql 52條SQL語句效能優化策略彙總MySql優化
- SQL優化器探討(zt)SQL優化
- SQL優化案例-union代替or（九）SQL優化