看到兄弟們最近分享甚是活躍，作為一個老司機(jī)那是必須要參與進(jìn)來的，如果兄弟們看完覺得還有點用，那將是我莫大的榮幸。

雖然寫過不少東西，但是一時半會又不知分享點啥方面內(nèi)容，想來想去，覺得在SQLTuning方面，怎么分析執(zhí)行計劃最重要，也知道有很多人對分析執(zhí)行計劃有一些誤區(qū)，特別是拿到超長執(zhí)行計劃又不知道如何入手，可能感覺到很迷茫。

其實ORACLE現(xiàn)在進(jìn)行SQLTuning的工具太多了，常見的通過setautotrace，獲取執(zhí)行計劃的有setstatistics_level=all或gather_plan_statisticshints，還有sqlmonitor、SQLHC、SQLT等一系列工具，做執(zhí)行計劃綁定有SQLPROFILE、SPM、SQLPATCH等，整個SQLTuning優(yōu)化家族的工具和方法已經(jīng)日趨完善和龐大。

所以該主題還是從執(zhí)行計劃這個最核心的開始，聊聊平時的分析誤區(qū)、正確的分析方法，希望對大家有一定的幫助。該主題分兩部：

• 上部：不正確執(zhí)行計劃分析方式；

• 下部：多維度解讀執(zhí)行計劃及實例解析；

對于做SQLTuning的人來說，掌握正確的執(zhí)行計劃分析方法，可以快速幫助我們定位問題的ROOTCAUSE，從而快速解決問題，一些不正確的執(zhí)行計劃分析方法，可能會誤導(dǎo)你，浪費您的寶貴時間。下面就列出典型的錯誤分析方式（或不完美的分析方法），希望能與大家產(chǎn)生一絲共鳴。

使用PL/SQLDeveloper的F5查看和分析執(zhí)行計劃

PL/SQLDev工具是ORACLE領(lǐng)域最流行的工具，開發(fā)人員大多使用它，做ORACLEDBA的很多人也喜歡用它，開發(fā)人員進(jìn)行SQL優(yōu)化喜歡用F5查看執(zhí)行計劃是很正常的，畢竟他們大多不夠?qū)I(yè)，然而很多DBA做優(yōu)化的時候也喜歡用它，這是很有問題的，為什么這么說呢？

可以這么說，使用F5去查看執(zhí)行計劃，來進(jìn)行SQLTuning的人，就是不夠?qū)I(yè)的（這句話一出，是不是得罪一大批人啊，哈哈，如果是，我對您說聲抱歉了），我提一個問題，你就明白了，如果用F5得到一個幾百行的執(zhí)行計劃，中間某一行是問題的關(guān)鍵，你怎么快速定位？

而且這玩意分析執(zhí)行計劃的問題很多，你知道它內(nèi)部其實是調(diào)用EXPLAINPLAN實現(xiàn)的，也就是得到的執(zhí)行計劃不一定準(zhǔn)確，比如有綁定變量的情況下，就是純估算的，從而對應(yīng)的指標(biāo)諸如cardinality、COST等都是不準(zhǔn)確的，你說用一個不準(zhǔn)確的玩意去找問題，那不是扯淡嗎？在實際做SQL優(yōu)化過程中，我連EXPLAINPLAN FOR都很少用，更別提用這種圖形化工具了。

紙上得來終覺淺，不來點實戰(zhàn)例子怎么行呢，下面就來個實戰(zhàn)的例子，讓大家明白，少用這些圖形化工具分析執(zhí)行計劃（當(dāng)然分析點簡單的執(zhí)行計劃還是有點用的，也不能一棒子打死啊）。

某日陽光明媚，吃完中午飯，正準(zhǔn)備小小休息一會的時候，一哥們在微信上問我一條SQL優(yōu)化問題，說的比較急，他百思不得其解，明明多帶帶測試的時候能夠走索引，為什么表一關(guān)聯(lián)，打死都不走索引，就算加了HINTS也不走索引，讓我?guī)兔纯?。下面就?gòu)造一個類似的SQL，如下所示：

select *

fromt1

leftjoin t2

ont1.name = t2.name

wheret1.name = 09DZ8H3XG8ORAH0HUZQI;

在這里我要說一下，他原來發(fā)的是用PL/SQLDeveloper發(fā)的執(zhí)行計劃，如下所示：

這里的t1,t2表的name都有索引，而且有很好的選擇性，那按理說，根據(jù)下面關(guān)聯(lián)條件：

ont1.name = t2.name

wheret1.name = 09DZ8H3XG8ORAH0HUZQI;

這里應(yīng)該謂詞傳遞給t2.name,轉(zhuǎn)為t2.name=’09DZ8H3XG8ORAH0HUZQI’,那么t2表應(yīng)該要走索引，然而卻沒有走索引，通過上面的執(zhí)行計劃可以看出一個問題：全表掃描那行的cardinality=31010,然而真實的結(jié)果是：

selectcount(*) from t2 where t2.name=09DZ8H3XG8ORAH0HUZQI;

COUNT(*)

----------

1

1row selected.

第一眼想到的是，這T2表統(tǒng)計信息不對啊，然而T2表的統(tǒng)計信息是剛收集過的：

selectnum_rows,sample_size,last_analyzed from dba_tab_statistics

wheretable_name=T2;

NUM_ROWSSAMPLE_SIZE LAST_ANALYZED

--------------------- -------------------

 3101013    31010132020-07-12 23:32:27

而且是100%收集啊，所以統(tǒng)計信息沒有問題。多帶帶測試的時候正常走索引，如下所示：

回頭一想，這里有個大坑啊，啥大坑呢？

像我平時做SQLTuning,我很少用PL/SQLDeveloper啊，這東西顯示的執(zhí)行計劃，一般看的不是很直觀，還有很多信息需要你自己添加，上面的執(zhí)行計劃就漏掉了最重要的謂詞信息，而且對于超長執(zhí)行計劃簡直是沒法分析啊。

我們做SQLTuning最好使用SQL*PLUS，文本格式，便于分析（后面會說正確執(zhí)行計劃分析方式就明白了為什么用文本格式），我又讓他把SETAUTOTRACE TRACEONLY 的執(zhí)行計劃弄出來給我看看，如下：

ExecutionPlan

----------------------------------------------------------

Planhash value: 2757452810

-----------------------------------------------------------------------------------------------

|Id  | Operation                            | Name   | Rows  | Bytes |Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT                     |        | 31010 |  2180K| 4757   (3)| 00:00:01 |

|  1 |  NESTED LOOPS OUTER                  |        | 31010 |  2180K| 4757   (3)| 00:00:01 |

|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     1 |    46 |    4   (0)| 00:00:01 |

|* 3 |    INDEX RANGE SCAN                  | IDX_T1 |     1 |       |    3   (0)| 00:00:01 |

|* 4 |   TABLE ACCESS FULL                  | T2     | 31010 |   787K| 4753   (3)| 00:00:01 |

-----------------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

---------------------------------------------------

3- access("T1"."NAME"=U09DZ8H3XG8ORAH0HUZQI)

 4- filter("T1"."NAME"=SYS_OP_C2C("T2"."NAME"(+))AND

            SYS_OP_C2C("T2"."NAME"(+))=U09DZ8H3XG8ORAH0HUZQI)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

16368 consistent gets

0 physical reads

0 redo size

831 bytes sent via SQL*Net to client

487 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

靠，瞬間發(fā)現(xiàn)了問題根源，還是SQL*PLUS親切啊，明顯在謂詞部分（PredicateInformation）發(fā)現(xiàn)了一個陌生的函數(shù)：SYS_OP_C2C，這TMD明顯是ORACLE內(nèi)部隱式類型轉(zhuǎn)換的函數(shù)嘛，然后CBO對于cardinality的計算使用函數(shù)的選擇性計算，所以和真實的cardinality不一樣，后面的字符串加了個U’,這明顯是NVARCHAR2啊，立馬讓他

DESC   T1和T2表：

desct1

Name                                                 Null?    Type

------------------------------------------------------------- ------------------------------------

ID                                                            NUMBER

NAME                                                        NVARCHAR2(100)

desct2

Name                                                 Null?    Type

------------------------------------------------------------- ------------------------------------

ID                                                            NUMBER

NAME                                                         VARCHAR2(100)

很明顯這是類型不一致，當(dāng)t1.name=t2.name,因為NVARCHAR2的優(yōu)先級高于VARCHAR2，所以把T2.name做了隱式類型轉(zhuǎn)換，所以嘛，有索引也用不上啦。以前的NVARCHAR2前面叫N’,現(xiàn)在改成U’,其實是一樣的東西，通過dump可以看出：

selectdump(09DZ8H3XG8ORAH0HUZQI) a,

dump(n09DZ8H3XG8ORAH0HUZQI)b,

dump(u09DZ8H3XG8ORAH0HUZQI)c,

dump(SYS_OP_C2C(09DZ8H3XG8ORAH0HUZQI))d

fromdual;
A

---------------------------------------------------------------------------------------------------

B

---------------------------------------------------------------------------------------------------

C

---------------------------------------------------------------------------------------------------

D

---------------------------------------------------------------------------------------------------

Typ=96Len=20: 48,57,68,90,56,72,51,88,71,56,79,82,65,72,48,72,85,90,81,73

Typ=96Len=40:0,48,0,57,0,68,0,90,0,56,0,72,0,51,0,88,0,71,0,56,0,79,0,82,0,65,0,72,0,48,0,72,0,85,

0,90,0,81,0,73

Typ=96Len=40:0,48,0,57,0,68,0,90,0,56,0,72,0,51,0,88,0,71,0,56,0,79,0,82,0,65,0,72,0,48,0,72,0,85,

0,90,0,81,0,73

Typ=96Len=40:0,48,0,57,0,68,0,90,0,56,0,72,0,51,0,88,0,71,0,56,0,79,0,82,0,65,0,72,0,48,0,72,0,85,

0,90,0,81,0,73

ORACLE通過SYS_OP_C2C函數(shù)將VARCHAR2轉(zhuǎn)成了NVARCHAR2，SO，找到了問題根源，那么就解決這個問題就簡單了，可以使用如下方式：

1.改語句，將t1.name加上to_char,這樣可以避免t2.name的類型轉(zhuǎn)換，因為t1.name已經(jīng)在where里有條件，這樣也不影響t1.name走索引：

select *

from t1

left join t2

on to_char(t1.name)= t2.name

where t1.name =09DZ8H3XG8ORAH0HUZQI;

執(zhí)行計劃如下：

ExecutionPlan

----------------------------------------------------------

Planhash value: 4205057668

---------------------------------------------------------------------------------------------

|Id  | Operation                            | Name   | Rows  | Bytes |Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT                     |        |     1 |    72 |    8   (0)| 00:00:01 |

|  1 |  NESTED LOOPS OUTER                  |        |     1 |    72 |    8   (0)| 00:00:01 |

|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     1 |    46 |    4   (0)| 00:00:01 |

|* 3 |    INDEX RANGE SCAN                  | IDX_T1 |     1 |       |    3   (0)| 00:00:01 |

|  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2     |     1 |    26 |    4   (0)| 00:00:01 |

|* 5 |    INDEX RANGE SCAN                  | IDX_T2 |     1 |       |    2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

---------------------------------------------------

3- access("T1"."NAME"=U09DZ8H3XG8ORAH0HUZQI)

5- access("T2"."NAME"(+)=SYS_OP_C2C("T1"."NAME"))

2.如果改不了語句，那么就對t2.name建立函數(shù)索引，如下所示走了函數(shù)索引：

create indexidx1_t2 on t2(SYS_OP_C2C(NAME));

執(zhí)行計劃如下：

ExecutionPlan

----------------------------------------------------------

Planhash value: 4208491579

---------------------------------------------------------------------------------------------

|Id  | Operation                             | Name    | Rows  | Bytes| Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT                      |         | 31010 | 2180K|  4645   (1)| 00:00:01 |

|  1 |  MERGE JOIN OUTER                     |         | 31010 | 2180K|  4645   (1)| 00:00:01 |

|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T1      |     1 |    46|     4   (0)| 00:00:01 |

|* 3 |    INDEX RANGE SCAN                   | IDX_T1  |     1 |      |     3   (0)| 00:00:01 |

|  4 |   BUFFER SORT                         |         | 31010 |  787K|  4641   (1)| 00:00:01 |

|  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2      | 31010 |  787K|  4641   (1)| 00:00:01 |

|* 6 |     INDEX RANGE SCAN                  | IDX1_T2 | 12404 |      |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

---------------------------------------------------

3- access("T1"."NAME"=U09DZ8H3XG8ORAH0HUZQI)

6- access("T2"."SYS_NC00003$"(+)=U09DZ8H3XG8ORAH0HUZQI)

3.改表設(shè)計，保持t1.name和t2.name的一致性，這個應(yīng)該在做設(shè)計的時候就考慮，這里不做闡述。

至此，問題解決，說句題外話，我們經(jīng)常會遇到類型轉(zhuǎn)換問題導(dǎo)致索引失效，要么是寫SQL時候自己加了TO_CHAR,TO_DATE,TO_NUMBER，要么因為類型不一致，ORACLE做了隱式類型轉(zhuǎn)換導(dǎo)致索引失效。要避免這種問題，還是要在表設(shè)計的時候，使用常用類型和一致的類型，避免使用一些不常用的比如NVARCHAR2,TIMESTAMP等，寫SQL時候遇到類型不一致的，要先測試好，避免上線后出現(xiàn)問題。

附測試語句：

droptable t1;

droptable t2;

createtable t1(id number,name nvarchar2(100));

createtable t2(id number,name varchar2(100));

createindex idx_t1 on t1(name);

createindex idx_t2 on t2(name);

begin

dbms_stats.gather_table_stats(ownname=> user,tabname => t1,no_invalidate => false);

dbms_stats.gather_table_stats(ownname=> user,tabname => t2,no_invalidate => false);

end;

/

insertinto t1

selectlevel,dbms_random.string(opt => x,len => 20)

fromdual

connectby level<100000;

insertinto t2

selectlevel,dbms_random.string(opt => x,len => 20)

fromdual

connectby level<1000000;

insertinto t2

select* from t1

whererownum<1000;

commit;

--問題語句

select*

fromt1

leftjoin t2

ont1.name = t2.name

wheret1.name = 09DZ8H3XG8ORAH0HUZQI;

其它不正確的執(zhí)行計劃分析方式還有很多，這里不再贅述，有興趣的可以自己分析下，比如：帶綁定變量的不獲取實際執(zhí)行的執(zhí)行計劃，過度關(guān)注COST等。

今天分享到此結(jié)束，我們下部見。