Query with buffer (sorts) taking age to complete.

From: The Human Fly <sjaffarhussain_at_gmail.com>
Date: Sat, 25 Mar 2006 10:55:53 +0300
Message-ID: <97b7fd2f0603242355u4d5f7dfje06fd278d5a38a49@mail.gmail.com>

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

Received on Sat Mar 25 2006 - 01:55:53 CST