OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » performance issue

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

performance issue [message #322421]

Fri, 23 May 2008 08:18

manoj12
Messages: 210
Registered: March 2008
Location: India

Senior Member

Dear Sir

This query is taking a long time to execute

Please check the query and the below is the explain plan mentioned to it

select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as year
from statement
where borrid= &var_borrId and user_id= &var_user_id and coaid = &var_mdlid
and to_char(stmtdt, 'yyyy') in
(
select os.year from
(
select borrid, year, count(*) as cntActuals
from subjective_prm_trans
where borrid= &var_borrId and user_id= &var_user_id and mdlid = &var_mdlid and endnodeflag = 'E'
group by year, borrid
) os,
(
select j.borrid, j.year, j.cntVariable + k.cntFixed as cntMdlTotals
from
(
select a.borrid, a.year , count(*) as cntVariable
from subjective_prm_trans a, mdl_Parameter_Tree m
where
a.prmid = m.parentid and a.mdlid = m.mdlid and
a.endnodeflag = 'N' and a.value between 0.0001 and 1 and
a.prmid not in (select r.prmid from mdl_parameter_tree r where trim(lower(r.prmname)) = 'project risk' and r.mdlid= &var_mdlid ) and
a.borrid= &var_borrId and a.mdlid= &var_mdlid and a.user_id= &var_user_id
group by a.borrid , a.year
) j,
(
select count(*) as cntFixed
from mdl_parameter_tree u
where u.prmid not in
(
select t.prmid
from mdl_parameter_tree t
where t.rootnode in
(
select b.rootnode
from subjective_prm_trans a, mdl_parameter_tree b
where
a.mdlid = b.mdlid and a.prmid = b.prmid and
a.endnodeflag = 'N' and a.value between 0.0001 and 1 and
a.prmid not in (select r.prmid from mdl_parameter_tree r where trim(lower(r.prmname)) = 'project risk' and r.mdlid= &var_mdlid ) and
a.borrid= &var_borrId and a.mdlid= &var_mdlid and a.user_id= &var_user_id
) and
t.endnodeflag = 'E' and parametertype = 'S' and mdlid= &var_mdlid
) and
u.endnodeflag ='E' and
u.parametertype = 'S' and
u.mdlid= &var_mdlid
) k
) om
where
os.borrid = om.borrid and
os.year = om.year and
os.cntActuals = om.cntMdlTotals
)
order by year desc

Below is the explain plan for the query

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=28 Card=1 Bytes=61
)

1 0 SORT (ORDER BY) (Cost=28 Card=1 Bytes=61)
2 1 HASH JOIN (SEMI) (Cost=26 Card=1 Bytes=61)
3 2 TABLE ACCESS (FULL) OF 'STATEMENT' (Cost=2 Card=1 Byte
s=48)

4 2 VIEW OF 'VW_NSO_1' (Cost=23 Card=1 Bytes=13)
5 4 NESTED LOOPS (Cost=23 Card=1 Bytes=91)
6 5 HASH JOIN (Cost=13 Card=1 Bytes=78)
7 6 VIEW (Cost=4 Card=1 Bytes=39)
8 7 SORT (GROUP BY) (Cost=4 Card=1 Bytes=55)
9 8 TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS
' (Cost=2 Card=1 Bytes=55)

10 6 VIEW (Cost=9 Card=1 Bytes=39)
11 10 SORT (GROUP BY) (Cost=7 Card=1 Bytes=107)
12 11 FILTER
13 12 HASH JOIN (Cost=5 Card=1 Bytes=107)
14 13 TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_T
RANS' (Cost=2 Card=1 Bytes=81)

15 13 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TR
EE' (Cost=2 Card=142 Bytes=3692)

16 12 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE
' (Cost=2 Card=1 Bytes=155)

17 5 VIEW
18 17 SORT (AGGREGATE)
19 18 FILTER
20 19 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE'
(Cost=2 Card=1 Bytes=284)

21 19 FILTER
22 21 NESTED LOOPS (Cost=6 Card=1 Bytes=636)
23 22 MERGE JOIN (CARTESIAN) (Cost=4 Card=1 By
tes=481)

24 23 TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM
_TRANS' (Cost=2 Card=1 Bytes=68)

25 23 BUFFER (SORT) (Cost=2 Card=1 Bytes=413
)

26 25 TABLE ACCESS (FULL) OF 'MDL_PARAMETE
R_TREE' (Cost=2 Card=1 Bytes=413)

27 22 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TR
EE' (Cost=2 Card=18 Bytes=2790)

28 21 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE
' (Cost=2 Card=1 Bytes=155)

Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
387229 consistent gets
306954 physical reads
0 redo size
432 bytes sent via SQL*Net to client
584 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
114 sorts (memory)
0 sorts (disk)
1 rows processed

Any help would help to resolve the issue