Ever have a need to do something with the library cache where it would be just plain convenient if the SQL always hard parsed? I’ve discussed the issues with bind variables.
In recent articles and blogs, I’ve discussed Oracle’s work on bind variable processing, particularly when the data is skewed. Oracle9 introduced bind variable peaking…where the contents of a bind variable are reviewed (just once though) on a hard parse. Prior to this, bind variable content was ignored and even data distribution was considered.
Oracle11 addressed this skewing of data in bind variables by implementing Adaptive Cursor Sharing. This feature requires a histogram on the bind variable column and will monitor SQL execution times. IF Oracle notices the execution time is off, it will check the histogram for different cardinality on the bind variable column and possibly produce another child cursor. I’m not totally clear how the CBO keeps track of these but I do have syntax that shows each of the child cursors.
The upside…is…this works and is simple to implement.
The downside…is…the SQL has to execute poorly…quite possibly super poorly once…once per different bind variable content …
I have long advocated that we could use a HARD_PARSE hint as even with these advances in monitoring and re-hard parsing (creating additional child cursors), these SQL statements still need to execute poorly once for Oracle to recognize there is an issue with the explain plans.
I’ve asked the optimizer group for such a hint…they think they don’t need such a hint…
If we had a hard parse hint, we could include in the SQL that is known to be working with skewed data…insuring a better execution time for every SQL, not most…or executing poorly once…
SO, here is how you write your own hint! I’ll use the ‘hard parse’ option above to create a hint and related code that will purge this SQL from the library cache…thus…re-hard parsing when it is submitted again.
First, you need to find the SQL and retrieve its ADDRESS and HASH_VALUEs.
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_TEXT like '%HARD_PARSE%';
ADDRESS HASH_VALUE---------------- ----------000000085FD77CF0 808321886
You can put in any text you like but if we focus on my example, we will create our own ‘hard parse’ hint.
Then you use the DBMS_SHARED_POOL.Purge procedure to remove this SQL from the library cache…thus…allowing for a hard parse to occur when the SQL is submitted again.
SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');
This PL/SQL code will purge the SQL when it shows up in the library cache:
declarecursor sql_cur is select address, hash_value, sql_id from v_sqlarea where sql_text like '%HARD_PARSE%';
for sql_rec in sql_curloop DBMS_SHARED_POOL.PURGE(sql_rec.address || ',' || sql_rec.hash_value , 'C'); DBMS_OUTPUT.PUT_LINE('SQL ID: || sql_rec.sql_id || ' purged from library cache ');end loop;
You can remove the DBMS_OUTPUT…that was there for display purposes.
There are 2 lines of thought on running this…
1) Schedule this code to run in DBMS_SCHEDULER…to run every minute…or how ever often you expect to see the SQL to be hard parsed…this would allow for a ‘hard parse’ hint to be used in a number of SQL statements perhaps.
2) A better place might be in the PL/SQL code, right after the SQL runs…and give it some /* specific text */ to be found…and purge it right after it executes. This would insure that the SQL will always be hard parsed.
As always…test this code! Make sure it works by perhaps running the 10046 SQL trace on either side of your test…run it several times…and make sure the SQL is always being parsed. I’m sure I’ve blogged on the 10046 trace…I do have scripts that turn on/off this trace and displays the trace file name. You also use TOAD/SQL DEVELOPER/TKProf against the trace file to give readable output.
I think this is a useful technique for problem SQL that contains bind variables.
Let me know what you think.
Dan HotkaOracle ACE DirectorInstructor/Author/CEO
If I have a plan with two child cursors, one is Ok and other is bad, but bad one is in use, how to purge according that (i.e. purge CHILD_NUMBER=2 only and left CHILD_NUMBER=1 alive)?