第一章 看了此文，Oracle SQL優化文章不必再看！

DBAplus社群 | 2015-11-17 23:44

目錄SQL優化的本質

SQL優化Road Map

2.1 制定SQL優化目標

2.2 檢查執行計劃

2.3 檢查統計資訊

2.4 檢查高效訪問結構

2.5 檢查影響優化器的引數

2.6 SQL語句編寫問題

2.7 SQL優??\x2F限制導致的執行計劃差

SQL優化案例

SQL執行計劃獲取

4.1 如何獲取準確的執行計劃

4.2 看懂執行計劃執行順序

一SQL優化的本質

一般來說，SQL優化是讓SQL執行得更快，使SQL更快的方式有很多，比如提高索引的使用效率，或者並行查詢。可以看到裡面的公式：

執行效率或者一般說的執行時間，是和完成一次SQL所需要訪問的資源總量（S）成正比以及單位時間內能夠訪問的資源量（V）成反比，S越大，效率越低，V越大效率越高。 比如通過並行查詢，則可以提升單位時間內訪問的資源量。

當然，這僅僅是從執行時間上考慮，SQL優化肯定不僅僅是執行時間降低，應該是資源使用與執行時間降低之間尋求一種平衡，否則，盲目並行，可能提升不了效率，反而讓系統資源消耗殆盡。

http\x3A?說，SQL優化的本質就是：1、縮短響應時間；2、提升系統吞吐量；3、提升系統負載能力。要使用多種手段，在提升系統吞吐量和增加系統負載能力，提高單個SQL效率之間尋求一種平衡。就是要儘量減少一條SQL需要訪問的資源總量，比如走索引更好，那麼不要使用全表掃描。

二SQL優化Road Map

一條SQL的優化路線圖如下所示：

具體操作步驟：

2.1 制定SQL優化目標

獲取待優化SQL、制定優化目標：從AWR、ASH、ORA工具等主動發現有問題的SQL、使用者報告有效能問題DBA介入等，通過對SQL的執行情況進行了解，先初步制定SQL的優化目標。

2.2 檢查執行計劃

explain工具、sql*plus autotrace、dbms_xplan、10046、10053、awrsqrpt.sql等。 執行計劃是我們進行SQL優化的核心內容，無計劃，不優化。看執行計劃有一些技巧，也有很多方式，各種方式之間是有區別的。

2.3 檢查統計資訊

ORACLE使用DBMS_STATS包對統計資訊進行管理，涉及系統統計資訊、表、列、索引、分割槽等物件的統計資訊，統計資訊是SQL能夠使用正確執行計劃的保證。我們知道，ORACLE CBO優化器是利用統計資訊來判斷正確的執行路徑，JOIN方式的，因此，準確的統計資訊是產生正確執行計劃的首要條件。

可以從這個圖看出，一條SQL產生執行計劃需要經過哪些步驟，在我看來：1、正確的查詢轉換；2、準確的統計資訊，是產生正確執行計劃的重要保證。當然，還有BUG，或優化器限制等也會導致SQL效率低下，無法產生正確的執行計劃。

如圖所示：

2.4 檢??/效訪問結構

重要的訪問結構，諸如索引、分割槽等能夠快速提高SQL執行效率。表儲存的資料本身，如碎片過多、資料傾斜嚴重、資料儲存離散度大，也會影響效率。

2.5 檢查影響優化器的引數

2016-02-21 23:17izer_index_cost_adj、optimizer_dynamic sampling、_optimizer_mjc_enabled、_optimizer_cost_based_transformation、hash_join_enable等對SQL執行計劃影響較大。比如有時候我們通過禁用_optimizer_mjc_enabled 引數，讓執行計劃不要使用笛卡爾積來提升效率，因為這個引數開啟有很多問題，所以一般生產庫都要求禁用。

還有什麼能夠影響執行計劃呢？對，new features,每個版本的new features，引入的目的都是好的，但是實際使用中，可能觸發BUG。比如11g的ACS(自適應遊標共享）、automatic serial direct path（自動序列直接路徑讀）、extended statistics、SQL query result cache等。有的新特性會導致問題，所以需要謹慎使用。

比如11g adaptive cursor sharing,自適應遊標共享，它的引入是為了解決使用繫結變數與資料傾斜值，要產生多樣性執行計劃。因為繫結變數是為了共享執行計劃，但是資料傾斜了，有的值要求走索引，有的值要求走全表，這樣與使用繫結變數就產生了矛盾。以前是通過cursor_sharing=similar這樣的設定可以解決，但是有很多BUG，會產生version count過高的問題，或者我們對不同的值（如果值很少），可以寫多條SQL來解決，這都不是好的方案，11g acs引入就是為了解決這些問題，讓這些東西交給oracle來做。但是事與願違，以後你們遇到執行計劃一會變一下，有快有慢，首先可以檢查acs有沒有關閉。

alter system set “_optimizer_extended_cursor_sharing_rel”=’NONE';

2.6 SQL語句編寫問題

SQL語句結構複雜、使用了不合理的語法，比如UNION代替UNION ALL都可能導致效能低下。 並不是說ORACLE優化器很強大了，我們就可以隨便寫SQL了，那是不正確的。SQL是一門程式語言，它能夠執行的快，是有一些普遍的規則的，遵循這種程式語言特性，簡化語句，才能寫出好的程式。SQL語句編寫出了問題，我們就需要改寫，就需要調整業務，改涉及等。

2.7 SQL優化器限制導致的執行計劃差

這個很重要，統計資訊準確，SQL也不復雜，索引也有。。。都滿足，為什麼我的SQL還是差，那麼得考慮優化器限制因素了。這裡說1點常見的執行計劃限制，當semi join與or連用的時候（也就是exists(subquery) or ...或者in (subquery) or...，如果執行計劃中因為OR導致有FILTER操作符，就得注意了，可能慢的因素就和OR有關。這時候我們得改寫SQL，當然改寫為UNION或UNION ALL了。

OK，以上全部檢查完畢，我的系統還是很差，功能還是很慢，或者已經無法從SQL本身進行調整提升效能了，那咋辦？優化設計，這是終極方法。有些東西不優化設計是無法解決的，比如業務高峰期跑了一堆SQL，CPU已經很吃緊，又不給增加，突然上線一個耗資源的業務，其他SQL已無法調整。那隻能優化設計，比如有些耗資源的業務可以換時間段執行等。

以上幾點，是我們進行優化需要考慮的地方，可以逐步檢查。當然，80%到90%的純SQL效能調整，我們通過建立索引，收集正確統計資訊，改寫避免優化器限制，已經能夠解決了。

三SQL優化案例

看第一個獲取待優化的SQL.......如果主動優化，一般從AWR、ASH等裡面找到效能差的SQL，然後優化之。

看一個案例，佔CPU 72%的SQL來自於同一模組，第一行是儲存過程，通過下面綠色框住的SQL與第一行比較，主要通過EXECUTION，基本判斷下面的綠色框住的SQL就是那個儲存過程中的。也可以和業務確認下，OK，這些SQL的執行頻次很高，因為是營銷業務，如果要優化，就得搞定這些SQL。

這些SQL，單條SQL的buffer gets也就1000多點，效率還是很高的，但是因為執行的太過於頻繁，所以資源消耗極大，因此，得檢查下，能不能更優呢？

以第1條SQL：58q183atbusat為例：

SELECT B.ACT_ID,

B.ACT_NAME,

B.TASK_ID,

B.MKT_DICTION,

B.CUST_GROUP_ID,

NVL(B.ATTEST_FLAG, 'N'),

NVL(B.DOUWIN_FLAG, 'N'),

B.CHN_DESC,

NVL(B.SIGN_FLAG, 'N'),

B.MAX_EXECUTE_NUM

FROM (SELECT DISTINCT (ACT_ID)

FROM MK_RULECHN_REL

WHERE CHN_STATUS = '04'

AND CHN_TYPE = :B1) A,

TABLE(CAST(:B2 AS TYPE_MK_ACTIONINFO_TABLE)) B

WHERE A.ACT_ID = B.ACT_ID

SQL其實很簡單，一個查詢構建的A表，一個TABLE函式構建的B表關聯..... 不知道大家對這個TABLE函式熟悉不熟悉？也就是將一個集合轉成表，是PL/SQL裡的東西

那個collection部分就是TABLE函式，下面的表走了全表掃描：

按步驟檢查，發現不了問題，但是知道，可能是因為HASH JOIN導致全表掃描的問題，是否走NESTED LOOPS+INDEX更好，很顯然，要檢查TABLE函式大概返回多少行。

經過確認，最多也就返回200-300行，最終結果集也是幾百行而已。

那麼猜測，問題就在於TABLE函式，走了HASH JOIN，上面的執行計劃，TABLE函式部分，ROWS為空。

來單獨檢查一把：返回8168行，返回8000多行，足以導致走HASH JOIN了....而事實，我們至多返回200-300行：

所以每個步驟返回的行，是JOIN方式選擇的重要因素，可以谷歌一把，TABLE函式返回8168就是個固定值，block_size=8K的時候就是這麼大，可以說，這是ORACLE的一個限制。

只要你用了TABLE函式，就偏向於走HASH JOIN了

http://www.oracle-developer.net/display.php?id=427 有興趣的可以看這個連結的內容。

解決方式很多了，也就是要走NESTED LOOPS+index, 既然8168很大，那麼我們就讓優化器知道TABLE函式返回的行少點，才百行左右。

以下些都可以，當然也可以使用hint:use_nl等

CARDINALITY hint (9i+) undocumented;

OPT_ESTIMATE hint (10g+) undocumented;

DYNAMIC_SAMPLING hint (11.1.0.7+);

Extensible Optimiser (10g+).

因為SQL的SELECT部分只訪問B，全部來自於TABLE函式，所以改寫為子查詢就可以了，使用子查詢，自然distinct也就沒有必要了，因為是semi join(半連線）。

最終改寫使用cardinality hint讓優化器知道B返回的行只有100行，你給我走NESTED LOOPS+INDEX，然後解決。

原來的sql：

修改後的sql：

效率提升幾十倍：

一個佔72%的應用，我們提升幾十倍後，那對系統效能明顯是極好的。最終，在執行次數增加50%的情況下，w4sd08pa主機CPU使用率由原來的高峰期平均47%的使用率降低為23%。

這個問題能夠解決有兩個方面：

1、猜測並測試優化器的限制（table函式固定返回行8168)；2、實際返回的行200-300。兩者缺一不可。如果實際返回的行就是幾千上萬，那麼，單純通過優化SQL，也是無法取得良好效果的。

掃描文末二維碼，關注DBA+社群微信公眾號（dbaplus），可下載DBA+社群技術沙龍、OOW大會、2015GOPS、DCon2015等技術盛典PPT。

四SQL執行計劃獲取

執行計劃就是SQL調優的核心，上面的SQL也是通過看到執行計劃走HASH JOIN可能有問題出發的。

那麼首先要搞定2個問題：

1、如何獲取我要的執行計劃（準確的計劃）；

2、怎麼看懂並找出執行計劃裡的問題。

4.1 如何獲取準確的執行計劃

獲取SQL執行計劃的方式：

EXPLAIN PLAN

估算

忽略繫結變數

非執行

SQL_TRACE

真實計劃，需要用TKPROF工具解析

可以獲得繫結變數值

EVENT 10053

真實計劃

研究執行計劃產生的原因

AUTOTRACE

內部使用EXPLAIN PLAN

DBMS_XPLAN

dbms_xplan.display_cursor

dbms_xplan.display_awr

真實計劃

OTHERS

如awrsqrpt、sqlt、pl/sql、sql developer、toad等

大家一般怎麼獲取執行計劃？我一般用的較多的是dbms_xplan.display_cursor，優點很明顯：1、獲取的是真實執行的計劃；2、多種引數。還可以獲取繫結變數的值方便驗證。

10053是檢查優化器行為的，實在搞不懂為什麼走那個計劃可以看看，用得較少。

10046可以檢查一些等待事件的內容，也可以獲取繫結變數，一般用得也比較少。

set autotrace traceonly或者explain,他們的執行計劃是同一來源，記住，都來自plan_table，是估算的，可能不是真實執行的計劃，可能是不準的。

所以，你看得不對勁了，就得質疑它的準確性，autotrace traceonly的好處是可以看到一致性讀，物理讀，返回行等，這是真實的。因為可以用一致性讀，物理讀來驗證優化效果

其他的，比如awrsqrpt等都可以獲取執行計劃，不過我很少用，特別是plsq developer這種工具，F5看計劃，我幾乎是不用的，他也是plan table裡的估算計劃。如果很長，那無法分析。

建議大家看真實的計劃，說一點，我經常通過alter session set statistics_level=all或者gather_plan_statistics hint，然後執行sql,然後通過

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));來看實際執行的資訊

好處很明顯，能夠看到執行計劃每步的E-ROWS（估算的行）,A-ROWS（真實的行）,STARTS,BUFFER GETS，A-TIME（真實的執行時間）等資訊。。。我們通過對比估算的與真實的差距，可以判斷哪些表統計資訊可能有問題，執行計劃是不是走錯了，省的我們自己根據謂詞去計算這步導致返回多少行。

注意一點，如果一SQL執行很長時間，通過上面的方式來看計劃，我們是可以終止的，比如執行2小時執行不玩的SQL，一般我沒有耐心，最多5分鐘，我就終止。終止完，通過display_cursor也是可以看出執行資訊的。

比如某個步驟執行100萬次，我這條SQL才能執行完，要3小時才可以，我5分鐘執行了100次，我終止了SQL我要看的就是一個比例情況，可以通過這個比例來判斷，哪個步驟耗的時間最長，哪裡大概有問題，然後解決。

優化器很多限制的，比如剛才的TABLE函式固定返回8168，或者演算法限制.....很多不準的，如果演算法算出來的與真實差別很大，那可能就會導致問題。統計資訊有時候也無法收集準確的，比如直方圖，就有很多問題，所以12c的直方圖多了幾種....之前只有等高和等頻直方圖。

剛才的set statistics_level直接寫會輸出結果，我們可以讓他不輸出結果：

1、sql內容放到檔案中，前面加上set termout off （這樣可以對輸出結果不輸出）

2、然後display_cursor檔案中

用這種東西看執行計劃，有時候很方便找出問題,否則我們自己得手動根據每個步驟對應的謂詞，自己寫SQL去計算真實返回的行，然後再來比較，用這個，ORACLE全幫我們幹好了。

4.2 看懂執行計劃執行順序

一般怎麼看執行計劃呢？

COPY到UE裡去。

用游標大法，找到入口，最先執行的，游標定位ID=0的，然後一直縮排向下，如果被擋住了，那麼這部分就是入口了。

比如ID=10的繼續索引，就被ID=11的擋住了，所以第10步就是入口。

找到入口後，反向游標來，利用平行級別的最上最先執行，最右最先執行原則，來看父操作與子操作的關係，移動游標即可。

比如這裡的第13步，我只需要定位游標在PARTITION這個P前面，然後向上移動，立馬就知道，它的驅動表是ID=5的VIEW，因為他們是對齊的。

然後看看之間的JOIN關係是不是有問題，返回的行估算等。

執行計劃最右最上最先執行規則，有個例外，大家知道不？？就是通過以上規則，是不正確的。

（標量子查詢）

SELECT a.employee_id,

a.department_id,

(SELECT COUNT(*) FROM emp_b b

WHERE a.department_id=b.department_id

) cnt

FROM emp_a a;

比如這個ID=2的在前面，但是它事實上是被ID=3的驅動的，也就是被emp_a驅動的，這違背了一般的執行計劃順序規則，平時注意點就行了，標量子查詢謂詞裡會出現繫結變數，比如這裡的：B1，因為每次帶一個值去驅動子查詢。

搞清楚執行計劃怎麼幹，那麼看執行計劃看啥？

1、看JOIN的方式

2、看錶的訪問方式，走全表，走索引

3、看有沒有一些經常影響效能的操作，比如FILTER

4、看cardinality(rows)與真實的差距

不要太過於關注COST，COST是估算的，大不一定就慢，小不一定就快……當然比如COST很小，rows返回的都是很小的，很慢。那麼，我們可能得考慮統計資訊是不是過舊問題。

統計資訊很重要，就說一個例子：

走了索引，COST很小，一切都很完美，但是AWR現實佔80%的資源。一般啥情況？單純從SQL上看，也就是這執行計劃估計不對，自己測一下，很慢。也就是COST很小，ROWS很小，走索引，很完美的計劃是錯誤的，那麼很顯然，基本就是統計資訊導致的了。

實際第4步走sendtime索引，應該返回1689393行，但是執行計劃估算返回1行，統計資訊不準確，再次檢查統計資訊收集日期是5月前的。

SQL> SELECT COUNT(1) FROM MSP.T_MS_MEDIA_TASK WHERE SENDTIME >=TRUNC(SYSDATE,'dd') AND MONTHDAY = TO_CHAR(SYSDATE,'mmdd') ;

? COUNT(1)

----------

? ?1689393

收集統計資訊，for all columns size repeat 保持原有直方圖資訊

?exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'MSP',tabname=>'T_MS_MEDIA_TASK',estimate_percent=>10,method_opt=>'for all columns size repeat', no_invalidate=>false,cascade=>true,degree => 10);

返回168萬行，但是現有統計資訊卻讓cbo認為是1行，這差別也太大了。

method_opt=>'for all columns size repeat', 這裡說下，更新統計資訊，最好使用for all columns size repeat...

repeat的好處是啥，比如列有直方圖，會給你保留，列沒有統計資訊會按照for all columns size 1收集。。。其他原來怎麼收就怎麼收。

你用一個for all columns size 1或size skewonly,或者不寫（auto)都可能改變原有統計資訊的收集方式，都有可能影響SQL的執行效率。

高效訪問結構讓SQL更快，這個不說了，主要是建索引。如何建索引也是一個很複雜的問題，說一點，一般複合索引，等值查詢條件頻率高的，作為前導列較好。因為直接訪問可能效率比>,<...等高，後者訪問了還需要過濾。

下面看下影響優化器的引數導致的效能問題。

這是10g執行計劃，一個檢視是UNION ALL做的，全部走索引：

但是11.2.0.4全表掃描了。

10g檢視有謂詞推薦，也就是查詢轉換裡的一種OJPPD=OLD JOIN PUSH PREDICATE

升級到11.2.0.4，檢視裡的10張表都變成FULL SCAN。

連線謂詞(A.“PAYIO_SN”=“B”.“WRTOFF_SN”)未推入到檢視中。

執行時間從0.01s到4s,buffer gets從212到99w。

很顯然，我要檢查，統計資訊沒有問題，然後怎麼幹？？看在11g裡做優化器降級如何。

在11.2.0.4中使用optimizer_features_enable分別測試10.2.0.4和11.2.0.3均可謂詞推入到檢視中走索引。那麼問題就出現在11.2.0.4了，因為11.2.0.3都是可以的。說明11.2.0.4對檢視謂詞推入演算法有了改變。很多優化器的東西，oracle都有引數控制的，除了引數，還有各補對應的fix control。那麼先檢查補丁相關的

from v$system_fix_control WHERE sql_feature LIKE ‘%JPPD%’

查到了，各種開啟關閉，沒有用。最後看10053，分析10053，詳細參看是否是BUG導致，還是優化器改進問題，引數設定問題：

10053看到預設引數被關了，檢查下，大概和查詢轉換的兩個引數:

_optimizer_cost_based_transformation

_optimizer_squ_bottomup

都被關了，當然10.2.0.4和11.2.0.3被關了也是可以的。

還看到基於CBO的查詢轉換失敗，因為引數被關了，OJPPD（10g那種方式）失效了……那當然走不了，JPPD是11g的，也失效了。

基本知道執行計劃如何看，關注哪些就很有用了，不要太關注啥COST前面講了11.2.0.3都可以，到11.2.0.4不行了，那可能有2種原因：1、演算法改了；2、BUG。

當然基於正常的理解，檢視謂詞推薦，ORACLE是必須支援的，也是不存在問題的，所以肯定有正規的解決方式。先看第2個 BUG，按理說，這種常見的東西，特別是這SQL不算複雜，ORACLE應該不會觸發BUG，當然，查詢轉換是存在各種BUG的，11.2.0,4少了很多MOS中搜一下，比如這個JPPD，就有很多BUG，但是沒有看到11.2.0.4對應的。

**************************

Predicate Move-Around (PM)

**************************

。。。

OJPPD: OJPPD bypassed: View semijoined to table.

JPPD: JPPD bypassed: View not on right-side of outer-join.

通過這個判斷，10.2.0.4那種OJPPD，基於規則的查詢轉換不行了，也就是演算法改變，因為cost_base_query_transformation引數關了，應該走OJPPD的。現在JPPD也走不了，因為引數被關了，這個是基於成本的查詢轉換才可以。

所以，這是由於演算法更新導致的問題，要求必須按照ORACLE官方建議，恢復對應查詢轉換引數預設值：在基於COST的查詢轉換部分，只能走JPPD（和OJPPD類似），ORACLE建議設定CBQT引數，基於COST查詢轉換更準確。

開啟COST查詢轉換，初始化優化器引數 _optimizer_cost_based_transformation設為預設值(linear)。CBQT引數有如下值：

"exhaustive", "iterative", "linear", "on", "off"。

另外通過測試得知，還需要設定_optimizer_squ_bottomup （enables unnesting of subquery in a bottom-up manner）

引數預設值true.

這個問題，但是發了SR，老外也不知道，然後我發現這2個引數恢復預設值可以，當然首先cbqt引數我認為肯定有關係，後面的squ_bottomup是測試出來的。。。後來告訴老外，老外也認可演算法改變導致的問題。所以核心引數的預設值改變，是很危險的，可能影響全域性，如果這兩個引數不恢復，涉及數百條核心SQL就無法正常執行，也就是系統不具有可用性了。

最後說一下，經常碰到的一個優化器缺陷：

SELECT ELEMENT_TYPEA,

ELEMENT_IDA,

ELEMENT_TYPEB,

ELEMENT_IDB,

RELATION_TYPE,

EFF_RULE_ID,

EXP_RULE_ID,

CTRL_CODE,

EFF_DATE,

EXP_DATE,

GROUP_ID,

BASE_TIME_ TYPE,

POWER_RIGHT,

POSITIVE_TYPE,

BOTHWAY_FLAG

FROM DBPRODADM.pd_prc_rel a

WHERE EXISTS (SELECT 1

FROM DBPRODADM.pd_prc_dict b

WHERE a.element_ida = b.prod_prcid

AND b.prod_prc_type = '1')

AND a.exp_date > SYSDATE

AND (EXISTS (SELECT 1

FROM DBPRODADM.pd_prc_dict c

WHERE a.element_idb = c.prod_prcid

AND c.prod_prc_type = '1')

OR a.element_idb = 'X')

AND a.relation_type = '10'

當OR與semi join放在一起的時候，會觸發無法進行subquery unnest的問題，也就是可能會產生FILTER,導致SQL非常緩慢，有的甚至幾天，幾十天也別想執行結束了。

第5、6步執行92萬多次，那肯定慢了……問題就是有個FILTER……

FILTER類似迴圈，在無法unnest子查詢中存在，類似標量子查詢那種走法，謂詞裡也有繫結變數的東西。

他們唯一的好處就是內部構建HASH 表，如果匹配的重複值特別多，那麼探測次數少，效率好，但是大部分時候，重複值不多，那麼就是災難了

對於這種優化器限制的，一般就是得改寫了，因為SQL結構決定無法走高效的執行計劃。。。因為我這裡雖然走了所以，但是執行次數太多，如果執行次數少，到也無所謂。

改寫後的sql：

SELECT ELEMENT_TYPEA,

ELEMENT_IDA,

ELEMENT_TYPEB,

ELEMENT_IDB,

RELATION_TYPE,

EFF_RULE_ID,

EXP_RULE_ID,

CTRL_CODE,

EFF_DATE,

EXP_DATE,

GROUP_ID,

BASE_TIME_ TYPE,

POWER_RIGHT,

POSITIVE_TYPE,

BOTHWAY_FLAG

FROM DBPRODADM.pd_prc_rel a

WHERE EXISTS (SELECT 1

FROM DBPRODADM.pd_prc_dict b

WHERE a.element_ida = b.prod_prcid

AND b.prod_prc_type = '1')

AND a.exp_date > SYSDATE

AND (EXISTS (SELECT 1

FROM DBPRODADM.pd_prc_dict c

WHERE a.element_idb = c.prod_prcid

AND c.prod_prc_type = '1')

OR a.element_idb = 'X')

AND a.relation_type = '10'

很顯然，這裡的條件是exists or ...那麼改寫得用UNION或UNION ALL了，為了避免有重複行，用UNION

select ELEMENT_TYPEA,ELEMENT_IDA,ELEMENT_TYPEB,ELEMENT_IDB,RELATION_TYPE

,EFF_RULE_ID,EXP_RULE_ID,CTRL_CODE,EFF_DATE,EXP_DATE,GROUP_ID,BASE_TIME_TYPE,

POWER_RIGHT,POSITIVE_TYPE,BOTHWAY_FLAG

from DBPRODADM.pd_prc_rel a

where exists

(select 1

from DBPRODADM.pd_prc_dict b

where a.element_ida = b.prod_prcid

and b.prod_prc_type = '1')

and a.exp_date > sysdate

and exists (select 1

from DBPRODADM.pd_prc_dict c

where a.element_idb = c.prod_prcid

and c.prod_prc_type = '1')

and a.relation_type = '10'

union

select ELEMENT_TYPEA,ELEMENT_IDA,ELEMENT_TYPEB,ELEMENT_IDB,RELATION_TYPE

,EFF_RULE_ID,EXP_RULE_ID,CTRL_CODE,EFF_DATE,EXP_DATE,GROUP_ID,BASE_TIME_TYPE,

POWER_RIGHT,POSITIVE_TYPE,BOTHWAY_FLAG

from DBPRODADM.pd_prc_rel a

where exists

(select 1

from DBPRODADM.pd_prc_dict b

where a.element_ida = b.prod_prcid

and b.prod_prc_type = '1')

and a.exp_date > sysdate

and a.element_idb = 'X'

and a.relation_type = '10';

兩個分支都走HASH JOIN，starts全部為1，雖然全部是全表掃描，但是執行效率提升很明顯，執行時間從12s到7s,gets從222w到4.5w之後，是否還有優化空間？

特別邏輯讀少了很多。後續優化：

1)改寫使用了UNION,是否能改成UNION ALL避免排序？

2)這麼多全表掃描，是否能夠讓一些可以走索引？當然，這些是可以做到的，但是不是主要工作了。這個案例告訴我們，優化器是有很多限制的，不是萬能的。

除了統計資訊正確，良好的SQL結構，能夠讓SQL正確進行查詢轉換，正確的訪問結構，如索引等……都是讓SQL高效執行的前提條件。複雜！=低效，簡單！=高效。讓優化器理解，並且有合適的訪問結構支援，才是王道！

簡單的SQL不是快的保證，複雜的也不一定見得慢，高效的執行計劃才是最重要的，索引優化SQL，最重要的就是讓不好的執行計劃變得好。

也就是從多個方面入手，最終達到我們的優化目標。

 

About Me

....................................................................................................................................................

本文來自於微信公眾號轉載文章，若有侵權，請聯絡小麥苗及時刪除

ITPUB BLOG：http://blog.itpub.net/26736162

QQ：642808185 若加QQ請註明您所正在讀的文章標題

【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】

....................................................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2080721/，如需轉載，請註明出處，否則將追究法律責任。