RE: Long execute phase for SELECT query

Both of these parameters seem to be session modifiable - you could set one or both in just those sessions with SQL that have a large number of tables producing the large number of permutations...

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

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] Sent: Thursday, June 29, 2006 1:15 PM
To: Michael McMullen; oracle-l_at_freelists.org Subject: RE: Long execute phase for SELECT query

Oops - I just realized I misread your email - I thought it said optimizer_features_enable. Optimizer_max_permutations is hidden in 10g and defaults to 2000. I just tried setting it to 1000, and sure enough it cut the parse time in half - down to 6 seconds from 12 seconds, but still much longer than it was taking in 8.0.6 with the same setting (1000) for opmtizer_max_permutations. Next I tested with o_m_p=100 and that brought the parse time down to a respectable .95 seconds, but I don't think I want to go with that low of a setting instance wide though. I'd imagine it could have some nasty side effects.

I also tested with _b_tree_bitmap_plans=FALSE and that cut the parse time down from 12 to 6 as well, but that's another one I'm not so sure I want to set instance wide.

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

From: Michael McMullen [mailto:ganstadba_at_hotmail.com] Sent: Thursday, June 29, 2006 12:00 PM
To: Allen, Brandon; oracle-l_at_freelists.org Subject: Re: Long execute phase for SELECT query

Can
optimizer_max_permutations still be set in 10g? If so, you could try playing with that.

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jun 30 2006 - 11:37:43 CDT