問題：生產(chǎn)庫RAC其中一節(jié)點無法自動生產(chǎn)快照

查看Mmonslave進程m000的trace文件蹦魔，出現(xiàn)以下報錯：

*** KEWROCISTMTEXEC - encountered error: (ORA-12751: cpu time or run time policy

violation)

*** SQLSTR: total-len=295, dump-len=240,

STR={insertinto WRH$_SERVICE_STAT(snap_id, dbid,instance_number,service_name_hash,stat_id, value) select:snap_id, :dbid,:instance_number,stat.service_name_hash, stat.stat_id, stat.value fromv$active_services asvc, v$service_st}

DDE rules only execution for: ORA 12751

----- START Event Driven Actions Dump ----

---- END Event Driven Actions Dump ----

----- START DDE Actions Dump -----

Executing SYNC actions

Executing ASYNC actions

----- START DDE Action: 'ORA_12751_DUMP' (Sync) -----

CPU time exceeded 300 seconds

手動執(zhí)行awr快照很慢，約10分鐘唇聘，查看活動會話版姑，awr快照會話在執(zhí)行以下SQL：

insert into WRH$_SERVICE_STAT

(snap_id, dbid, instance_number, service_name_hash, stat_id, value)

select

stat.service_name_hash,

stat.stat_id,

stat.value

from v$active_services asvc, v$service_stats stat

where asvc.name_hash = stat.service_name_hash

順理查看該SQL為何執(zhí)行緩慢柱搜，發(fā)現(xiàn)v$service_stats視圖中記錄數(shù)超多迟郎。

聯(lián)想到在做expdp備份時，alert日志中會出現(xiàn)修改service name的片段聪蘸。

expdp每次備份開始宪肖，都會新增一個service name表制，備份結(jié)束后會去掉該service name，該動作會記錄在alert log中：

    Tue Apr 26 18:15:48 2016
    ALTER SYSTEM SET service_names='***','***','***','SYS$SYS.KUPC$C_2_20160426181545.db' SCOPE=MEMORY SID='db2';
    ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_2_20160426181545.db','***','***','***','SYS$SYS.KUPC$S_2_20160426181545.db' SCOPE=MEMORY SID='db2';
    Tue Apr 26 18:15:49 2016
    DM00 started with pid=782, OS id=5830, job SYSTEM.SYS_EXPORT_TABLE_02
    Tue Apr 26 18:15:52 2016
    DW00 started with pid=943, OS id=5832, wid=1, job SYSTEM.SYS_EXPORT_TABLE_02
    Tue Apr 26 18:15:59 2016
    ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_2_20160426181545.db','***','***','***' SCOPE=MEMORY SID='db2';
    ALTER SYSTEM SET service_names='***','***','***' SCOPE=MEMORY SID='db2';

這個動作就會導(dǎo)致v$service_stats 視圖出現(xiàn)很多unknown的記錄

Paste_Image.png

expdp的問題相關(guān)資料：

    Bug 10421418 : UNKNOWN SERVICE_NAME IN V$SERVICE_STATS EVERY TIME CREATE/DELETE SERVICES
    On RAC, expdp Removes the Service Name [ID 1269319.1] 
    Bug 11927911 : THE AWR SNAPSHOT IS NOT OBTAINED
    Bug 5974572 : WRH$_SERVICE_STAT CAN GROW VERY LARGE ON RAC

通過以上現(xiàn)象和mos資料以及測試控乾，可以基本判定原因：
1 數(shù)據(jù)泵bug導(dǎo)致每次邏輯導(dǎo)出時么介，會在v$service_stats視圖中增加56條service_name=unknow的記錄，由于在該節(jié)點的每日數(shù)據(jù)泵備份任務(wù)蜕衡，導(dǎo)致v$service_stats視圖中累積存儲了大量unknow service name的記錄（如上圖所見庫中已有25W條）壤短；
2 AWR快照生成過程中在執(zhí)行上述SQL時，由于fixed table統(tǒng)計信息不準(zhǔn)確或者尚無統(tǒng)計信息慨仿，oracle選擇了效率較低的執(zhí)行計劃久脯，SQL的執(zhí)行消耗大量時間，導(dǎo)致oracle維護任務(wù)cpu time policy violation镰吆，AWR快照生成中斷帘撰。

針對AWR快照中間sql執(zhí)行過慢的解決辦法：手動收集fixed table的統(tǒng)計信息（約執(zhí)行14min），執(zhí)行計劃改變万皿，效率提升摧找。
--Before 12c, the fixed stats statistics were not gathered automatically

Begin
      DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(no_invalidate => false);
end;

原執(zhí)行計劃：

Paste_Image.png

新執(zhí)行計劃：

Paste_Image.png

收集fixed table的統(tǒng)計信息后，AWR快照可以正常生成：

Paste_Image.png