繼續(xù)前幾天的一個(gè)案例一個(gè)SQL性能問(wèn)題的優(yōu)化探索（一）(r11筆記第33天)

如下的SQL語(yǔ)句存在索引字段CARD_NO,但是執(zhí)行的時(shí)候卻走了全表掃描，因?yàn)檫@是一個(gè)核心表，數(shù)據(jù)量很大，導(dǎo)致數(shù)據(jù)庫(kù)負(fù)載很高。

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
SELECT ID,CN,CARD_NO,TO_CHAR(CHARGE_DATE,'yyyy-MM-dd HH24:mi:ss') AS CHARGE_DATE ,IP,POINT,PRESENT_P
OINT,SUCCESS,CARD_TYPE,PRODUCTNUM,SALEMETHOD,ITEM_ID,SERVICEID,ORDER_NO,TO_CHAR(EXPIREDATE,'yyyy-MM-
dd HH24:mi:ss') AS EXPIREDATE,REMARK FROM RECHARGE_LOG WHERE CARD_NO = TO_NUMBER(:1)

???? 對(duì)于這個(gè)問(wèn)題，苦于無(wú)法復(fù)現(xiàn)，通過(guò)各種途徑進(jìn)行分析，有些信息似乎有些矛盾。如果說(shuō)是統(tǒng)計(jì)信息的問(wèn)題，但是查看直方圖的信息是有的。而且分為了200多個(gè)bucket，索引列card_no的統(tǒng)計(jì)信息也沒(méi)有發(fā)現(xiàn)什么問(wèn)題。而且比較奇怪的是問(wèn)題發(fā)生后查看執(zhí)行計(jì)劃情況，后面的語(yǔ)句是可以走索引的。這個(gè)問(wèn)題怎么來(lái)進(jìn)一步分析呢。

????我們可以化被動(dòng)為主動(dòng)，我對(duì)這個(gè)列對(duì)應(yīng)的索引添加了索引監(jiān)控，即alter index xxxx monitoring usage;然后有了這個(gè)標(biāo)識(shí)之后，就可以隨時(shí)查看v$object_usage查看索引是否被啟用，當(dāng)然這個(gè)視圖得到的信息畢竟很有限。我們只能知道索引是否被啟用，但是每次啟用的時(shí)間等這些沒(méi)法定位，而且因?yàn)樽吡怂饕旧韴?zhí)行效率就很高，所以在快照中是很可能不會(huì)被收編進(jìn)來(lái)的。

? 這個(gè)問(wèn)題還有一個(gè)疑點(diǎn)就是統(tǒng)計(jì)信息的收集時(shí)間，和同事確認(rèn)問(wèn)題發(fā)生當(dāng)天他是沒(méi)有收集統(tǒng)計(jì)信息的，但是查看字段last_analyzed的時(shí)間卻恰恰是發(fā)生收集以后。

?? 問(wèn)題暫時(shí)擱置下來(lái)，在這兩天問(wèn)題有了進(jìn)一步的發(fā)現(xiàn)，另外一個(gè)報(bào)警引起了我的注意。報(bào)警是CPU使用率較高，查看數(shù)據(jù)庫(kù)層面的負(fù)載也突然有了較大的抖動(dòng)。

定位到相關(guān)的語(yǔ)句如下：

?? SNAP_ID SQL_ID??????? EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
---------- ------------- ---------------- ---------- ----------
??? 108829 bbtwpb1aamud2??????????????? 0 1766s????? 45%
??? 108829 7zmnkpfaumuk1??????????????? 1 1141s????? 29%
??? 108829 4y30fbyjmrrqr??????????????? 1 432s?????? 11%
??? 108829 f7h0xrkw1wy48??????????????? 1 192s?????? 4%
??? 108829 63t40nvuuwrdp?????????? 258730 116s?????? 2%

可以看到明顯有幾個(gè)SQL執(zhí)行效率較差。

查看TOP 1的語(yǔ)句發(fā)現(xiàn)竟然是一個(gè)JOB在運(yùn)行。

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job;? next_date TIMESTAMP WITH TIME ZONE := :mydate;? broken BOOLEAN
:= FALSE;? job_name VARCHAR2(30) := :job_name;? job_subname VARCHAR2(30) := :job_subname;? job_owner
?VARCHAR2(30) := :job_owner;? job_start TIMESTAMP WITH TIME ZONE := :job_start;? job_scheduled_start
?TIMESTAMP WITH TIME ZONE := :job_scheduled_start;? window_start TIMESTAMP WITH TIME ZONE := :window
_start;? window_end TIMESTAMP WITH TIME ZONE := :window_end;? BEGIN? BEGIN
dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'RECHARGE_LOG',cascade=>true);
END;? :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

語(yǔ)句的核心就是dbms_stats的部分，竟然是收集表recharege_log的統(tǒng)計(jì)信息。

后面的幾個(gè)語(yǔ)句也是收集統(tǒng)計(jì)相關(guān)的遞歸SQL，我列舉出一個(gè)是因?yàn)檫@里面有非常重要的一個(gè)信息。先賣(mài)個(gè)關(guān)子。

$sh showsqltext.sh 7zmnkpfaumuk1
insert /*+ append */ into sys.ora_temp_1_ds_33892 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dy
namic_sampling(0) no_monitoring */"CHARGE_DATE","SERVICEID","ORDER_NO","EXPIREDATE", rowid SYS_DS_ALIAS_0 from "TEST"."RECHARGE_LOG" sample ( 12.12452
80643) t

這個(gè)時(shí)候查看語(yǔ)句的執(zhí)行計(jì)劃情況，只有全表掃描的執(zhí)行計(jì)劃。
明顯不合理嘛。看起來(lái)問(wèn)題又想回到了原點(diǎn)。

這個(gè)時(shí)候不要?dú)怵H，我們重新捋一捋信息，列的統(tǒng)計(jì)信息的情況如下：

>? select column_name,histogram from dba_tab_col_statistics
where table_name='RECHARGE_LOG';
COLUMN_NAME????????? HISTOGRAM
-------------------- ---------------
ID?????????????????? NONE
CN?????????????????? HEIGHT BALANCED
CARD_NO????????????? HEIGHT BALANCED
CHARGE_DATE????????? HEIGHT BALANCED
IP?????????????????? HEIGHT BALANCED
POINT??????????????? FREQUENCY
PRESENT_POINT??????? NONE
SUCCESS????????????? FREQUENCY
CARD_TYPE??????????? FREQUENCY
PRODUCTNUM?????????? NONE
SALEMETHOD?????????? FREQUENCY
ITEM_ID????????????? FREQUENCY
SERVICEID??????????? NONE
ORDER_NO???????????? HEIGHT BALANCED
REMARK?????????????? NONE
EXPIREDATE?????????? NONE
ID?????????????????? NONE
CN?????????????????? NONE
CARD_NO????????????? NONE
CHARGE_DATE????????? NONE
IP?????????????????? NONE
POINT??????????????? NONE
....

看起來(lái)有點(diǎn)不大對(duì)勁，因?yàn)镃ARD_NO的直方圖信息是空的。

查看另外一個(gè)數(shù)據(jù)字典，發(fā)現(xiàn)這個(gè)列的直方圖信息竟然是存在的，輸出有上百行。

select to_char(endpoint_value) value,endpoint_number,column_name ,ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where table_name = 'RECHARGE_LOG' and column_name in ('CARD_NO') ORDER BY endpoint_number

所以這個(gè)問(wèn)題就比較有意思了，存在直方圖的列的統(tǒng)計(jì)信息，但是兩個(gè)視圖中信息卻有些沖突。

由此一來(lái)可以推斷是這個(gè)地方導(dǎo)致全表掃描的一個(gè)原因。

我們來(lái)看看另外一個(gè)潛在的問(wèn)題，收集統(tǒng)計(jì)信息的部分，可以看到是在凌晨3點(diǎn)運(yùn)行，明顯就是一個(gè)定時(shí)任務(wù)。所以同事所說(shuō)的沒(méi)有手工收集統(tǒng)計(jì)信息是真實(shí)的。

select table_name,
? ????? to_char(LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
? ????? from dba_tables where TABLE_NAME='RECHARGE_LOG';
TABLE_NAME???????????????????? LAST_ANALYZED
------------------------------ -------------------
RECHARGE_LOG?????????????????? 2017-01-07 03:40:45?

? 還有一個(gè)問(wèn)題，那就是收集統(tǒng)計(jì)信息，怎么會(huì)有這些影響呢，我們得看看dbms_stats.gather_table_stats的參數(shù)method_opt,因?yàn)閙ethod_opt:決定histograms信息是怎樣被統(tǒng)計(jì)的.method_opt的取值(默認(rèn)值為FOR ALL COLUMNS SIZE AUTO)，也就意味著是一個(gè)動(dòng)態(tài)的過(guò)程。這不card_no攤上事情了，直接忽略了，沒(méi)收集。

????關(guān)于這個(gè)問(wèn)題，可以參考一個(gè)bug?

Bug 5949981 - Bad cardinality with histogram (Doc ID 5949981.8)

?而問(wèn)題原因跟進(jìn)一步，為什么card_no沒(méi)有收集統(tǒng)計(jì)信息。可以參考一條SQL語(yǔ)句的執(zhí)行計(jì)劃變化探究（r10筆記第9天） 原因也是大同小異。

總結(jié)

以上是生活随笔為你收集整理的一个SQL性能问题的优化探索（二）(r11笔记第38天)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。