RE: tkprof output - Cause unknown ?

The Brackets have NOT been put in the Code , but by Oracle tkprof

Table is NOT analyzed , Hence RULE based optimization will apply

ACTION :-
1) Will check if index was analyzed or NOT

2) Created a NEW index in the same Order :- (ORIG_EXTN_CNTR_CODE, and ORIG_BR_CODE, ORIG_BANK_CODE,ORIG_DATE,IBA_NUM) CAUSE - IBA_NUM field was NOT getting in the UNIQUE Index as the 2 fields preceeding it in the Index i.e. tran_cat_code,schm_code are NOT part of the Where Clause

RESULT - Transaction Query time Reduced from 3 to 2 Hours

NOTE - We are Considering Chnging the Ordering of the Key fields of the index to allow fields
with the MOST variable Data (Cardinality) to come First on the Left in respective order :-
(IBA_NUM,ORIG_DATE,ORIG_BR_CODE,ORIG_BANK_CODE,ORIG_EXTN_CNTR_CODE) Thanks indeed

> -----Original Message-----
> From: Kresimir Fabijanic [SMTP:kresimir_at_ozemail.com.au]
> Sent: Sunday, August 06, 2000 2:59 PM
> To: VIVEK_SHARMA
> Subject: Re: tkprof output - Cause unknown ?
>
> Vivek
>
> Are you really sure you need al those brackets?
>
> All your conditions are 'AND'.
>
> Try running the querry without all those brackets as optimiser will
> evaluate
> each condition out of the context (which I think you do not want)
> alternativelly try adding 'RULE' hint if you think that your brackets are
> affecting the result set (given the fact that all your where conditions
> are
> 'AND', I doubt it very much).
>
> I beleieve what happens is that optimizer evaluates your first condition,
> and inputs it into second etc.all down your where clause. Also did you
> analysed the index and when?
>
> HTH
>
> Regards
>
> Kresimir Fabijanic
>
> ----- Original Message -----
> From: "VIVEK_SHARMA" <vivek_sharma_at_inf.com>
> To: <kelly.goodrich_at_lmco.com>; <oracledba_at_quickdoc.co.uk>;
> <ORACLE-L_at_fatcity.com>
> Sent: Sunday, August 06, 2000 2:09 PM
> Subject: tkprof output - Cause unknown ?
>
>
> > Kelly / List
> >
> > Your Comments on the following tkprof output please ?
> > Especially on the Large Value of query=94322530 & rows=88940824
> >
> > NOTE - IDX_INTER_BR_ORIG_TRAN_TABLE Index fields =
> > (orig_extn_cntr_code, orig_br_code, orig_bank_code,
> orig_date,tran_cat_code,
> > schm_code, iba_num, reversal_ind, srl_num, crncy_code)
> >
> > Qs. Does the index Need to be Chenged o Allow ALL fields of the where
> Clause
> > Below to Run on the index ?
> >
> > tkprof OUTPUT :-
> >
> > > select count(*)
> ,TO_CHAR(sum(DECODE(DR_CR_IND,'D',TRAN_AMT,'C',(TRAN_AMT*
> > > (-1))))) into :b0,:b1
> > > from
> > > IOT
> > > where ((((((ORIG_EXTN_CNTR_CODE=:b2
> > > and ORIG_BR_CODE=:b3)
> > > and ORIG_BANK_CODE=:b4)
> > > and ORIG_DATE=TO_DATE(:b5,'DD-MM-YYYY HH24:MI:SS'))
> > > and IBA_NUM=:b6)
> > > and REVERSAL_IND<>'AD')
> > > and MATCHED_FLG='N')
> > >
> > >
> > > call count cpu elapsed disk query current
> > > rows
> > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > ----------
> > > Parse 1 0.00 0.00 0 0 0
> > > 0
> > > Execute 2234 0.29 0.23 0 0 0
> > > 2233
> > > Fetch 2234 1727.27 1739.28 0 94322530 0
> > > 2229
> > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > ----------
> > > total 4469 1727.56 1739.51 0 94322530 0
> > > 4462
> > >
> > > Misses in library cache during parse: 1
> > > Optimizer goal: CHOOSE
> > > Parsing user id: 10 (TBAADM)
> > >
> > > Rows Execution Plan
> > > ------- ---------------------------------------------------
> > > 0 SELECT STATEMENT GOAL: CHOOSE
> > > 2249 SORT (AGGREGATE)
> > > 88940824 TABLE ACCESS (BY ROWID) OF 'INTER_BR_ORIG_TRAN_TABLE'
> > > 88943058 INDEX (RANGE SCAN) OF 'IDX_INTER_BR_ORIG_TRAN_TABLE'
> (UNIQUE)
> > >
> > >
> >
> > --------
> > If you're bored, then visit the list's website: http://www.lazydba.com
> (updated daily)
> > to unsubscribe, send a blank email to
Received on Mon Aug 07 2000 - 06:48:39 CDT