RE: tkprof Trace Output Evaluation

A Small Addition :-
SST Table only has 50 Records with index on (set_id,sol_id)

Qs. Under the Column "count" , Is a SMALL Value of "Execute" ( = 2) Versus a
Large Value of "Fetch" ( = 2525 ) considered Good or Expensive for performance ?
Qs. What does the above mean to indicate ? I am Quite basic in tkprof evaluation .
I have Generally Seen "count-Execute" EQUAL to "count-Fetch" , for Other Traces

Thanks so much

> -----Original Message-----
> From: Bill Zakrzewski [SMTP:bill_at_intactus.com]
> Sent: Tuesday, October 02, 2001 1:59 AM
> To: LazyDBA.com Discussion; VIVEK_SHARMA
> Subject: Re: tkprof Trace Output Evaluation
>
> I quickly looked at your query and one improvement you can make is to
> remove
> the
>
> "WHERE B.sol_id
> in (
> SELECT sol_id from SST WHERE set_id = :1 ) "
>
> and replace it with
>
> WHERE exists (select 'x' from SST where sol_id=B.sol_id
> and set_id = :1)
>
>
> ----- Original Message -----
> From: "VIVEK_SHARMA" <VIVEK_SHARMA_at_infy.com>
> To: "LazyDBA.com Discussion" <oracledba_at_lazydba.com>
> Sent: Monday, October 01, 2001 4:18 PM
> Subject: tkprof Trace Output Evaluation
>
>
>
>
> Under the Column "count" , Is a SMALL Value of "Execute" ( = 2) Versus
> a
> Large Value of "Fetch" ( = 2525 ) considered Good or Expensive for
> performance ?
>
> NOTE -
> TBA_FLEXI_FIXED_PARM_TBL Contains 600,000 records only
> 2 indexes exist on TBA_FLEXI_FIXED_PARM_TBL
> Index 1 = UNIQUE index on acid
> Index 2 = Index on sol_id
>
> TBA_GENERAL_ACCT_MAST_TBL Contains about 11,000,000 records
>
> Following is the Detailed Trace :-
>
> **********************************************************************
> **
> ********
>
> SELECT A.ACID
> FROM
> TBA_FLEXI_FIXED_PARM_TBL A, TBA_GENERAL_ACCT_MAST_TBL B WHERE
> B.sol_id
> in (
> SELECT sol_id from SST WHERE set_id = :1 ) AND B.ACID = A.ACID AND
> ( A.NEXT_AUTO_CR_DATE IS NULL OR A.NEXT_AUTO_CR_DATE <= TO_DATE(
> :2 ,
> 'DD-MM-YYYY HH24:MI:SS') OR ( A.AUTO_CR_FREQ_HLDY_STAT = 'P' AND
> A.NEXT_AUTO_CR_DATE >TO_DATE( :3 , 'DD-MM-YYYY HH24:MI:SS') AND
> A.NEXT_AUTO_CR_DATE <TO_DATE( :4 ,'DD-MM-YYYY HH24:MI:SS') ) )
> AND
> A.DEL_FLG != 'Y' AND A.ENTITY_CRE_FLG = 'Y' AND
> A.AUTO_CR_FROM_OPER_ACCT_FLG = 'Y' ORDER BY B.CRNCY_CODE,
> B.ACCT_CRNCY_CODE, B.SCHM_CODE, B.FORACID
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.01 0.01 0 0 0
> 0
> Execute 2 0.01 0.01 0 0 0
> 0
> Fetch 2525 78.62 111.15 77441 2538463 11
> 25248
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2528 78.64 111.17 77441 2538463 11
> 25248
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 20 (TBAGEN)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 SORT (ORDER BY)
> 0 NESTED LOOPS
> 0 NESTED LOOPS
> 0 PARTITION RANGE (ALL) PARTITION: START=1 STOP=2
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'FLEXI_FIXED_PARM_TABLE' PARTITION: START=1 STOP=2
> 0 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID) OF
> 'GENERAL_ACCT_MAST_TABLE' PARTITION:ROW LOCATION
> 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> 'IDX_GENERAL_ACCT_MAST_TABLE' (UNIQUE)
> 0 VIEW OF 'VW_NSO_1'
> 0 SORT (UNIQUE)
> 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'IDX_SOL_ID_SET_TABLE' (UNIQUE)
>
> **********************************************************************
> **
> ********
>
>
>
> --------
> Oracle documentation is here:
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to
> oracledba-unsubscribe_at_LAZYDBA.com
> To subscribe: send a blank email to oracledba-subscribe_at_LAZYDBA.com
> Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
>
>
>

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