第一章 問題概述
本案例中的問題現(xiàn)象發(fā)生于當(dāng)前主流的ORACLE 11G環(huán)境。不排除隨著后續(xù)數(shù)據(jù)庫版本的升級(jí)可能會(huì)有一定細(xì)微的差異表現(xiàn)。但問題的原因及解決方案，在所有版本中都是通用的。
數(shù)據(jù)庫版本:ORACLE 11G
應(yīng)用人員反饋，當(dāng)前數(shù)據(jù)庫存在一條低效SQL。不確定什么原因?qū)е?。需要定位問題原因。且應(yīng)用人員反應(yīng)之前該SQL執(zhí)行效率沒有問題，是這兩天才出現(xiàn)的性能問題。
為此。需要排查何種原因?qū)е碌漠?dāng)前性能低效，并找出解決方案。
第二章 問題排查步驟
2.1 查看活動(dòng)會(huì)話
應(yīng)用人員反饋當(dāng)前SQL正在跑，為此查看當(dāng)前活動(dòng)會(huì)話。
可以看到有一條SQL在累計(jì)執(zhí)行，且平均執(zhí)行時(shí)間達(dá)到了3W多秒?？梢源_定是該低效SQL。
2.2 分析SQL的歷史執(zhí)行情況：
可以看到，SQL在20號(hào)之前的執(zhí)行效率是可以的。真正出現(xiàn)問題是從21號(hào)開始。執(zhí)行計(jì)劃發(fā)生了改變，導(dǎo)致了執(zhí)行效率非常低效。這也符合應(yīng)用人員反饋的，執(zhí)行效率近期才開始變差。
2.3 分析執(zhí)行計(jì)劃：
SQL語句較長(zhǎng)，這里優(yōu)先分析執(zhí)行計(jì)劃。只列出部分
原始高效執(zhí)行計(jì)劃：
通過T3做驅(qū)動(dòng)表，通過FINISH_TIME索引完成過濾。
當(dāng)前低效執(zhí)行計(jì)劃：
當(dāng)前問題執(zhí)行計(jì)劃，通過T_AGENT_TYPE表做驅(qū)動(dòng)表，并關(guān)聯(lián)后續(xù)多張表。從COST成本來看，新產(chǎn)生的執(zhí)行計(jì)劃成本遠(yuǎn)小于之前的。因此優(yōu)化器選擇了新的執(zhí)行計(jì)劃。
2.4 實(shí)際測(cè)試執(zhí)行效率：
針對(duì)出現(xiàn)性能差異的代碼段，單獨(dú)測(cè)試執(zhí)行效率。
當(dāng)前低效執(zhí)行情況：
主要耗時(shí)發(fā)生在T_AGENT_TYPE表關(guān)聯(lián)的后續(xù)一系列多張表，執(zhí)行效率較低。更近一步分析，根源在于其直接關(guān)聯(lián)的T_T1_CHANGE表，其預(yù)估行數(shù)明明很大，達(dá)到了34M，而優(yōu)化器估算成本時(shí)確只有121。關(guān)聯(lián)后的成本也只有125。
這里同樣測(cè)試如果用FINISH_TIME日期索引關(guān)聯(lián)。則成本會(huì)遠(yuǎn)高于當(dāng)前的。
這樣就導(dǎo)致當(dāng)前使用T_AGENT_TYPE做驅(qū)動(dòng)表，后續(xù)再關(guān)聯(lián)多張表時(shí)候選擇了嵌套循環(huán)關(guān)聯(lián)，多次訪問造成執(zhí)行較慢的問題。
2.5 分析目標(biāo)表：
這里懷疑執(zhí)行成本較小的原因與驅(qū)動(dòng)表T_AGENT_TYPE有關(guān)。是否是統(tǒng)計(jì)信息差異導(dǎo)致的當(dāng)前預(yù)估成本過小問題？
首先查看統(tǒng)計(jì)信息的最大最小值。
--獲取列中的最大最小值
set serveroutput on
declare
a_low dba_tab_columns.low_value%type;
a_high dba_tab_columns.high_value%type;
aa XXX.T_AGENT_TYPE.SERVICE_ID%type;
begin
SELECT LOW_VALUE, HIGH_VALUE
into a_low, a_high
FROM dba_tab_columns
WHERE owner = 'XXX' ----用戶名
AND table_name in ('T_AGENT_TYPE') ---表名
and COLUMN_NAME in (upper('SERVICE_ID'));
dbms_stats.convert_raw_value(a_low, aa);
dbms_output.put_line('MIN:'||aa);
dbms_stats.convert_raw_value(a_high, aa);
dbms_output.put_line('MAX:'||aa);
end;
/
set serveroutput off
表中數(shù)據(jù)量較少，當(dāng)前表的最大最小值，與表中實(shí)際的數(shù)據(jù)分布完全吻合，不存在記錄差異影響關(guān)聯(lián)行數(shù)，進(jìn)而影響查詢成本的問題。
查看統(tǒng)計(jì)信息：
該表上一次統(tǒng)計(jì)信息收集時(shí)間是在5月20日，盡管時(shí)間偏長(zhǎng)，但該表是個(gè)小表，沒有頻繁 數(shù)據(jù)變動(dòng)的情況下，也不需要保持統(tǒng)計(jì)信息的最新。
根據(jù)關(guān)聯(lián)列，這里繼續(xù)分析關(guān)聯(lián)列SERVICE_ID的最大最小值。
通過CAST_TO_varchar2函數(shù)來看，該列是字符型列。且轉(zhuǎn)換后正好是對(duì)應(yīng)的最大最小值。
但從列統(tǒng)計(jì)信息上看，該列確是number類型。
通過CAST_TO_NUMBER函數(shù)轉(zhuǎn)換時(shí)不匹配。這里就是很奇怪的地方。為何統(tǒng)計(jì)信息記錄的類型，和實(shí)際存儲(chǔ)的最大最小值轉(zhuǎn)換時(shí)不匹配呢？
懷疑是否是應(yīng)用人員修改過該字段的類型。
且查看該表相關(guān)對(duì)象，發(fā)現(xiàn)有相同表名的不同用戶，其目標(biāo)字段正好是VARCHAR2類型的。
通過LAST_DDL_TIME及上述對(duì)比，上面的假設(shè)很有可能。
2.6 創(chuàng)建測(cè)試表實(shí)驗(yàn)?zāi)M：
為了驗(yàn)證上述猜想是否正確。在經(jīng)過應(yīng)用人員同意的情況下，創(chuàng)建了相應(yīng)測(cè)試表。
create table T_AGENT_TYPE_copy2 as select * from T_AGENT_TYPE;
truncate table T_AGENT_TYPE_copy2;
alter table T_AGENT_TYPE_copy2 modify(SERVICE_ID VARCHAR2(4));
insert into T_AGENT_TYPE_copy2 select * from T_AGENT_TYPE;
--并收集統(tǒng)計(jì)信息：
exec dbms_stats.gather_table_stats(ownname=>'xxx',tabname=>'T_AGENT_TYPE_COPY2',cascade=>true,no_invalidate=>false);
1.首先測(cè)試VARCHAR2列類型：
SELECT count(*)
FROM T_T1_CHANGE TPC
WHERE
TPC.SERVICE_ID IN (SELECT SERVICE_ID FROM T_AGENT_TYPE_COPY2)
AND TPC.CHANGE_STATUS = '3'
AND TPC.SERVICE_ID NOT IN (451, 437);
模擬相同的表關(guān)聯(lián)情況：
在當(dāng)前列類型時(shí)，關(guān)聯(lián)成本是很高的?？梢钥吹郊词故侨饕龗呙?，優(yōu)化器仍然使用了HASH關(guān)聯(lián)。而不會(huì)考慮問題執(zhí)行計(jì)劃中的嵌套循環(huán)。
試圖讓其走回嵌套循環(huán)：
查詢成本甚至上升到了80M。也正是如此，放棄嵌套循環(huán)使用HASH關(guān)聯(lián)是正確的選擇。
- 修改類型為number類型：不收集統(tǒng)計(jì)信息
truncate table T_AGENT_TYPE_copy2;
alter table T_AGENT_TYPE_copy2 modify(SERVICE_ID NUMBER(10,0));
insert into T_AGENT_TYPE_copy2 select * from T_AGENT_TYPE;
在插入數(shù)據(jù)后，保持當(dāng)前目標(biāo)表的未收集統(tǒng)計(jì)信息狀態(tài)。再次測(cè)試：
SELECT /*+ 22 */count(*)
FROM T_T1_CHANGE TPC
WHERE
TPC.SERVICE_ID IN (SELECT SERVICE_ID FROM T_AGENT_TYPE_COPY2)
AND TPC.CHANGE_STATUS = '3'
AND TPC.SERVICE_ID NOT IN (451, 437);
執(zhí)行計(jì)劃如下：
可以看到，此時(shí)重現(xiàn)了問題現(xiàn)象。盡管關(guān)聯(lián)后預(yù)估返回行數(shù)很多，但預(yù)估的查詢成本確很低。這里就懷疑與修改字段未收集統(tǒng)計(jì)信息有關(guān)。
3.修改類型后重新收集統(tǒng)計(jì)信息：
exec dbms_stats.gather_table_stats(ownname=>'xxx',tabname=>'T_AGENT_TYPE_COPY2',cascade=>true,no_invalidate=>false );
再次測(cè)試查詢：
SELECT /*+ 33 */count(*)
FROM T_T1_CHANGE TPC
WHERE
TPC.SERVICE_ID IN (SELECT SERVICE_ID FROM T_AGENT_TYPE_COPY2)
AND TPC.CHANGE_STATUS = '3'
AND TPC.SERVICE_ID NOT IN (451, 437);
對(duì)比執(zhí)行情況：
此時(shí)又走回了HASH關(guān)聯(lián)。
分析到此，基本確定就是由于修改字段類型但未收集統(tǒng)計(jì)信息有關(guān)。
對(duì)比當(dāng)前測(cè)試表的歷史統(tǒng)計(jì)信息：
進(jìn)一步印證了上述實(shí)驗(yàn)。首先創(chuàng)建T_AGENT_TYPE_COPY2測(cè)試表后，直接修改字段類型為VARCHAR2類型，之后收集統(tǒng)計(jì)信息。此時(shí)記錄的最大最小值均為313036、39323038。也就是最開始看到的目標(biāo)表的最大最小值情況。（當(dāng)前列為字符型）
之后修改字段類型為number。但由于未收集統(tǒng)計(jì)信息，此時(shí)產(chǎn)生了性能問題。對(duì)應(yīng)實(shí)驗(yàn)步驟2
最后收集統(tǒng)計(jì)信息后，再次觀察歷史統(tǒng)計(jì)信息。
此時(shí)的記錄變成了C20207、C25D09。也就是統(tǒng)計(jì)信息成功的記錄為了number類型。
再次對(duì)比原始目標(biāo)表，
與我們實(shí)驗(yàn)的測(cè)試表現(xiàn)象完全一致。至此問題明確。
第三章 解決方案
通過上述分析，從問題現(xiàn)象推導(dǎo)出是應(yīng)用人員對(duì)目標(biāo)表做過字段類型修改。之后與應(yīng)用人員溝通，確定正是在前一天修改過SERVICE_ID的字段類型。從varchar2修改為number類型。從而造成了看到的性能問題。
因此，又經(jīng)過上面的實(shí)驗(yàn)測(cè)試。只需要在事后及時(shí)的收集目標(biāo)表的統(tǒng)計(jì)信息。即可讓優(yōu)化器順利的評(píng)估各表關(guān)聯(lián)后的查詢成本，從而走回正確的執(zhí)行計(jì)劃。
優(yōu)化建議：
在修改字段類型后及時(shí)收集統(tǒng)計(jì)信息。
exec dbms_stats.gather_table_stats(ownname=>'xxx',tabname=>'T_AGENT_TYPE',cascade=>true,no_invalidate=>false);
收集統(tǒng)計(jì)信息后，對(duì)比SQL執(zhí)行情況：
后續(xù)走回了正確的執(zhí)行計(jì)劃。
第四章 問題總結(jié)
本案例應(yīng)用人員對(duì)表做DDL變更。但未收集統(tǒng)計(jì)信息所致。數(shù)據(jù)庫在評(píng)估執(zhí)行計(jì)劃時(shí)，是依賴統(tǒng)計(jì)信息的，如果統(tǒng)計(jì)信息與實(shí)際的差異過大，甚至出現(xiàn)本例中的字段類型與記錄的最大最小值類型完全不是一類的時(shí)候，很可能造成走錯(cuò)執(zhí)行計(jì)劃的問題。因此就需要及時(shí)的收集統(tǒng)計(jì)信息以保證查詢效率。
