由于sys的统计信息过旧，awr进程mmon不工作？
awr进程mmon不工作情况1：
生产环境的一套系统awr突然不自动产生了。手动执行create能创建。通过开sr确定，是由于系统中sys统计信息过旧，导致不能正常生成awr
现象：
从ORAODS1_mmon_194475.trc中，有下列信息
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*** 2018-03-19 20:38:15.253 <<<<<<<<在这个时间点后，就没有awr报告产生
*** KEWRRFC: Remote Flush slave failed, return Code: 4.
*** 2018-03-19 20:38:30.256
…………
*** 2018-03-22 15:13:14.337 <<<<<<<后面报下列的告警
Unable to schedule a MMON slave at: Auto Flush Main 1
Slave action has been temporarily suspended
- Slave action had prior policy violations.
Unknown return code: 101
*** 2018-03-22 15:15:02.717
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
在具体的trace文件中
LOG FILE
-----------------------
Filename =ORAODS1_m000_320630.trc
See the following error:
*** 2018-03-22 02:37:45.198
*** SESSION ID:(2613.20879) 2018-03-22 02:37:45.198
*** CLIENT ID:() 2018-03-22 02:37:45.198
*** SERVICE NAME:(SYS$BACKGROUND) 2018-03-22 02:37:45.198
*** MODULE NAME:(MMON_SLAVE) 2018-03-22 02:37:45.198
*** ACTION NAME:(Auto-Flush Slave Action) 2018-03-22 02:37:45.198
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
Time limit violation detected at:
ksedsts <- kspol_12751_dump <- dbgdaExecuteAction <- dbgerRunAction <- dbgerRunActions
<- dbgexPhaseII <- dbgexProcessError <- dbgeExecuteForError <- dbgePostErrorKGE <- dbkePostKGE_kgsf
<- kgeselv <- ksucin <- qerfxFetch <- qerjotRowProc <- qerhjInnerProbeHashTable
<- qerfxFetch <- rwsfcd <- qerhjFetch <- qerjotFetch <- rwsfcd
<- qerltcFetch <- insexe <- opiexe <- kpoal8 <- opiodr
<- rpiswu2 <- kpoodr <- upirtrc <- kpurcsc <- kpuexec
<- OCIStmtExecute <- kewrose_oci_stmt_exec <- kewrgwxf1_gwrsql_exft_1 <- kewrgwxf_gwrsql_exft <- kewrews_execute_wr_sql
<- kewrftbs_flush_table_by_sql <- kewrfsb_flush_sqlbind <- kewrft_flush_table <- kewrftec_flush_table_ehdlcx <- kewrftsq_flush_topsql
<- kewrft_flush_table <- kewrftec_flush_table_ehdlcx <- kewrfat_flush_all_tables <- kewrfos_flush_onesnap <- kewrafs_auto_flush_slave
<- kebm_slave_main <- ksvrdp <- opirip <- opidrv <- sou2o
<- opimai_real <- ssthrdmain <- main
解决方法：
不要在高负载的时候收集，因为统计信息收集到某表时会invalidate该表上所有的sql游标，造成该表之上的sql需要重新硬解析。对于sys表来说，它上面的递归sql更多，所以对于高负载的系统来说建议在业务空闲时执行。
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
参考资料：
AWR Snapshots Are Not Generated due to ORA-12751 (文档 ID 2243670.1)
In this Document
AWR does not generate with error with ORA-12751 cpu time or run time policy violation
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.
SYMPTOMS
- Following message is also shown in the alert log:
"Suspending MMON slave action kewrmafsa_ for 82800 seconds"
- Following message is shown in MMON slave trace:
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) -----
Runtime exceeded 900 seconds
Time limit violation detected at:
ksedsts()+465<-kspol_12751_dump()+145<-dbgdaExecuteAction()+1065<-dbgerRunAction()+109<-dbgerRunActions()+4134<-dbgexPhaseII()+1873<-dbgexProcessError()+2680<-dbgeExecuteForError()+88<-dbgePostErrorKGE()+2136<-dbkePostKGE_kgsf()+71<-kgeselv()+276<-ksesecl0()+162<-ksucin()+147<-qerfxFetch()+5073<-qerjotRowProc()+359<-qerfxFetch()+1106<-qerjotFetch()+2025<-rwsfcd()+103<-qeruaFetch()+574<-qersoProcessULS()+223<-ersoFetch()+6131<-qervwFetch()+139<-qergsFetch()+757<-rwsfcd()+103<-qerltcFetch()+1223<-insexe()+691<-opiexe()+5632
CHANGES
N/A
CAUSE
Stale SYS object statistics cause suboptimal execution plans to be generated, making the statements for AWR Auto-Flush Slave Action to run longer and time-out.
SOLUTION
Collect new SYS objects statistics to provide optimizer with better stats and generate more efficient execution plans:
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
REFERENCES
NOTE:457926.1 - How to Gather Statistics on Objects Owned by the 'SYS' User and 'Fixed' Objects
NOTE:798257.1 - Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations
备注：
How to Gather Statistics on Objects Owned by the 'SYS' User and 'Fixed' Objects (文档 ID 457926.1) 中提到，由于是收集的动态性能视图。所有在系统有一定负载的时候，执行EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;动态性能视图统计信息收集。
awr报表统计信息不收集情况2：
Error ORA-32701 'On Current SQL: insert into wrh$_sql_bind_metadata' (文档 ID 2226216.1)
在alertlog中报下列错误。
Errors in file /u01/app/oracle/diag/rdbms/oraods/ORAODS1/trace/ORAODS1_dia0_194398.trc (incident=304445):
ORA-32701: Possible hangs up to hang ID=465 detected
Incident details in: /u01/app/oracle/diag/rdbms/oraods/ORAODS1/incident/incdir_304445/ORAODS1_dia0_194398_i304445.trc
DIA0 terminating blocker (ospid: 203282 sid: 2481 ser#: 63437) of hang with ID = 465
requested by master DIA0 process on instance 1
Hang Resolution Reason: Although the number of affected sessions did not
justify automatic hang resolution initially, this previously ignored
hang was automatically resolved.
by terminating the process ospid:203282
DIA0 successfully terminated process ospid:203282.
DIA0 successfully resolved a GLOBAL, HIGH confidence hang with ID=465.
current sql: insert into wrh$_sql_bind_metadata (snap_id, dbid, sql_id, name, position, dup_position, datatype, datatype_string, character_sid, precision,
scale, max_length) SELECT /*+ ordered use_nl(bnd) index(bnd sql_id) */ :lah_snap_id, :dbid, bnd.sql_id, name, position, dup_position, datatype, dataty
和文档2226216.1情况比较match
解决方法：
1. Collect statistics on following fixed table:
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRATTRNEW');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRSQLIDTAB');
PL/SQL procedure successfully completed.
Or
2. Restarting the database will release of X$KQLFBC table data
Or
3. Flush shared_pool on a regular basis
++文档
In this Document
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 12.1.0.2 [Release 11.2 to 12.1]
Information in this document applies to any platform.
SYMPTOMS
- ORA-32701 on both instances of a RAC database:
Errors in file /u01/app/oracle/diag/rdbms/eraeconp/eraeconp1/trace/eraeconp1_dia0_83031.trc (incident=271771):
ORA-32701: Possible hangs up to hang ID=88 detected
Incident details in: /u01/app/oracle/diag/rdbms/eraeconp/eraeconp1/incident/incdir_271771/eraeconp1_dia0_83031_i271771.trc
Sun Dec 11 19:38:18 2016
Sweep [inc][271771]: completed
Sweep [inc2][271771]: completed
Errors in file /u01/app/oracle/diag/rdbms/eraeconp/eraeconp1/trace/eraeconp1_dia0_83031.trc (incident=271772): - Tracefile shows following function and sql as 'insert into wrh$_sql_bind_metadata':
Short stack dump:
ksedsts()+244<-ksdxfstk()+58<-ksdxcb()+918<-sspuser()+224<-__sighandler()<-kghalf()+263<-kqlfbctc()+830<-kgligi()+40<-kglic_cbk()+373<-kglic0()+692<-kglic()+78<-kqlfbct()+436<-qerfxFetch()+5141<-qerjotRowProc()+381<-qerhjWalkHashBucket()+524<-qerhjInnerProbeHashTable()+775<-qerfxFetch()+1946<-rwsfcd()+120<-qerhjFetch()+1100<-qerjotFetch()+1522<-rwsfcd()+120<-qerltcFetch()+1209<-insexe()+742<-opiexe()+5615<-kpoal8()+2876<-opiodr()+1165<-kpoodrc()+42<-rpiswu2()+2277<-kpoodr()+674<-upirtrc()+2410<-kpurcsc()+102<-kpuexec()+10930<-OCIStmtExecute()+41<-kewrose_oci_stmt_exec()+78<-kewrgwxf1_gwrsql_exft_1()+735<-kewrgwxf_gwrsql_exft()+540<-kewrfdbs_flush_data_by_sql()+276<-kewrfabt_flush_attribute_table()+1025<-kewrfsb_flush_sqlbind()+60<-kewrft_flush_table()+427<-kewrftec_flush_table_ehdlcx()+1112<-kewrftsq_flush_topsql()+552<-kewrft_flush_table()+427<-kewrftec_flush_table_ehdlcx()+1112<-kewrfat_flush_all_tables()+680<-kewrfop_flush_one_phase()+766<-kewrfap_flush_all_phases()+386<-kewrflrs_flush_local_racslv_snap()+91<-kewrfrss_flush_racslv_snap()+222<-kewrrfs_remote_flush_slave()+1936<-kebm_slave_main()+497<-ksvrdp()+1936<-opirip()+679<-opidrv()+616<-sou2o()+145<-opimai_real()+270<-ssthrdmain()+412<-main()+236<-__libc_start_main()+253-------------------------------------------------------------------------------
Process diagnostic dump actual duration=3.810000 sec
(max dump time=15.000000 sec)*** 2016-12-11 19:38:21.706
[TOC00004-END]
current SQL: insert into wrh$_sql_bind_metadata (dbid, con_dbid, snap_id, sql_id, name, position, dup_position, datatype, datatype_string, character_sid, precision, scale, max_length ) SELECT /*+ ordered use_nl(bnd) index(bnd sql_id) */ :dbid, :srcdbid con_dbid, :lah_snap_id, bnd.sql_id, name, position, dup_posi
CAUSE
View v$sqlbind_capture corresponds to fixed table X$KQLFBC table which is mainly used to store variables associated with the binding of data. This error can be noticed in large databases using large amount of binding variables.
SOLUTION
1. Collect statistics on following fixed table:
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRATTRNEW');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRSQLIDTAB');
PL/SQL procedure successfully completed.
Or
2. Restarting the database will release of X$KQLFBC table data
Or
3. Flush shared_pool on a regular basis
REFERENCES
BUG:23082542 - CREATE AWR SNAPSHOT FAILED DUE PERFORMANCE ON WRH$_SQL_BIND_METADATA
BUG:22733141 - AWR SNAPSHOTS NOT CREATED, GATHERING STATS ON X$KQLFBC HANGS
BUG:23022578 - DBMS_STATS.GATHER_FIXED_OBJECT_STATS HANGS ON X$KQLFSQCE
