聽“俊”一席話，勝讀十年書。看了這篇由dba+社群聯合發起人丁俊大師（網名：dingjun123）分享的sql優化大作，其他oracle sql優化文章都不必再看了！
專家簡介
丁俊
網名：dingjun123
dba+社群聯合發起人
性能優化專家，oracle acea，itpub開發版資深版主。8年電信行業從業經驗，在某大型電信系統提供商工作7年，任資深工程師，從事過系統開發與維護、業務架構和資料分析、系統優化等工作。擅長基于oracle的系統優化，精通sql、pl/sql、java等。電子工業出版社終身榮譽作者，《劍破冰山-oracle開發藝術》副主編。
目錄
sql優化的本質
sql優化road map
2.1 制定sql優化目标
2.2 檢查執行計劃
2.3 檢查統計資訊
2.4 檢查高效通路結構
2.5 檢查影響優化器的參數
2.6 sql語句編寫問題
2.7 sql優化器限制導緻的執行計劃差
sql優化案例
sql執行計劃擷取
4.1 如何擷取準确的執行計劃
4.2 看懂執行計劃執行順序
一sql優化的本質
一般來說，sql優化是讓sql運作得更快，使sql更快的方式有很多，比如提高索引的使用效率，或者并行查詢。可以看到裡面的公式：
執行效率或者一般說的執行時間，是和完成一次sql所需要通路的資源總量（s）成正比以及機關時間内能夠通路的資源量（v）成反比，s越大，效率越低，v越大效率越高。 比如通過并行查詢，則可以提升機關時間内通路的資源量。
當然，這僅僅是從執行時間上考慮，sql優化肯定不僅僅是執行時間降低，應該是資源使用與執行時間降低之間尋求一種平衡，否則，盲目并行，可能提升不了效率，反而讓系統資源消耗殆盡。
最終來說，sql優化的本質就是：1、縮短響應時間；2、提升系統吞吐量；3、提升系統負載能力。要使用多種手段，在提升系統吞吐量和增加系統負載能力，提高單個sql效率之間尋求一種平衡。就是要盡量減少一條sql需要通路的資源總量，比如走索引更好，那麼不要使用全表掃描。
二sql優化road map
一條sql的優化路線圖如下所示：
具體操作步驟：
擷取待優化sql、制定優化目标：從awr、ash、ora工具等主動發現有問題的sql、使用者報告有性能問題dba介入等，通過對sql的執行情況進行了解，先初步制定sql的優化目标。
explain工具、sql*plus autotrace、dbms_xplan、10046、10053、awrsqrpt.sql等。 執行計劃是我們進行sql優化的核心内容，無計劃，不優化。看執行計劃有一些技巧，也有很多方式，各種方式之間是有差別的。
oracle使用dbms_stats包對統計資訊進行管理，涉及系統統計資訊、表、列、索引、分區等對象的統計資訊，統計資訊是sql能夠使用正确執行計劃的保證。我們知道，oracle cbo優化器是利用統計資訊來判斷正确的執行路徑，join方式的，是以，準确的統計資訊是産生正确執行計劃的首要條件。
可以從這個圖看出，一條sql産生執行計劃需要經過哪些步驟，在我看來：1、正确的查詢轉換；2、準确的統計資訊，是産生正确執行計劃的重要保證。當然，還有bug，或優化器限制等也會導緻sql效率低下，無法産生正确的執行計劃。
如圖所示：
重要的通路結構，諸如索引、分區等能夠快速提高sql執行效率。表存儲的資料本身，如碎片過多、資料傾斜嚴重、資料存儲離散度大，也會影響效率。
optimizer_mode、optimizer_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';
sql語句結構複雜、使用了不合理的文法，比如union代替union all都可能導緻性能低下。 并不是說oracle優化器很強大了，我們就可以随便寫sql了，那是不正确的。sql是一門程式設計語言，它能夠執行的快，是有一些普遍的規則的，遵循這種程式設計語言特性，簡化語句，才能寫出好的程式。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了
解決方式很多了，也就是要走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、怎麼看懂并找出執行計劃裡的問題。
擷取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：
很顯然，這裡的條件是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
where exists
(select 1
and exists (select 1
union
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，最重要的就是讓不好的執行計劃變得好。
也就是從多個方面入手，最終達到我們的優化目标。
<b></b>
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2015-11-18</b>