RE: Pinning/keeping plans in shared pool

So, you're at 10.2.0.2 on AIX 5.3 from 8.0.6. You have a couple of queries joining 20+ tables which performs OK on 806 and has a very long parse time with 10gR2.

You believe it's due to bind variable peeking; you ran the statement with literals instead and then the Parse phase is long and the Execute phase drops to 0.

You've tested lowering opmtizer_max_permutations by half which brought parse time down by about half and tested _b_tree_bitmap_plans=FALSE which also brought the parse time down.

You have cursor_space_for_time set to false which is the default (I think you should leave this alone in this case unless you have a large amount of memory available and can test it without affecting production).

You can't set these parameters [easily] in your app (Baan ERP), thus they would have to be set at the instance level which would probably not be a good idea as this problem is only affecting about 2 queries out of thousands.

You have statistics (at least at the table level) and they are gathered using the gather_stats_job - I'm looking at this being the potential source of the problem.

While I believe you may have success in using dbms_advanced_rewrite to get Oracle to use an equivalent bit of SQL to solve your immediate problem, it doesn't necessarily solve the root cause of it.

Do you have histograms on any of the columns in the where clause(s)?

Can you post a 10053 trace?

I'm very curious to see the outcome of this.

-----Original Message-----

From: Allen, Brandon [mailto:Brandon.Allen_at_OneNeck.com] Sent: Sunday, July 09, 2006 12:21 AM
To: ramick_at_dotster.com; Boris Dali; rjamya_at_gmail.com; tanel.poder.003_at_mail.ee
Cc: oracle-l_at_freelists.org
Subject: RE: Pinning/keeping plans in shared pool

I could use a stored outline, but I was hoping to avoid that. Someone else recommended using dbms_advanced_rewrite and from what I've read so far it looks promising so I'm going to do some testing with it next week. I'm still curious about why dbms_shared_pool.keep doesn't seem to be keeping the sql plan though - I'll ask Oracle support about it next week and come back to post their answer.

Thanks,
Brandon  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ramick

Can you use an outline?

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l