Home » RDBMS Server » Performance Tuning » Oracle Performance Tuning of a view (Oracle version 9.2.0.1.0)
Oracle Performance Tuning of a view [message #390446] Fri, 06 March 2009 06:04 Go to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

This query is taking 5 minutes to execute.
select l.borrid, l.mdlid, l.year, l.user_id, l.prmname, l.value, to_char(m.score) as marks, l.roword
from
(
  select a.borrid, b.mdlid, to_number(to_char(d.stmtdt, 'yyyy'), '9999') as year, a.user_id, lpad(c.item_name, length(decode(c.item_name, 'DSCR (Mandatory)', 'DSCR', c.item_name))+((c.tr_lvl-1)*5), ' ') as prmname, to_char(a.value) as value, a.item_id as roword
  from coaratios a, model b, coaitems c, statement d, coatree e, audit_method f
  where
  a.coaid = b.coaid and
  a.coaid = c.coaid and
  a.item_id = c.item_id and
  a.tr_id = c.tr_id and
  a.coaid = d.coaid and
  a.stmtid = d.stmtid and
  a.borrid = d.borrid and
  a.user_id = d.user_id and
  a.coaid = e.coaid and
  a.tr_id = e.tr_id and
  d.audit_method_id = f.id and
  trim(lower(e.tr_name)) = 'key financial ratios'
) l,
(
  select  distinct x.borrid, x.mdlid, x.year, x.user_id, x.ratioid, x.score from rat_calc_finratio x
) m
where l.borrid = m.borrid (+) and
l.year = m.year (+) and
l.mdlid = m.mdlid (+) and
l.user_id = m.user_id (+) and
l.roword = m.ratioid (+)
union all
select a.borrid, a.mdlid, a.year, a.user_id, replace(b.prmname, 'FR - ','') as prmname, d.attribute as value, TO_CHAR(a.value) as Marks, a.prmid as roword
from subjective_prm_trans a, mdl_parameter_tree b, mdl_parameter_tree c, mdl_grading_details d
where a.mdlid = b.mdlid and
a.prmid = b.prmid and
b.mdlid = c.mdlid and
b.parentid = c.prmid and
a.mdlid = d.mdlid and
a.gddid = d.gddid and
trim(lower(c.prmname)) = 'frs'



The reason for taking 5 minutes is the distinct clause which is impacting as well as the table with the name coaratios is having huge data of more than 14 Lakhs and statement table having 2.5 Lakhs which keeps on increasing every week

Request you to please help in optimising the below query

Elapsed: 00:02:03.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=42283827442 Card=152
          6094534760 Bytes=3307046853865440)

   1    0   UNION-ALL
   2    1     MERGE JOIN (OUTER) (Cost=42283825854 Card=1526094533281
          Bytes=3307046853619930)

   3    2       SORT (JOIN) (Cost=42283792237 Card=1526094533281 Bytes
          =3188011480024010)

   4    3         VIEW (Cost=9381 Card=1526094533281 Bytes=31880114800
          24010)

   5    4           HASH JOIN (Cost=9381 Card=1526094533281 Bytes=2746
          97015990580)

   6    5             INDEX (FULL SCAN) OF 'IDX_COATREE_2' (NON-UNIQUE
          ) (Cost=1 Card=2 Bytes=46)

   7    5             HASH JOIN (Cost=1998 Card=106964620 Bytes=167934
          45340)

   8    7               NESTED LOOPS (Cost=1966 Card=614 Bytes=34998)
   9    8                 HASH JOIN (Cost=1966 Card=1023 Bytes=55242)
  10    9                   TABLE ACCESS (FULL) OF 'STATEMENT' (Cost=1
          82 Card=256850 Bytes=6164400)

  11    9                   HASH JOIN (Cost=836 Card=700849 Bytes=2102
          5470)

  12   11                     TABLE ACCESS (FULL) OF 'MODEL' (Cost=2 C
          ard=28 Bytes=168)

  13   11                     TABLE ACCESS (FULL) OF 'COARATIOS' (Cost
          =813 Card=1401698 Bytes=33640752)

  14    8                 INDEX (RANGE SCAN) OF 'IDX_AUDIT_METHOD_ID'
          (NON-UNIQUE)

  15    7               TABLE ACCESS (FULL) OF 'COAITEMS' (Cost=29 Car
          d=5774 Bytes=577400)

  16    2       SORT (JOIN) (Cost=33617 Card=452673 Bytes=35308494)
  17   16         VIEW (Cost=7011 Card=452673 Bytes=35308494)
  18   17           SORT (UNIQUE) (Cost=7011 Card=452673 Bytes=9506133
          )

  19   18             TABLE ACCESS (FULL) OF 'RAT_CALC_FINRATIO' (Cost
          =412 Card=452673 Bytes=9506133)

  20    1     HASH JOIN (Cost=1588 Card=1479 Bytes=245514)
  21   20       TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Ca
          rd=27 Bytes=945)

  22   20       HASH JOIN (Cost=1581 Card=59234 Bytes=7759654)
  23   22         TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3
          Card=876 Bytes=42048)

  24   22         HASH JOIN (Cost=1557 Card=71216 Bytes=5910928)
  25   24           TABLE ACCESS (FULL) OF 'MDL_GRADING_DETAILS' (Cost
          =4 Card=2204 Bytes=130036)

  26   24           TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cos
          t=1151 Card=1357360 Bytes=32576640)





Statistics
----------------------------------------------------------
          0  recursive calls
        176  db block gets
      42674  consistent gets
      85740  physical reads
          0  redo size
   92877819  bytes sent via SQL*Net to client
    1196639  bytes received via SQL*Net from client
     108742  SQL*Net roundtrips to/from client
          0  sorts (memory)
          3  sorts (disk)
    1631109  rows processed


I can never imagine the query having a huge cost and let me also tell you that my statistics are upto date.

This distinct clause is hindering the performance of the query as well as there are some big tables getting join to smal tables.
Well let me also tell you that there are indexes in all the big tables.

Request you to restructure this query for better performance.

Regards
Re: Oracle Performance Tuning of a view [message #390466 is a reply to message #390446] Fri, 06 March 2009 08:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have you tried replacing your outer join to the inline view M with an EXISTS subquery, and adding an inline select statement into the SELECT list to replace m.score.
Re: Oracle Performance Tuning of a view [message #390564 is a reply to message #390446] Sat, 07 March 2009 09:11 Go to previous messageGo to next message
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
Senior Member
To help identify the bottle neck, run the 4 sqls seperately, let us know the execution time respectively.


Sql1:
select a.borrid, b.mdlid, to_number(to_char(d.stmtdt, 'yyyy'), '9999') as year, a.user_id, lpad(c.item_name, length(decode(c.item_name, 'DSCR (Mandatory)', 'DSCR', c.item_name))+((c.tr_lvl-1)*5), ' ') as prmname, to_char(a.value) as value, a.item_id as roword
from coaratios a, model b, coaitems c, statement d, coatree e, audit_method f
where
a.coaid = b.coaid and
a.coaid = c.coaid and
a.item_id = c.item_id and
a.tr_id = c.tr_id and
a.coaid = d.coaid and
a.stmtid = d.stmtid and
a.borrid = d.borrid and
a.user_id = d.user_id and
a.coaid = e.coaid and
a.tr_id = e.tr_id and
d.audit_method_id = f.id and
trim(lower(e.tr_name)) = 'key financial ratios'

Sql2:
select distinct x.borrid, x.mdlid, x.year, x.user_id, x.ratioid, x.score from rat_calc_finratio x

Sql3:
run the out join of Sql1 and Sql2

Sql4:
select a.borrid, a.mdlid, a.year, a.user_id, replace(b.prmname, 'FR - ','') as prmname, d.attribute as value, TO_CHAR(a.value) as Marks, a.prmid as roword
from subjective_prm_trans a, mdl_parameter_tree b, mdl_parameter_tree c, mdl_grading_details d
where a.mdlid = b.mdlid and
a.prmid = b.prmid and
b.mdlid = c.mdlid and
b.parentid = c.prmid and
a.mdlid = d.mdlid and
a.gddid = d.gddid and
trim(lower(c.prmname)) = 'frs'

Regards,
Alex
Re: Oracle Performance Tuning of a view [message #390565 is a reply to message #390564] Sat, 07 March 2009 09:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Oracle Performance Tuning of a view [message #390567 is a reply to message #390446] Sat, 07 March 2009 09:31 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>The reason for taking 5 minutes
>Elapsed: 00:02:03.07

Why the discrepancy?

What else are you misstating or not telling us?
Previous Topic: Changes to the query
Next Topic: Effective SQL
Goto Forum:
  


Current Time: Fri Dec 09 23:31:06 CST 2016

Total time taken to generate the page: 0.13504 seconds