Archive for the ‘High Version Count’ Category
Purging a cursor in Oracle – revisited
A few years ago I created a post about “how to flush a cursor out the shared pool“, using DBMS_SHARED_POOL.PURGE. For the most part, this method has helped me to get rid of an entire parent cursor and all child cursors for a given SQL, but more often than not I have found than on 12c this method may not work, leaving active a set of cursors I want to flush.
Script below is an enhanced version, where besides using DBMS_SHARED_POOL.PURGE, we also create a dummy SQL patch, then drop it. This method seems to completely flush parent and child cursors. Why using this method instead?: We are implementing SQL Plan Management (SPM), and we have found that in some cases, some child cursors are still shared several hours after a SQL Plan Baseline (SPB) is created. We could argue a possible bug and pursue as such, but in the meantime my quick and dirty workaround is: whenever I want to flush an individual parent cursor for one SQL, and all of its child cursors, I just execute script below passing SQL_ID.
Anyways, just wanted to share and document this purge_cursor.sql script for those in similar need. I have developed it on 12.1.0.2, and haven’t tested it on lower or higher versions.
-- purge_cursor.sql DECLARE l_name VARCHAR2(64); l_sql_text CLOB; BEGIN -- get address, hash_value and sql text SELECT address||','||hash_value, sql_fulltext INTO l_name, l_sql_text FROM v$sqlarea WHERE sql_id = '&&sql_id.'; -- not always does the job SYS.DBMS_SHARED_POOL.PURGE ( name => l_name, flag => 'C', heaps => 1 ); -- create fake sql patch SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH ( sql_text => l_sql_text, hint_text => 'NULL', name => 'purge_&&sql_id.', description => 'PURGE CURSOR', category => 'DEFAULT', validate => TRUE ); -- drop fake sql patch SYS.DBMS_SQLDIAG.DROP_SQL_PATCH ( name => 'purge_&&sql_id.', ignore => TRUE ); END; /
Poor’s man script to summarize reasons WHY cursors are not shared
Having a large number of child cursors can affect parsing performance as hinted by Abel Macias on his blog post about Diagnosis of a High Version Count (HVC). On his post, Abel also refers to a note on MOS which includes a script that dives into the reasons WHY our cursors are not getting shared. Then, for deep-dives in this area, I strongly suggest to read his post and use the referenced script provided at MOS.
Besides longer parse times, and potential library cache contention, manifested by some waits (such as on mutex), there is another side effect that may bite us: CBO may produce a different plan when a SQL statement is hard-parsed while creating a new child cursor. This latter side effect can be critical for transactional applications with SLA depending on very short latencies of some queries.
This post is about a poor’s man script, that with no installation whatsoever, it lists an aggregated summary of the reasons why our cursors are not shared, including child cursor counts and distinct SQL_IDs counts for each reason (see sample output below). I had to write such script since in our environments we cannot simply run diagnostics scripts that create objects in the database, such as the one provided by MOS.
CURSORS SQL_IDS REASON_NOT_SHARED
---------- ---------- -----------------------------
226916 7826 ROLL_INVALID_MISMATCH
29387 105 BIND_EQUIV_FAILURE
21794 4027 HASH_MATCH_FAILED
11588 2134 OPTIMIZER_MISMATCH
11027 413 BIND_LENGTH_UPGRADEABLE
11008 384 BIND_MISMATCH
10125 2697 USE_FEEDBACK_STATS
4540 109 OPTIMIZER_MODE_MISMATCH
1652 72 PURGED_CURSOR
1245 81 BIND_UACS_DIFF
1062 316 LANGUAGE_MISMATCH
771 103 LOAD_OPTIMIZER_STATS
500 52 STATS_ROW_MISMATCH
238 86 MV_QUERY_GEN_MISMATCH
94 3 MULTI_PX_MISMATCH
28 4 AUTH_CHECK_MISMATCH
23 1 INSUFF_PRIVS
Once I get to see some reasons for not sharing, some responsible for a large number of child cursors (and distinct SQL_IDs), then I can search on MOS as Abel suggested. Ideally, if you are interested in plan stability, you may want to reduce the times the CBO is tasked to create a new child cursor (and potentially a new Execution Plan).
In output sample above, top in our list is ROLL_INVALID_MISMATCH, causing 226,916 child cursors in as many as 7,826 SQL statements. This particular reason for not sharing cursors is due to a persistent gathering of schema object statistics with the explicit request to invalidate cursors. Since we want to promote plan stability, we would need to suspend such aggressive gathering of CBO statistics and validate reason ROLL_INVALID_MISMATCH is reduced.
Anyways, free script used is below. Enjoy it!
*** edited *** a new version of the script is now available (below). Thanks to stewashton for his input.
-- sql_shared_cursor.sql SET HEA OFF LIN 300 NEWP NONE PAGES 0 FEED OFF ECHO OFF VER OFF TRIMS ON TRIM ON TI OFF TIMI OFF SQLBL ON BLO . RECSEP OFF; SPO all_reasons.sql SELECT CASE WHEN ROWNUM = 1 THEN '( ' ELSE ', ' END||column_name FROM dba_tab_columns WHERE table_name = 'V_$SQL_SHARED_CURSOR' AND owner = 'SYS' AND data_type = 'VARCHAR2' AND data_length = 1 / SPO OFF; GET all_reasons.sql I ) I ) I WHERE value = 'Y' I GROUP BY reason_not_shared I ORDER BY cursors DESC, sql_ids DESC, reason_not_shared 0 ( value FOR reason_not_shared IN 0 FROM v$sql_shared_cursor UNPIVOT 0 SELECT COUNT(*) cursors, COUNT(DISTINCT sql_id) sql_ids, reason_not_shared L SET HEA ON NEWP 1 PAGES 30 PRO please wait / !rm all_reasons.sql
Carlos Sierra's Tools and Tips 