ORA-00600 [17059]: Script to determine reason(s) for high Child Cursor Count - V$SQL_SHARED_CURSOR

(Doc ID 1422573.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Information in this document applies to any platform.

Goal

Problem
For SQL statements which are identical but are not being shared, the view V$SQL_SHARED_CURSOR can be used to determine why the cursors are not shared.

V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.

Due to the large number columns which describe the reasons for child cursors. Resultant from that, the output doesn't provide a good overview which makes it hard to diagnose problem cause.

SQL_ID         ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M ... H P B U
-------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - ... - - - -
fsdh8dn2tut06  BE6B2EE8 BE5FAE90            0 N N N N N N N N N N N N N N N N
N N N N N N N N N N N ... N N N N


The following script will help to report only the corresponding columns which are relevant for diagnosing the cause.


Usage

Note:
ORA-00600 [17059]: Child table size of 32768 exceeded.
ORA-00600 [15206]: Child table size of 65536 exceeded.



Prerequisites

It is recommended to execute the PLSQL block as privileged user. The DBA role covers all the necessary privileges to execute the PLSQL code, otherwise the SELECT privilege has to be granted to a non-privileged user before executing the PLSQL code below.

Note: The DBA role includes all the necessary privileges.

grant select on V_$SQL_SHARED_CURSOR to {user_name};
grant select on DBA_TAB_COLUMNS to {user_name};

select owner,table_name,privilege
from dba_tab_privs where grantee='{user_name}';

OWNER    TABLE_NAME                     PRIVILEGE
-------- ------------------------------ ------------------
SYS      V_$SQL_SHARED_CURSOR           SELECT
SYS      DBA_TAB_COLUMNS                SELECT

 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms