Re: set optimizer_features_enable to lower version SQL run faster?

From: Guillermo Alan Bort <cicciuxdba_at_gmail.com>
Date: Tue, 2 Feb 2010 22:03:55 -0200
Message-ID: <172762181002021603r210e9f5ek550226750af4bbbf_at_mail.gmail.com>

If you can send the sql statement perhaps somebody will be able to help you... otherwise I think you won't have much luck here..

hth
Alan.-

On Tue, Feb 2, 2010 at 9:26 PM, Kerry Osborne <kerry.osborne_at_enkitec.com>wrote:

> Too bad you can't set it to version 2. That SQL statement would probably
> run in 1/1000 of a second.
>
> Seriously, have a look at the statement and see if you can figure out why
> changing the parameter caused it to run faster (if it did). Hints tuning the
> statement to a certain version of the optimizer are one reason this could
> happen. But at this point it sounds like you're just shooting in the dark.
>
> Kerry Osborne
> Enkitec
> blog: kerryosborne.oracle-guy.com
>
>
>
>
>
>
>
> On Feb 2, 2010, at 5:09 PM, dba1 mcc wrote:
>
> we have ORACLE enterprise version 10.2.0.4 on LINUX. recently we found
>> some SQL statemnets run very slow (even we run analyze statistics). But if
>> we set optimizer_features_enable to lower version it run much faster.
>> Following is data we got:
>>
>> When the SQL is run with OPTIMIZER_FEATURE_ENABLE=
>>
>> 10.2.0.4 73 seconds
>>
>> 10.2.0.3 50 seconds
>>
>> 9 30 seconds
>>
>> 8 1 second.
>>
>> 1. does there has some way we can do (NOT set optimizer_features_enable to
>> lower version) still get better SQL performance?
>>
>> 2. if unfortunately we need set 'optimizer_feature_enable to lower version
>> like 8.0) does database will have any problem?
>>
>> Thanks.
>>
>>
>>
>>
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Tue Feb 02 2010 - 18:03:55 CST