Wednesday, April 15, 2009

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.

at

5 comments:

  1. coskan3:43 PM, April 16, 2009

    Hi Momen,

    Can you also put the definition of index ? Is it a simple b-tree index or something different

    Thanks

    ReplyDelete
  2. Asif Momen2:07 PM, April 17, 2009

    Coskan,

    Yes, it is a simple b-tree index. Here's the definition:


    SQL> set pages 5000
    SQL> set lines 10000
    SQL> set long 50000

    SQL> select dbms_metadata.get_ddl('INDEX','IDX_COMMIT_DET','PAYMAST') from dual;

    DBMS_METADATA.GET_DDL('INDEX','IDX_COMMIT_DET','PAYMAST')
    --------------------------------------------------------------------------------

    CREATE INDEX "PAYMAST"."IDX_COMMIT_DET" ON "PAYMAST"."COMMIT_DETAILS" ("COMMIT_REFE
    RENCE", "TERM_NO", "COMMIT_TYPE")
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "VOU_TS"

    ReplyDelete
  3. Anonymous12:14 PM, May 28, 2009

    We have the same error on our production environment.
    select * from v$sql_plan where object_type='INDEX';

    Did you have a response for this problem?

    Thank you.

    ReplyDelete
  4. Anonymous12:19 PM, May 28, 2009

    Did you have a response by Oracle for this problem?

    ReplyDelete
  5. Anonymous8:16 AM, January 19, 2012

    Workaround does not crash but “some information will be missing”:

    ORA-07445 [MSQSUB()+32] When Select From V$SQL_PLAN [ID 791225.1]

    Bug 7022234 is Fixed in 11.2
    Patches exist for it on most platforms on top of 10.2.0.4 at the moment of writing this note

    1- apply the patch 7022234 if available
    2- If the patch is not available you may use the following workaround
    alter session set “_cursor_plan_unparse_enabled”=false;

    ReplyDelete