Hi Are your stats uptodate? Cheers On 3/25/06, The Human Fly <sjaffarhussain@xxxxxxxxx> wrote: > > Hello list, > > We have recently moved our datawarehouse oracle database 9i Rel.2 from > AIX to HP-SUPERDOM, using export and import utility. > > Well, I have used the same init file for both database, i.e. they are > identical. > > Today, while running a batch we are facing very wired problem. The job > which runs in 25 mins. now it is finished for 1.5 days. The execution > plan is diffeent, the problem execution plan shows buffer (sorts) and > MERGE JOIN (CARTESIAN) taking too much time. > > Following is the query and its execution plan, can anyone shed some > lime light on the issue : > > SELECT max(as_of_date), max(cust_code), nvl(abs(sum(run_offs_sar)), 0), > nvl(abs(sum(inst_amt_sar)), 0), nvl(abs(sum(bal_sar)), 0) > FROM ofdm_ods.a_account a > WHERE acct_no = '00100100120' > AND as_of_date = (SELECT max(as_of_date) > FROM ofdm_ods.a_account b > WHERE b.acct_no = a.acct_no > AND b.run_offs_sar <> 0) > > AIX > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=602 Card=1 Bytes=55) > 1 0 SORT (AGGREGATE) > 2 1 VIEW (Cost=602 Card=1 Bytes=55) > 3 2 FILTER > 4 3 SORT (GROUP BY) (Cost=602 Card=1 Bytes=75) > 5 4 HASH JOIN (Cost=529 Card=167 Bytes=12525) > 6 5 PARTITION RANGE (ALL) > 7 6 TABLE ACCESS (BY LOCAL INDEX ROWID) OF > 'A_ACCOUNT' (Cost=264 Card=167 Bytes=4509) > 8 7 INDEX (SKIP SCAN) OF 'IDX_DT_ACCT_FLAG' > (NON-UNIQUE) (Cost=189 Card=167) > 9 5 PARTITION RANGE (ALL) > 10 9 TABLE ACCESS (BY LOCAL INDEX ROWID) OF > 'A_ACCOUNT' (Cost=264 Card=167 Bytes=8016) > 11 10 INDEX (SKIP SCAN) OF 'IDX_DT_ACCT_FLAG' > (NON-UNIQUE) (Cost=189 Card=167) > > > SUPERDOM > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=630319908 Card=1 > Bytes=55) > 1 0 SORT (AGGREGATE) > 2 1 VIEW (Cost=630319908 Card=1602 Bytes=88110) > 3 2 FILTER > 4 3 SORT (GROUP BY) (Cost=630319908 Card=1602 Bytes=107334) > 5 4 MERGE JOIN (CARTESIAN) (Cost=630319180 Card=225809 > Bytes=15129203) > 6 5 PARTITION RANGE (ALL) > 7 6 TABLE ACCESS (BY LOCAL INDEX ROWID) OF > 'A_ACCOUNT' (Cost=700 Card=440 Bytes=10560) > 8 7 INDEX (SKIP SCAN) OF 'IDX_DT_ACCT_FLAG' > (NON-UNIQUE) (Cost=519 Card=513) > 9 5 BUFFER (SORT) (Cost=630319208 Card=513 Bytes=22059) > 10 9 PARTITION RANGE (ALL) > 11 10 TABLE ACCESS (FULL) OF 'A_ACCOUNT' > (Cost=1432542 Card=513 Bytes=22059) > > thanks for your cooperation. > > Sorry for the > -- > Best Regards, > Syed Jaffar Hussain > 8i,9i & 10g, OCP DBA > Banque Saudi Fransi, > Saudi Arabia > http://jaffardba.blogspot.com/ > > ---------------------------------------------------------------------------------- > "Winners don't do different things. They do things differently." > -- > //www.freelists.org/webpage/oracle-l > > >