Oracle 變數窺視引起執行計劃異常故障分析

執行計劃異常分析與處理

 

故障描述：

SIEBEL  生產資料庫在定時統計資訊更新後,在兩節點的RAC中其中一個例項上有一個語句執行計劃發生改變，數百個程式同時掃描一個21G大小的表S_ORDER，主機資源耗盡導致例項無法響應而發生ORA-29740錯誤後CRASH。例項重新啟動後執行計劃又恢復正確。

 

故障現象：

 

1.  檢視v$session_longops表，可以看到大量對於S_ORDER的table scan。

select  sid,  serial  #,  username  ,  opname  ,  target  ,  round  (  totalwork  *  8  /  1024  ,  0  )  ||  'MB'  table_size  ,  round  (  sofar  /  totalwork  *  100  )  ||  '%'  pct  ,  start_time  ,  time_remaining  ,  elapsed_seconds  ,  sql_hash_value   from  v$session_longops

 

2.statspack  看到的等待事件

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     % Total

Event                                               Waits    Time (s) Ela Time

-------------------------------------------- ------------ ----------- ------

buffer busy global CR                           1,105,605     181,354    26.51

global cache cr request                        13,655,069     160,309    23.44

latch free                                      3,812,488     110,950    16.22

buffer busy waits                               5,073,632      84,394    12.34

db file scattered read                          8,261,925      78,891    11.53

 

 

 

原因分析：

除了更新統計資訊之外，系統環境，引數，應用版本等方面配置均沒有做任何改動。考慮到可能是由於Oracle9i版本上的特性bind peeking，引起了相應SQL語句在進行應解析時，產生了非最優的執行計劃，造成了效能的嚴重下降。

 

解決措施：

問題發生時，建議採取以下方法儘可能減少該問題對生產系統的影響。

 

診斷方法：

針對這個問題的深入分析需要生成故障發生時該SQL會話的TRACE資訊，以便確認引起本次效能問題的具體原因。生成TRACE資訊的具體步驟如下：（效能問題發生時執行）

1  ）alter session set events '10053 trace name context forever, level 1';

2  ）#執行存在問題的SQL語句

3  ）alter session set events '10053 trace name context off';

 

 

緊急處理：  （任選其中一種方法即可）

如果判斷該語句的大量併發將導致系統故障，需要按以下方法進行處理。

該問題的根本原因是由於一次硬解析時選擇了一個極端的非最優執行計劃。一般情況下，此時再出發一次硬解析時又會重新選擇正確的執行計劃。可以透過如下方法在該問題再次出現時，緊急處理。

1.     execute dbms_stat.set_table_stats(ownname=><user_name>, tabname=><table_name>, no_invalidation=>true);

替換其中的user_name和table_name為相應的使用者名稱及表名，如：

execute dbms_stats.set_table_stats(ownname=>'HR',tabname=>'REGIONS', no_invalidate=>true);

 

2.     grant  select on <table_name> to public;

revoke select on  <table_name> from public;

該步驟，如果第一句執行後已經產生了正確的執行計劃，則該後面的revoke可以選擇在非業務繁忙時段再做

 

3.     將表分析的資訊導回資料庫，找出異常的  SQL  的  hash_value:

select  'EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'  ||  ''''  ||object_owner||  ''''  ||  ','  ||  'TABNAME=>'  ||  ''''  ||object_name||  ''''  ||  ','  ||  'STATOWN=>'  ||  ''''  ||  'PERFSTAT'  ||  ''''  ||  ','  ||  'STATTAB=>'  ||  ''''  ||  'CTZJ_STAT'  ||  ''''  ||  ','  ||  'CASCADE=>TRUE'  ||  ','  ||  'STATID=>1'  ||  ','  ||  'no_invalidate=>true'  ||  ')'  ||  ';'  from  (select  distinct  object_name  ,  object_owner  from  v$sql_plan  where  operation  =  'TABLE ACCESS'  and  object_owner  =  'SIEBEL'  and  optimizer  =  'ANALYZED'  and  hash_value  =  'hash_value'  );

建議將相關的統計資訊全部匯入。

 

4.         對相關表進行統計資訊分析;

Exec dbms_stats.gather_table_stats(ownname=>’siebel’, tabname=>’S_ORDER’, estimate_percent=>  0.0001, cascade=>true);

Estimate_percent  可以根據情況進行調整。

 

5.      alter system flush shared_pool;  （慎重）

 

6.      重啟例項（慎重）

 

建議採取方法1或方法2，可以以較小的代價觸發新的硬解析，以期產生最優的執行計劃。同時，在利用以上方式使得原有的CURSOR失效之後， DBA可以使用擁有較好賦值的該語句手工執行一次，從而保證新的硬解析之後會產生最優的執行計劃，避免可能的新的硬解析之後又會產生非最優的執行計劃的情形。如果能夠保證使用較好的賦值的繫結變數的語句執行了第一次硬解析，則能夠保證由於該問題導致的系統效能下降會在該語句被正常執行後的很短時間內恢復正常。

 

方法5或方法6一般不建議採用。

 

 

 

後續工作：

在分析trace資訊後，如果確定確實由於bind peeking特定引起的執行計劃發生改變導致了效能下降，則可以考慮關閉該特性。  但是關閉該特性會引起資料庫無法根據實際的情況動態選擇最優執行計劃，請考慮對整體業務的影響。

關閉該特性的命令如下：

alter system set "_optim_peek_user_binds"=false scope=spfile

重啟資料庫

 

或者

 

在init.ora引數檔案中設定

"_optim_peek_user_binds"=false

重起資料庫使引數生效

在未分析trace資訊之前，不建議進行該引數設定。

 

附：  發現執行計劃異常可從幾個方面:

 

1.  從等待事件

select event,sql_hash_value,count(*) ,(count(*)*2-sum(inst_id)) RAC_1,(sum(inst_id)-count(*)) RAC_2,max(username) user1,min(username) user2 ,max(machine) machine1,min(machine) machine2 from (

select a.inst_id, event,username,decode(sql_hash_value,0,prev_hash_value,sql_hash_value) sql_hash_value,machine from gv$session_wait a,

gv$session b where a.inst_id=b.inst_id and a.sid=b.sid and a.event not in (select event from perfstat.stats$idle_event))

group by event,sql_hash_value  order by count(*) desc;

 

   如果看到有一語句上等待事件數較多  ,  很可能是執行計劃異常  ,  特別是只在其中一個例項上有等待  ,  可以確認是執行計劃異常  .

 

2.  從語句執行時間

select sid||','||serial# sid,status,username,logon_time,last_call_et,machine,process,sql_hash_value

,prev_hash_value,inst_id iid,'alter system kill session '''||sid||','||serial#||''';' kill_text

from gv$session where status<>'INACTIVE' and type='USER' and last_call_et>1 order by last_call_et desc;

如果看到有一語句上執行時間長  ,  很可能是執行計劃異常  ,  特別是只在其中一個例項上有  ,  可以確認是執行計劃異常  .

或

select sql_hash_value,count(*),round(avg(last_call_et)) avg_time

 from gv$session where status<>'INACTIVE' and type='USER' and last_call_et>1 and sql_hash_value<>0 group by sql_hash_value order by count(*) desc;

 

 

檢視執行計劃歷史確認執行計劃變化  :

select snap_time,a.* from perfstat.stats$sql_plan_usage a, perfstat.stats$snapshot b where a.snap_id=b.snap_id

and a.hash_value=:hash_value order by snap_time desc

 

確認後按處理過程文件方法進行處理  ,  注意相關的處理都會引起  libary cache pin,  需要慎重  ,  如果前臺沒有上報問題  ,  可等到中午  12:00  進行處理  .

 

按文件處理後  ,KILL  掉當前在執行的  session:

 

select 'alter system kill session '''||sid||','||serial#||''';' kill_text

from gv$session where status<>'INACTIVE' and type='USER' and sql_hash_value=:sql_hash_value and last_call_et>1 order by last_call_et desc