周六傍晚開(kāi)車(chē)帶著老婆兒子去山姆超時(shí)購(gòu)物，半路開(kāi)發(fā)同仁打電話反應(yīng)，說(shuō)MES有兩個(gè)功能模塊超時(shí)，跑不出來(lái)讓幫忙看看是不是數(shù)據(jù)庫(kù)有問(wèn)題？
做為一個(gè)合格的運(yùn)維dog，找個(gè)路邊停車(chē)，從后備箱拿出電腦，連上熱點(diǎn)，打開(kāi)vpn登陸到公司內(nèi)網(wǎng)開(kāi)始排查問(wèn)題！
老婆開(kāi)車(chē)，我在后座工作！
1.問(wèn)題處理過(guò)程
先打開(kāi)OEM看看數(shù)據(jù)庫(kù)整體狀態(tài)
看到數(shù)據(jù)庫(kù)負(fù)載呈現(xiàn)規(guī)律性升高降低
從top sql中可以看到一個(gè)sql id 執(zhí)行時(shí)間過(guò)久
之前遇到過(guò)sql執(zhí)行計(jì)劃變化引起的性能問(wèn)題，這里首先想到的就是用coe_xfr_sql_profile.sql
查看一下 是不是選擇了效率低的執(zhí)行計(jì)劃？
查詢結(jié)果可以看到這個(gè)sqlid 有五個(gè)執(zhí)行計(jì)劃，而現(xiàn)在卻選擇了最差的平均時(shí)間35秒的執(zhí)行計(jì)劃
以恢復(fù)系統(tǒng)為第一優(yōu)先級(jí)，最簡(jiǎn)單的辦法就是使用coe_xfr_sql_profile.sql來(lái)綁定執(zhí)行計(jì)劃
參數(shù)1：為問(wèn)題sql id 參數(shù)2：為需要綁定的執(zhí)行計(jì)劃，執(zhí)行結(jié)束后會(huì)生產(chǎn)一個(gè)sql如下
coe_xfr_sql_profile_54ftmkvm9z5kj_4062773594.sql
執(zhí)行這個(gè)sql 就會(huì)將sql id和最優(yōu)的執(zhí)行計(jì)劃綁定；
另外還有一個(gè)sql 更離譜，也利用coe_xfr_sql_profile.sql來(lái)做修正，修正后系統(tǒng)恢復(fù)正常！
恢復(fù)正常后的OEM數(shù)據(jù)庫(kù)負(fù)載圖
2.為什么執(zhí)行計(jì)劃會(huì)跑偏
周一上班再來(lái)仔細(xì)追查一下sql的執(zhí)行計(jì)劃為什么會(huì)跑偏？
首先使用sqlhc來(lái)查看一下sql的具體執(zhí)行情況 ，sqlhc是oracle提供的一個(gè)sql性能調(diào)優(yōu)工具，具體可以查看如下文檔
SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
使用方法也簡(jiǎn)單，選擇模式（TDN），輸入需要診斷的sqlid
在當(dāng)前目錄下會(huì)生成sqlhc_20240318_1324_54ftmkvm9z5kj.zip壓縮包
壓縮包下有如下幾個(gè)文件，這里重點(diǎn)關(guān)注2/3文件
diagnostics summary
關(guān)鍵信息1：sql plan summary
可以看到這個(gè)sql有五個(gè)執(zhí)行計(jì)劃，第五個(gè)是最差的
關(guān)鍵信息2 ：sql statistics
可以看到10號(hào)前后執(zhí)行計(jì)劃都是最優(yōu)的4062773594
可以看到問(wèn)題時(shí)段有幾個(gè)執(zhí)行計(jì)劃選擇了最差的2891383906，也就是造成系統(tǒng)卡頓的主要原因
關(guān)鍵信息3：查看具體的sql plan
找到有問(wèn)題的sql plan
在sqlplan的最后有note 關(guān)鍵信息
cardinality feedback used for this statement，表面改執(zhí)行計(jì)劃是因?yàn)榛鶖?shù)反饋而從新生成的執(zhí)行計(jì)劃
另外一個(gè)520秒的異常sql plan也是因?yàn)榛鶖?shù)反饋
3.何為基數(shù)反饋？
Cardinality Feedback是11gR2出現(xiàn)的新特性(ps:12C后改名為Statistics Feedback，統(tǒng)計(jì)反饋，本文還是以個(gè)人習(xí)慣稱為基數(shù)反饋 ），基數(shù)反饋是優(yōu)化器自動(dòng)改進(jìn)對(duì)基數(shù)估計(jì)錯(cuò)誤的重復(fù)查詢的計(jì)劃的能力。由于多種原因，優(yōu)化器可能不正確地估算基數(shù)，例如缺少統(tǒng)計(jì)信息、不準(zhǔn)確的統(tǒng)計(jì)信息或復(fù)雜的謂詞。基數(shù)反饋幫助優(yōu)化器從錯(cuò)誤計(jì)算中學(xué)習(xí)，以便使用更準(zhǔn)確的基數(shù)估計(jì)生成更好的計(jì)劃。
基數(shù)反饋是如何工作
即使統(tǒng)計(jì)數(shù)據(jù)被盡可能準(zhǔn)確地計(jì)算，估計(jì)的基數(shù)可能也是不準(zhǔn)確的。在第一次執(zhí)行 SQL 語(yǔ)句時(shí)，會(huì)生成一個(gè)執(zhí)行計(jì)劃。在計(jì)劃優(yōu)化期間，會(huì)注意到某些類型的估算，并監(jiān)視生成的游標(biāo)。執(zhí)行完成后，計(jì)劃中的一些基數(shù)估算會(huì)與執(zhí)行期間實(shí)際觀察到的基數(shù)進(jìn)行比較。如果發(fā)現(xiàn)這些估算與實(shí)際基數(shù)存在顯著差異，則會(huì)存儲(chǔ)更正后的基數(shù)以供以后使用。下次執(zhí)行查詢時(shí)，將再次對(duì)其進(jìn)行優(yōu)化（硬解析），而這次優(yōu)化器將使用這些更正后的估算值來(lái)替代之前使用的原始估算值?；诟鼫?zhǔn)確的統(tǒng)計(jì)數(shù)據(jù)可能會(huì)創(chuàng)建不同的計(jì)劃。
Oracle 能夠使用統(tǒng)計(jì)反饋重復(fù)地重新優(yōu)化語(yǔ)句。這可能是必要的，因?yàn)榛鶖?shù)差異可能取決于計(jì)劃的結(jié)構(gòu)和形狀。因此，在第二次執(zhí)行查詢時(shí)，使用統(tǒng)計(jì)反饋生成新計(jì)劃后，仍可能發(fā)現(xiàn)更多的基數(shù)估算與實(shí)際基數(shù)存在顯著偏差。在這種情況下，Oracle 可以在下次執(zhí)行時(shí)再次重新優(yōu)化。
但是，有一些保障措施可確保在少數(shù)執(zhí)行后這種情況將穩(wěn)定下來(lái)，因此您可能會(huì)在最初的幾次執(zhí)行中看到計(jì)劃的變化，但最終將選擇出一個(gè)計(jì)劃，并用于所有后續(xù)的執(zhí)行。
流程圖如下
官方建議什么情況下適合啟用基數(shù)反饋
- 沒(méi)有統(tǒng)計(jì)信息且未使用動(dòng)態(tài)采樣表
- 表上有多個(gè)連接或分離的過(guò)濾謂詞，且沒(méi)有擴(kuò)展統(tǒng)計(jì)信息
- 包含復(fù)雜運(yùn)算符的謂詞，優(yōu)化器無(wú)法準(zhǔn)確計(jì)算選擇性估算值
在某些情況下，還有其他可用的技術(shù)來(lái)改善估算；例如，動(dòng)態(tài)采樣或多列統(tǒng)計(jì)允許優(yōu)化器更準(zhǔn)確地估算連接謂詞的選擇性。在這些技術(shù)適用的情況下，Statistics Feedback將不被啟用。
然而，如果對(duì)于相關(guān)列的組合不存在多列統(tǒng)計(jì)信息，則優(yōu)化器可以回退到使用Statistics Feedback。
如何關(guān)閉基數(shù)反饋
基數(shù)反饋有隱含參數(shù)_OPTIMIZER_USE_FEEDBACK控制，默認(rèn)是開(kāi)啟的 ，可以在session和system級(jí)別關(guān)閉
1.會(huì)話級(jí)別或者系統(tǒng)級(jí)別關(guān)閉基數(shù)反饋
alter session set "_OPTIMIZER_USE_FEEDBACK" = FALSE;
alter system set "_OPTIMIZER_USE_FEEDBACK" = FALSE;
2.sql級(jí)別加hint
select /*+ opt_param('_optimizer_use_feedback' 'false') */ ...為什么基數(shù)反饋后執(zhí)行計(jì)劃反而變壞
參考Bug 16837274 - Cardinality feedback produces poor subsequent plan (Doc ID 16837274.8)
Description
A suboptimal execution plan may be produced due to cardinality feedback for
the object on the right side of NLJ .（nested loops join）
Rediscovery Notes
Bad plan due to cardinality feedback for the object on the right side of NLJ.
Workaround
Set "_optimizer_use_feedback"=false
Note:
This fix effectively fixes all of the cases fixed by Bug 13454409 and should
be used instead of that fix.
基數(shù)反饋是否會(huì)經(jīng)常發(fā)生
是的 只要游標(biāo)被share pool剔除，再次被加載到share pool時(shí)就會(huì)觸發(fā)基數(shù)反饋
4.處理辦法
數(shù)據(jù)庫(kù)當(dāng)前統(tǒng)計(jì)信息正常，近期未有DDL相關(guān)操作，出問(wèn)題的sql確實(shí)存在 復(fù)雜的表連接和大量的nested loops，而且也不是第一次出現(xiàn)這種情況
綜合以上信息在系統(tǒng)級(jí)別關(guān)閉了技術(shù)反饋功能，并將不好的sql plan flush出share pool！
如何將sql plan 刷出share pool
1.Find ADDRESS and HASH_VALUE using SQL_ID
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '<SQL_ID>';
Example:
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='XXXXXXXXXXX';
ADDRESS HASH_VALUE
---------------- ----------
000000085FD77CF0 808321886
2) Now purge the plan from Shared pool using DBMS_SHARED_POOL procedure
SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');
PL/SQL procedure successfully completed.
NOTE:
‘C’ (for cursor) or ‘S’ (for SQL)
3) Check the shared pool again after the purge successfully completes which should show no rows.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='XXXXXXXXXXX';
no rows selected
sqlhc+coe_xfr_sql_profile.sql 可以關(guān)注我公眾號(hào)點(diǎn)sqlhc 就可得到網(wǎng)盤(pán)連接
參考文檔
 |
