Re: Help - tkprof output different for 2 schemas with same objects

you should include your select statement without it, it is very hard to say
there are many possible explanations for this difference one can be, that when you select more than 20% (or something like that) of the data in the table, the full table scan is faster than using the index

Leigh Gold wrote:

> I have 2 schemas with exactly the same objects (tables, indexes, triggers,
> procedures, etc), and both are analyzed for tables and indexes.
> However, when I did the same transactions to these 2 schemas with the same
> number of rows, one was done in under a minute and the other over 2 minutes.
>
> I did a trace on both with their session ids (the transaction were done
> through an app), I noticed the only different from the tkprof output were as
> listed below.
>
> Can anyone help in explaining what caused the difference?
>
> ** schema with better performance **
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 92 NESTED LOOPS
> 93 NESTED LOOPS (OUTER)
> 93 NESTED LOOPS (OUTER)
> 93 NESTED LOOPS (OUTER)
> 93 NESTED LOOPS (OUTER)
> 93 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'TAB_TRIPS'
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'TAB_WAYBILL'
> 92 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> 'TAB_WAYBILL_PK' (UNIQUE)
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'TAB_CONTRACT_RATE'
> 92 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> 'TAB_CONTRACT_RATE_PK' (UNIQUE)
>
> ------------------------------------------------------------------
> ** schema with slow performance **
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 92 CONCATENATION
> 1 NESTED LOOPS (OUTER)
> 2 NESTED LOOPS (OUTER)
> 2 NESTED LOOPS
> 2 NESTED LOOPS (OUTER)
> 2 NESTED LOOPS (OUTER)
> 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'TAB_TRIPS'
> 2 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> 'TAB_TRIPS_IDX1' (UNIQUE)
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'TAB_CONTRACT_RATE'
> 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> 'TAB_CONTRACT_RATE_PK' (UNIQUE)
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'TAB_WAYBILL'
>
> Thanks,
>
> L.Gold
Received on Thu Aug 23 2001 - 06:15:41 CDT