Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: tkprof output - Cause unknown ?
Vivek,
Given the intersection of the fields present in the where clause to those used in the index, what is the cardinality from your data? It seems there is not much filtering of unique data, as your index is getting 40,000 rows for the given values, per execution.
Also, would it be feasible in your system to not repeatedly do so many executions, but rather arrange for a single scan of the table to provide the aggregated sums by the columns you want? Thus a PL/SQL or Pro*C scan could go through the data ordered by the columns in the where clause, and provide totals in one shot.
Akshay Jain
-----Original Message-----
From: srinivas.katta_at_hartfordlife.com
[mailto:srinivas.katta_at_hartfordlife.com]
Sent: Monday, August 07, 2000 9:44 AM
To: Multiple recipients of list ORACLE-L
Subject: 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')
> >> rows
> call count cpu elapsed disk query current
>
>> 2249 SORT (AGGREGATE)
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
-- 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 also send the HELP command for other information (like subscribing). -- Author: INET: srinivas.katta_at_hartfordlife.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-LReceived on Tue Aug 08 2000 - 10:48:19 CDT