A quick look at the explain plan indicates that there exists an index on
TAB_TRIPS in the schema with **poor** performance. It seems that the query
performs better with a FTS.
HTH.,
Vinayak
--
I never X-post.
"Leigh Gold" <intan_5ee_at_yahoo.com> wrote in message
news:9lgq1t$rfl$1_at_news3.cadvision.com...
> 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 16 2001 - 13:40:59 CDT
