Numerous m000 trace files in which KEWROCISTMTEXEC encountered ORA-12899

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.