Home » RDBMS Server » Performance Tuning » Performance tuning
Performance tuning [message #209940] Mon, 18 December 2006 10:20 Go to next message
misha603
Messages: 20
Registered: July 2005
Location: India
Junior Member
Hi
I have the following query, which takes a long time to execute for this caseid
alone ('515157'). I have tried analyze command but there was no improvement in
response time. The response time for all other caseids is just the blink of an
eye and there are not much difference in the number of records. I have also
noticed that this uses only indexes. Apart from this I don't have any clue from
the explain plan. The explain plan is the same for all case ids. Can you please
help me out. I'm new to performance tuning.
SELECT pt.plcysymbl, pt.plcyid
FROM view_dbl_bllgrp v,
exprncgrp_bllgrp_assoc eba,
pega_exprncgrp_assoc pexa,
case_commission_plan ccp,
policy_translation pt
WHERE EXISTS (
SELECT 'X'
FROM view_dbl_bllgrp v2,
exprncgrp_bllgrp_assoc eba2,
pega_exprncgrp_assoc pexa2,
case_commission_plan ccp2,
policy_translation pt2
WHERE ( NVL (ccp2.fltamt, 0) != NVL (ccp.fltamt, 0)
OR NVL (ccp2.frqncycd, ' ') != NVL (ccp.frqncycd, ' ')
OR NVL (ccp2.cmmssnschdlid, 0) !=
NVL (ccp.cmmssnschdlid, 0)
)
AND NVL (ccp2.cmmssnplntrmdt, v2.frstbllduedt + 1) >
v2.frstbllduedt
AND v2.bllgrpid = eba2.bllgrpid
AND v2.caseid = eba2.caseid
AND NVL (eba2.exprncgrpbllgrptrmdt,
pexa2.pegaexprncgrpeffctvdt + 1
) > pexa2.pegaexprncgrpeffctvdt
AND eba2.exprncgrpbllgrpeffctvdt <
NVL (pexa2.pegaexprncgrptrmdt,
eba2.exprncgrpbllgrpeffctvdt + 1
)
AND eba2.exprncgrpid = pexa2.exprncgrpid
AND eba2.caseid = pexa2.caseid
AND pexa2.fndngmthdplnnbr = pt2.fndngmthdplnnbr
AND pexa2.cvrgplnnbr = pt2.cvrgplnnbr
AND pexa2.fndngmthdcd = pt2.fndngmthdcd
AND pexa2.cvrgtypcd = pt2.cvrgtypcd
AND pexa2.cvrgctgrycd = pt2.cvrgctgrycd
AND pexa2.caseid = pt2.caseid
AND NVL (ccp2.cmmssnplntrmdt, ccp.cmmssnplneffctvdt + 1) >
ccp.cmmssnplneffctvdt
AND ccp2.cmmssnplneffctvdt <
NVL (ccp.cmmssnplntrmdt, ccp2.cmmssnplneffctvdt + 1)
AND ccp2.plnnbr =
DECODE (ccp2.fndngmthdcd,
'N/A', pt2.cvrgplnnbr,
pt2.fndngmthdplnnbr
)
AND ccp2.fndngmthdcd IN ('N/A', pt2.fndngmthdcd)
AND ccp2.cvrgtypcd IN ('N/A', pt2.cvrgtypcd)
AND ccp2.cvrgctgrycd IN ('N/A', pt2.cvrgctgrycd)
AND ccp2.caseid = pt2.caseid
AND ( pt2.fndngmthdplnnbr != pt.fndngmthdplnnbr
OR pt2.cvrgplnnbr != pt.cvrgplnnbr
OR pt2.fndngmthdcd != pt.fndngmthdcd
OR pt2.cvrgtypcd != pt.cvrgtypcd
OR pt2.cvrgctgrycd != pt.cvrgctgrycd
)
AND pt2.plcysymbl = pt.plcysymbl
AND pt2.plcyid = pt.plcyid
AND pt2.caseid = pt.caseid)
AND NVL (ccp.cmmssnplntrmdt, v.frstbllduedt + 1) > v.frstbllduedt
AND v.bllgrpid = eba.bllgrpid
AND v.caseid = eba.caseid
AND NVL (eba.exprncgrpbllgrptrmdt, pexa.pegaexprncgrpeffctvdt + 1) >
pexa.pegaexprncgrpeffctvdt
AND eba.exprncgrpbllgrpeffctvdt <
NVL (pexa.pegaexprncgrptrmdt, eba.exprncgrpbllgrpeffctvdt + 1)
AND eba.exprncgrpid = pexa.exprncgrpid
AND eba.caseid = pexa.caseid
AND pexa.fndngmthdplnnbr = pt.fndngmthdplnnbr
AND pexa.cvrgplnnbr = pt.cvrgplnnbr
AND pexa.fndngmthdcd = pt.fndngmthdcd
AND pexa.cvrgtypcd = pt.cvrgtypcd
AND pexa.cvrgctgrycd = pt.cvrgctgrycd
AND pexa.caseid = pt.caseid
AND ccp.plnnbr =
DECODE (ccp.fndngmthdcd,
'N/A', pt.cvrgplnnbr,
pt.fndngmthdplnnbr
)
AND ccp.fndngmthdcd IN ('N/A', pt.fndngmthdcd)
AND ccp.cvrgtypcd IN ('N/A', pt.cvrgtypcd)
AND ccp.cvrgctgrycd IN ('N/A', pt.cvrgctgrycd)
AND ccp.caseid = pt.caseid
AND (pt.caseid, pt.plcyid, pt.plcysymbl, 'MORE') IN (
SELECT caseid, plcyid, plcysymbl,
DECODE (COUNT (DISTINCT cvrgctgrycd
|| '/'
|| cvrgtypcd
|| '/'
|| fndngmthdcd
|| '/'
|| cvrgplnnbr
|| '/'
|| fndngmthdplnnbr
),
1, 'ONE',
'MORE'
)
FROM policy_translation
WHERE caseid = '515157'
GROUP BY caseid, plcyid, plcysymbl)
ORDER BY pt.plcysymbl, pt.plcyid

Elapsed: 00:20:15.08

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=86 Card=1 Bytes=181)
1 0 SORT (ORDER BY) (Cost=20 Card=1 Bytes=181)
2 1 FILTER
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'CASE_COMMISSION_PLAN
' (Cost=2 Card=1 Bytes=38)

4 3 NESTED LOOPS (Cost=14 Card=1 Bytes=181)
5 4 NESTED LOOPS (Cost=12 Card=1 Bytes=143)
6 5 NESTED LOOPS (Cost=9 Card=1 Bytes=106)
7 6 NESTED LOOPS (Cost=6 Card=1 Bytes=67)
8 7 NESTED LOOPS (Cost=4 Card=1 Bytes=47)
9 8 NESTED LOOPS (Cost=3 Card=1 Bytes=28)
10 9 INDEX (UNIQUE SCAN) OF 'UICASE' (UNIQUE) (Cost=1
Card=1 Bytes=8)

11 9 TABLE ACCESS (BY INDEX ROWID) OF
'CASE_ADMIN_PROVISION' (Cost=2 Card=1 Bytes=20)

12 11 INDEX (RANGE SCAN) OF 'UICASEADMINPROV' (UNIQUE)
(Cost=1 Card=3)

13 8 TABLE ACCESS (BY INDEX ROWID) OF 'BILL_GROUP'
(Cost=1 Card=1 Bytes=19)

14 13 INDEX (UNIQUE SCAN) OF 'UIBILLGROUP' (UNIQUE)

15 7 TABLE ACCESS (BY INDEX ROWID) OF
'EXPRNCGRP_BLLGRP_ASSOC' (Cost=2 Card=1 Bytes=20)

16 15 INDEX (RANGE SCAN) OF 'IEXPBLLGRPASSOC' (NON-UNIQUE)
(Cost=1 Card=1)

17 6 TABLE ACCESS (BY INDEX ROWID) OF 'PEGA_EXPRNCGRP_ASSOC'
(Cost=3 Card=1 Bytes=39)

18 17 INDEX (RANGE SCAN) OF 'IPEGAEXPGRPASSOC' (NON-UNIQUE)
(Cost=2 Card=1)

19 5 TABLE ACCESS (BY INDEX ROWID) OF 'POLICY_TRANSLATION'
(Cost=3 Card=1 Bytes=37)

20 19 INDEX (RANGE SCAN) OF 'IPOLICYTRNSLTN1' (NON-UNIQUE)
(Cost=2 Card=1)

21 4 INDEX (RANGE SCAN) OF 'UICASECOMMPLN' (UNIQUE) (Cost=1
Card=1)

22 2 TABLE ACCESS (BY INDEX ROWID) OF 'PEGA_EXPRNCGRP_ASSOC' (Cost=3
Card=1 Bytes=39)

23 22 NESTED LOOPS (Cost=14 Card=1 Bytes=181)
24 23 NESTED LOOPS (Cost=11 Card=1 Bytes=142)
25 24 NESTED LOOPS (Cost=9 Card=1 Bytes=122)
26 25 NESTED LOOPS (Cost=6 Card=1 Bytes=85)
27 26 NESTED LOOPS (Cost=4 Card=1 Bytes=47)
28 27 NESTED LOOPS (Cost=3 Card=1 Bytes=28)
29 28 INDEX (UNIQUE SCAN) OF 'UICASE' (UNIQUE)(Cost=1
Card=1 Bytes=8)

30 28 TABLE ACCESS (BY INDEX ROWID) OF
'CASE_ADMIN_PROVISION' (Cost=2 Card=1 Bytes=20)

31 30 INDEX (RANGE SCAN) OF 'UICASEADMINPROV' (UNIQUE)
(Cost=1 Card=3)

32 27 TABLE ACCESS (BY INDEX ROWID) OF 'BILL_GROUP'
(Cost=1 Card=1 Bytes=19)

33 32 INDEX (UNIQUE SCAN) OF 'UIBILLGROUP' (UNIQUE)

34 26 TABLE ACCESS (BY INDEX ROWID) OF
'CASE_COMMISSION_PLAN' (Cost=2 Card=1 Bytes=38)

35 34 INDEX (RANGE SCAN) OF 'UICASECOMMPLN' (UNIQUE)
(Cost=1 Card=1)

36 25 TABLE ACCESS (BY INDEX ROWID) OF 'POLICY_TRANSLATION'
(Cost=3 Card=1 Bytes=37)

37 36 INDEX (RANGE SCAN) OF 'IPOLICYTRNSLTN' (NON-UNIQUE)
(Cost=2 Card=1)

38 24 TABLE ACCESS (BY INDEX ROWID) OF 'EXPRNCGRP_BLLGRP_ASSOC'
(Cost=2 Card=1 Bytes=20)

39 38 INDEX (RANGE SCAN) OF 'IEXPBLLGRPASSOC' (NON-UNIQUE)
(Cost=1 Card=1)

40 23 INDEX (RANGE SCAN) OF 'IPEGAEXPGRPASSOC' (NON-UNIQUE)
(Cost=2 Card=1)

41 2 FILTER
42 41 SORT (GROUP BY) (Cost=52 Card=1 Bytes=37)
43 42 TABLE ACCESS (BY INDEX ROWID) OF 'POLICY_TRANSLATION'
(Cost=44 Card=862 Bytes=31894)

44 43 INDEX (RANGE SCAN) OF 'IPOLICYTRNSLTN' (NON-UNIQUE)
(Cost=5 Card=862)

Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
25394348 consistent gets
0 physical reads
0 redo size
188 bytes sent via SQL*Net to client
3173 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

Report message to a moderator

Re: Performance tuning [message #209959 is a reply to message #209940] Mon, 18 December 2006 13:08 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Run you SQL after :

ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Format the trace file with TKPROF and post it.

Report message to a moderator

Previous Topic: LATCH FREE - HIGH DURING MID NIGHT
Next Topic: how to handle this wait event: "control file sequential read"?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 17 00:35:03 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.