Home » RDBMS Server » Performance Tuning » Performance tunning (Oracle 9i)
Performance tunning [message #340974] Fri, 15 August 2008 06:33 Go to next message
manoj12
Messages: 185
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 #340975 is a reply to message #340974] Fri, 15 August 2008 06:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are your statistics up to date?
Re: Performance tunning [message #340977 is a reply to message #340974] Fri, 15 August 2008 06:52 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post TKPROF, indexes definitions, table stats.
Re: Performance tunning [message #341007 is a reply to message #340977] Fri, 15 August 2008 08:46 Go to previous messageGo to next message
manoj12
Messages: 185
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
manoj12
Messages: 185
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 #341256 is a reply to message #341250] Mon, 18 August 2008 00:37 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Any update on the above

Regards
Re: Performance tunning [message #341820 is a reply to message #341256] Wed, 20 August 2008 07:42 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Query Tunning
Next Topic: solaris 10
Goto Forum:
  


Current Time: Wed Dec 07 05:11:51 CST 2016

Total time taken to generate the page: 0.14169 seconds