一個connect by查詢語句的優化
問題描述:
資料庫從10g升級到11g後發現以下查詢語句的效能下降,其平均執行時間從0.04秒增長到了0.32秒
-
with q as
-
(Select trading, calculated_date,
-
rank() over (partition by calculated_date order by mkt_for_nda_id nulls last) rank1
-
From Operating_Calendar
-
Where trunc(CALCULATED_DATE) BETWEEN v3_stdt and v3_eddt
-
AND (mkt_for_nda_id = v_mkt_nda_id or geo_for_Nda_id in
-
(Select Nda_id From Geog
-
Where Nda_id in
-
(Select Geo_in_nda_id From Geog_Structure
-
Start With Geo_with_nda_id = v_geog_nda_id
-
Connect By Prior Geo_in_nda_id = Geo_with_nda_id)
-
UNION (SELECT NDA_ID FROM GEOG WHERE NDA_ID = v_geog_nda_id)
-
)
-
)
-
)
-
select T_calcdt_reason_Type(calculated_date, NULL)
-
bulk collect into v_calcdt_reason -- bulk collect into a table object
-
from q
- where rank1 = 1 and trading = \'H\';
分析:
該語句中包括一個connect
by查詢,
對於connect
by查詢來說,
資料庫在解析階段很難根據統計資訊對其cardinality值作出準確估計,所以oracle在為這種查詢選擇執行計劃時,由於該侷限性,常常難以得到令人滿意的結果,如下例:
-
explain plan for SELECT GEO_IN_NDA_ID FROM GEOG_STRUCTURE START WITH GEO_WITH_NDA_ID = 858 CONNECT BY PRIOR GEO_IN_NDA_ID = GEO_WITH_NDA_ID;
-
PLAN_TABLE_OUTPUT
-
----------------------------------------------------------------------------------------------------
-
Plan hash value: 498378953
-
-
---------------------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-
---------------------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 2 | 52 | 3 (34)| 00:00:01
-
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | |
-
| 2 | INDEX FAST FULL SCAN | GST_UK1 | 2356 | 18848 | 2 (0)| 00:00:01
-
-------------------------------------------------------------------------------------------------
-
explain plan for SELECT GEO_IN_NDA_ID FROM GEOG_STRUCTURE START WITH GEO_WITH_NDA_ID = 100 CONNECT BY PRIOR GEO_IN_NDA_ID = GEO_WITH_NDA_ID;
-
PLAN_TABLE_OUTPUT
-
-------------------------------------------------------------------------------------------------
-
Plan hash value: 498378953
-
-------------------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-
-------------------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 2 | 52 | 3 (34)| 00:00:01 |* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | |
-
| 2 | INDEX FAST FULL SCAN | GST_UK1 | 2356 | 18848 | 2 (0)| 00:00:01
-
-------------------------------------------------------------------------------------------------
-
SQL> select count(*) from GEOG_STRUCTURE
-
START WITH GEO_WITH_NDA_ID = 858
-
CONNECT BY PRIOR GEO_IN_NDA_ID = GEO_WITH_NDA_ID;
-
COUNT(*)
-
----------
-
7
-
SQL> select count(*) from GEOG_STRUCTURE
-
START WITH GEO_WITH_NDA_ID = 100
-
CONNECT BY PRIOR GEO_IN_NDA_ID = GEO_WITH_NDA_ID;
-
COUNT(*)
-
----------
- 4
從以上測試中可看到,在給查詢謂詞中GEO_WITH_NDA_ID列分別指定100或858時,執行計劃中cardinality的估計值均是2,但實際上他們分別為7和4. 說到這裡仍然沒有解釋為什麼前面的查詢語句在資料庫升級後效能發生了大幅下降,
但實際上正是這種對connect
by查詢cardinality估計的缺陷導致了這類查詢執行計劃的不穩定性.
我們來看看上述語句在10g和11g中的執行計劃有什麼不同:
10g:
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 32 (100)| |
|* 1 | VIEW | | 5 | 125 | 32 (13)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK | | 5 | 80 | 32 (13)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL | OPERATING_CALENDAR | 99 | 1584 | 31 (10)| 00:00:01 |
| 5 | SORT UNIQUE | | 2 | 21 | 7 (58)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | NESTED LOOPS | | 1 | 17 | 4 (25)| 00:00:01 |
|* 8 | VIEW | VW_NSO_1 | 1 | 13 | 3 (34)| 00:00:01 |
| 9 | SORT UNIQUE | | 1 | 8 | 3 (34)| 00:00:01 |
|* 10 | CONNECT BY WITH FILTERING | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | GEOG_STRUCTURE | 1 | 8 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | GST_IND03 | 1 | | 1 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | | | | |
| 14 | CONNECT BY PUMP | | | | | |
| 15 | TABLE ACCESS BY INDEX ROWID| GEOG_STRUCTURE | 1 | 8 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | GST_IND03 | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | GEO_PK | 1 | 4 | 1 (0)| 00:00:01 |
|* 18 | FILTER | | | | | |
|* 19 | INDEX RANGE SCAN | GEO_PK | 1 | 4 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
11g:
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 32 (100)| |
|* 1 | VIEW | | 5 | 125 | 32 (13)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK | | 5 | 80 | 32 (13)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL | OPERATING_CALENDAR | 99 | 1584 | 31 (10)| 00:00:01 |
| 5 | SORT UNIQUE | | 2 | 21 | 8 (63)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
|* 7 | HASH JOIN | | 1 | 17 | 5 (40)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | GEO_PK | 1 | 4 | 1 (0)| 00:00:01 |
|* 9 | VIEW | VW_NSO_1 | 2 | 26 | 3 (34)| 00:00:01 |
| 10 | SORT UNIQUE | | 2 | 34 | 3 (34)| 00:00:01 |
|* 11 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 12 | INDEX FAST FULL SCAN | GST_UK1 | 2356 | 18848 | 2 (0)| 00:00:01 |
|* 13 | FILTER | | | | | |
|* 14 | INDEX RANGE SCAN | GEO_PK | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
比較執行計劃可以看到, connect by查詢的執行計劃從10g的CONNECT BY WITH FILTERING變為了11g中的CONNECT BY NO FILTERING WITH SW (UNIQUE).
查詢相關資料可發現:這是oracle對connect by查詢所引入的一種新的演算法,在原來的演算法中(CONNECT BY WITH FILTERING),會在每次迴圈中作行過濾操作,然後只讀取需要的行.而在新的演算法中(CONNECT BY NO FILTERING WITH SW),則會把所有需要的資料讀入記憶體,然後統一作記憶體排序來過濾掉不需要的列.
個人認為前一種演算法適用於從資料集中取很小一部分資料的場景,而後一種演算法則適用於從大資料量中取大量資料的操作.而我們的場景正是前一種:全表有2000多行,而每次只是取其中的幾行,但由於oracle對connect by語句cardinality估算的不準確性,導致oracle並不能準確地在兩種演算法間作出合理的抉擇.
備註:這裡所說的”新的演算法”實際上是在10.2.0.2版本就引入了,但以前的版本中似乎並沒有將其作為一種優先選擇.
好在oracle提供了hint /*+ connect_by_filtering */來幫助我們干預執行計劃的選擇.我們給原語句加上hint:
-
WITH Q AS
-
(SELECT TRADING,
-
CALCULATED_DATE,
-
RANK() OVER (PARTITION BY CALCULATED_DATE ORDER BY MKT_FOR_NDA_ID NULLS LAST) RANK1
-
FROM OPERATING_CALENDAR
-
WHERE TRUNC(CALCULATED_DATE) BETWEEN to_date(\'20130704\',\'yyyymmdd\') AND to_date(\'40000101\',\'yyyymmdd\')
-
AND (MKT_FOR_NDA_ID = 455
-
OR GEO_FOR_NDA_ID IN
-
(SELECT NDA_ID
-
FROM GEOG
-
WHERE NDA_ID IN
-
(SELECT /*+ connect_by_filtering */GEO_IN_NDA_ID
-
FROM GEOG_STRUCTURE
-
START WITH GEO_WITH_NDA_ID = 858
-
CONNECT BY PRIOR GEO_IN_NDA_ID = GEO_WITH_NDA_ID
-
)
-
UNION
-
(SELECT NDA_ID FROM GEOG WHERE NDA_ID = 858
-
)
-
) )
-
)
-
SELECT T_CALCDT_REASON_TYPE(CALCULATED_DATE, NULL)
-
FROM Q
-
WHERE RANK1 = 1
- AND TRADING = \'H\';
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 32 (100)| |
|* 1 | VIEW | | 44 | 1100 | 32 (13)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK | | 44 | 704 | 32 (13)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL | OPERATING_CALENDAR | 872 | 13952 | 31 (10)| 00:00:01 |
| 5 | SORT UNIQUE | | 2 | 21 | 12 (42)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
|* 7 | HASH JOIN | | 1 | 17 | 9 (23)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | GEO_PK | 1 | 4 | 1 (0)| 00:00:01 |
|* 9 | VIEW | VW_NSO_1 | 2 | 26 | 7 (15)| 00:00:01 |
| 10 | SORT UNIQUE | | 2 | 34 | 7 (15)| 00:00:01 |
|* 11 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | GEOG_STRUCTURE | 1 | 8 | 2 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | GST_IND03 | 1 | | 1 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 21 | 4 (0)| 00:00:01 |
| 15 | CONNECT BY PUMP | | | | | |
| 16 | TABLE ACCESS BY INDEX ROWID | GEOG_STRUCTURE | 1 | 8 | 2 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | GST_IND03 | 1 | | 1 (0)| 00:00:01 |
|* 18 | FILTER | | | | | |
|* 19 | INDEX RANGE SCAN | GEO_PK | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
從新的執行計劃我們可以看到,oracle已經選擇了CONNECT BY WITH FILTERING執行計劃.
到這裡,與10g中的執行計劃比較會發現,新的執行計劃還是與原有的計劃有所不同,主要體現在第7步,原來採用的是nested loop連線,而這裡使用的是hash join. view VW_NSO_1的真實結果集實際上只有幾行, GEO_PK索引所在的GEOG表大約2000行,這種情況理論上nested loop會比較合適,實際測試也發現的確是採用nested loop的執行計劃更高效,那為什麼這裡會選擇hash join呢?
通過10053事件跟蹤解析過程,發現11g中新增的隱含引數'_optimizer_connect_by_elim_dups'和'_connect_by_use_union_all'較為可疑,查詢metalink和google也發現11g中這兩個引數的引入伴隨著許多的bug,所以有些專家建議在新的系統中disable掉這兩個引數.我對這兩個引數尚沒有更為深入的理解,但可以肯定的是這裡他們對connect by查詢的cost結果是有影響的,進而會干擾後面的連線方式的選擇.
所以這裡我嘗試著disable了他們:
alter system set "_optimizer_connect_by_elim_dups" = false;
alter system set "_connect_by_use_union_all" = "old_plan_mode";
然後,可以看到執行計劃迴歸了與10g中所採用的執行計劃.
注： 如果不想在整個資料庫更改以上隱含引數，可以使用opt_param hint在語句級修改。
總結：
前面的分析過程已經包括解決方案，就是使用connect_by_filtering以及修改隱含引數來影響語句執行計劃的選擇。由於該案例中我們知道什麼樣的執行計劃是適用的，所以也可以選擇hint, sql profile等常規手段去控制。該文的重點不是採用什麼方式去解決該問題，而是瞭解connect by查詢本身所存在的問題，以及connect by查詢獲取資料的可能方式，從而在遇到這類查詢時心中有數。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27243841/viewspace-1147046/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 效能優化查詢語句優化
- Sql語句本身的優化-定位慢查詢SQL優化
- 一個SQL語句的優化SQL優化
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- 請教一個mysql查詢語句！！MySql
- 資料庫設計與查詢語句的優化資料庫優化
- 【效能優化】查詢繫結變數的sql語句優化變數SQL
- 記一個實用的sql查詢語句SQL
- 一個經典的查詢及其SQL語句SQL
- MySql常用30種SQL查詢語句優化方法MySql優化
- mysql查詢語句優化工具MySql優化
- PostgreSQL 原始碼解讀（29）- 查詢語句#14（查詢優化-上拉子查詢）SQL原始碼優化
- PostgreSQL 原始碼解讀（25）- 查詢語句#10（查詢優化概覽）SQL原始碼優化
- PostgreSQL 原始碼解讀（17）- 查詢語句#2（查詢優化基礎）SQL原始碼優化
- ORACLE結構化查詢語句Oracle
- 一個insert插入語句很慢的優化優化
- 查詢語句（SELECT）的最佳化
- PostgreSQL 原始碼解讀（36）- 查詢語句#21（查詢優化-消除外連線）SQL原始碼優化
- PostgreSQL 原始碼解讀（37）- 查詢語句#22（查詢優化-grouping_plan...SQL原始碼優化
- 優化SQL查詢：如何寫出高效能SQL語句優化SQL
- 查詢前等待事件語句，進行效能分析和優化事件優化
- 幾個定位、查詢session的sql語句SessionSQL
- 淺談MySQL中優化sql語句查詢常用的30種方法MySql優化
- PostgreSQL 原始碼解讀（30）- 查詢語句#15（查詢優化-扁平化處理UNION ALL）SQL原始碼優化
- PostgreSQL 原始碼解讀（35）- 查詢語句#20（查詢優化-簡化Having和Grou...SQL原始碼優化
- Oracle分頁查詢語句（一）Oracle
- 一條sql語句的優化SQL優化
- Hql查詢語句
- mysql查詢語句MySql
- 從一個MysqL的例子來學習查詢語句(轉)MySql
- 一條查詢語句的執行流程
- oracle查詢語句查詢增加一列內容Oracle
- [Mysql 查詢語句]——查詢欄位MySql
- 45 個非常有用的 Oracle 查詢語句Oracle
- Oracle常用的查詢語句Oracle
- KunlunDB 查詢優化（一）優化
- PostgreSQL 原始碼解讀（26）- 查詢語句#11（查詢優化-上拉子連結#1）SQL原始碼優化
- PostgreSQL 原始碼解讀（27）- 查詢語句#12（查詢優化-上拉子連結#2）SQL原始碼優化