Re: Query with buffer (sorts) taking age to complete.

From: LiShan Cheng <exriscer_at_gmail.com>
Date: Sat, 25 Mar 2006 09:07:48 +0100
Message-ID: <6e9345580603250007k395c1802w453f8a669c424a72@mail.gmail.com>

Hi

Do you use same stats gathering method?

I am not sure if the cartesian is the problem, the cost jumps in this stage:

  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)

Also if we compare your aix plan and hp

AIX

  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)

HP

  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)

That is already different. Does the tables changed a lot between those plans? i.e if A_ACCOUNT has grown a lot

Can you show us the query?

On 3/25/06, The Human Fly <sjaffarhussain_at_gmail.com> 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."
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Sat Mar 25 2006 - 02:07:48 CST