Oracle 'or exists/in'結合使用引起的filter執行計劃 的優化
註釋:
所以這對這個sql看下怎麼優化.
環境:
AIX |
7.1 |
>> | CPU 64 (16C) |
>> | Mem 64 |
Oracle | 11.2.0.3.0 |
>>
|
Mem 40G
|
表結構/資料情況
TABLE_NAME |
INDEX_NAME |
COLUMN_NAME |
BLEVEL |
LEAF_BLOCKS |
DISTINCT_KEYS |
CLUSTERING_FACTOR |
NUM_ROWS |
STATUS |
CS_CHANGE_ACCE | PK_CHANGE_ORDER_ID | ORDER_ID | 1 | 392 | 91178 | 65010 | 91178 | VALID |
CS_ORDER_ITEM
|
IDX_COI_ORDER_ID
|
ORDER_ID
|
2
|
35082
|
638272
|
9077480
|
15016701
|
VALID
|
SQL> set autot on
SQL> set timing on
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> set timing on
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select count(1) ---->篇幅原因,選擇count(1)復現
2 from cs_order_item t3 where 1 = 1
4 AND (T.ORDER_ID = 1528168 OR
5 T.ORDER_ID IN
6 (SELECT CA.ORDER_ID
7 FROM CS_CHANGE_ACCE CA
8 WHERE CA.TAG IN (SELECT t.TAG
9 FROM CS_CHANGE_ACCE t
10 WHERE t.ORDER_ID = 1537775)))
11 and t.oi_source in (1, 2)
12 order by t.begin_date;
COUNT(1)
----------
12
已用時間: 00: 01: 13.28
執行計劃
----------------------------------------------------------
Plan hash value: 1964544126
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU )| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 108 K (2)| 00:21 :44 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | CS_ORDER_ITEM | 14M| 234M| 108 K (2)| 00:21 :44 |
| 4 | NESTED LOOPS | | 1 | 22 | 3 (0)| 00:00 :01 |
| 5 | TABLE ACCESS BY INDEX ROWID | CS_CHANGE_ACCE | 1 | 11 | 2 (0)| 00:00 :01 |
|* 6 | INDEX UNIQUE SCAN | PK_CHANGE_ORDER_ID | 1 | | 1 (0)| 00:00 :01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | CS_CHANGE_ACCE | 1 | 11 | 1 (0)| 00:00 :01 |
|* 8 | INDEX UNIQUE SCAN | PK_CHANGE_ORDER_ID | 1 | | 0 (0)| 00:00 :01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."ORDER_ID"=1528168 OR EXISTS (SELECT 0 FROM "CS_CHANGE_ACCE"
"CA","CS_CHANGE_ACCE" "T" WHERE "T"."ORDER_ID"=1537775 AND "CA"."TAG"="T"."TAG" AND
"CA"."ORDER_ID"=:B1))
3 - filter("T"."OI_SOURCE"=1 OR "T"."OI_SOURCE"=2)
6 - access("CA"."ORDER_ID"=:B1)
7 - filter("CA"."TAG"="T"."TAG")
8 - access("T"."ORDER_ID"=1537775)
統計資訊
----------------------------------------------------------
1 recursive calls
1 db block gets
19981341 consistent gets
392349 physical reads
0 redo size
217 bytes sent via SQL*Net to client
252 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Filter/子查詢非巢狀 知識點:
Subquery Unnesting(子查詢非巢狀): 如果SQL語句中的where條件後面有子查詢,子查詢前面有in,not in,exists,not exists, CBO很可能會對該子查詢進行等價改寫,改寫的過程其實就叫做子查詢擴充套件。Oracle始終認為SQL語句進行改寫之後, CBO能更好的優化,當然了,並不是所有的子查詢都會被改寫,子查詢中有些限制條件會阻止CBO進行改寫(因為改寫之後不等價)。 說白了就是 將半連線與反連線的子查詢語句轉化成普通的連線,如果沒有子查詢非巢狀化,執行的基本的方法就是基於FILTER操作. 這是一個類似於nested loop的一種方法,和nest loop不同之處是要維護一個hash table 且filter的效能實際上跟列值distinct數有關, oracle執行時實際上做了很大優化,最壞情況下才會出現對外表每一行 執行一次filter操作,如果distinct值比較少,那執行效率還是非常高的,甚至有可能比nl更高. **** hint UNNEST – 子查詢展開,不讓子查詢巢狀在裡面,也就是這裡說的子查詢非巢狀化. NO_UNNEST – 子查詢巢狀化. |
*** 檢視生產環境子查詢非巢狀_unnest_subquery value
SQL> select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault
2 from sys.x$ksppi x, sys.x$ksppcv y
3 where x.inst_id = userenv('Instance')
4 and y.inst_id = userenv('Instance')
5 and x.indx = y.indx
6 and x.ksppinm like '_unnest%'
7 order by translate(x.ksppinm, ' _', ' ');
NAME VALUE ISDEFAULT
-----------------------------------------------------------------------------------------------------
_unnest_subquery TRUE TRUE
SQL>
2 from sys.x$ksppi x, sys.x$ksppcv y
3 where x.inst_id = userenv('Instance')
4 and y.inst_id = userenv('Instance')
5 and x.indx = y.indx
6 and x.ksppinm like '_unnest%'
7 order by translate(x.ksppinm, ' _', ' ');
NAME VALUE ISDEFAULT
-----------------------------------------------------------------------------------------------------
_unnest_subquery TRUE TRUE
SQL>
分析 : cs_order_item.order_id選擇性較高的索引,CBO 沒做成子查詢擴充套件,
導致cs_order_item 14M的驅動表和CS_CHANGE_ACCE 小表做迴圈
(驅動表(CS_ORDER_ITEM)行數等於(CS_CHANGE_ACCE)被驅動表掃描次數(14M) )
所以我們選擇用union all形式手動子查詢擴充套件(**註釋用hint無作用)
SQL> SELECT COUNT(1)
2 FROM (select t.*
3 from cs_order_item t
4 where 1 = 1
5 AND (T.ORDER_ID = 1528168)
6 and t.oi_source in (1, 2)
7 union all
8 select t.*
9 from cs_order_item t
10 where 1 = 1
11 AND t.oi_source in (1, 2)
12 and T.ORDER_ID IN
13 (SELECT CA.ORDER_ID
14 FROM CS_CHANGE_ACCE CA
15 WHERE CA.TAG IN (SELECT t.TAG
16 FROM CS_CHANGE_ACCE t
17 WHERE t.ORDER_ID = 1537775)));
COUNT(1)
----------
12
已用時間: 00: 00: 00.02
執行計劃
----------------------------------------------------------
Plan hash value: 3789129724
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU )| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 158 (2 )| 00: 00:02 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 69 | | 158 (2 )| 00: 00:02 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | CS_ORDER_ITEM | 23 | 207 | 18 (0)| 00:00 :01 |
|* 5 | INDEX RANGE SCAN | INDEX_ORDER_ID | 23 | | 3 (0)| 00:00 :01 |
| 6 | NESTED LOOPS | | | | | |
| 7 | NESTED LOOPS | | 46 | 1426 | 140 (2)| 00:00 :02 |
| 8 | NESTED LOOPS | | 2 | 44 | 106 (2)| 00:00 :02 |
| 9 | TABLE ACCESS BY INDEX ROWID | CS_CHANGE_ACCE | 1 | 11 | 2 (0)| 00:00 :01 |
|* 10 | INDEX UNIQUE SCAN | PK_CHANGE_ORDER_ID | 1 | | 1 (0)| 00:00 :01 |
|* 11 | TABLE ACCESS FULL | CS_CHANGE_ACCE | 2 | 22 | 104 (2)| 00:00 :02 |
|* 12 | INDEX RANGE SCAN | INDEX_ORDER_ID | 23 | | 2 (0)| 00:00 :01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | CS_ORDER_ITEM | 23 | 207 | 17 (0)| 00:00 :01 |
--------------------------------------------------------------------------------------------------------
Predicate Information ( identified by operation id):
---------------------------------------------------4 - filter("T"."OI_SOURCE"=1 OR "T"."OI_SOURCE"=2)
5 - access("T"."ORDER_ID"=1528168)
10 - access("T"."ORDER_ID"=1537775)
11 - filter("CA"."TAG"="T"."TAG")
12 - access("T"."ORDER_ID"="CA"."ORDER_ID")
13 - filter("T"."OI_SOURCE"=1 OR "T"."OI_SOURCE"=2)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
217 bytes sent via SQL*Net to client
252 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
總結:
看來CBO很多時候遇到一些寫法也做不到我們想的那樣,所以需要我們手動做子查詢擴充套件;
最近發現好多sql的過濾條件 ( a.id=1 or exists/in ..子查詢) 都選擇了filter 執行計劃,都選擇了union all 做的解決。
【源於本人筆記】 若有書寫錯誤,表達錯誤,請指正...
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28602568/viewspace-1462937/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【優化】Oracle 執行計劃優化Oracle
- 【優化】ORACLE執行計劃分析優化Oracle
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- Oracle 執行計劃中access 和 filter的區別OracleFilter
- oracle執行計劃的使用(EXPLAIN)OracleAI
- 解析Oracle執行計劃的結果Oracle
- 使用no_merge結合其它hint完全控制Oracle執行計劃Oracle
- 使用spm繫結執行計劃來線上優化資料庫優化資料庫
- Oracle優化案例-統計資訊對執行計劃的影響(十三)Oracle優化
- 使用leading(,)優化sql執行計劃優化SQL
- VIEW和SYNONYM引起的執行計劃的異常View
- 【MySQL】MySQL的執行計劃及索引優化MySql索引優化
- Oracle調優之看懂Oracle執行計劃Oracle
- 對IN & EXISTS NOT IN & NOT EXISTS的優化優化
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- Oracle-繫結執行計劃Oracle
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT,執行計劃】-帶HINT的索引執行計劃Oracle索引
- 轉摘_使用leading(,)優化sql執行計劃優化SQL
- 分析執行計劃最佳化SQLORACLE的執行計劃(轉)SQLOracle
- Oracle 變數窺視引起執行計劃異常故障分析Oracle變數
- Oracle優化案例-從執行計劃定位SQL問題(三)Oracle優化SQL
- sqm執行計劃的繫結
- 看懂Oracle中的執行計劃Oracle
- ORACLE執行計劃的介紹Oracle
- ORACLE執行計劃的檢視Oracle
- ORACLE執行計劃Oracle
- Oracle優化案例-改變那些CBO無能為力的執行計劃(一)Oracle優化
- Oracle學習系列—資料庫優化—In和Exists的使用Oracle資料庫優化
- MySQL查詢優化之優化器工作流程以及優化的執行計劃生成MySql優化
- Oracle執行計劃Explain Plan 如何使用OracleAI
- 【sql調優之執行計劃】獲取執行計劃SQL
- Oracle中檢視已執行sql的執行計劃OracleSQL
- MySQL 5.7 優化不能只看執行計劃MySql優化
- 【效能優化】執行計劃與直方圖優化直方圖
- oracle執行計劃與統計資訊的一些總結Oracle