Re: 10g Performance: its crawling

Just an FYI. The query is having bad condition in where clause.

           AND fcl.application_id(+) = 800
           AND fcl.lookup_type(+) = 'NATIONALITY'

Its causing MERGE Cartesian Join, its causing an extra courtesian join in 10g.

There are two solutions I found 1. Creating a SQL profile which
doesn't use cortesian jon 2. removing (+) from conditons listed
above..

So I got it back to 33 mins from 4+ hours.

Thanks,
MVR




On 12/29/06, MVR <yoursraju007@xxxxxxxxx> wrote:

Here is an update.

Last night change: Rebuilding one index and gathering statistics at
schema level .. Benifit is around 30 Mins...

I have got an older version of 9i database, non-prod. I have tried to
compare.. Major difference I see is Merge Join Cartesian. Im attaching
query, 9i and 10g plans.

Thank you very much for your time.



On 12/29/06, Nuno Souto <dbvision@xxxxxxxxxxxx> wrote:
> MVR wrote,on my timestamp of 29/12/2006 9:22 AM:
> > Nope, unfortunately I dont have old plan (of 9206). Well, I know the
> > SQL. I have rebuilt one
> > of index(reclaimed more than 100M, and now size of the index is 279M)
> > ... I will see if that makes any difference.. I guess it is INDEX
> > RANGE SCAN... so dont think it makes much difference... if its INDEX
> > FULL SCAN, and resetting highlevel watermark by rebuild makes sense...
> >
> > Tuning Advisor recommends SQL profile which <10% benifit. If this
> > index thing does not help, next option is to create a SQL profile..
> > even it is <10%, but it matters when no# of executions are more.
>
>
> the other thing to try - but only if this starts to happen
> with a lot of your SQL - is to reset the optimizer_features_enable
> parameter to your prior release.  That usually cures these
> "runaway" problems on install of a new version.
>
> Of course then you need to check the plans with the old release
> level, compare them with the 10gr2 plans and see if you can then
> figure out why things are going "clunk".  Usually it's some minor
> difference in the CBO behavior that just happens to clash with
> the particular conditions of your database and data/index distributions.
>
> This parameter can be set with ALTER SESSION and ALTER SYSTEM,
> so it's relatively simple to modify - meaning: doesn't involve
> elaborate re-starts.
>
> Had to do this a number of times already on some of our clients
> who upgraded to 10gr2 and experienced similar problems.
> Have a good look at it in the doco: it's quite a useful
> way of temporarily resolving these unexpected/undocumented
> problems.
>
> Let me stress the "temporary": try to use the appropriate
> level of CBO for your release whenever possible.  This parameter
> is not a "cure-all", it's there to help minimize problems.
>
> --
> Cheers
> Nuno Souto
> dbvision@xxxxxxxxxxxx
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


--
"Happy people plan actions, they don't plan results."






--
"Happy people plan actions, they don't plan results."
--
http://www.freelists.org/webpage/oracle-l

Other related posts: