Re: SQL Trace

On Tuesday 19 November 2002 22:03, you wrote:
> Qs What is the Cause in particular (or in General) of Time Difference
> between "cpu" & "elapsed" Columns in the following Query ?
>

e = c + wait time (of anykind) (+ rounding errors)

> Qs Is there Any Scope for improvement in the following Query ?
>

There probably is.

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

-- 
----------------------------------------------------------------
Anjo Kolk
http://www.oraperf.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.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).