Did you know that Oracle11 has figured out a way to solve SQL performance issues with SQL that contains bind variables?
Let me clarify this. Not all SQL with bind variables have issues. Just the SQL where the where clause item has skewed data…such as a name lookup on SMITH vs HOTKA.
Oracle 9 & 10…peek at the bind variable once on the hard parse, producing an explain plan that is efficient for the data submitted in the bind variable. This is good unless the data is highly skewed, skewed to the point of its more efficient to do a full table scan rather than use an available index.
This is the case with SMITH vs HOTKA. The first SQL in for a hard parse with HOTKA being passed via a bind variable would produce an explain plan using the available index. If you have been following my blogs…indexes do single-block reads. When the same SQL is requested but now has SMITH for its bind variable content, the SQL will use the same explain plan (a soft-parse because the SQL text has not changed…) and still uses the index. This SQL, now using SMITH as a lookup requirement, would have been far more efficient using a full-table scan and its multi-block read-ahead nature.
Oracle11 solved this with a concept known as Adaptive Cursor Sharing. This feature requires a histogram on the database column associated with the bind variable. The only real issue with this technology is that the SQL has to perform poorly as compared to its prior execution for this to kick in.
Here is how this works…the SQL is hard parsed. It executes. When requested again…it’s a soft-parse (i.e.: already has an explain plan) but takes quite a bit longer than the first execution (either SMITH or HOTKA going first then second would produce this…). Oracle11, with its knowledge stored in AWR, notices that this took longer and will use the histogram to research the bind variable content for a significant different row count than the prior execution. IF Oracle11 finds this to be true, then the SQL is re-hard-parsed producing a second child cursor! Oracle then keeps track of which one to execute based on bind variable row count.
Some additional info. Oracle11 will produce up to 5 different child cursors. These can be viewed using DBMS_XPLAN (yet another prior blog!). The database column has to have a histogram for this technology to kick in. No histogram, Oracle11 reverts to the Oracle9/10 behavior of bind variable peeking and only producing 1 child cursor.
So…run histograms on all bind variable related database columns! Let Oracle11 and 12 decide.
Oracle ACE Director
Oracle will not use AWR in order to notice the amount of work and time the current cursor has accomplished witht the current bind variable value. It uses instead the v$sql_cs_histogram view and particularly the couple (bucket_id, count) and another secret sauce I think I have partially discovered to mark a cursor bind aware. If you have time to read my two blog articles (the third will come later this month I hope) then don't hesitate to crittic them
Why are you saying that Oracle will produce up to 5 different child cursors? can you elaborate please?
Finally, there are two others conditions that might make a cursor bind sensitive in addition to the histogram pre-requisite. They are (a) range predicate and (b) when a partition key is used in the predicate part.
I am writing a complete chapter on that ACS feature I hope it will be published in an incoming book I am participating in writing it with other Oracle persons.