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 Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 #390564 is a reply to message #390446] |
Sat, 07 March 2009 09:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
Goto Forum:
Current Time: Wed Sep 11 00:09:29 CDT 2024
|