While clearing up some space in diag/trace directory, I noticed numerous trace files were being generated by the Memory Monitor slave process (m000). These trace files were being generated at regular intervals. Below is the contents of one of the files.
Trace file /u01/app/oracle/diag/rdbms/ora11gr1/ora11gr1/trace/ORA11GR1_m000_15456.trc Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /orabin/oracle/product/11g System name: Linux Node name: oelinux Release: 2.6.9-67.0.7.ELsmp Version: #1 SMP Wed Feb 27 04:47:23 EST 2008 Machine: x86_64 Instance name: ORA11G1 Redo thread mounted by this instance: 1 Oracle process number: 117 Unix process pid: 15456, image: oracle@oelinux (m000) *** 2011-04-20 13:00:20.188 *** SESSION ID:(932.48429) 2011-04-20 13:00:20.188 *** CLIENT ID:() 2011-04-20 13:00:20.188 *** SERVICE NAME:(SYS$BACKGROUND) 2011-04-20 13:00:20.188 *** MODULE NAME:(MMON_SLAVE) 2011-04-20 13:00:20.188 *** ACTION NAME:(Auto-Flush Slave Action) 2011-04-20 13:00:20.188 *** KEWROCISTMTEXEC - encountered error: (ORA-12899: value too large for column "SYS"."WRH$_LATCH_MISSES_SUMMARY"."WHERE_IN_CODE" (actual: 66, maximum: 64) ) *** SQLSTR: total-len=390, dump-len=240, STR={insert into wrh$_latch_misses_summary (snap_id, dbid, instance_number, parent_name, where_in_code, nwfail_count, sleep_count, wtr_slp_count) select :snap_id, :dbid, :instance_number, parent_name, "WHERE", sum(nwfail_count), sum(} *** KEWRAFM1: Error=13509 encountered by kewrfteh
The above error looks very similar to Oracle Support Note 330552.1 MMON generating Trace File ‘_m000_’ Frequently Ora-12899. In the case documented in 330552 the problems lies in the flushing of Active Session History Information. WRH$_ACTIVE_SESSION_HISTORY.PROGRAM
is a VARCHAR2(48)
whereas V$SESSION.PROGRAM
, the source of the data is VARCHAR2(64).
The suggested work around is to make the size of WRH$_ACTIVE_SESSION_HISTORY
match the size of V$SESSION.PROGRAM
.
The differences in the error in the trace file above and Oracle Support Note 330552 is that the operations is fetch instead of a flush and the table in question is WRH$_LATCH_MISSES_SUMMARY
.
The trace file does not have the entire query but you can find the query if it is still in the cursor cache or look through ASH (requires Tuning and Diagnostics Packs licenses). Below is the entire query.
insert into wrh$_latch_misses_summary (snap_id, dbid, instance_number, parent_name, where_in_code, nwfail_count, sleep_count, wtr_slp_count) select :snap_id, :dbid, :instance_number, parent_name, "WHERE", sum(nwfail_count), sum(sleep_count), sum(wtr_slp_count) from v$latch_misses where sleep_count > 0 group by parent_name, "WHERE" order by parent_name, "WHERE"
From the query we can see that WRH$_LATCH_MISSES_SUMMARY.WHERE_IN_CODE
gets its value from V$LATCH_MISSES.WHERE
. Looking at WRH$_LATCH_MISSES_SUMMARY
we can see that WHERE_IN_CODE
is a VARCHAR2(64)
.
SQL> describe WRH$_LATCH_MISSES_SUMMARY Name Null? Type ----------------------------------------- -------- ---------------------------- SNAP_ID NOT NULL NUMBER DBID NOT NULL NUMBER INSTANCE_NUMBER NOT NULL NUMBER PARENT_NAME NOT NULL VARCHAR2(50) WHERE_IN_CODE NOT NULL VARCHAR2(64) NWFAIL_COUNT NUMBER SLEEP_COUNT NUMBER WTR_SLP_COUNT NUMBER SQL>
The column V$LATCH_MISSES.WHERE is a VARCHAR2(80) which is the root of the ORA-12899
.
SQL> describe v$latch_misses Name Null? Type ----------------------------------------- -------- ---------------------------- PARENT_NAME VARCHAR2(64) WHERE VARCHAR2(80) NWFAIL_COUNT NUMBER SLEEP_COUNT NUMBER WTR_SLP_COUNT NUMBER LONGHOLD_COUNT NUMBER LOCATION VARCHAR2(80) SQL>
While it might seem to make sense that same solution to Oracle Support Note: 30552 would also resolve this issue this not the course of action to take at this time. Changing internal data dictionary tables or tables used for ASH/AWR is not something that should be done without the advice of Oracle Support. Keep in mind there is no mention of this particular case in the note and there is not a separate note that matches this issue. A quick look at an 11gR2 database reveled that in the case of Note 30552 the columns in question now have matching sizes. This is not the case with WRH$_LATCH_MISSES_SUMMARY
and V$LATCH_MISSES.WHERE
.
I have opened a support ticket with Oracle Support and will update this post later with more information and possibly a supported resolution.
Did you get an answer to this issue from Support? I am seeing something similiar in my version 10 database.
Hello Cyndy,
I do not think I got a chance to create the SR. If I remember correctly the project decided to go with 11gR2 which did not have this problem. Since you are seeing this in 10g database you might want to check note 330552.1 to see if it matches your case.
Eric
Nice Post