SQL Trace Qs.
- From: "VIVEK_SHARMA" <VIVEK_SHARMA@xxxxxxxxxxx>
- To: "LazyDBA.com Discussion" <oracledba@xxxxxxxxxxx>,<oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Apr 2004 16:43:15 +0530
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
