ORA-07445 When Querying V$SQL_PLAN View

On one of our production databases, I was looking for a particular index usage as how often was it used and at what times it was used. To achieve this, I did a little mining on AWR repository tables and found out that it was used 11 times in the last one month (AWR retention period).

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

Elapsed: 00:00:00.01
SQL>


SQL> select sp.sql_id,
  2         sp.options,
  3         count(1) cnt
  4    from dba_hist_sql_plan sp,
  5         dba_hist_sqlstat ss
  6    where sp.sql_id = ss.sql_id
  7      and sp.object_owner  =  'PAYMAST' 
  8      and sp.operation like '%INDEX%'
  9      and sp.object_name = 'IDX_COMMIT_DET'
 10  group by sp.sql_id, 
 11           sp.options
 12  order by sp.sql_id, 
 13           sp.options;

SQL_ID        OPTIONS                               CNT
------------- ------------------------------ ----------
1b4xf87ntvfgn SAMPLE FAST FULL SCAN                   1
8qaj7c6wqcyvg SAMPLE FAST FULL SCAN                   7
9jcrv4kunuhg4 SAMPLE FAST FULL SCAN                   1
afzq942kp848t RANGE SCAN                              1
ar6c0r4s4kntp RANGE SCAN                              1

Elapsed: 00:00:00.54
SQL>          

However, querying V$SQL_PLAN view for the same index threw ORA-03113 error. Hmm, I connected to the database and ran the same query again and it resulted in the same error.

SQL> select * from V$SQL_PLAN where  operation  ='INDEX' and object_name ='IDX_COMMIT_DET';

ADDRESS          HASH_VALUE SQL_ID        PLAN_HASH_VALUE CHILD_ADDRESS    CHILD_NUMBER TIMESTAMP       
---------------- ---------- ------------- --------------- ---------------- ------------ ------------
FILTER_PREDICATES                                                                                       
----------------------------------------------------------------------------------------------------
REMARKS                                                                                                 
----------------------------------------------------------------------------------------------------
07000000D98392B0 2773750041 afzq942kp848t       380836487 07000000D986D420            0 15-APR2009 12:2
"VOU_TYPE_NAME"=:B1                                                                                     


ERROR:
ORA-03113: end-of-file on communication channel



ERROR:
ORA-03114: not connected to ORACLE


Elapsed: 00:00:03.82
SQL>

Oracle has bumped some information into the alert log and here it is:

Errors in file /dbdata1/oradba/admin/PAYPROD/udump/payprod_ora_6488244.trc:
ORA-07445: exception encountered: core dump [msqsub+0008] [SIGSEGV] [Address not mapped to object] [0x35B000000000070] [] []

and it created a 18MB trace file in the USER_DUMP_DEST which has all the gory details for the Oracle Support to work on. Below is an excerpt from the trace file:

*** 2009-04-15 12:27:46.646
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [msqsub+0008] [SIGSEGV] [Address not mapped to object] [0x35B000000000070] [] []
Current SQL statement for this session:
select * from V$SQL_PLAN where  operation  ='INDEX' and object_name ='IDX_COMMIT_DET'

The ORA-00600/ORA-07445 Lookup Tool in Oracle Metalink reports “A description for this ORA-07445 error is not yet available”.

I have to end up by lodging an SR and wait for Oracle Support’s response.