Comments
| 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! Best regards Aman.... |
Peter Wiseman said... Excellent article.Some further testing (11.1.0.7) 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. |
Bala said... Hi,Great work, your articles are very useful. One suggestion: 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; Regards, Bala |
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 |
Tim... said... Hi.True. It's an improvement, but it's not perfect. :) Cheers Tim... |
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. Thank You |
Tim... said... Hi.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? Cheers Tim... |
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. |
Tim... said... Hi.Thanks for the heads-up on those typos. They are corrected now. :) Cheers Tim... |
DO NOT ask technical questions here, that's what my forum is for!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!