暴力破解Oracle數(shù)據(jù)庫性能問題

1、暴力破解 oracle性能問題從一個性能故障案例開始某客戶系統(tǒng)突然CPU飆升，大量活動會話積壓大量以前毫秒級的SQL, 突然變成平均執(zhí)行3-5秒執(zhí)行計劃異變fromSQL不復(fù)雜：select count(distinct(pany) V_xxxx_USER t1, V_xxxx_APPLY t2where t1.id = t2.idand t2.cert_no = :1 and t2.insert_time sysdate 90好的執(zhí)行計劃，cost很低差的執(zhí)行計劃，cost極高訪問的是視圖兩個視圖： V_xxxx_USER:selectID,. from c_apply_userunion

2、allselect b.app_no ID,. from xxxx_p_info b, xxxx_info xxxx_info where b.app_no = e1.app_no and b.app_no = e2.app_no V_xxxx_APPLY:select ID,. from C_xxxx_APPLYunion allselect a.app_no ID, . from xxxx_MAIN執(zhí)行計劃最大的區(qū)別是 UNION ALL PUSHED PREDICATEselect count(distinct(pany)from V_xxxx_USER t1, V_xxxx_APPLY

3、 t2where t1.id = t2.id View中兩個表id列都有相應(yīng)索引，且選擇性極高and t2.cert_no = :1 View中兩個表cert_no列都有相應(yīng)索引，且選擇性極高 and t2.insert_time sysdate 90UNION ALL PUSHED PREDICATE不生效是關(guān)鍵嘗試解決嘗試 SQL Profile 和 SPM進行綁定 DECLAREclsql_text CLOB; BEGINSELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = &SQL_ID; DBMS_SQLT

4、UNE.IMPORT_SQL_PROFILE(sql_text =clsql_text,profile = sqlprof_attr(.),name = PROFILE_&SQL_ID,force_match = TRUE ); END;/竟然都很不穩(wěn)定，時而生效，時而不生效10053、MOS文檔均無法迅速定位問題關(guān)鍵優(yōu)化器選擇執(zhí)行計劃的基本原則： 選COST低的執(zhí)行計劃異變/異常的通?，F(xiàn)象：執(zhí)行計劃評估錯誤，導(dǎo)致差的執(zhí)行計劃COST比好的執(zhí)行計劃低為何選擇了一個幾十萬COST的執(zhí)行計劃而不選擇COST幾百的執(zhí)行計劃？ SQL Profile/SPM 為何不生效?暴力解決思路Oracle里充滿

5、了各種探針、flag、開關(guān)Oracle 數(shù)據(jù)庫 12.2，它有近 2500 萬行 C 代碼。這有多恐怖，簡直難以想象！你無法在不破壞成千上萬個現(xiàn)有測試的情況下更改產(chǎn)品中的單行代碼。好幾代程序 員在有限的項目期限內(nèi)編寫了這些代碼，其中充斥著大量的垃圾代碼。非常復(fù)雜的邏輯、內(nèi)存管理、上下文切換等，這些都用數(shù)千個 flag 連接起來。整個代碼充斥著神秘的宏命令， 如果不拿出筆記本，并且手動去展開相關(guān)的宏命令，就無法理清楚這些命令。甚至可能需要一兩天才能真正理解 某個宏命令的作用。有時你需要理順 20 個不同 flag 的值和效果來預(yù)測代碼在不同情況下的行為方式，有時多達數(shù)百個 flag ！這一 點也

6、不夸張。這個產(chǎn)品仍然存活并且仍然可用的唯一原因是數(shù)百萬次的測試！引用自某 Oracle研發(fā)你知道優(yōu)化器里有多少個參數(shù)嗎？以O(shè)racle 為例Select count(*) from v$sys_optimizer_env;64Select count(*) from x$ksppi where ksppinm like %optimizer%201Alter session set events10053 trace name context forever,level 1;550特殊的參數(shù)：_fix_controlOracle 新引入的一個參數(shù)目的是回退某個bug帶來的行為 相當(dāng)于一個短路開

7、關(guān)不啟用某個bug的fixAlter session set “_fix_control”=“bugno:off”; Alter session set “_fix_control”=“bugno:0”;啟用某個bug的fixAlter session set “_fix_control”=“bugno:on”; Alter session set “_fix_control”=“bugno:1”;指定多個：Alter session set “_fix_control”=“bug1:0”,”bug2:1”,”bugn:off;v$system_fix_control/v$session_fi

8、x_controlSelect * from v$system_fix_control; Bugno: BUG 號Value: 1開啟，0未開啟Optimizer_Feature_enable: 跟隨某個Optimizer_Feature_enable參數(shù)生效暴力解決思路這么多開關(guān)，逐一開啟和關(guān)閉測試一下效果。對于優(yōu)化器參數(shù)把FALSE 改為 TRUE 把TRUE改為FALSE對于Bug Fix把 1/on 改為 0/off 把 0/off 改為 1/on修改fix_control的PLSQL代碼示例DeclareTarget_value number; beginFor bugs in (s

9、elect * from v$session_fix_controlwhere sid = (select sid from v$mystat where rownum=1) loop If(bugs.value=1 ) then Target_value :=0; ElseTarget_value:=1; End if;execute immediate alter session set _fix_control=|bugs.bugno|:|target_value|; -探測execute immediate alter session set _fix_control=|bugs.bu

10、gno|:|bugs.value|;修改fix_control的PLSQL代碼示例beginFor bugs in (select bugno,value,decode(value,1,0,0,1) target_value from v$session_fix_controlwhere sid = (select sid from v$mystat where rownum=1) and value in (0,1) loopexecute immediate alter session set _fix_control=|bugs.bugno|:|bugs.target_value|; -

11、探測execute immediate alter session set _fix_control=|bugs.bugno|:|bugs.value|;修改優(yōu)化器參數(shù)的PLSQL代碼示例選擇v$sys_optimizer_env/x$ksppi 還是10053作為輸入？10053最全面，但需要人為load數(shù)據(jù)。只考慮可簡單開關(guān)的參數(shù): value in (TRUE,FALSE)beginFor para in (select name,value,decode(value,TRUE,FALSE,FALSE,TRUE) target_value from where value in (TRU

12、E,FALSE) loopexecute immediate alter session set | |=|para.target_value; -探測execute immediate alter session set | |=|para.value;如何探測是否成功？1500-2000個開關(guān)，直接執(zhí)行顯然太慢對于多數(shù)SQL性能問題，問題的關(guān)鍵是執(zhí)行計劃走錯 第一步：找到?jīng)]問題的執(zhí)行計劃。第二步： 找到不一樣的關(guān)鍵步驟如何探測是否成功？第三步： 使用explain plan 生成執(zhí)行計劃，注意set statement_idexecute immediate alter session s

13、et | |=|para.target_value; Execute immediate explain plan set statement_id = | bug.bugno | for ;execute immediate alter session set | |=|para.value;第四步：從PLAN_TABLE中找到生效的Bug Fix開關(guān)或者優(yōu)化器參數(shù)開關(guān) Select statement_id from plan_tableWhere operation = UNION ALL PUSHED PREDICATE6987082BUG 6987082Bug 6987082Over

14、estimated Multicolumn Selectivity/ Join Predicate Not Pushed into UNION ViewDescription This Fix addresses 2 different unrelated problemsCost-based Join Predicate Push Down (JPPD) in union view does not occur if index is promoted in a single branch due to the restrictions imposed by bug 4127058.Inco

15、rrect Multi-Column Join Selectivity is disabled (possibly1.0)This fix is disabled by default.To enable : alter session set _fix_control=6987082:on;altersystemset_fix_control=6987082:on;BUG 4127058select * from v$system_fix_control where bugno = 4127058; - no rows selected案例2：奇怪的報錯客戶升級到12c, 通過DB Link

16、的某SQL出現(xiàn)報selecta.start_date,a.user_id,duct_id frompdw.K_xxx_TYPE a, pdw.K_xxx_USER b whereduct_mode=:aand a.user_id = b.user_id;ORA-00932: inconsistent datatypes: expected got 背景SQL涉及兩個對象均為兩個遠程DB Link表的union Create view .AsSelect id,start_date,productcode From t1dblink UnionSelect id,start_date,produ

17、ctcode From t2dblink11g可以正常運行各種嘗試均告失敗確認date類型欄位數(shù)據(jù), 未發(fā)現(xiàn)異常數(shù)據(jù)修改語句，直接拿null 或者 sysdate 替換該列數(shù)據(jù)，依然抱錯 Create view .AsSelect id,cast (null as date) start_date,productcode From t1dblink UnionSelect id,null start_date,productcode From t2dblink10046、10053、errorstack trace 均無特殊發(fā)現(xiàn)只好再次暴力出擊執(zhí)行計劃未發(fā)現(xiàn)異常, 且執(zhí)行計劃主體在遠程數(shù)據(jù)庫，

18、無法通過explainplan 判斷出錯時每次執(zhí)行不到一秒就報錯不出錯是每次執(zhí)行100多秒加快分析速度的小技巧由于是升級出來的錯誤，可以只看12相關(guān)的bugs.Select * from V$xxx_fixed_controlwhere optmizer_feature_enable in (,) 可以大幅減少探測次數(shù)。順利找到問題beginFor bugs in (select bugno,value,decode(value,1,0,0,1) target_value from v$session_fix_controlwhere sid = (select sid from v$myst

19、at where rownum=1)andoptimizer_feature_enablelike12%) loopexecute immediate alter session set _fix_control=|bugs.bugno|:|bugs.target_value|; BeginExecute immediate SQL; Exception when others thenNull; - 有報錯不管End;Dbms_output.put_line(bugs.bugno| |bugs.target_value); -輸出能正常執(zhí)行的bug fixexecute immediate

20、alter session set _fix_control=|bugs.bugno|:|bugs.value|;BUG: 20232513altersystemset_fix_control=6987082:off;其他暴力？有時候有些等待事件異常高漲但無法定位其原因需要尋找關(guān)聯(lián)Oracle有多個數(shù)據(jù)源可供搜索AWR裸數(shù)據(jù)中的變化規(guī)律是否存在哪些指標(biāo)變化規(guī)律與其相關(guān)？ 那也許就是問題的根因？AWR裸數(shù)據(jù)中的變化規(guī)律指標(biāo)太多，逐個檢查超出人力范圍Oracle采集了數(shù)千個性能指標(biāo) DBA_HIST_SYSSTAT DBA_HIST_SYSMETRIC_SUMMARY DBA_HIST_LATCH_SUMMRY DBA_HIST_ROWCACHE_SUMMARY.DBA_HIST_UNDOSTAT難道都畫成圖肉眼檢查？暴力的統(tǒng)計函數(shù) corrCorr是Oracle內(nèi)置的一個對兩組數(shù)據(jù)求相關(guān)性的一個函數(shù)，變化規(guī)律 越相近，返回值越接近1。如果規(guī)律越相反，則會越接近-1指標(biāo)獲取select e.snap_id, -對應(yīng)快照e.instance_number, event_name,