Help - tkprof output different for 2 schemas with same objects 
Author Message
 Help - tkprof output different for 2 schemas with same objects

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



Mon, 02 Feb 2004 23:54:02 GMT
 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
Quote:

> 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



Mon, 09 Feb 2004 19:15:41 GMT
 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.

Quote:

>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



Mon, 09 Feb 2004 19:49:58 GMT
 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
Quote:

> 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



Mon, 09 Feb 2004 19:15:41 GMT
 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.

Quote:

>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



Mon, 09 Feb 2004 19:49:58 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. RFD: schemas and different kinds of Postgres objects

2. RFD: schemas and different kinds of Postgres objects

3. export/import objects to different schemas

4. tkprof output?

5. TKPROF output inconsistency

6. tkprof output?

7. Understanding TKPROF output

8. Q: tkprof output

9. tkprof-output

10. TKPROF output for an SQL Insert statement

11. Problems with different schemas in ORACLE

12. Do FROM items of different schemas conflict?


 
Powered by phpBB® Forum Software