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_at_gmail.com> 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_at_iinet.net.au> 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_at_iinet.net.au
> > --
> > 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