Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

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...
Prepared Statement Error: Table './oraclebasecms/cms_page_comment_uuids' is marked as crashed and should be repaired