ORA-00600 [17059]: Script to determine reason(s) for high Child Cursor Count - V$SQL_SHARED_CURSOR
(Doc ID 1422573.1)
Last updated on APRIL 06, 2020
Applies to:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
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.
-------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - ... - - - -
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
- This script could be useful to diagnosing the reason for a huge number of child cursors.
- It might also be very useful to run the script as an initial step on any ORA-00600 [17059] or ORA-00600 [15206] occurrence as this particular error is reported whenever the maximum number of child cursors was reached.
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 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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |
| References |