Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: impact of changing optimizer_features_enable

Re: impact of changing optimizer_features_enable

From: Ujang Jaenudin <ujang.jaenudin_at_gmail.com>
Date: Sun, 24 Jun 2007 12:00:38 +0700
Message-ID: <3edcb66e0706232200u6f9052e8ubde3e1ab88ef107b@mail.gmail.com>


john,

select ...... from ( select ...... from (select .... from (select .... from ))); but inside bracket, there are many of aggregate function.

i've tried these parameters, but don't help....

*._gby_hash_aggregation_enabled=FALSE
*._old_connect_by_enabled=TRUE
*._optimizer_cost_based_transformation=OFF

but when combined those 3 hidden parameter with *.optimizer_features_enable=8.1.7, it runs so..... fast.

now, i'm not checking the 10053 trace. by OFE=817, the v$session_longops give none of information, so i could not monitor hows the jobs was done.... :(

also, when monitoring the OS, there was swap memory used .... I used workares policy to manual and set sort area size to 5M

regards
ujang

On 6/24/07, John Clarke <jclarke_at_centroidsys.com> wrote:
> Sure, there could be differences in executions plans.
>
> What does your query look like?
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Ujang Jaenudin
> Sent: Friday, June 22, 2007 9:10 PM
> To: oracle-l_at_freelists.org
> Subject: impact of changing optimizer_features_enable
>
> all,
>
> i have a funny things about how oracle given execution plan.
>
> now my db is 10.2.0.3 on itanium 64bit.
> when tried *.optimizer_features_enable from 10.2 until 9.2 , the
> report was not much different (so slow). but when change to
> *.optimizer_features_enable=8.1.7, it amazingly so so so fast....
>
> so, does anyone have experiences about changing the above parameter
> and the impact to the applications?
>
> thanks for your sharing...
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Jun 24 2007 - 00:00:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US