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 > -- > //www.freelists.org/webpage/oracle-l > > > -- "Happy people plan actions, they don't plan results."
-- "Happy people plan actions, they don't plan results." -- //www.freelists.org/webpage/oracle-l