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

There is something very odd about your execution plans. They don't appear to be valid.

each plan has Five Nested Loops.

In the first listing, each table (other than the first table) appears to be accessed through the index that follows it.

Consequently your plan shows accesses to only 3 data objects - but five Nested Loops should require Six data objects.

In the second listing things are even worse - the last line shows a 'Table access by index rowid', but this means there should be a line following it identifying the index that was used to generate the rowids used. On top of that, there is a concatenation operator - which means there should be at least one more entire structured access path to give a second result set for the concatenation operator to concatenate !

--
Jonathan Lewis

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

For latest news of public appearances
See http://www.jlcomp.demon.co.uk

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.




Leigh Gold wrote in message <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

>

>