Help - tkprof output different for 2 schemas with same objects

From: Leigh Gold <intan_5ee_at_yahoo.com>
Date: Thu, 16 Aug 2001 09:54:02 -0600
Message-ID: <9lgq1t$rfl$1@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 - 10:54:02 CDT