Re: tkprof output - Cause unknown ?

VIVEK I think this should help you.

After analyzing the concerned tables, kindly try to include the HINT given below in the select statement and try to do an explain plan for the statement.

select /*+INDEX(TABLE_NAME,INDEX_NAME_ON_THAT_TABLE) */

KAS VIVEK_SHARMA <vivek_sharma_at_inf.com> on 08/06/2000 01:54:00 AM

Please respond to ORACLE-L_at_fatcity.com

                                                                            
                                                                            
                                                                            


                                                              
                                                              
                                                              
 To:      Multiple recipients of list ORACLE-L                
          <ORACLE-L_at_fatcity.com>                              
                                                              
 cc:      (bcc: Srinivas Katta/HLIFE)                         
                                                              
                                                              
                                                              
 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)
>
>

--
Author: VIVEK_SHARMA
  INET: vivek_sharma_at_inf.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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