8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | Linux

Home » Misc » Here

Comments for Adaptive Cursor Sharing in Oracle Database 11g Release 1

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

Peter Wiseman said...

Excellent article.

Some further testing ( 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...


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;


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...


True. It's an improvement, but it's not perfect. :)



Yogita Mishra said...


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...


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.

Tim... said...


Thanks for the heads-up on those typos. They are corrected now. :)



DO NOT ask technical questions here! They will be deleted!

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!