Qs What is the Cause in particular (or in General) of
Time Difference between "cpu" & "elapsed" Columns in the following Query
?
Qs Is there Any Scope for improvement in the
following Query ?
Qs Is there any Best practise of working with Such
Tables ?
NOTE -
1) (tran_date , tran_id , part_tran_srl_num)
fields form the unique index on the Table
2) Some Other Columns of the Table are also
indexed
3) The Table is a Very Huge History Table to which only
INSERT & SELECT Operations happen
4) The Table is the Largest of ALL Tables in the
Database With a Size of about 100 GB
********************************************************************************
select del_flg, tran_type,
tran_sub_type, part_tran_type, gl_sub_head_code,
acid, TO_CHAR(value_date,'DD-MM-YYYY HH24:MI:SS'),
tran_amt||'!'||tran_crncy_code, tran_particular, entry_user_id,
pstd_user_id, vfd_user_id, TO_CHAR(entry_date,'DD-MM-YYYY HH24:MI:SS'),
TO_CHAR(pstd_date,'DD-MM-YYYY HH24:MI:SS'), TO_CHAR(vfd_date,'DD-MM-YYYY
HH24:MI:SS'), rpt_code, ref_num, instrmnt_type, TO_CHAR(instrmnt_date,
'DD-MM-YYYY HH24:MI:SS'), instrmnt_num, instrmnt_alpha, tran_rmks, pstd_flg,
prnt_advc_ind, amt_reservation_ind, reservation_amt||'!'||tran_crncy_code,
restrict_modify_ind, 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'),
cust_id, voucher_print_flg, module_id, br_code,
fx_tran_amt||'!'||crncy_code, rate_code, TO_CHAR(rate), crncy_code,
navigation_flg, tran_crncy_code, ref_crncy_code,
ref_amt||'!'||ref_crncy_code, sol_id, bank_code, trea_ref_num,
TO_CHAR(trea_rate), NVL(ts_cnt,0), rowid
FROM
TBA_CUM_TRAN_DETAIL_TBL WHERE tran_date = TO_DATE( :1 ,'DD-MM-YYYY
HH24:MI:SS') AND tran_id = :2 AND part_tran_srl_num = :3
acid, TO_CHAR(value_date,'DD-MM-YYYY HH24:MI:SS'),
tran_amt||'!'||tran_crncy_code, tran_particular, entry_user_id,
pstd_user_id, vfd_user_id, TO_CHAR(entry_date,'DD-MM-YYYY HH24:MI:SS'),
TO_CHAR(pstd_date,'DD-MM-YYYY HH24:MI:SS'), TO_CHAR(vfd_date,'DD-MM-YYYY
HH24:MI:SS'), rpt_code, ref_num, instrmnt_type, TO_CHAR(instrmnt_date,
'DD-MM-YYYY HH24:MI:SS'), instrmnt_num, instrmnt_alpha, tran_rmks, pstd_flg,
prnt_advc_ind, amt_reservation_ind, reservation_amt||'!'||tran_crncy_code,
restrict_modify_ind, 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'),
cust_id, voucher_print_flg, module_id, br_code,
fx_tran_amt||'!'||crncy_code, rate_code, TO_CHAR(rate), crncy_code,
navigation_flg, tran_crncy_code, ref_crncy_code,
ref_amt||'!'||ref_crncy_code, sol_id, bank_code, trea_ref_num,
TO_CHAR(trea_rate), NVL(ts_cnt,0), rowid
FROM
TBA_CUM_TRAN_DETAIL_TBL WHERE tran_date = TO_DATE( :1 ,'DD-MM-YYYY
HH24:MI:SS') AND tran_id = :2 AND part_tran_srl_num = :3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 1 0 1 0
Execute 20000 2.62 2.43 0 0 0 0
Fetch 20000 7.10 8.79 7705 100001 0 20000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40001 9.74 11.24 7706 100001 1 20000
Misses in library cache during parse:
1
Optimizer goal: RULE
Parsing user id: 20 (TBAGEN)
Optimizer goal: RULE
Parsing user id: 20 (TBAGEN)
Rows Row Source
Operation
------- ---------------------------------------------------
20000 TABLE ACCESS BY INDEX ROWID CUM_TRAN_DETAIL_TABLE
40000 INDEX UNIQUE SCAN (object id 10353)
------- ---------------------------------------------------
20000 TABLE ACCESS BY INDEX ROWID CUM_TRAN_DETAIL_TABLE
40000 INDEX UNIQUE SCAN (object id 10353)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
20000 TABLE ACCESS (BY INDEX ROWID) OF 'CUM_TRAN_DETAIL_TABLE'
40000 INDEX (UNIQUE SCAN) OF 'IDX_CUM_TRAN_DETAIL_TABLE' (UNIQUE)
