AWR實戰分析之----CPU 100%(smon_scn_time)

    smon_scn_time表和索引交叉引用失效導致伺服器性下降，CPU使用率100%，簡單記錄一下處理過程。

     從伺服器負載來看，負載非常小，session也在比較低的範圍內，如下圖所示

     
     從TOP 5上看，也沒有特殊的等待事件，如下圖所示
     
     從SQL ordered by Elapsed Time、SQL ordered by CUP Time等幾個模組來看，同樣一個表smon_scn_time的刪除操作有明顯異常，如下圖所示

     當時以為是業務上的以一表，用普通使用者連資料庫以後檢視提示不存在，再仔細一看錶名，像是sys下面的一個表，當時自己也不知道這個表做什麼的，網上查了一下，smon_scn_time是由smon程式維護的，記錄scn和time之間對應關係的一種表，此表是聚簇表並且是迴圈寫，意思是邊寫邊刪除，它是由SMON程式每隔5分鐘或10分鐘生成一次SCN和TIME之間的對映記錄，具體多少分鐘要看業務繁忙程度，並更新到SMON_SCN_TIME表中，同時刪除五天以前的記錄，該表的維護週期是5天最多能儲存144000條記錄，並且該表和索引交叉引用失效會導致伺服器效能下降（事後查資料才知道），而我遇到的情況更狠，直接CPU使用率100%了，資料庫版本是10.2.0.4的資料庫，因為是十一期間，其它同型別資料庫該表的記錄在1500-1700條記錄之間，而CPU使用率100%的資料庫記錄在87146條，明顯多出很多，並且該表的刪除操作從AWR上看已經嚴重不正常了。

     當時的處理方法是：（此方法並沒有解決根本問題）

     1> 檢視索引狀態

        select index_name,status,last_analyzed from dba_indexes where table_name='SMON_SCN_TIME';

        狀態是VALID 上次分析時間是2013-05-07小半年了沒分析了

     2> 檢視錶的上次統計資訊收集時間

        select last_analyzed from dba_tables where owner='SYS' and table_name='SMON_SCN_TIME'

        結果也是2013-05-07

        當時果斷以為是統計資訊過舊造成的執行效率低下，進行了如下處理

     3> 重創索引

        alter index SMON_SCN_TIME_SCN_IDX rebuild online;
        alter index SMON_SCN_TIME_SCN_IDX rebuild online;

     3> begin

             dbms_stats.gather_table_stats(ownname => 'SYS',

                                           tabname => 'SMON_SCN_TIME',

                                           cascade => TRUE);

         end;

        處理完以後，CPU並沒有很快降下來，停掉應用，大概過了十幾分鍾以後，CPU才逐步恢復到正常值，後來總感覺處理方法那裡出了問題，沒真正搞明白是怎麼回事，同時又看了伺服器上的AWR報告，delete from smon_scn_time這個操作還是有異常,又查詢了相關資料，此問題的根本原因在於索引和表的交叉引用失效導致的，並且rebuild index並不能解決問題，正確的方法方法是：

      1> 檢視錶和索引的狀態，此處省略，和上面一樣，要確保索引狀態是VALID狀態

      2> 判斷是否是因為表和索引交叉引用失效

         analyze table smon_scn_time validate structure cascade online;

         

         報錯很明顯，表和索引交叉引用失效，失效後會對錶smon_scn_time加一個排它鎖，並且不釋放，導致刪除效能下除，體現在AWR報告中，這是原因，下面進行驗證是否有排它鎖長期存在

      3> 先找查對像ID

         select object_id from dba_objects where object_name = 'SMON_SCN_TIME';

                576     

      4> 檢視是否存在鎖，以及鎖的模式

          select object_id,locked_mode from v$locked_object where object_id = '576';

                576          3    --排它鎖，並且多次重新整理一直存在，證明我們前期的猜測

      5> 表和交叉索引失效後，rebuilt索引並不能解決問題，只能drop index然後重新，刪除前需要備份建立

          DDL以防萬一（切記）

          --查詢索引

          select * from dba_indexes where table_name='SMON_SCN_TIME';

          --備份索引建立DDL
          select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') from dual;

          --備份索引建立DDL
          select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_SCN_IDX','SYS') FROM dual;      
       6> 刪除索引並重新建立

          --刪除索引

          drop index sys.SMON_SCN_TIME_TIM_IDX;
          drop index sys.SMON_SCN_TIME_SCN_IDX;

          注意，刪除時會提示ORA-00054: resource busy and acquire with NOWAIT specified是

          因為和索引交叉引用失效後，刪除語句找時間執行不能正常結束，此時只能不停的刪除嘗試，因為此

          處無法使用kill session方式進行處理，多試幾次等待刪除SQL執行完以後就可以正常刪除索。     

          --重新建立索引

          create unique index "sys"."smon_scn_time_tim_idx" on "sys"."smon_scn_time" ("time_mp");
          create unique index "sys"."smon_scn_time_scn_idx" on "sys"."smon_scn_time" ("scn");

       7> 重新驗證表和索引交叉引用是否正常

          analyze table smon_scn_time validate structure cascade online;

          --注意要用online否則將導致DML無法進行

          --此時無報錯，問題解決

       8> 檢視鎖已經消失

          select object_id,locked_mode from v$locked_object where object_id = '576';

          --此處無資料

       9>查詢並處理失效對像

          selec