如何「暴力破解」Oracle性能優化的極端問題（附精彩案例解讀）

  • 2019 年 12 月 10 日
  • 筆記

本期，我們請到雲和恩墨性能優化專家 羅海雄 先生，羅老師是一名 Oracle ACE-A，還是ITPUB論壇資料庫管理版版主，2012 ITPUB全國SQL大賽冠軍得主；資深的架構師和性能優化專家，對 SQL 優化和理解尤其深入；作為業內知名的技術傳播者之一，經常出席各類技術分享活動；從開發到性能管理，有著超過10年的企業級系統設計和優化經驗；曾服務於甲骨文公司，組織和主講過多次《甲骨文技術開發人員日》和《Oracle圓桌會議》，在任職甲骨文公司之前，還曾經服務於大型製造企業中芯國際，具備豐富的製造行業系統架構經驗。

本次嘉年華他帶來題為：Oracle性能優化之暴力破解 主題分享。下面，讓我們跟隨羅老師的文字，一同回顧屬於他的嘉年華精彩瞬間。

在網上曾經有一位前Oracle公司員工描述過：Oracle 資料庫 12.2，它有近 2500 萬行 C 程式碼。

這有多恐怖，簡直難以想像！你無法在不破壞成千上萬個現有測試的情況下更改產品中的單行程式碼。好幾代程式設計師在有限的項目期限內編寫了這些程式碼，其中充斥著大量的垃圾程式碼。 非常複雜的邏輯、記憶體管理、上下文切換等，這些都用數千個 flag 連接起來。整個程式碼充斥著神秘的宏命令，如果不拿出筆記型電腦，並且手動去展開相關的宏命令，就無法理清楚這些命令。甚至可能需要一兩天才能真正理解某個宏命令的作用。 有時你需要理順 20 個不同 flag 的值和效果來預測程式碼在不同情況下的行為方式，有時多達數百個 flag ！這一點也不誇張。 這個產品仍然存活並且仍然可用的唯一原因是數百萬次的測試！

看起來確實很恐怖，但這正是Oracle設計縝密的地方。Oracle在程式碼中埋入了很多性能探針，還添加了很多可選擇的程式碼旁路開關。

性能探針給了使用者很多的有用資訊，對於資料庫的優化和診斷來說，可以獲益不少。而各種程式碼執行的旁路開關，也可以讓使用者在特定的情況下，選擇合適自己環境的路徑。

在Oracle 10.2 之後，Oracle對很多bug fix添加了旁路開關，允許通過設置初始化參數或者會話級參數，跳過或啟用某個bug fix引入的程式碼邏輯。

這個參數就是 「_fix_control」。

它就是一個旁路開關。下面，我簡單介紹下使用方法：

在會話中不啟用某個bug的fix：

Alter session set 「_fix_control」=「bugno:off」;  Alter session set 「_fix_control」=「bugno:0」;

在會話中啟用某個bug的fix： 

Alter session set 「_fix_control」=「bugno:on」;  Alter session set 「_fix_control」=「bugno:1」;

在會話中指定多個_fix_control參數：

Alter session set 「_fix_control」=「bug1:0」,」bug2:1」…,」bugn:off;

當然，把alter session改為alter system就變成系統級生效了。

正常情況下，我們都是已知一個bug, 然後通過控制這個bug的fix_control參數來關閉或啟用。但有時候，我們在不知道哪個bug影響了系統時，也可以通過「暴力破解」的方式來發現具體的bug。

下面從一個性能故障案例來說明如何進行「暴力破解」。

某客戶系統突然CPU飆升，大量活動會話積壓

大量以前毫秒級的SQL, 突然變成平均執行3-5秒

SQL並不複雜：

select count(distinct(t1.company))   from V_xxxx_USER t1, V_xxxx_APPLY t2  where t1.id = t2.id and t1.cert_no = :1  and t2.insert_time > sysdate – 90

其中有兩個視圖：

V_xxxx_USER:

select  ID,... from c_xxx_user  union all  select 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

視圖中的2個表，都有很好選擇度的索引：

c_xxx_user: cert_no  xxxx_p_info: cert_no

V_xxxx_APPLY：

select ID,... from C_xxxx_APPLY  union all   select a.app_no ID, ... from xxxx_MAIN  C_xxxx_APPLY: id

視圖中的2個表，也都有很好選擇度的索引：

xxxx_MAIN: id

C_xxxx_APPLY: id

看看執行計劃，出現3個新的執行計劃，性能非常差，而好的執行計劃單次僅為0.7ms：

細看執行計劃，好的執行計劃cost 372：

差的3個執行計劃，cost都是286k:

好和差的執行計劃最大的區別是，好的走了UNION ALL PUSHED PREDICATE。

而差的執行計劃沒走UNION ALL PUSHED PREDICATE，也就導致沒有用上視圖裡兩個表的id索引，走了其他索引或者乾脆全表掃，因此性能變得非常差。

嘗試 SQL Profile 和 SPM進行綁定：

DECLARE  clsql_text CLOB;  BEGIN  SELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = 『&SQL_ID』; DBMS_SQLTUNE.IMPORT_SQL_PROFILE(  sql_text => clsql_text,  profile => sqlprof_attr(….),  name => 'PROFILE_&SQL_ID',  force_match => TRUE );  END;  /

但都無法穩定生效，時而複發。

優化器選擇執行計劃的基本原則是選COST低的執行計劃。而異變/異常的通常現象是執行計劃評估cost錯誤，導致差的執行計劃COST比好的執行計劃低。顯然，這次的問題，沒有選擇cost低的執行計劃。

這時候，開始懷疑是某個優化器參數或者某個bug fix導致的問題，於是開始考慮嘗試測試一些優化器參數或者_fix_control參數。

由於一開始不清楚是哪個優化器參數或者_fix_control參數，手動逐個測試顯然太慢，只能考慮用自動程式碼來遍歷。

所以，思路就是：

1. 遍歷優化器相關參數+遍歷_fix_control參數

2. 修改改參數

3. 檢查問題有沒解決

顯然，由於單次執行很慢，直接通過執行SQL的方式來檢查，耗時太大，不可行。考慮到問題的關鍵是執行計劃中沒有用到UNION ALL PUSHED PREDICATE，可以通過僅僅生成執行計劃，並檢查執行計劃中是否存在UNION ALL PUSHED PREDICATE步驟的方式來判斷。

優化器相關參數的來源有3個：

v$sys_optimizer_env或者v$sess_optimizer_env

x$ksppi+x$ksppcv (v$parameter的基表)

10053 trace里的內容

……

_fix_control參數來源：

v$system_fix_control或者 v$session_fix_control;

示例程式碼：

Declare  Target_value number;  begin  For bugs in (select * from v$session_fix_control  where sid = (select sid from v$mystat where rownum=1)  ) loop  If(bugs.value=1 ) then  Target_value :=0;  Else  Target_value:=1;  End if;  // fix_control  execute immediate 'alter session set "_fix_control"='『』||bugs.bugno||':』||target_value||''『』;  Execute immediate 『explain plan set statement_id = 『|| bug.bugno ||『 for  … <有問題的SQL> …』;   execute immediate 'alter session set "_fix_control"='『』||bugs.bugno||':』||bugs.value||''『』;  end Loop;  For para in (select name,value,decode(value,』TRUE』,』FALSE』,』FALSE』,』TRUE』) target_value  from …  where value in (』TRUE』,』FALSE』)  ) loop  //優化器參數  execute immediate 'alter session set "'|| para.name||'"=『||para.target_value;  … --探測  execute immediate 'alter session set "'|| para.name||'"=『||para.value;  …

然後，檢查哪個fix_control或者初始化參數能解決問題：

Select statement_id from plan_table  Where operation = 『UNION ALL PUSHED PREDICATE』;

最終發現是BUG 4127058 導致。

這就是我想要與大家進行分享的，謝謝！

PPT下載鏈接：https://www.modb.pro/doc/1366

VirMach 便宜 VPS

QNews

QNews