|Adaptive Cursor Sharing in Oracle Database 11g Release 1 - Overcome the problems associated with using bind variables against indexed columns containing skewed data.|
Jocelyn Simard said...Excellent article! Thanks a lot that make it clear.
That reveal what Oracle is actually doing: it run one bad execution (the one that end up setting is_bind_sensitive to 'Y') before it fugures out that it needs to set is_bind_aware to 'Y'. The documentation doesn't state that.
Lesio said...Please extend this article about parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES. If this parameter is set to TRUE acs is not working.
Coskan Gundogar said...Hi Tim, all series is excellent.
One quick question;
I followed the example exactly the same and my system generated same sql_id with the sql_id generated for you.
Is this normal and expected behaviour?
Aman.... said...I have the same doubt sir, I got the same sql id as per your demo. I didn't expect this to happen.Any reasons for this?
Excellent article(s) as like always!
Peter Wiseman said...Excellent article.
Some further testing (220.127.116.11) would indicate that it can require a lot more than one bad execution before switching to bind aware cursors. If there are N executions of the original peeked values, then there needs to be N bad executions, and then the subsequent execution creates the bind aware cursor.
And I would acs not to work if sql plan baselines are being captured. The additional plans will be created and added to the sql plan baselines as non-accepted plans. They will need to be manually accepted before they can be used.
Great work, your articles are very useful.
For inserting the data we can use the connect by trick on dual (found on asktom)
insert into acs_test_tab select level,decode(mod(level,2),0,2,level),'Description for '||level from dual connect by 1=1 and level<100001;
Taras said...2-nd execution comes with better plan and sets is_bind_aware=Y but what about first execution which still reuses plan generated by peeking ? For some app it might not be tolerable as 1-st execution might take forever before they even come to 2-nd one
True. It's an improvement, but it's not perfect. :)
Yogita Mishra said...Sir,
Your demo is very helpful for me but i think, that is not perfect because after using your demo code system sql_id is equivalent to your demo sql_id.
Sorry, I don't understand what you are saying. We expect a single SQL_ID with multiple execution plans. Is that what you are saying?
Lisa Tansey said...Great article, as usual. If you're interested, I found three typos you could correct:
peaks => peeks (only incorrect the first time it appears)
and example of adaptive cursor sharing => an example
as not acted on this yet=> has not acted
Thanks for the helpful doc! And the nifty coding tricks.
Thanks for the heads-up on those typos. They are corrected now. :)