Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: impact of changing optimizer_features_enable
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-lReceived on Sun Jun 24 2007 - 00:00:38 CDT