Home » RDBMS Server » Performance Tuning » Performance tunning (Oracle 9i)
| Performance tunning [message #340974] |
Fri, 15 August 2008 06:33  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
MY query is getting retrieved in 3 -4 seconds to execute the above query
select rownum as Sr_No,dc2.tfg_id,dc2.fac_detail,
dc2.particulars,
dc2.recd,dc2.sv, dc2.diffral,
dc2.diff_auth,
dc2.seal,
dc2.stmpduty,
dc2.stmpppr,
dc2.remarks ,
rmrk.mkr_name,
rmrk.last_edited_by_dt,
rmrk.mkr_remarks,
rmrk.ckr_name,rmrk.last_checked_by_dt,
rmrk.ckr_remarks from
c3vw_ckr_trndisbchklst2_rpt dc2,
( select atps.trn_cam_id , atps.base_entity_id , atps.trn_c3_fac_group_id
, atps.mstwf_process_item_id , atps.parentid
, decode(mkr.id,0, '', mkr.first_name || ' ' || replace(mkr.middle_name, '~', '') || ' ' || replace(mkr.last_name, '~', '')) as mkr_name
, decode(mkr.id,0, '', to_char(tps.last_edited_by_dt, 'dd/mm/yyyy hh24:mi:ss')) as last_edited_by_dt
, replace(tps.mkr_remarks, '~', ' ') as mkr_remarks
, decode(ckr.id,0, '', ckr.first_name || ' ' || replace(ckr.middle_name, '~', '') || ' ' || replace(ckr.last_name, '~', '')) as ckr_name
, decode(ckr.id,0, '', to_char(atps.last_checked_by_dt, 'dd/mm/yyyy hh24:mi:ss')) as last_checked_by_dt
, decode(ckr.id,0, '', replace(atps.ckr_remarks, '~', ' ')) as ckr_remarks from umstusers mkr , umstusers ckr , trn_proc_state tps , aud_trn_proc_state atps ,
(
select tps.base_entity_id, tps.trn_cam_id,tps.trn_c3_fac_group_id, tps.mstwf_process_item_id
, max(tps.aud_id) as aud_id
from aud_trn_proc_state tps
where
FN_C3_GETLASTCKRDT_WKF(tps.base_entity_id, tps.trn_cam_id, tps.trn_c3_fac_group_id, tps.mstwf_process_item_id) >= tps.trans_datetime
and tps.base_entity_id = 2865
and tps.trn_cam_id = 6854
and tps.parentid in (4,9,19,26)
group by tps.base_entity_id, tps.trn_cam_id, tps.trn_c3_fac_group_id, tps.mstwf_process_item_id
) ps
where ps.aud_id = atps.aud_id
and ps.base_entity_id = atps.base_entity_id
and ps.trn_cam_id = atps.trn_cam_id
and ps.trn_c3_fac_group_id = atps.trn_c3_fac_group_id
and ps.mstwf_process_item_id = atps.mstwf_process_item_id
and atps.base_entity_id = tps.base_entity_id
and atps.trn_cam_id = tps.trn_cam_id
and atps.trn_c3_fac_group_id = tps.trn_c3_fac_group_id
and atps.mstwf_process_item_id = tps.mstwf_process_item_id
and atps.mstwf_process_item_id = 21
and tps.last_edited_by = mkr.id
and atps.last_checked_by = ckr.id) rmrk
where dc2.base_entity_id = rmrk.base_entity_id
and dc2.tfg_id = rmrk.trn_c3_fac_group_id
and dc2.base_entity_id = 2865 and
dc2.sub_process_id = 6854
After looking at the query I can see that the function is taking a lot of time to execute the below query.I am also sending you the explain plan of the above query
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=9132)
1 0
COUNT
2 1
HASH JOIN (Cost=34 Card=1 Bytes=9132)
3 2
VIEW (Cost=15 Card=1 Bytes=4924)
4 3
SORT (UNIQUE) (Cost=14 Card=1 Bytes=452)
5 4
HASH JOIN (Cost=12 Card=1 Bytes=452)
6 5
MERGE JOIN (CARTESIAN) (Cost=9 Card=1 Bytes=352)
7 6
MERGE JOIN (CARTESIAN) (Cost=7 Card=1 Bytes=252)
8 7
HASH JOIN (Cost=5 Card=1 Bytes=152)
9 8
TABLE ACCESS (FULL) OF 'TRN_DISB_CHK1' (Cost=2 Card=1 Bytes=52)
10 8
TABLE ACCESS (FULL) OF 'TRN_C3_FAC_GROUP' (Cost=2 Card=1 Bytes=100)
11 7
BUFFER (SORT) (Cost=5 Card=7 Bytes=700)
12 11
TABLE ACCESS (FULL) OF 'MST_ANSWERS' (Cost=2 Card=7 Bytes=700)
13 6
BUFFER (SORT) (Cost=7 Card=95 Bytes=9500)
14 13
TABLE ACCESS (FULL) OF 'MST_QUESTIONS' (Cost=2 Card=95 Bytes=9500)
15 5
TABLE ACCESS (FULL) OF 'TRN_DISB_CHK5' (Cost=2 Card=1 Bytes=100)
16 2
VIEW (Cost=18 Card=1 Bytes=4208)
17 16
FILTER
18 17
SORT (GROUP BY) (Cost=18 Card=1 Bytes=449)
19 18
HASH JOIN (Cost=16 Card=1 Bytes=449)
20 19
TABLE ACCESS (BY INDEX ROWID) OF 'AUD_TRN_PROC_STATE' (Cost=2 Card=1 Bytes=100)
21 20
NESTED LOOPS (Cost=13 Card=1 Bytes=349)
22 21
MERGE JOIN (CARTESIAN) (Cost=11 Card=1 Bytes=249)
23 22
MERGE JOIN (CARTESIAN) (Cost=5 Card=1 Bytes=200)
24 23
TABLE ACCESS (FULL) OF 'TRN_PROC_STATE' (Cost=3 Card=1 Bytes=100)
25 23
BUFFER (SORT) (Cost=2 Card=226 Bytes=22600)
26 25
TABLE ACCESS (FULL) OF 'UMSTUSERS' (Cost=2 Card=226 Bytes=22600)
27 22
BUFFER (SORT) (Cost=9 Card=1 Bytes=49)
28 27
TABLE ACCESS (BY INDEX ROWID) OF 'AUD_TRN_PROC_STATE' (Cost=6 Card=1 Bytes=49)
29 28
INDEX (RANGE SCAN) OF 'IDX_AUD_TRN_PROC_STATE' (NON-UNIQUE) (Cost=1 Card=35)
30 21
INDEX (RANGE SCAN) OF 'IDX_AUD_TRN_PROC_STATE' (NON-UNIQUE) (Cost=1 Card=2)
31 19
TABLE ACCESS (FULL) OF 'UMSTUSERS' (Cost=2 Card=226 Bytes=22600)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
922 bytes sent via SQL*Net to client
1057 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
Please help to optimise this query.I wanted to execute this query in less than one second
Regards
[Updated on: Fri, 15 August 2008 06:34] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Performance tunning [message #341007 is a reply to message #340977] |
Fri, 15 August 2008 08:46  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
Please check the tkprof stats
I have taken one composite index on three columns
select rownum as Sr_No,dc2.tfg_id,dc2.fac_detail,dc2.particulars,dc2.recd,dc2.sv, dc2.diffral, dc2.diff_auth, dc2.seal, dc2.stmpduty, dc2.stmpppr, dc2.remarks ,rmrk.mkr_name, rmrk.last_edited_by_dt,rmrk.mkr_remarks, rmrk.ckr_name,rmrk.last_checked_by_dt, rmrk.ckr_remarks from c3vw_ckr_trndisbchklst2_rpt dc2, ( select atps.trn_cam_id , atps.base_entity_id , atps.trn_c3_fac_group_id
, atps.mstwf_process_item_id , atps.parentid
, decode(mkr.id,0, '', mkr.first_name || ' ' || replace(mkr.middle_name, '~', '') || ' ' || replace(mkr.last_name, '~', '')) as mkr_name
, decode(mkr.id,0, '', to_char(tps.last_edited_by_dt, 'dd/mm/yyyy hh24:mi:ss')) as last_edited_by_dt
, replace(tps.mkr_remarks, '~', ' ') as mkr_remarks
, decode(ckr.id,0, '', ckr.first_name || ' ' || replace(ckr.middle_name, '~', '') || ' ' || replace(ckr.last_name, '~', '')) as ckr_name
, decode(ckr.id,0, '', to_char(atps.last_checked_by_dt, 'dd/mm/yyyy hh24:mi:ss')) as last_checked_by_dt
, decode(ckr.id,0, '', replace(atps.ckr_remarks, '~', ' ')) as ckr_remarks
from umstusers mkr
, umstusers ckr
, trn_proc_state tps
, aud_trn_proc_state atps
,
(
select tps.base_entity_id, tps.trn_cam_id,tps.trn_c3_fac_group_id, tps.mstwf_process_item_id
, max(tps.aud_id) as aud_id
from aud_trn_proc_state tps
where
FN_C3_GETLASTCKRDT_WKF(tps.base_entity_id, tps.trn_cam_id, tps.trn_c3_fac_group_id, tps.mstwf_process_item_id) >= tps.trans_datetime
and tps.base_entity_id = 2865
and tps.trn_cam_id = 68254
and tps.parentid in (4,9,19,26)
group by tps.base_entity_id, tps.trn_cam_id, tps.trn_c3_fac_group_id, tps.mstwf_process_item_id
) ps
where ps.aud_id = atps.aud_id
and ps.base_entity_id = atps.base_entity_id
and ps.trn_cam_id = atps.trn_cam_id
and ps.trn_c3_fac_group_id = atps.trn_c3_fac_group_id
and ps.mstwf_process_item_id = atps.mstwf_process_item_id
and atps.base_entity_id = tps.base_entity_id
and atps.trn_cam_id = tps.trn_cam_id
and atps.trn_c3_fac_group_id = tps.trn_c3_fac_group_id
and atps.mstwf_process_item_id = tps.mstwf_process_item_id
and atps.mstwf_process_item_id = 21
and tps.last_edited_by = mkr.id
and atps.last_checked_by = ckr.id ) rmrk
where dc2.base_entity_id = rmrk.base_entity_id
and dc2.tfg_id = rmrk.trn_c3_fac_group_id
and dc2.base_entity_id = 2865 and dc2.sub_process_id = 6854
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.21 0.19 0 59 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.26 0.24 0 61 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 96 (IRS2007_ubs_C3)
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT
0 HASH JOIN
16 VIEW
16 SORT UNIQUE
589 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 TABLE ACCESS FULL TRN_DISB_CHK2
3 TABLE ACCESS FULL MST_YESNONA
3 TABLE ACCESS FULL MST_YESNONA
3 TABLE ACCESS FULL MST_YESNONA
3 TABLE ACCESS FULL MST_YESNONA
3 TABLE ACCESS FULL MST_YESNONA
26 TABLE ACCESS FULL TRN_DISB_CHK1
26 TABLE ACCESS FULL TRN_C3_FAC_GROUP
204 TABLE ACCESS FULL MST_DEFERRAL_AUTH
631 TABLE ACCESS FULL TRN_DOC_REG
0 VIEW
0 FILTER
0 SORT GROUP BY
0 TABLE ACCESS BY INDEX ROWID AUD_TRN_PROC_STATE
1 NESTED LOOPS
0 MERGE JOIN CARTESIAN
0 HASH JOIN
0 MERGE JOIN CARTESIAN
0 TABLE ACCESS FULL TRN_PROC_STATE
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID AUD_TRN_PROC_STATE
0 INDEX RANGE SCAN IDX_AUD_TRN_PROC_STATE (object id 113809)
0 TABLE ACCESS FULL UMSTUSERS
0 BUFFER SORT
0 TABLE ACCESS FULL UMSTUSERS
0 INDEX RANGE SCAN IDX_AUD_TRN_PROC_STATE (object id 113809)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 COUNT
0 HASH JOIN
16 VIEW OF 'C3VW_CKR_TRNDISBCHKLST2_RPT'
16 SORT (UNIQUE)
589 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 HASH JOIN
16 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'TRN_DISB_CHK2'
3 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'MST_YESNONA'
3 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
3 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
3 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
3 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
26 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TRN_DISB_CHK1'
26 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TRN_C3_FAC_GROUP'
204 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_DEFERRAL_AUTH'
631 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TRN_DOC_REG'
0 VIEW
0 FILTER
0 SORT (GROUP BY)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'AUD_TRN_PROC_STATE'
1 NESTED LOOPS
0 MERGE JOIN (CARTESIAN)
0 HASH JOIN
0 MERGE JOIN (CARTESIAN)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TRN_PROC_STATE'
0 BUFFER (SORT)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'AUD_TRN_PROC_STATE'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_AUD_TRN_PROC_STATE' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'UMSTUSERS'
0 BUFFER (SORT)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'UMSTUSERS'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_AUD_TRN_PROC_STATE' (NON-UNIQUE)
********************************************************************************
SELECT tcf.id, mbf.bank_facility_name, tcf.proposed_limit
from trn_c3_fac_group_dtls dn, trn_c3_fac tcf, mst_bank_facility mbf
where dn.trn_c3_fac_id = tcf.id and
tcf.mst_bank_facility_id = mbf.id and
dn.trn_c3_fac_group_id = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1178 0.04 0.02 0 0 0 0
Fetch 2356 0.92 1.02 0 18848 0 1178
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3535 0.96 1.05 0 18848 0 1178
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 96 (IRS2007_ubs_C3) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 HASH JOIN
0 MERGE JOIN (CARTESIAN)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TRN_C3_FAC_GROUP_DTLS'
0 BUFFER (SORT)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_BANK_FACILITY'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TRN_C3_FAC'
********************************************************************************
SELECT *
FROM
c3vw_ckr_trndisbchklst2_rpt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.01 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 96 (IRS2007_ubs_C3)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 VIEW OF 'C3VW_CKR_TRNDISBCHKLST2_RPT'
0 SORT (UNIQUE)
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TRN_DOC_REG'
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_DEFERRAL_AUTH'
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TRN_C3_FAC_GROUP'
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TRN_DISB_CHK1'
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TRN_DISB_CHK2'
********************************************************************************
select rownum as Sr_No,dc2.tfg_id,dc2.fac_detail,dc2.particulars,dc2.recd,dc2.sv, dc2.diffral, dc2.diff_auth, dc2.seal, dc2.stmpduty, dc2.stmpppr, dc2.remarks ,rmrk.mkr_name, rmrk.last_edited_by_dt,rmrk.mkr_remarks, rmrk.ckr_name,rmrk.last_checked_by_dt, rmrk.ckr_remarks from c3vw_ckr_trndisbchklst2_rpt dc2, ( select atps.trn_cam_id , atps.base_entity_id , atps.trn_c3_fac_group_id
, atps.mstwf_process_item_id , atps.parentid
, decode(mkr.id,0, '', mkr.first_name || ' ' || replace(mkr.middle_name, '~', '') || ' ' || replace(mkr.last_name, '~', '')) as mkr_name
, decode(mkr.id,0, '', to_char(tps.last_edited_by_dt, 'dd/mm/yyyy hh24:mi:ss')) as last_edited_by_dt
, replace(tps.mkr_remarks, '~', ' ') as mkr_remarks
, decode(ckr.id,0, '', ckr.first_name || ' ' || replace(ckr.middle_name, '~', '') || ' ' || replace(ckr.last_name, '~', '')) as ckr_name
, decode(ckr.id,0, '', to_char(atps.last_checked_by_dt, 'dd/mm/yyyy hh24:mi:ss')) as last_checked_by_dt
, decode(ckr.id,0, '', replace(atps.ckr_remarks, '~', ' ')) as ckr_remarks
from umstusers mkr
, umstusers ckr
, trn_proc_state tps
, aud_trn_proc_state atps
,
(
select tps.base_entity_id, tps.trn_cam_id,tps.trn_c3_fac_group_id, tps.mstwf_process_item_id
, max(tps.aud_id) as aud_id
from aud_trn_proc_state tps
where
FN_C3_GETLASTCKRDT_WKF(tps.base_entity_id, tps.trn_cam_id, tps.trn_c3_fac_group_id, tps.mstwf_process_item_id) >= tps.trans_datetime
and tps.base_entity_id = 2865
and tps.trn_cam_id = 6854
and tps.parentid in (4,9,19,26)
group by tps.base_entity_id, tps.trn_cam_id, tps.trn_c3_fac_group_id, tps.mstwf_process_item_id
) ps
where ps.aud_id = atps.aud_id
and ps.base_entity_id = atps.base_entity_id
and ps.trn_cam_id = atps.trn_cam_id
and ps.trn_c3_fac_group_id = atps.trn_c3_fac_group_id
and ps.mstwf_process_item_id = atps.mstwf_process_item_id
and atps.base_entity_id = tps.base_entity_id
and atps.trn_cam_id = tps.trn_cam_id
and atps.trn_c3_fac_group_id = tps.trn_c3_fac_group_id
and atps.mstwf_process_item_id = tps.mstwf_process_item_id
and atps.mstwf_process_item_id = 21
and tps.last_edited_by = mkr.id
and atps.last_checked_by = ckr.id ) rmrk
where dc2.base_entity_id = rmrk.base_entity_id
and dc2.tfg_id = rmrk.trn_c3_fac_group_id
and dc2.base_entity_id = 2865 and dc2.sub_process_id = 6854
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1.64 1.62 0 122181 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.68 1.67 0 122183 0 16
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 96 (IRS2007_ubs_C3)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 COUNT
0 HASH JOIN
0 VIEW OF 'C3VW_CKR_TRNDISBCHKLST2_RPT'
0 SORT (UNIQUE)
0 HASH JOIN
0 HASH JOIN
0 HASH JOIN
0 HASH JOIN
0 HASH JOIN
0 HASH JOIN
0 HASH JOIN
0 HASH JOIN
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'TRN_DISB_CHK2'
0 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'MST_YESNONA'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_YESNONA'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TRN_DISB_CHK1'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TRN_C3_FAC_GROUP'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MST_DEFERRAL_AUTH'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TRN_DOC_REG'
0 VIEW
0 FILTER
0 SORT (GROUP BY)
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'AUD_TRN_PROC_STATE'
0 NESTED LOOPS
0 MERGE JOIN (CARTESIAN)
0 MERGE JOIN (CARTESIAN)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TRN_PROC_STATE'
0 BUFFER (SORT)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'UMSTUSERS'
0 BUFFER (SORT)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'AUD_TRN_PROC_STATE'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_AUD_TRN_PROC_STATE' (NON-UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_AUD_TRN_PROC_STATE' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'UMSTUSERS'
********************************************************************************
SELECT count(*) from c3vw_last_ckrdt_wkf wdt
where
wdt.base_entity_id = :b4 and
wdt.trn_cam_id = :b3 and
wdt.trn_c3_fac_group_id = :b2 and
wdt.mstwf_process_item_id = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 39 0.00 0.00 0 0 0 0
Fetch 39 0.03 0.02 0 1623 0 39
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 79 0.04 0.03 0 1623 0 39
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 96 (IRS2007_ubs_C3) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 VIEW OF 'C3VW_LAST_CKRDT_WKF'
0 SORT (GROUP BY)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'AUD_TRN_PROC_STATE'
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MSTWF_MKR_CHKR_STATE'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_AUD_TRN_PROC_STATE' (NON-UNIQUE)
********************************************************************************
SELECT wdt.last_checked_by_dt from c3vw_last_ckrdt_wkf wdt
where
wdt.base_entity_id = :b4 and
wdt.trn_cam_id = :b3 and
wdt.trn_c3_fac_group_id = :b2 and
wdt.mstwf_process_item_id = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 39 0.01 0.00 0 0 0 0
Fetch 39 0.01 0.02 0 1623 0 39
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 79 0.03 0.02 0 1623 0 39
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 96 (IRS2007_ubs_C3) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (GROUP BY)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'AUD_TRN_PROC_STATE'
0 NESTED LOOPS
0 SORT (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'MSTWF_MKR_CHKR_STATE'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_AUD_TRN_PROC_STATE' (NON-UNIQUE)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 14 0.12 0.11 0 4 0 0
Execute 13 0.00 0.00 0 0 0 8
Fetch 4 1.85 1.82 0 122246 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31 1.98 1.94 0 122250 0 26
Misses in library cache during parse: 5
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.01 0.00 0 0 0 0
Execute 1270 0.06 0.03 0 0 0 0
Fetch 2448 0.96 1.07 0 22131 0 1270
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3728 1.04 1.11 0 22131 0 1270
Misses in library cache during parse: 4
18 user SQL statements in session.
7 internal SQL statements in session.
25 SQL statements in session.
7 statements EXPLAINed in this session.
********************************************************************************
Trace file: imacs_ora_3584.trc
Trace file compatibility: 9.00.01
Sort options: default
1 session in tracefile.
18 user SQL statements in trace file.
7 internal SQL statements in trace file.
25 SQL statements in trace file.
13 unique SQL statements in trace file.
7 SQL statements EXPLAINed using schema:
IRS2007_ubs_C3.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
4035 lines in trace file.
Please check the tkprof utility
I have one index on three columns
base_entity_id,
tps.trn_cam_id,
parentid
Please help to resolve the issue
Regards
|
|
|
|
| Re: Performance tunning [message #341046 is a reply to message #341007] |
Fri, 15 August 2008 17:09 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1.64 1.62 0 122181 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.68 1.67 0 122183 0 16
Your TKPROF is showing that every step of the plan is picking up 0 rows, but still 122,181 buffers have been retrieved from the row cache. Are all of the tables empty?
If so, did any of the table PREVIOUSLY contain many rows that have now been deleted. This empty space left behind can affect queries. Search HWM for more on this.
Alternatively, your full table scans could be scanning many rows and then your filter criteria could be filtering out every row.
We need to know how many rows are in each table.
Ross Leishman
|
|
|
|
| Re: Performance tunning [message #341250 is a reply to message #341046] |
Mon, 18 August 2008 00:00 |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
Well let me tell you that in the query there is a view in the query by the name
c3vw_ckr_trndisbchklst2_rpt.This query is taking 6.625 seconds to execute it
select distinct
tdc2.BASE_ENTITY_ID, tdc2.PROCESS_ID, tdc2.SUB_PROCESS_ID,
tfg.id as tfg_id,
replace(fn_getfacgrpfacdtls(tfg.id), '<br/>', chr(13)) as fac_detail ,
tdc2.PARTICULARS ,
yn_rec.yesnona as recd,
yn_sv.yesnona as sv,
replace(fngetdate(tdc2.DEFERRAL_IN_PLACE), '01/01/1235', '') as diffral,
decode(da.id, 0, ' ', da.deferral_auth) AS diff_auth,
yna_seal.yesnona as seal ,
yna_stmpduty.yesnona as stmpduty,
yna_stmpppr.yesnona as stmpppr,
replace(tdc2.REMARKS, '~', '') as remarks,
tdr.mst_doc_id,
tdr.mst_doc_catg_id
from trn_disb_chk2 tdc2, mst_yesnona yn_rec, mst_yesnona yn_sv, mst_deferral_auth da, mst_yesnona yna_seal, mst_yesnona yna_stmpduty, mst_yesnona yna_stmpppr,
trn_disb_chk1 tdc1, trn_c3_fac_group tfg, trn_doc_reg tdr
where tdc2.received = yn_rec.id and
tdc2.sv_done = yn_sv.id and
tdc2.deferral_authority_id = da.id and
tdc2.common_seal_affixed = yna_seal.id and
tdc2.stmp_ppr_lt_6mth = yna_stmpppr.id and
tdc2.corrt_stmp_duty_pd = yna_stmpduty.id and
tdc2.trn_disb_chk1_id = tdc1.id and
tdc1.trn_c3_fac_group_id = tfg.id and
tdr.mst_doc_id = tdc2.trn_doc_reg_id
order by tdc2.base_entity_id, tdc2.sub_process_id, tfg.id, tdr.mst_doc_catg_id, tdr.mst_doc_id
To execute this view it is taking 6.625 seconds to execute it
In the umstusers there are 226 rows
in the aud_proc_trn_state 20245 rows
In the trn_proc_state there are 552 rows
The above view as well as the function by the name
FN_C3_GETLASTCKRDT_WKF(tps.base_entity_id, tps.trn_cam_id, tps.trn_c3_fac_group_id, tps.mstwf_process_item_id) >= tps.trans_datetime
in the query is killing the performance
Can you please help me to optimise the view as well as the function to increase the performance,
Regards
[Updated on: Mon, 18 August 2008 00:00] Report message to a moderator |
|
|
|
|
|
| Re: Performance tunning [message #341820 is a reply to message #341256] |
Wed, 20 August 2008 07:42 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Sorry, your query is too complex, and I have too little spare time.
If your function contains SQL, then you need to work out a different way to run this without using the function. See this article - http://www.orafaq.com/node/1981
If your view contains DISTINCT, GROUP BY, or other complexities, it may affect Oracle's ability to optimise the overall query. You could try writing the query from first principals without the view.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 14:50:06 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
