一般來說，SQL優(yōu)化是讓SQL運(yùn)行得更快，使SQL更快的方式有很多，比如提高索引的使用效率，或者并行查詢?？梢钥吹嚼锩娴墓剑?br>

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

當(dāng)然，這僅僅是從執(zhí)行時(shí)間上考慮，SQL優(yōu)化肯定不僅僅是執(zhí)行時(shí)間降低，應(yīng)該是資源使用與執(zhí)行時(shí)間降低之間尋求一種平衡，否則，盲目并行，可能提升不了效率，反而讓系統(tǒng)資源消耗殆盡。

最終來說，SQL優(yōu)化的本質(zhì)就是：1、縮短響應(yīng)時(shí)間；2、提升系統(tǒng)吞吐量；3、提升系統(tǒng)負(fù)載能力。要使用多種手段，在提升系統(tǒng)吞吐量和增加系統(tǒng)負(fù)載能力，提高單個(gè)SQL效率之間尋求一種平衡。就是要盡量減少一條SQL需要訪問的資源總量，比如走索引更好，那么不要使用全表掃描。

一條SQL的優(yōu)化路線圖如下所示：

具體操作步驟：

2.1 制定SQL優(yōu)化目標(biāo)

獲取待優(yōu)化SQL、制定優(yōu)化目標(biāo)：從AWR、ASH、ORA工具等主動(dòng)發(fā)現(xiàn)有問題的SQL、用戶報(bào)告有性能問題DBA介入等，通過對(duì)SQL的執(zhí)行情況進(jìn)行了解，先初步制定SQL的優(yōu)化目標(biāo)。

2.2 檢查執(zhí)行計(jì)劃

explain工具、sql*plus autotrace、dbms_xplan、10046、10053、awrsqrpt.sql等。 執(zhí)行計(jì)劃是我們進(jìn)行SQL優(yōu)化的核心內(nèi)容，無計(jì)劃，不優(yōu)化?？磮?zhí)行計(jì)劃有一些技巧，也有很多方式，各種方式之間是有區(qū)別的。

2.3 檢查統(tǒng)計(jì)信息

ORACLE使用DBMS_STATS包對(duì)統(tǒng)計(jì)信息進(jìn)行管理，涉及系統(tǒng)統(tǒng)計(jì)信息、表、列、索引、分區(qū)等對(duì)象的統(tǒng)計(jì)信息，統(tǒng)計(jì)信息是SQL能夠使用正確執(zhí)行計(jì)劃的保證。我們知道，ORACLE CBO優(yōu)化器是利用統(tǒng)計(jì)信息來判斷正確的執(zhí)行路徑，JOIN方式的，因此，準(zhǔn)確的統(tǒng)計(jì)信息是產(chǎn)生正確執(zhí)行計(jì)劃的首要條件。

可以從這個(gè)圖看出，一條SQL產(chǎn)生執(zhí)行計(jì)劃需要經(jīng)過哪些步驟，在我看來：1、正確的查詢轉(zhuǎn)換；2、準(zhǔn)確的統(tǒng)計(jì)信息，是產(chǎn)生正確執(zhí)行計(jì)劃的重要保證。當(dāng)然，還有BUG，或優(yōu)化器限制等也會(huì)導(dǎo)致SQL效率低下，無法產(chǎn)生正確的執(zhí)行計(jì)劃。

如圖所示：

2.4 檢查高效訪問結(jié)構(gòu)

重要的訪問結(jié)構(gòu)，諸如索引、分區(qū)等能夠快速提高SQL執(zhí)行效率。表存儲(chǔ)的數(shù)據(jù)本身，如碎片過多、數(shù)據(jù)傾斜嚴(yán)重、數(shù)據(jù)存儲(chǔ)離散度大，也會(huì)影響效率。

2.5 檢查影響優(yōu)化器的參數(shù)

optimizer_mode、optimizer_index_cost_adj、optimizer_dynamic sampling、_optimizer_mjc_enabled、_optimizer_cost_based_transformation、hash_join_enable等對(duì)SQL執(zhí)行計(jì)劃影響較大。比如有時(shí)候我們通過禁用_optimizer_mjc_enabled 參數(shù)，讓執(zhí)行計(jì)劃不要使用笛卡爾積來提升效率，因?yàn)檫@個(gè)參數(shù)開啟有很多問題，所以一般生產(chǎn)庫都要求禁用。

還有什么能夠影響執(zhí)行計(jì)劃呢？對(duì)，new features,每個(gè)版本的new features，引入的目的都是好的，但是實(shí)際使用中，可能觸發(fā)BUG。比如11g的ACS(自適應(yīng)游標(biāo)共享）、automatic serial direct path（自動(dòng)串行直接路徑讀）、extended statistics、SQL query result cache等。有的新特性會(huì)導(dǎo)致問題，所以需要謹(jǐn)慎使用。

比如11g adaptive cursor sharing,自適應(yīng)游標(biāo)共享，它的引入是為了解決使用綁定變量與數(shù)據(jù)傾斜值，要產(chǎn)生多樣性執(zhí)行計(jì)劃。因?yàn)榻壎ㄗ兞渴菫榱斯蚕韴?zhí)行計(jì)劃，但是數(shù)據(jù)傾斜了，有的值要求走索引，有的值要求走全表，這樣與使用綁定變量就產(chǎn)生了矛盾。以前是通過cursor_sharing=similar這樣的設(shè)置可以解決，但是有很多BUG，會(huì)產(chǎn)生version count過高的問題，或者我們對(duì)不同的值（如果值很少），可以寫多條SQL來解決，這都不是好的方案，11g acs引入就是為了解決這些問題，讓這些東西交給oracle來做。但是事與愿違，以后你們遇到執(zhí)行計(jì)劃一會(huì)變一下，有快有慢，首先可以檢查acs有沒有關(guān)閉。

alter system set “_optimizer_extended_cursor_sharing_rel”=’NONE';

2.6 SQL語句編寫問題

SQL語句結(jié)構(gòu)復(fù)雜、使用了不合理的語法，比如UNION代替UNION ALL都可能導(dǎo)致性能低下。 并不是說ORACLE優(yōu)化器很強(qiáng)大了，我們就可以隨便寫SQL了，那是不正確的。SQL是一門編程語言，它能夠執(zhí)行的快，是有一些普遍的規(guī)則的，遵循這種編程語言特性，簡化語句，才能寫出好的程序。SQL語句編寫出了問題，我們就需要改寫，就需要調(diào)整業(yè)務(wù)，改涉及等。

2.7 SQL優(yōu)化器限制導(dǎo)致的執(zhí)行計(jì)劃差

這個(gè)很重要，統(tǒng)計(jì)信息準(zhǔn)確，SQL也不復(fù)雜，索引也有。。。都滿足，為什么我的SQL還是差，那么得考慮優(yōu)化器限制因素了。這里說1點(diǎn)常見的執(zhí)行計(jì)劃限制，當(dāng)semi join與or連用的時(shí)候（也就是exists(subquery) or ...或者in (subquery) or...，如果執(zhí)行計(jì)劃中因?yàn)镺R導(dǎo)致有FILTER操作符，就得注意了，可能慢的因素就和OR有關(guān)。這時(shí)候我們得改寫SQL，當(dāng)然改寫為UNION或UNION ALL了。

OK，以上全部檢查完畢，我的系統(tǒng)還是很差，功能還是很慢，或者已經(jīng)無法從SQL本身進(jìn)行調(diào)整提升性能了，那咋辦？優(yōu)化設(shè)計(jì)，這是終極方法。有些東西不優(yōu)化設(shè)計(jì)是無法解決的，比如業(yè)務(wù)高峰期跑了一堆SQL，CPU已經(jīng)很吃緊，又不給增加，突然上線一個(gè)耗資源的業(yè)務(wù)，其他SQL已無法調(diào)整。那只能優(yōu)化設(shè)計(jì)，比如有些耗資源的業(yè)務(wù)可以換時(shí)間段執(zhí)行等。

以上幾點(diǎn)，是我們進(jìn)行優(yōu)化需要考慮的地方，可以逐步檢查。當(dāng)然，80%到90%的純SQL性能調(diào)整，我們通過建立索引，收集正確統(tǒng)計(jì)信息，改寫避免優(yōu)化器限制，已經(jīng)能夠解決了。

看第一個(gè)獲取待優(yōu)化的SQL.......如果主動(dòng)優(yōu)化，一般從AWR、ASH等里面找到性能差的SQL，然后優(yōu)化之。

看一個(gè)案例，占CPU 72%的SQL來自于同一模塊，第一行是存儲(chǔ)過程，通過下面綠色框住的SQL與第一行比較，主要通過EXECUTION，基本判斷下面的綠色框住的SQL就是那個(gè)存儲(chǔ)過程中的。也可以和業(yè)務(wù)確認(rèn)下，OK，這些SQL的執(zhí)行頻次很高，因?yàn)槭菭I銷業(yè)務(wù)，如果要優(yōu)化，就得搞定這些SQL。

這些SQL，單條SQL的buffer gets也就1000多點(diǎn)，效率還是很高的，但是因?yàn)閳?zhí)行的太過于頻繁，所以資源消耗極大，因此，得檢查下，能不能更優(yōu)呢？

以第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其實(shí)很簡單，一個(gè)查詢構(gòu)建的A表，一個(gè)TABLE函數(shù)構(gòu)建的B表關(guān)聯(lián)..... 不知道大家對(duì)這個(gè)TABLE函數(shù)熟悉不熟悉？也就是將一個(gè)集合轉(zhuǎn)成表，是PL/SQL里的東西

那個(gè)collection部分就是TABLE函數(shù)，下面的表走了全表掃描：

按步驟檢查，發(fā)現(xiàn)不了問題，但是知道，可能是因?yàn)镠ASH JOIN導(dǎo)致全表掃描的問題，是否走NESTED LOOPS+INDEX更好，很顯然，要檢查TABLE函數(shù)大概返回多少行。

經(jīng)過確認(rèn)，最多也就返回200-300行，最終結(jié)果集也是幾百行而已。

那么猜測(cè)，問題就在于TABLE函數(shù)，走了HASH JOIN，上面的執(zhí)行計(jì)劃，TABLE函數(shù)部分，ROWS為空。

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

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

只要你用了TABLE函數(shù)，就偏向于走HASH JOIN了

http://www./display.php?id=427 有興趣的可以看這個(gè)鏈接的內(nèi)容。

解決方式很多了，也就是要走NESTED LOOPS+index, 既然8168很大，那么我們就讓優(yōu)化器知道TABLE函數(shù)返回的行少點(diǎn)，才百行左右。

以下些都可以，當(dāng)然也可以使用hint:use_nl等

CARDINALITY hint (9i+) undocumented;

OPT_ESTIMATE hint (10g+) undocumented;

DYNAMIC_SAMPLING hint (11.1.0.7+);

Extensible Optimiser (10g+).

因?yàn)镾QL的SELECT部分只訪問B，全部來自于TABLE函數(shù)，所以改寫為子查詢就可以了，使用子查詢，自然distinct也就沒有必要了，因?yàn)槭莝emi join(半連接）。

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

原來的sql：

修改后的sql：

效率提升幾十倍：

一個(gè)占72%的應(yīng)用，我們提升幾十倍后，那對(duì)系統(tǒng)性能明顯是極好的。最終，在執(zhí)行次數(shù)增加50%的情況下，w4sd08pa主機(jī)CPU使用率由原來的高峰期平均47%的使用率降低為23%。

這個(gè)問題能夠解決有兩個(gè)方面：

1、猜測(cè)并測(cè)試優(yōu)化器的限制（table函數(shù)固定返回行8168)；2、實(shí)際返回的行200-300。兩者缺一不可。如果實(shí)際返回的行就是幾千上萬，那么，單純通過優(yōu)化SQL，也是無法取得良好效果的。

執(zhí)行計(jì)劃就是SQL調(diào)優(yōu)的核心，上面的SQL也是通過看到執(zhí)行計(jì)劃走HASH JOIN可能有問題出發(fā)的。

那么首先要搞定2個(gè)問題：

1、如何獲取我要的執(zhí)行計(jì)劃（準(zhǔn)確的計(jì)劃）；

2、怎么看懂并找出執(zhí)行計(jì)劃里的問題。

4.1 如何獲取準(zhǔn)確的執(zhí)行計(jì)劃

獲取SQL執(zhí)行計(jì)劃的方式：

EXPLAIN PLAN

估算

忽略綁定變量

非執(zhí)行

SQL_TRACE

真實(shí)計(jì)劃，需要用TKPROF工具解析

可以獲得綁定變量值

EVENT 10053

真實(shí)計(jì)劃

研究執(zhí)行計(jì)劃產(chǎn)生的原因

AUTOTRACE

內(nèi)部使用EXPLAIN PLAN

DBMS_XPLAN

dbms_xplan.display_cursor

dbms_xplan.display_awr

真實(shí)計(jì)劃

OTHERS

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

大家一般怎么獲取執(zhí)行計(jì)劃？我一般用的較多的是dbms_xplan.display_cursor，優(yōu)點(diǎn)很明顯：1、獲取的是真實(shí)執(zhí)行的計(jì)劃；2、多種參數(shù)。還可以獲取綁定變量的值方便驗(yàn)證。

10053是檢查優(yōu)化器行為的，實(shí)在搞不懂為什么走那個(gè)計(jì)劃可以看看，用得較少。

10046可以檢查一些等待事件的內(nèi)容，也可以獲取綁定變量，一般用得也比較少。

set autotrace traceonly或者explain,他們的執(zhí)行計(jì)劃是同一來源，記住，都來自plan_table，是估算的，可能不是真實(shí)執(zhí)行的計(jì)劃，可能是不準(zhǔn)的。

所以，你看得不對(duì)勁了，就得質(zhì)疑它的準(zhǔn)確性，autotrace traceonly的好處是可以看到一致性讀，物理讀，返回行等，這是真實(shí)的。因?yàn)榭梢杂靡恢滦宰x，物理讀來驗(yàn)證優(yōu)化效果

其他的，比如awrsqrpt等都可以獲取執(zhí)行計(jì)劃，不過我很少用，特別是plsq developer這種工具，F(xiàn)5看計(jì)劃，我?guī)缀跏遣挥玫?，他也是plan table里的估算計(jì)劃。如果很長，那無法分析。

建議大家看真實(shí)的計(jì)劃，說一點(diǎn)，我經(jīng)常通過alter session set statistics_level=all或者gather_plan_statistics hint，然后執(zhí)行sql,然后通過

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));來看實(shí)際執(zhí)行的信息

好處很明顯，能夠看到執(zhí)行計(jì)劃每步的E-ROWS（估算的行）,A-ROWS（真實(shí)的行）,STARTS,BUFFER GETS，A-TIME（真實(shí)的執(zhí)行時(shí)間）等信息。。。我們通過對(duì)比估算的與真實(shí)的差距，可以判斷哪些表統(tǒng)計(jì)信息可能有問題，執(zhí)行計(jì)劃是不是走錯(cuò)了，省的我們自己根據(jù)謂詞去計(jì)算這步導(dǎo)致返回多少行。

注意一點(diǎn)，如果一SQL執(zhí)行很長時(shí)間，通過上面的方式來看計(jì)劃，我們是可以終止的，比如執(zhí)行2小時(shí)執(zhí)行不玩的SQL，一般我沒有耐心，最多5分鐘，我就終止。終止完，通過display_cursor也是可以看出執(zhí)行信息的。

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

優(yōu)化器很多限制的，比如剛才的TABLE函數(shù)固定返回8168，或者算法限制.....很多不準(zhǔn)的，如果算法算出來的與真實(shí)差別很大，那可能就會(huì)導(dǎo)致問題。統(tǒng)計(jì)信息有時(shí)候也無法收集準(zhǔn)確的，比如直方圖，就有很多問題，所以12c的直方圖多了幾種....之前只有等高和等頻直方圖。

剛才的set statistics_level直接寫會(huì)輸出結(jié)果，我們可以讓他不輸出結(jié)果：

1、sql內(nèi)容放到文件中，前面加上set termout off （這樣可以對(duì)輸出結(jié)果不輸出）

2、然后display_cursor文件中

用這種東西看執(zhí)行計(jì)劃，有時(shí)候很方便找出問題,否則我們自己得手動(dòng)根據(jù)每個(gè)步驟對(duì)應(yīng)的謂詞，自己寫SQL去計(jì)算真實(shí)返回的行，然后再來比較，用這個(gè)，ORACLE全幫我們干好了。

4.2 看懂執(zhí)行計(jì)劃執(zhí)行順序

一般怎么看執(zhí)行計(jì)劃呢？

COPY到UE里去。

用光標(biāo)大法，找到入口，最先執(zhí)行的，光標(biāo)定位ID=0的，然后一直縮進(jìn)向下，如果被擋住了，那么這部分就是入口了。

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

找到入口后，反向光標(biāo)來，利用平行級(jí)別的最上最先執(zhí)行，最右最先執(zhí)行原則，來看父操作與子操作的關(guān)系，移動(dòng)光標(biāo)即可。

比如這里的第13步，我只需要定位光標(biāo)在PARTITION這個(gè)P前面，然后向上移動(dòng)，立馬就知道，它的驅(qū)動(dòng)表是ID=5的VIEW，因?yàn)樗麄兪菍?duì)齊的。

然后看看之間的JOIN關(guān)系是不是有問題，返回的行估算等。

執(zhí)行計(jì)劃最右最上最先執(zhí)行規(guī)則，有個(gè)例外，大家知道不？？就是通過以上規(guī)則，是不正確的。

（標(biāo)量子查詢）

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;

比如這個(gè)ID=2的在前面，但是它事實(shí)上是被ID=3的驅(qū)動(dòng)的，也就是被emp_a驅(qū)動(dòng)的，這違背了一般的執(zhí)行計(jì)劃順序規(guī)則，平時(shí)注意點(diǎn)就行了，標(biāo)量子查詢謂詞里會(huì)出現(xiàn)綁定變量，比如這里的：B1，因?yàn)槊看螏б粋€(gè)值去驅(qū)動(dòng)子查詢。

搞清楚執(zhí)行計(jì)劃怎么干，那么看執(zhí)行計(jì)劃看啥？

1、看JOIN的方式

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

3、看有沒有一些經(jīng)常影響性能的操作，比如FILTER

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

不要太過于關(guān)注COST，COST是估算的，大不一定就慢，小不一定就快……當(dāng)然比如COST很小，rows返回的都是很小的，很慢。那么，我們可能得考慮統(tǒng)計(jì)信息是不是過舊問題。

統(tǒng)計(jì)信息很重要，就說一個(gè)例子：

走了索引，COST很小，一切都很完美，但是AWR現(xiàn)實(shí)占80%的資源。一般啥情況？單純從SQL上看，也就是這執(zhí)行計(jì)劃估計(jì)不對(duì)，自己測(cè)一下，很慢。也就是COST很小，ROWS很小，走索引，很完美的計(jì)劃是錯(cuò)誤的，那么很顯然，基本就是統(tǒng)計(jì)信息導(dǎo)致的了。

實(shí)際第4步走sendtime索引，應(yīng)該返回1689393行，但是執(zhí)行計(jì)劃估算返回1行，統(tǒng)計(jì)信息不準(zhǔn)確，再次檢查統(tǒng)計(jì)信息收集日期是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

收集統(tǒng)計(jì)信息，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萬行，但是現(xiàn)有統(tǒng)計(jì)信息卻讓cbo認(rèn)為是1行，這差別也太大了。

method_opt=>'for all columns size repeat', 這里說下，更新統(tǒng)計(jì)信息，最好使用for all columns size repeat...

repeat的好處是啥，比如列有直方圖，會(huì)給你保留，列沒有統(tǒng)計(jì)信息會(huì)按照for all columns size 1收集。。。其他原來怎么收就怎么收。

你用一個(gè)for all columns size 1或size skewonly,或者不寫（auto)都可能改變?cè)薪y(tǒng)計(jì)信息的收集方式，都有可能影響SQL的執(zhí)行效率。

高效訪問結(jié)構(gòu)讓SQL更快，這個(gè)不說了，主要是建索引。如何建索引也是一個(gè)很復(fù)雜的問題，說一點(diǎn)，一般復(fù)合索引，等值查詢條件頻率高的，作為前導(dǎo)列較好。因?yàn)橹苯釉L問可能效率比>,<...等高，后者訪問了還需要過濾。

下面看下影響優(yōu)化器的參數(shù)導(dǎo)致的性能問題。

這是10g執(zhí)行計(jì)劃，一個(gè)視圖是UNION ALL做的，全部走索引：

但是11.2.0.4全表掃描了。

10g視圖有謂詞推薦，也就是查詢轉(zhuǎn)換里的一種OJPPD=OLD JOIN PUSH PREDICATE

升級(jí)到11.2.0.4，視圖里的10張表都變成FULL SCAN。

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

執(zhí)行時(shí)間從0.01s到4s,buffer gets從212到99w。

很顯然，我要檢查，統(tǒng)計(jì)信息沒有問題，然后怎么干？？看在11g里做優(yōu)化器降級(jí)如何。

在11.2.0.4中使用optimizer_features_enable分別測(cè)試10.2.0.4和11.2.0.3均可謂詞推入到視圖中走索引。那么問題就出現(xiàn)在11.2.0.4了，因?yàn)?1.2.0.3都是可以的。說明11.2.0.4對(duì)視圖謂詞推入算法有了改變。很多優(yōu)化器的東西，oracle都有參數(shù)控制的，除了參數(shù)，還有各補(bǔ)對(duì)應(yīng)的fix control。那么先檢查補(bǔ)丁相關(guān)的

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

查到了，各種開啟關(guān)閉，沒有用。最后看10053，分析10053，詳細(xì)參看是否是BUG導(dǎo)致，還是優(yōu)化器改進(jìn)問題，參數(shù)設(shè)置問題：

10053看到默認(rèn)參數(shù)被關(guān)了，檢查下，大概和查詢轉(zhuǎn)換的兩個(gè)參數(shù):

_optimizer_cost_based_transformation

_optimizer_squ_bottomup

都被關(guān)了，當(dāng)然10.2.0.4和11.2.0.3被關(guān)了也是可以的。

還看到基于CBO的查詢轉(zhuǎn)換失敗，因?yàn)閰?shù)被關(guān)了，OJPPD（10g那種方式）失效了……那當(dāng)然走不了，JPPD是11g的，也失效了。

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

當(dāng)然基于正常的理解，視圖謂詞推薦，ORACLE是必須支持的，也是不存在問題的，所以肯定有正規(guī)的解決方式。先看第2個(gè) BUG，按理說，這種常見的東西，特別是這SQL不算復(fù)雜，ORACLE應(yīng)該不會(huì)觸發(fā)BUG，當(dāng)然，查詢轉(zhuǎn)換是存在各種BUG的，11.2.0,4少了很多MOS中搜一下，比如這個(gè)JPPD，就有很多BUG，但是沒有看到11.2.0.4對(duì)應(yīng)的。

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

Predicate Move-Around (PM)

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

。。。

OJPPD: OJPPD bypassed: View semijoined to table.

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

通過這個(gè)判斷，10.2.0.4那種OJPPD，基于規(guī)則的查詢轉(zhuǎn)換不行了，也就是算法改變，因?yàn)閏ost_base_query_transformation參數(shù)關(guān)了，應(yīng)該走OJPPD的。現(xiàn)在JPPD也走不了，因?yàn)閰?shù)被關(guān)了，這個(gè)是基于成本的查詢轉(zhuǎn)換才可以。

所以，這是由于算法更新導(dǎo)致的問題，要求必須按照ORACLE官方建議，恢復(fù)對(duì)應(yīng)查詢轉(zhuǎn)換參數(shù)默認(rèn)值：在基于COST的查詢轉(zhuǎn)換部分，只能走JPPD（和OJPPD類似），ORACLE建議設(shè)置CBQT參數(shù)，基于COST查詢轉(zhuǎn)換更準(zhǔn)確。

開啟COST查詢轉(zhuǎn)換，初始化優(yōu)化器參數(shù) _optimizer_cost_based_transformation設(shè)為默認(rèn)值(linear)。CBQT參數(shù)有如下值：

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

另外通過測(cè)試得知，還需要設(shè)置_optimizer_squ_bottomup （enables unnesting of subquery in a bottom-up manner）

參數(shù)默認(rèn)值true.

這個(gè)問題，但是發(fā)了SR，老外也不知道，然后我發(fā)現(xiàn)這2個(gè)參數(shù)恢復(fù)默認(rèn)值可以，當(dāng)然首先cbqt參數(shù)我認(rèn)為肯定有關(guān)系，后面的squ_bottomup是測(cè)試出來的。。。后來告訴老外，老外也認(rèn)可算法改變導(dǎo)致的問題。所以核心參數(shù)的默認(rèn)值改變，是很危險(xiǎn)的，可能影響全局，如果這兩個(gè)參數(shù)不恢復(fù)，涉及數(shù)百條核心SQL就無法正常執(zhí)行，也就是系統(tǒng)不具有可用性了。

最后說一下，經(jīng)常碰到的一個(gè)優(yōu)化器缺陷：

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'

當(dāng)OR與semi join放在一起的時(shí)候，會(huì)觸發(fā)無法進(jìn)行subquery unnest的問題，也就是可能會(huì)產(chǎn)生FILTER,導(dǎo)致SQL非常緩慢，有的甚至幾天，幾十天也別想運(yùn)行結(jié)束了。

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

FILTER類似循環(huán)，在無法unnest子查詢中存在，類似標(biāo)量子查詢那種走法，謂詞里也有綁定變量的東西。

他們唯一的好處就是內(nèi)部構(gòu)建HASH 表，如果匹配的重復(fù)值特別多，那么探測(cè)次數(shù)少，效率好，但是大部分時(shí)候，重復(fù)值不多，那么就是災(zāi)難了

對(duì)于這種優(yōu)化器限制的，一般就是得改寫了，因?yàn)镾QL結(jié)構(gòu)決定無法走高效的執(zhí)行計(jì)劃。。。因?yàn)槲疫@里雖然走了所以，但是執(zhí)行次數(shù)太多，如果執(zhí)行次數(shù)少，到也無所謂。

改寫后的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了，為了避免有重復(fù)行，用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';

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

特別邏輯讀少了很多。后續(xù)優(yōu)化：

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

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

除了統(tǒng)計(jì)信息正確，良好的SQL結(jié)構(gòu)，能夠讓SQL正確進(jìn)行查詢轉(zhuǎn)換，正確的訪問結(jié)構(gòu)，如索引等……都是讓SQL高效執(zhí)行的前提條件。復(fù)雜！=低效，簡單！=高效。讓優(yōu)化器理解，并且有合適的訪問結(jié)構(gòu)支持，才是王道！

簡單的SQL不是快的保證，復(fù)雜的也不一定見得慢，高效的執(zhí)行計(jì)劃才是最重要的，索引優(yōu)化SQL，最重要的就是讓不好的執(zhí)行計(jì)劃變得好。

也就是從多個(gè)方面入手，最終達(dá)到我們的優(yōu)化目標(biāo)。