The column is there for a long time – even 9i documentation have it. I’ve never thought about it until today when I caought something extraordinary on 188.8.131.52 instance.
Starting with 10g oracle introduced SQL_ID for simplicity; it is used in combination with CHILD_NUMBER to locate a particular row in the V$SQL. The documentation is clear (bold is mine):
V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text entered
Today I’ve seen a query that was running too long on a 184.108.40.206 instance. I’ve tried to get its runtime execution plan with DBMS_XPLA.DISPLAY_CURSOR, as I usually do, and it took literally a minute to get it although I’ve supplied CHILD_NUMBER and there was no much load on the DB. It was strange. When I got the result I was astonished. DISPLAY_CURSOR selected more than 80K rows and the plan was a total mess composed of numerous identical plans merged together. WTF?! I’ve started looking around and noticed that parent cursor has 1800+ child cursors – absolutely no good situation. Since there’s no v$sql_unshared_cursor (yet :)) to show why there are so many child cursors in easy way, I wrote a query to return something like this with what I have right now – V$SQL_SHARED_CURSOR + DBA_TAB_COLS:
select * from (select sql_id, nonshared_reason, count(*) from v$sql_shared_cursor unpivot (nonshared_value for nonshared_reason in ( UNBOUND_CURSOR as 'UNBOUND_CURSOR', SQL_TYPE_MISMATCH as 'SQL_TYPE_MISMATCH', OPTIMIZER_MISMATCH as 'OPTIMIZER_MISMATCH', OUTLINE_MISMATCH as 'OUTLINE_MISMATCH', STATS_ROW_MISMATCH as 'STATS_ROW_MISMATCH', LITERAL_MISMATCH as 'LITERAL_MISMATCH', FORCE_HARD_PARSE as 'FORCE_HARD_PARSE', EXPLAIN_PLAN_CURSOR as 'EXPLAIN_PLAN_CURSOR', BUFFERED_DML_MISMATCH as 'BUFFERED_DML_MISMATCH', PDML_ENV_MISMATCH as 'PDML_ENV_MISMATCH', INST_DRTLD_MISMATCH as 'INST_DRTLD_MISMATCH', SLAVE_QC_MISMATCH as 'SLAVE_QC_MISMATCH', TYPECHECK_MISMATCH as 'TYPECHECK_MISMATCH', AUTH_CHECK_MISMATCH as 'AUTH_CHECK_MISMATCH', BIND_MISMATCH as 'BIND_MISMATCH', DESCRIBE_MISMATCH as 'DESCRIBE_MISMATCH', LANGUAGE_MISMATCH as 'LANGUAGE_MISMATCH', TRANSLATION_MISMATCH as 'TRANSLATION_MISMATCH', BIND_EQUIV_FAILURE as 'BIND_EQUIV_FAILURE', INSUFF_PRIVS as 'INSUFF_PRIVS', INSUFF_PRIVS_REM as 'INSUFF_PRIVS_REM', REMOTE_TRANS_MISMATCH as 'REMOTE_TRANS_MISMATCH', LOGMINER_SESSION_MISMATCH as 'LOGMINER_SESSION_MISMATCH', INCOMP_LTRL_MISMATCH as 'INCOMP_LTRL_MISMATCH', OVERLAP_TIME_MISMATCH as 'OVERLAP_TIME_MISMATCH', EDITION_MISMATCH as 'EDITION_MISMATCH', MV_QUERY_GEN_MISMATCH as 'MV_QUERY_GEN_MISMATCH', USER_BIND_PEEK_MISMATCH as 'USER_BIND_PEEK_MISMATCH', TYPCHK_DEP_MISMATCH as 'TYPCHK_DEP_MISMATCH', NO_TRIGGER_MISMATCH as 'NO_TRIGGER_MISMATCH', FLASHBACK_CURSOR as 'FLASHBACK_CURSOR', ANYDATA_TRANSFORMATION as 'ANYDATA_TRANSFORMATION', PDDL_ENV_MISMATCH as 'PDDL_ENV_MISMATCH', TOP_LEVEL_RPI_CURSOR as 'TOP_LEVEL_RPI_CURSOR', DIFFERENT_LONG_LENGTH as 'DIFFERENT_LONG_LENGTH', LOGICAL_STANDBY_APPLY as 'LOGICAL_STANDBY_APPLY', DIFF_CALL_DURN as 'DIFF_CALL_DURN', BIND_UACS_DIFF as 'BIND_UACS_DIFF', PLSQL_CMP_SWITCHS_DIFF as 'PLSQL_CMP_SWITCHS_DIFF', CURSOR_PARTS_MISMATCH as 'CURSOR_PARTS_MISMATCH', STB_OBJECT_MISMATCH as 'STB_OBJECT_MISMATCH', CROSSEDITION_TRIGGER_MISMATCH as 'CROSSEDITION_TRIGGER_MISMATCH', PQ_SLAVE_MISMATCH as 'PQ_SLAVE_MISMATCH', TOP_LEVEL_DDL_MISMATCH as 'TOP_LEVEL_DDL_MISMATCH', MULTI_PX_MISMATCH as 'MULTI_PX_MISMATCH', BIND_PEEKED_PQ_MISMATCH as 'BIND_PEEKED_PQ_MISMATCH', MV_REWRITE_MISMATCH as 'MV_REWRITE_MISMATCH', ROLL_INVALID_MISMATCH as 'ROLL_INVALID_MISMATCH', OPTIMIZER_MODE_MISMATCH as 'OPTIMIZER_MODE_MISMATCH', PX_MISMATCH as 'PX_MISMATCH', MV_STALEOBJ_MISMATCH as 'MV_STALEOBJ_MISMATCH', FLASHBACK_TABLE_MISMATCH as 'FLASHBACK_TABLE_MISMATCH', LITREP_COMP_MISMATCH as 'LITREP_COMP_MISMATCH', PLSQL_DEBUG as 'PLSQL_DEBUG', LOAD_OPTIMIZER_STATS as 'LOAD_OPTIMIZER_STATS', ACL_MISMATCH as 'ACL_MISMATCH', FLASHBACK_ARCHIVE_MISMATCH as 'FLASHBACK_ARCHIVE_MISMATCH', LOCK_USER_SCHEMA_FAILED as 'LOCK_USER_SCHEMA_FAILED', REMOTE_MAPPING_MISMATCH as 'REMOTE_MAPPING_MISMATCH', LOAD_RUNTIME_HEAP_FAILED as 'LOAD_RUNTIME_HEAP_FAILED', HASH_MATCH_FAILED as 'HASH_MATCH_FAILED', PURGED_CURSOR as 'PURGED_CURSOR', BIND_LENGTH_UPGRADEABLE as 'BIND_LENGTH_UPGRADEABLE', USE_FEEDBACK_STATS as 'USE_FEEDBACK_STATS' )) where nonshared_value = 'Y' group by sql_id, nonshared_reason ) where sql_id = :sql_id
And the result was
NONSHARED_REASON COUNT(*) ----------------------------- -------------------- INSUFF_PRIVS_REM 1815 AUTH_CHECK_MISMATCH 1815
The query uses DB links, so I was not surprised to see the reason of non-shared cursors as “Insufficient privileges on remote objects referenced by the existing child cursor”. But why so many of them? I don’t know. Maybe it is supposed to work this way – if an error is caused by insufficient privileges on a remote system, Oracle marks child as “wrong” and tries again next time with a new child. It sounds like an easy implementation option but looks very clumsy.
I looked at a particular child cursor and…
SQL> select sql_id, child_address from v$sql where sql_id = '22d06uj1ut8d4' and child_number = 63; SQL_ID CHILD_ADDRESS ------------- ---------------- 22d06uj1ut8d4 00000002C76328D0 22d06uj1ut8d4 00000002CF528C90 22d06uj1ut8d4 00000002D45C6E58 22d06uj1ut8d4 00000002AF4C1470 22d06uj1ut8d4 00000002AF3D8C10 22d06uj1ut8d4 00000002C78AD830 22d06uj1ut8d4 00000002971BF538 22d06uj1ut8d4 00000002864ABFF0 22d06uj1ut8d4 00000002B7FAC550 22d06uj1ut8d4 00000002BFC95E50 22d06uj1ut8d4 00000002A7AB1B98 22d06uj1ut8d4 000000029E76EAA8 22d06uj1ut8d4 00000002B67A7C38 22d06uj1ut8d4 000000027F23AFF8 22d06uj1ut8d4 0000000276EF9040 22d06uj1ut8d4 00000002969C4A68 22d06uj1ut8d4 00000002BEC691C8 22d06uj1ut8d4 00000002869F2AF0 18 rows selected.
Wow. This just can’t be right. Next I run a query to see if this behavior is system-wide or cursor-specific and it turned out it’s cursor-specific. No other cursors have more than 1 row with the same SQL_ID and CHILD_NUMBER in V$SQL. After some more experiments it turned out that the statement from documentation for this particular cursor is incorrect; and a correct version would have been written as
V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each not obsolete child of the original SQL text entered
SQL> select sql_id, child_number, is_obsolete, max(cnt) 2 from (select sql_id 3 ,child_number 4 ,is_obsolete 5 ,count(*) over(partition by sql_id, child_number, is_obsolete) cnt 6 ,count(*) over(partition by sql_id, child_number) total_cnt 7 from v$sql) 8 where total_cnt > 1 9 group by sql_id, child_number, is_obsolete 10 order by sql_id, child_number; Child SQL_ID number I MAX(CNT) ------------- ------ - -------------------- 22d06uj1ut8d4 0 N 1 22d06uj1ut8d4 0 Y 14 22d06uj1ut8d4 1 N 1 22d06uj1ut8d4 1 Y 14 22d06uj1ut8d4 2 N 1 ... 22d06uj1ut8d4 95 Y 15 22d06uj1ut8d4 96 Y 15 22d06uj1ut8d4 97 Y 15 22d06uj1ut8d4 98 Y 15 22d06uj1ut8d4 99 Y 15
So the pattern is: either there is just 1 non obsolete child cursor for a given child number and some obsolete children or there’re obsolete children only. I don’t have an explanation of what is going on, but clearly, it looks like a bug. Even Oracle’s code to show an execution plan can’t handle this situation right. I also think it may have something to do with bug 10187168.8.
Please leave a comment here if you see something similar in your 220.127.116.11 environment. You can use a query to find such evil cursors with a query (but please be careful – it’s not safe to run it on a production system)
select sql_id, avg(cnt), sum(cnt), count(*) from (select sql_id, child_number, count(*) cnt from v$sql group by sql_id, child_number having count(*) > 1) group by sql_id