Home » RDBMS Server » Performance Tuning » Oracle 9i Cost very high (Oracle 9i)
Oracle 9i Cost very high [message #404890] Mon, 25 May 2009 04:07 Go to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Above query cost is very high.The query is having concatenated indexes on three tables COABALANCESM,FUNCTION BASED INDEX ON COATREE,concatenated ON STATEMENT TABLE.I have analyze table to make the statistics up-to date.But could you please tell me why the cost of the query is very high.However the query is taking lesser amount of records to process 247 records.

SELECT 
  r.item_name,
  r.value,
  r.mdlid,
  r.rowsort,
  r.year,
  r.stmtid
FROM 
  ( SELECT t.item_grpflag,
  t.item_name AS item_name,
  ROUND(t.value,
  0) AS value,
  t.mdlid,
  t.item_id AS rowsort,
  t.year,
  t.stmtid FROM( SELECT l.item_grpflag,
  l.item_name AS item_name,
  l.value,
  l.mdlid,
  l.item_id,
  l.stmtid,
  m.year FROM ( SELECT a.item_grpflag,
  NVL(b.stmtid,
  0) AS stmtid,
  a.fm_row,
  d.mdlid,
  LPAD(a.item_name,
  LENGTH(a.item_name)+((a.tr_lvl-1)*5),
  ' ') AS item_name,
  b.value,
  a.item_id FROM coaitems a,
  coabalances b,
  coatree c,
  model d
WHERE 
  a.coaid = b.coaid (+) AND
  a.tr_id = b.tr_id (+) AND
  a.item_id = b.item_id (+) AND
  a.fm_row = b.coarow (+) AND
  a.coaid = c.coaid AND
  a.tr_id = c.tr_id AND
  a.coaid = d.mdlid AND
  trim(LOWER(c.tr_name)) = 'balance sheet' AND
  a.item_id > 0 AND
  d.mdlid  =1 AND
  b.borrid (+) = 3280 AND
  b.user_id (+) = 24  ORDER BY a.coaid, a.fm_row ) l, ( SELECT 0 AS stmtid, '0' AS year FROM dual UNION ALL SELECT n.stmtid, '(' || ROWNUM || ')' || CHR(13) || n.year FROM ( SELECT x.stmtid, z.auditmethod_name||'  '||TO_CHAR(x.stmtdt) || ' [ ' || x.stmtperiod || 'M ]' AS year FROM STATEMENT x, model y, audit_method z WHERE x.audit_method_id = z.id AND
  x.coaid = y.coaid AND
  y.mdlid = 1 AND
  x.borrid = 3280 AND
  x.user_id = 24 AND
  x.stmtid IN (SELECT stmtid FROM (SELECT stmtid, ROWNUM FROM STATEMENT WHERE borrid = 3280 AND
  coaid = 1 AND
  user_id = 24 ORDER BY stmtid DESC) WHERE ROWNUM <= 3)  ) n ) m WHERE(l.stmtid = m.stmtid) ORDER BY l.fm_row, l.stmtid)t WHERE  t.stmtid IN (SELECT 0 AS stmtid FROM dual UNION ALL SELECT stmtid FROM (SELECT stmtid, ROWNUM FROM (SELECT DISTINCT a.stmtid FROM STATEMENT a WHERE A.BORRID = 3280 AND
  A.coaid = 1 AND
  A.USER_ID = 24 ORDER BY a.stmtid DESC )) WHERE ROWNUM <= 3) ORDER BY t.stmtid ASC )r

Below is the explain plan of the above query

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21399039 Card=669617
          7 Bytes=14296337895)

   1    0   VIEW (Cost=21399039 Card=6696177 Bytes=14296337895)
   2    1     SORT (ORDER BY) (Cost=21399039 Card=6696177 Bytes=143699
          95842)

   3    2       HASH JOIN (Cost=17665 Card=6696177 Bytes=14369995842)
   4    3         VIEW OF 'VW_NSO_1' (Cost=16 Card=8169 Bytes=106197)
   5    4           SORT (UNIQUE) (Cost=16 Card=8169 Bytes=13)
   6    5             UNION-ALL
   7    6               TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=81
          68)

   8    6               COUNT (STOPKEY)
   9    8                 VIEW (Cost=5 Card=1 Bytes=13)
  10    9                   COUNT
  11   10                     VIEW (Cost=5 Card=1 Bytes=13)
  12   11                       SORT (UNIQUE) (Cost=4 Card=1 Bytes=10)
  13   12                         TABLE ACCESS (BY INDEX ROWID) OF 'ST
          ATEMENT' (Cost=2 Card=1 Bytes=10)

  14   13                           INDEX (RANGE SCAN) OF 'IDX_BORRID_
          USER_COAID' (NON-UNIQUE) (Cost=1 Card=1)

  15    3         VIEW (Cost=17341 Card=13935 Bytes=29723355)
  16   15           SORT (ORDER BY) (Cost=17341 Card=13935 Bytes=30085
          665)

  17   16             HASH JOIN (Cost=46 Card=13935 Bytes=30085665)
  18   17               VIEW (Cost=21 Card=29 Bytes=60001)
  19   18                 SORT (ORDER BY) (Cost=21 Card=29 Bytes=11368
          )

  20   19                   HASH JOIN (OUTER) (Cost=18 Card=29 Bytes=1
          1368)

  21   20                     HASH JOIN (Cost=15 Card=29 Bytes=10759)
  22   21                       MERGE JOIN (CARTESIAN) (Cost=3 Card=1
          Bytes=168)

  23   22                         TABLE ACCESS (BY INDEX ROWID) OF 'CO
          ATREE' (Cost=1 Card=1 Bytes=155)

  24   23                           INDEX (RANGE SCAN) OF 'IDX_TR_NAME
          ' (NON-UNIQUE) (Cost=1 Card=1)

  25   22                         BUFFER (SORT) (Cost=2 Card=1 Bytes=1
          3)

  26   25                           TABLE ACCESS (FULL) OF 'MODEL' (Co
          st=2 Card=1 Bytes=13)

  27   21                       TABLE ACCESS (FULL) OF 'COAITEMS' (Cos
          t=11 Card=258 Bytes=52374)

  28   20                     TABLE ACCESS (BY INDEX ROWID) OF 'COABAL
          ANCES' (Cost=2 Card=1 Bytes=21)

  29   28                       INDEX (RANGE SCAN) OF 'IDX_BORRID_USER
          _ID' (NON-UNIQUE) (Cost=1 Card=5)

  30   17               VIEW (Cost=22 Card=8169 Bytes=735210)
  31   30                 UNION-ALL
  32   31                   TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Car
          d=8168)

  33   31                   COUNT
  34   33                     HASH JOIN (Cost=11 Card=1 Bytes=100)
  35   34                       TABLE ACCESS (BY INDEX ROWID) OF 'STAT
          EMENT' (Cost=2 Card=1 Bytes=21)

  36   35                         NESTED LOOPS (Cost=8 Card=1 Bytes=60
          )

  37   36                           MERGE JOIN (CARTESIAN) (Cost=6 Car
          d=1 Bytes=39)

  38   37                             TABLE ACCESS (FULL) OF 'MODEL' (
          Cost=2 Card=1 Bytes=26)

  39   37                             BUFFER (SORT) (Cost=4 Card=1 Byt
          es=13)

  40   39                               VIEW OF 'VW_NSO_2' (Cost=4 Car
          d=1 Bytes=13)

  41   40                                 SORT (UNIQUE)
  42   41                                   COUNT (STOPKEY)
  43   42                                     VIEW (Cost=4 Card=1 Byte
          s=13)

  44   43                                       SORT (ORDER BY STOPKEY
          ) (Cost=4 Card=1 Bytes=10)

  45   44                                         COUNT
  46   45                                           TABLE ACCESS (BY I
          NDEX ROWID) OF 'STATEMENT' (Cost=2 Card=1 Bytes=10)

  47   46                                             INDEX (RANGE SCA
          N) OF 'IDX_BORRID_USER_COAID' (NON-UNIQUE) (Cost=1 Card=1)

  48   36                           INDEX (RANGE SCAN) OF 'IDX_STATEME
          NT_4FLDS' (NON-UNIQUE) (Cost=1 Card=1)

  49   34                       TABLE ACCESS (FULL) OF 'AUDIT_METHOD'
          (Cost=2 Card=82 Bytes=3280)





Statistics
----------------------------------------------------------
       2699  recursive calls
          0  db block gets
        872  consistent gets
        189  physical reads
          0  redo size
      11942  bytes sent via SQL*Net to client
        689  bytes received via SQL*Net from client
         18  SQL*Net roundtrips to/from client
         39  sorts (memory)
          0  sorts (disk)
        247  rows processed


Appreciate your help on the above as to why the cost of the query is very high.?

Regards
Re: Oracle 9i Cost very high [message #404953 is a reply to message #404890] Mon, 25 May 2009 13:34 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
manoj12 wrote on Mon, 25 May 2009 10:07

The query is having concatenated indexes on three tables COABALANCESM,FUNCTION BASED INDEX ON COATREE,concatenated ON STATEMENT TABLE.


Are we supposed to guess which columns are indexed?

Quote:

However the query is taking lesser amount of records to process 247 records.



What does that mean?

You really need to format that query. Code tags alone aren't going to make a query that complex understandable. Indentation is also required.



Previous Topic: More archivelogs are generating.
Next Topic: Database Performance (merged2) 10.2
Goto Forum:
  


Current Time: Fri Dec 09 13:29:43 CST 2016

Total time taken to generate the page: 0.10138 seconds