Oracle-L: SQL Trace

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Tue, 19 Nov 2002 22:03:26 -0800
Message-ID: <F001.0050788E.20021119220326@fatcity.com>

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

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)

Rows Row Source Operation

-------  ---------------------------------------------------

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)
--

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

Author: VIVEK_SHARMA
INET: VIVEK_SHARMA_at_infosys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Nov 20 2002 - 00:03:26 CST