涉及知識(shí)點(diǎn)：
1.NL連接的優(yōu)化
2.收集統(tǒng)計(jì)信息方式
3.查看執(zhí)行計(jì)劃的6種方式及其優(yōu)缺點(diǎn)
4.SQL執(zhí)行計(jì)劃的執(zhí)行順序查看方法
問題現(xiàn)象：在進(jìn)行壓力測(cè)試登錄的時(shí)候，發(fā)現(xiàn)有大量查詢消息的操作。SQL卡頓時(shí)間長(zhǎng)達(dá)1分鐘玻佩。
具體SQL如下：
select
*
from
( select
row_. * ,
rownum rownum_
from
( select
斋竞。益眉。COLORKEY ,
RESENDTIMES
from
sm_msg_content
where
receiver = '1001WW1000000000054U'
and msgtype = 'nc'
and destination = 'inbox'
and msgsourcetype in (
select
typecode
from
sm_msg_msgtype
where
displocation = 'worklist'
)
and (
pk_detail in (
select
nt.pk_checkflow
from
pub_workflownote nt
where
nt.PK_BILLTYPE in (
select
bt.pk_billtypecode
from
bd_billtype bt
where
(
bt.pk_billtypecode in (
select
acbt.billtype
from
wfm_acceptnctype acbt
where
acbt.ext4 = 'ALLOW_MSG'
oracbt.ext4 is null
)
andbt.parentbilltype = '~'
)
or (
bt.parentbilltype < > '~'
andbt.parentbilltype in (
select
accbt.billtype
from
wfm_acceptnctype accbt
where
accbt.ext4 = 'ALLOW_MSG'
oraccbt.ext4 is null
)
)
)
)
or pk_detail is null
)
and not exists (
select
PK_CHECKFLOW
from
pub_workflownote nt
where
nt.ACTIONTYPE ='MAKEBILL'
and nt.PK_CHECKFLOW =PK_DETAIL
)
order by
sendtime desc ) row_
where
rownum < = 100
)
where
rownum_ > 0
問題分析：
針對(duì)問題現(xiàn)象陕壹，可以得到初步結(jié)論应狱。該SQL返回結(jié)果集很少共郭，SQL解析的時(shí)間長(zhǎng)達(dá)50秒左右。所以應(yīng)該是可以優(yōu)化到2s以內(nèi)疾呻。
由于可以執(zhí)行出SQL結(jié)果除嘹，并且不需要查看物理讀和邏輯讀。
因此罐韩，通過statistics_level=all的方式查看執(zhí)行計(jì)劃憾赁。
查看執(zhí)行順序的時(shí)候污朽，我們可以將執(zhí)行計(jì)劃拷貝到TXT文本里散吵，用光標(biāo)大法進(jìn)行查詢。
找到縮進(jìn)的最深的第一個(gè)語句蟆肆，就是SQL執(zhí)行的第一條語句矾睦，然后按照從上到下，逐步向外合并的順序進(jìn)行閱讀炎功。
| Id | Operation | Name |Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:49.67 | 952K| | | |
|* 1 | VIEW | | 1 | 100 | 100 |00:00:49.67 | 952K| | | |
|* 2 | COUNT STOPKEY | | 1 | | 100 |00:00:49.67 | 952K| | | |
| 3 | VIEW | | 1 | 199 | 100 |00:00:49.67 | 952K| | | |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 199 | 100 |00:00:49.67 | 952K| 160K| 160K| 142K (0)|
|* 5 | FILTER | | 1 | | 14017 |00:00:49.34 | 952K| | | |
|* 6 | HASH JOIN RIGHT ANTI | | 1 | 3933 | 14487 |00:00:01.63 | 41121 | 2616K| 2616K| 161K (0)|
|* 7 | TABLE ACCESS FULL |PUB_WORKFLOWNOTE | 1 | 1| 0 |00:00:00.67 | 26438 | | | |
| 8 | NESTED LOOPS | | 1 | 3934 | 14487 |00:00:00.85 | 14683 | | | |
| 9 | NESTED LOOPS | | 1 | 3934 | 14487 |00:00:00.19 | 203 | | | |
| 10 | SORT UNIQUE | | 1 | 11 | 11 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
|* 11 | TABLE ACCESS FULL | SM_MSG_MSGTYPE | 1 | 11 | 11 |00:00:00.01 | 6 | | | |
|* 12 | INDEX RANGE SCAN | I_RCVMSGTYPE | 11 | 183 | 14487 |00:00:00.18 | 197 | | | |
|* 13 | TABLE ACCESSBY INDEX ROWID| SM_MSG_CONTENT | 14487 | 1311 | 14487 |00:00:00.61 | 14480 | | | |
|* 14 | FILTER | | 14487 | | 14017 |00:00:47.06 | 911K| | | |
| 15 | NESTED LOOPS | | 14487 | 1| 14017 |00:00:46.80 | 911K| | | |
| 16 | TABLE ACCESS BY INDEX ROWID| PUB_WORKFLOWNOTE | 14487 | 1 | 14017 |00:00:02.25 | 56851 | | | |
|* 17 | INDEX UNIQUE SCAN | PK_PUB_WORKFLOWNOT| 14487 | 1 | 14017 |00:00:01.72| 42834 | | | |
|* 18 | TABLE ACCESSFULL | BD_BILLTYPE | 14017 | 1 | 14017 |00:00:44.46 | 854K| | | |
|* 19 | TABLE ACCESS FULL |WFM_ACCEPTNCTYPE | 1 | 1| 1 |00:00:00.01 | 2 | | | |
|* 20 | TABLE ACCESS FULL |WFM_ACCEPTNCTYPE | 0 | 1| 0 |00:00:00.01 | 0 | | | |
可以明顯發(fā)現(xiàn)枚冗，時(shí)間在第| 15 | NESTED LOOPS | | 14487 | 1 | 14017 |00:00:46.80 | 911K| | | |有了突然的上漲。
該處屬于NL連接蛇损。而細(xì)看我們可以發(fā)現(xiàn)赁温，驅(qū)動(dòng)表為PK_PUB_WORKFLOWNOT 而 被驅(qū)動(dòng)表為WFM_ACCEPTNCTYPE 坛怪。
驅(qū)動(dòng)表為1W4的行數(shù)，而被驅(qū)動(dòng)表為1行股囊。由于NL的機(jī)制袜匿，在兩表進(jìn)行合并操作的時(shí)候會(huì)進(jìn)行被驅(qū)動(dòng)表次數(shù)的循環(huán)。
這里顯然是驅(qū)動(dòng)表的順序錯(cuò)誤稚疹，變成了大表驅(qū)動(dòng)小表居灯。而這樣情況的發(fā)生，很多時(shí)候是由于統(tǒng)計(jì)信息不準(zhǔn)確導(dǎo)致内狗。
這也就是為什么采用statistics_level=all的方式進(jìn)行執(zhí)行計(jì)劃的查看怪嫌。該方法是唯一可以看到預(yù)估行數(shù)E-ROWS和實(shí)際行數(shù)A-ROWs的方式。
這里我們看到柳沙，PK_PUB_WORKFLOWNOT 預(yù)估值為1岩灭，而實(shí)際值為14017。顯然是這里的預(yù)估出現(xiàn)了問題赂鲤。
問題解決：
知道是統(tǒng)計(jì)信息不準(zhǔn)確導(dǎo)致的問題川背，就比較好解決了。這里注意蛤袒，需要使用oracle 11g推薦的方式來收集統(tǒng)計(jì)信息熄云。
exec dbms_stats.gather_table_stats(‘nc_user','PUB_WORKFLOWNOTE',CASCADE=>true,method_opt=>'for all indexed columns');
同時(shí)，由于NL的機(jī)制比較關(guān)注關(guān)聯(lián)列的效率問題妙真，而第18
18 - filter("NT"."PK_BILLTYPE"="BT"."PK_BILLTYPECODE")走了filter缴允。而這里是可以通過走索引來避免全表掃描的。于是在這兩個(gè)關(guān)聯(lián)列上都添加了索引珍德。
最終练般，該SQL的執(zhí)行時(shí)間從50秒變?yōu)榱?秒以內(nèi)，達(dá)到了優(yōu)化的效果锈候。使得壓力測(cè)試順利進(jìn)行薄料。
| Id | Operation | Name |Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.58 | 181K| | | |
|* 1 | VIEW | | 1 | 1| 100 |00:00:00.58 | 181K| | | |
|* 2 | COUNT STOPKEY | | 1 | | 100 |00:00:00.58 | 181K| | | |
| 3 | VIEW | | 1 | 1| 100 |00:00:00.58 | 181K| | | |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 1| 100 |00:00:00.58 | 181K| 142K| 142K| 126K (0)|
|* 5 | FILTER | | 1 | | 17988 |00:00:00.55 | 181K| | | |
| 6 | NESTED LOOPS ANTI | | 1 | 4| 18458 |00:00:00.33 | 73030 | | | |
|* 7 | HASH JOIN | | 1 | 376 | 18458 |00:00:00.14 | 18589 | 1645K| 1645K| 1298K (0)|
| 8 | SORT UNIQUE | | 1 | 11 | 11|00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
|* 9 | TABLE ACCESS FULL | SM_MSG_MSGTYPE | 1 | 11 | 11|00:00:00.01 | 6 | | | |
|* 10 | TABLE ACCESSBY INDEX ROWID| SM_MSG_CONTENT | 1 | 1377 | 18458 |00:00:00.12 | 18583 | | | |
|* 11 | INDEX RANGE SCAN |I_RCV_ISREAD | 1 | 548 | 18459 |00:00:00.01 | 133 | | | |
|* 12 | TABLE ACCESS BYINDEX ROWID | PUB_WORKFLOWNOTE | 18458 | 6048 | 0 |00:00:00.17| 54441 | | | |
|* 13 | INDEX UNIQUESCAN | PK_PUB_WORKFLOWNOT | 18458 | 1| 17988 |00:00:00.10 | 36453 | | | |
|* 14 | FILTER | | 18458 | | 17988 |00:00:00.18 | 108K| | | |
| 15 | NESTED LOOPS | | 18458 | 2| 17988 |00:00:00.17 | 108K| | | |
| 16 | TABLE ACCESS BY INDEX ROWID| PUB_WORKFLOWNOTE | 18458| 1 | 17988 |00:00:00.08 | 72722 | | | |
|* 17 | INDEX UNIQUE SCAN |PK_PUB_WORKFLOWNOT | 18458 | 1 | 17988 |00:00:00.06 | 54734 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID| BD_BILLTYPE | 17988 | 2| 17988 |00:00:00.07 | 35981 | | | |
|* 19 | INDEX RANGE SCAN |PK_BILLTYPEIDCODE | 17988 | 2| 17988 |00:00:00.04 | 17993 | | | |
|* 20 | TABLE ACCESS FULL | WFM_ACCEPTNCTYPE | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 21 | TABLE ACCESS FULL | WFM_ACCEPTNCTYPE | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
