SQL Trace Qs.

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Wed, 21 Apr 2004 16:43:15 +0530
Message-ID: <F0CB3C9983B77E4AB4ADEFA63DAB109F071144FE@twrmsg03.ad.infosys.com>

Folks

What could be the Cause for HIGH Value of elapsed time=3D3.27 in the following SELECT Queries?

NOTE - It is doing a UNIQUE Index Scan=20

Will provide any info required

Thanks

---

---

SELECT COUNT(*)
FROM
 TSD WHERE SCHM_CODE =3D :1 AND FLOW_CODE =3D :2 call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.01          0          0          0   0
Execute    268      0.08       3.27          0          0          0   0
Fetch      268      0.04       0.11          0        536          0

268
------- ------ -------- ---------- ---------- ---------- ----------

---

total 537 0.12 3.40 0 536 0 268

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 12 (TBAGEN)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (AGGREGATE)
      0    TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
               'TD_SCHM_DEFN_TABLE'
      0     INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                'IDX_TD_SCHM_DEFN_TABLE' (UNIQUE)


************************************************************************

---

select del_flg ,gst_upd_flg ,fst_upd_flg ,lchg_user_id
,TO_CHAR(lchg_time,

  'DD-MM-YYYY HH24:MI:SS') ,rcre_user_id ,TO_CHAR(rcre_time,'DD-MM-YYYY   HH24:MI:SS') ,iso_flg ,eabfab_upd_flg ,lift_lien_flg ,proxy_post_ind ,   si_srl_num ,TO_CHAR(si_org_exec_date,'DD-MM-YYYY HH24:MI:SS')
,pr_srl_num , serial_num ,del_memo_pad ,module_id ,module_key
,TO_CHAR(reversal_date,

  'DD-MM-YYYY HH24:MI:SS') ,TO_CHAR(reversal_value_date,'DD-MM-YYYY   HH24:MI:SS') ,pttm_event_type ,proxy_acid ,tod_entity_type
,tod_entity_id,

  ign_neg_lien_or_carv_flg ,NVL(ts_cnt,0) ,dtd_sol_id ,dtd_pstd_flg , =20
dth_init_sol_id,TO_CHAR(regularization_amt),TO_CHAR(principal_portion_am t)

   ,dtd_tran_crncy_code ,tf_entity_sol_id ,rowid=20 into :b0,:b1,:b2,:b3,:b4,:b5,
:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15,:b16,:b17,:b18,:b19,:b20,: b21,
  :b22,:b23,:b24,:b25,:b26,:b27,:b28,:b29,:b30,:b31,:b32,:b33 from
 TBA_TEMP_DAILY_TRAN_TBL=20
where ((tran_date=3DTO_DATE(:b34,'DD-MM-YYYY HH24:MI:SS')=20 and tran_id=3D:b35) and part_tran_srl_num=3D:b36)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.02       1.27          0          0          0   0
Execute    268      0.14       8.71          0          0          0   0
Fetch      268      0.45       4.36        128      25393          0

268
------- ------ -------- ---------- ---------- ---------- ----------

---

total 537 0.61 14.35 128 25393 0 268

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 12 (TBAGEN)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
              'TEMP_DAILY_TRAN_TABLE'
      0    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
               'IDX_TEMP_DAILY_TRAN_TABLE' (UNIQUE)

************************************************************************

---

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Apr 21 2004 - 07:58:24 CDT