通過(guò)執(zhí)行計(jì)劃優(yōu)化SQL的正確姿勢(shì)（上）

IT那活兒發(fā)布于2023-01-11 13:20 / 770人閱讀

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

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

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

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

上部：不正確執(zhí)行計(jì)劃分析方式；
下部：多維度解讀執(zhí)行計(jì)劃及實(shí)例解析；

不正確執(zhí)行計(jì)劃分析方式

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

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

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

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

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

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

某日陽(yáng)光明媚，吃完中午飯，正準(zhǔn)備小小休息一會(huì)的時(shí)候，一哥們?cè)谖⑿派蠁?wèn)我一條SQL優(yōu)化問(wèn)題，說(shuō)的比較急，他百思不得其解，明明多帶帶測(cè)試的時(shí)候能夠走索引，為什么表一關(guān)聯(lián)，打死都不走索引，就算加了HINTS也不走索引，讓我?guī)兔纯础Ｏ旅婢蜆?gòu)造一個(gè)類(lèi)似的SQL，如下所示：

select *

fromt1

leftjoin t2

ont1.name = t2.name

wheret1.name = 09DZ8H3XG8ORAH0HUZQI;

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

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

ont1.name = t2.name

wheret1.name = 09DZ8H3XG8ORAH0HUZQI;

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

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

COUNT(*)

----------

1row selected.

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

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)計(jì)信息沒(méi)有問(wèn)題。多帶帶測(cè)試的時(shí)候正常走索引，如下所示：

回頭一想，這里有個(gè)大坑啊，啥大坑呢？

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

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

ExecutionPlan

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

Planhash value: 2757452810

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

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

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

DESC T1和T2表：

desct1

Name Null? Type

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

ID NUMBER

NAME NVARCHAR2(100)

desct2

Name Null? Type

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

ID NUMBER

NAME VARCHAR2(100)

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

selectdump(09DZ8H3XG8ORAH0HUZQI) a,

dump(n09DZ8H3XG8ORAH0HUZQI)b,

dump(u09DZ8H3XG8ORAH0HUZQI)c,

dump(SYS_OP_C2C(09DZ8H3XG8ORAH0HUZQI))d

fromdual;
A

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

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通過(guò)SYS_OP_C2C函數(shù)將VARCHAR2轉(zhuǎn)成了NVARCHAR2，SO，找到了問(wèn)題根源，那么就解決這個(gè)問(wèn)題就簡(jiǎn)單了，可以使用如下方式：

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

select *

from t1

left join t2

on to_char(t1.name)= t2.name

where t1.name =09DZ8H3XG8ORAH0HUZQI;

執(zhí)行計(jì)劃如下：

ExecutionPlan

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

Planhash value: 4205057668

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

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

| 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.如果改不了語(yǔ)句，那么就對(duì)t2.name建立函數(shù)索引，如下所示走了函數(shù)索引：

create indexidx1_t2 on t2(SYS_OP_C2C(NAME));

執(zhí)行計(jì)劃如下：

ExecutionPlan

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

Planhash value: 4208491579

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

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

| 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è)計(jì)，保持t1.name和t2.name的一致性，這個(gè)應(yīng)該在做設(shè)計(jì)的時(shí)候就考慮，這里不做闡述。

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

附測(cè)試語(yǔ)句：

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;

--問(wèn)題語(yǔ)句

select*

fromt1

leftjoin t2

ont1.name = t2.name

wheret1.name = 09DZ8H3XG8ORAH0HUZQI;

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

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

GPU云服務(wù)器云服務(wù)器正確姿勢(shì) sql的執(zhí)行順序執(zhí)行計(jì)劃 sql語(yǔ)句的優(yōu)化

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://specialneedsforspecialkids.com/yun/130196.html

發(fā)表評(píng)論

登陸后可評(píng)論

0條評(píng)論

IT那活兒

男|高級(jí)講師

我要關(guān)注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1346·2023-01-11 13:20
RAC雙節(jié)點(diǎn)crash回復(fù)一例

閱讀 1684·2023-01-11 13:20
ORA-600處理一例

閱讀 1132·2023-01-11 13:20
雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

閱讀 1858·2023-01-11 13:20
RAC集群節(jié)點(diǎn)1重啟分析一例

閱讀 4100·2023-01-11 13:20
CRS啟動(dòng)報(bào)錯(cuò)CRS-1656處理分享

閱讀 2704·2023-01-11 13:20
oracle 12CR2打補(bǔ)丁報(bào)錯(cuò)處理一例

閱讀 1385·2023-01-11 13:20
分布式緩存組件故障分析及監(jiān)控優(yōu)化

閱讀 3597·2023-01-11 13:20

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專(zhuān)欄INFORMATION COLUMN

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！

通過(guò)執(zhí)行計(jì)劃優(yōu)化SQL的正確姿勢(shì)（上）

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

相關(guān)文章

宜信-運(yùn)維-數(shù)據(jù)庫(kù)|SQL優(yōu)化：一篇文章說(shuō)清楚Oracle Hint的正確使用姿勢(shì)

如何成為一名優(yōu)秀的程序員

發(fā)表評(píng)論

0條評(píng)論

IT那活兒

男|高級(jí)講師

TA的文章

消息中間件故障分析一例

RAC雙節(jié)點(diǎn)crash回復(fù)一例

ORA-600處理一例

雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

RAC集群節(jié)點(diǎn)1重啟分析一例

CRS啟動(dòng)報(bào)錯(cuò)CRS-1656處理分享

oracle 12CR2打補(bǔ)丁報(bào)錯(cuò)處理一例

分布式緩存組件故障分析及監(jiān)控優(yōu)化

最新活動(dòng)