RE: Long execute phase for SELECT query
- From: "ramick" <ramick@xxxxxxxxxxx>
- To: <Brandon.Allen@xxxxxxxxxxx>, "'Michael McMullen'" <ganstadba@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Jun 2006 09:37:43 -0700
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
Sent: Thursday, June 29, 2006 1:15 PM
To: Michael McMullen; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx]
Sent: Thursday, June 29, 2006 12:00 PM
To: Allen, Brandon; oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
