DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Why?

  1. #1
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    Why?

    I copied the production database to performance testing machine with identical memory, disk, CPU, init.ora, tablespace,filesystem configs.
    When I run a query on both machines, I get vastly different explain plans. Why?

    Code:
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------------------------
    | Id  | Operation                           |  Name                      | Rows  | Bytes | Cost  |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |                            |  1132 |   223K|  7227 |
    |   1 |  SORT ORDER BY                      |                            |  1132 |   223K|  7227 |
    |   2 |   CONCATENATION                     |                            |       |       |       |
    |   3 |    NESTED LOOPS                     |                            |  1642 |   269K|    60 |
    |   4 |     NESTED LOOPS                    |                            |  1125 |   221K|  6628 |
    |   5 |      NESTED LOOPS                   |                            | 13481 |  2501K|  6628 |
    |   6 |       NESTED LOOPS                  |                            |  1642 |   234K|    60 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |*  7 |        TABLE ACCESS BY INDEX ROWID  | PROMSKU                    |    45 |  1485 |    14 |
    |*  8 |         INDEX RANGE SCAN            | PK_PROMSKU                 |    60 |       |     3 |
    |*  9 |        TABLE ACCESS BY INDEX ROWID  | ITEM_MASTER                |     1 |   113 |     2 |
    |* 10 |         INDEX UNIQUE SCAN           | PK_ITEM_MASTER             |     1 |       |     1 |
    |* 11 |          TABLE ACCESS BY INDEX ROWID| ITEM_MASTER                |     1 |    26 |     3 |
    |* 12 |           INDEX UNIQUE SCAN         | PK_ITEM_MASTER             |  1258K|       |     2 |
    |  13 |       TABLE ACCESS BY INDEX ROWID   | ITEM_ZONE_PRICE            |  6424K|   134M|     4 |
    |* 14 |        INDEX RANGE SCAN             | PK_ITEM_ZONE_PRICE         |     8 |       |     2 |
    |* 15 |      INDEX UNIQUE SCAN              | PK_PRICE_ZONE_GROUP_STORE  |     1 |    12 |       |
    |  16 |     TABLE ACCESS BY INDEX ROWID     | ITEM_MASTER                |  1258K|    26M|       |
    |* 17 |      INDEX UNIQUE SCAN              | PK_ITEM_MASTER             |     1 |       |     1 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |* 18 |    FILTER                           |                            |       |       |       |
    |  19 |     NESTED LOOPS                    |                            |  1642 |   269K|    60 |
    |  20 |      NESTED LOOPS                   |                            |  1125 |   221K|  6628 |
    |  21 |       NESTED LOOPS                  |                            | 13481 |  2501K|  6628 |
    |  22 |        NESTED LOOPS                 |                            |  1642 |   234K|    60 |
    |* 23 |         TABLE ACCESS BY INDEX ROWID | PROMSKU                    |    45 |  1485 |    14 |
    |* 24 |          INDEX RANGE SCAN           | PK_PROMSKU                 |    60 |       |     3 |
    |* 25 |         TABLE ACCESS BY INDEX ROWID | ITEM_MASTER                |     1 |   113 |     2 |
    
    |* 24 |          INDEX RANGE SCAN           | PK_PROMSKU                 |    60 |       |     3 |
    |* 25 |         TABLE ACCESS BY INDEX ROWID | ITEM_MASTER                |     1 |   113 |     2 |
    |* 26 |          INDEX RANGE SCAN           | ITEM_MASTER_I2             |     1 |       |     1 |
    |  27 |        TABLE ACCESS BY INDEX ROWID  | ITEM_ZONE_PRICE            |  6424K|   134M|     4 |
    |* 28 |         INDEX RANGE SCAN            | PK_ITEM_ZONE_PRICE         |     8 |       |     2 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |* 29 |       INDEX UNIQUE SCAN             | PK_PRICE_ZONE_GROUP_STORE  |     1 |    12 |       |
    |  30 |      TABLE ACCESS BY INDEX ROWID    | ITEM_MASTER                |  1258K|    26M|       |
    |* 31 |       INDEX UNIQUE SCAN             | PK_ITEM_MASTER             |     1 |       |     1 |
    |* 32 |     TABLE ACCESS BY INDEX ROWID     | ITEM_MASTER                |     1 |    26 |     3 |
    |* 33 |      INDEX UNIQUE SCAN              | PK_ITEM_MASTER             |  1258K|       |     2 |
    |* 34 |    FILTER                           |                            |       |       |       |
    |  35 |     NESTED LOOPS                    |                            |  1642 |   269K|    60 |
    |  36 |      NESTED LOOPS                   |                            |  1125 |   221K|  6628 |
    |  37 |       NESTED LOOPS                  |                            | 13481 |  2501K|  6628 |
    |  38 |        NESTED LOOPS                 |                            |  1642 |   234K|    60 |
    |* 39 |         TABLE ACCESS BY INDEX ROWID | PROMSKU                    |    45 |  1485 |    14 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |* 40 |          INDEX RANGE SCAN           | PK_PROMSKU                 |    60 |       |     3 |
    |* 41 |         TABLE ACCESS BY INDEX ROWID | ITEM_MASTER                |     1 |   113 |     2 |
    |* 42 |          INDEX RANGE SCAN           | ITEM_MASTER_I1             |     1 |       |     1 |
    |  43 |        TABLE ACCESS BY INDEX ROWID  | ITEM_ZONE_PRICE            |  6424K|   134M|     4 |
    |* 44 |         INDEX RANGE SCAN            | PK_ITEM_ZONE_PRICE         |     8 |       |     2 |
    |* 45 |       INDEX UNIQUE SCAN             | PK_PRICE_ZONE_GROUP_STORE  |     1 |    12 |       |
    |  46 |      TABLE ACCESS BY INDEX ROWID    | ITEM_MASTER                |  1258K|    26M|       |
    |* 47 |       INDEX UNIQUE SCAN             | PK_ITEM_MASTER             |     1 |       |     1 |
    |* 48 |     TABLE ACCESS BY INDEX ROWID     | ITEM_MASTER                |     1 |    26 |     3 |
    |* 49 |      INDEX UNIQUE SCAN              | PK_ITEM_MASTER             |  1258K|       |     2 |
    |  50 |    NESTED LOOPS                     |                            |  1125 |   221K|  6628 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |  51 |     NESTED LOOPS                    |                            | 13481 |  2501K|  6628 |
    |  52 |      NESTED LOOPS                   |                            |  1642 |   269K|    60 |
    |  53 |       NESTED LOOPS                  |                            |  1642 |   234K|    60 |
    |* 54 |        TABLE ACCESS BY INDEX ROWID  | PROMSKU                    |    45 |  1485 |    14 |
    
    |  53 |       NESTED LOOPS                  |                            |  1642 |   234K|    60 |
    |* 54 |        TABLE ACCESS BY INDEX ROWID  | PROMSKU                    |    45 |  1485 |    14 |
    |* 55 |         INDEX RANGE SCAN            | PK_PROMSKU                 |    60 |       |     3 |
    |* 56 |        TABLE ACCESS BY INDEX ROWID  | ITEM_MASTER                |     1 |   113 |     2 |
    |* 57 |         INDEX RANGE SCAN            | PK_ITEM_MASTER             |     1 |       |     1 |
    |* 58 |          TABLE ACCESS BY INDEX ROWID| ITEM_MASTER                |     1 |    26 |     3 |
    |* 59 |           INDEX UNIQUE SCAN         | PK_ITEM_MASTER             |  1258K|       |     2 |
    |  60 |       TABLE ACCESS BY INDEX ROWID   | ITEM_MASTER                |  1258K|    26M|       |
    |* 61 |        INDEX UNIQUE SCAN            | PK_ITEM_MASTER             |     1 |       |     1 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |  62 |      TABLE ACCESS BY INDEX ROWID    | ITEM_ZONE_PRICE            |  6424K|   134M|     4 |
    |* 63 |       INDEX RANGE SCAN              | PK_ITEM_ZONE_PRICE         |     8 |       |     2 |
    |* 64 |     INDEX UNIQUE SCAN               | PK_PRICE_ZONE_GROUP_STORE  |     1 |    12 |       |
    --------------------------------------------------------------------------------------------------

  2. #2
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    this is the other explain plan

    Code:
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------------------------
    | Id  | Operation                           |  Name                      | Rows  | Bytes | Cost  |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |                            |    12 |  2448 |   661 |
    |   1 |  SORT ORDER BY                      |                            |    12 |  2448 |   661 |
    |   2 |   CONCATENATION                     |                            |       |       |       |
    |   3 |    NESTED LOOPS                     |                            |     9 |  1530 |    64 |
    |   4 |     NESTED LOOPS                    |                            |     6 |  1224 |    91 |
    |   5 |      NESTED LOOPS                   |                            |    76 | 14592 |    91 |
    |   6 |       NESTED LOOPS                  |                            |     9 |  1332 |    64 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |*  7 |        TABLE ACCESS BY INDEX ROWID  | PROMSKU                    |    48 |  1680 |    15 |
    |*  8 |         INDEX RANGE SCAN            | PK_PROMSKU                 |    64 |       |     3 |
    |*  9 |        TABLE ACCESS BY INDEX ROWID  | ITEM_MASTER                |   139K|    14M|     2 |
    |* 10 |         INDEX UNIQUE SCAN           | PK_ITEM_MASTER             |     1 |       |     1 |
    |* 11 |          TABLE ACCESS BY INDEX ROWID| ITEM_MASTER                |     1 |    26 |     3 |
    |* 12 |           INDEX UNIQUE SCAN         | PK_ITEM_MASTER             |  1252K|       |     2 |
    |  13 |       TABLE ACCESS BY INDEX ROWID   | ITEM_ZONE_PRICE            |  8610 |   184K|     3 |
    |* 14 |        INDEX RANGE SCAN             | PK_ITEM_ZONE_PRICE         |     8 |       |     2 |
    |* 15 |      INDEX UNIQUE SCAN              | PK_PRICE_ZONE_GROUP_STORE  |    61 |   732 |       |
    |  16 |     TABLE ACCESS BY INDEX ROWID     | ITEM_MASTER                |  1252K|    26M|       |
    |* 17 |      INDEX UNIQUE SCAN              | PK_ITEM_MASTER             |     1 |       |     1 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |* 18 |    FILTER                           |                            |       |       |       |
    |  19 |     NESTED LOOPS                    |                            |     9 |  1530 |    64 |
    |  20 |      NESTED LOOPS                   |                            |     6 |  1224 |    91 |
    |  21 |       NESTED LOOPS                  |                            |    76 | 14592 |    91 |
    |  22 |        NESTED LOOPS                 |                            |     9 |  1332 |    64 |
    |* 23 |         TABLE ACCESS BY INDEX ROWID | PROMSKU                    |    48 |  1680 |    15 |
    |* 24 |          INDEX RANGE SCAN           | PK_PROMSKU                 |    64 |       |     3 |
    |* 25 |         TABLE ACCESS BY INDEX ROWID | ITEM_MASTER                |   139K|    14M|     2 |
    |* 26 |          INDEX RANGE SCAN           | ITEM_MASTER_I2             |     1 |       |     1 |
    |  27 |        TABLE ACCESS BY INDEX ROWID  | ITEM_ZONE_PRICE            |  8610 |   184K|     3 |
    |* 28 |         INDEX RANGE SCAN            | PK_ITEM_ZONE_PRICE         |     8 |       |     2 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |* 29 |       INDEX UNIQUE SCAN             | PK_PRICE_ZONE_GROUP_STORE  |    61 |   732 |       |
    |  30 |      TABLE ACCESS BY INDEX ROWID    | ITEM_MASTER                |  1252K|    26M|       |
    |* 31 |       INDEX UNIQUE SCAN             | PK_ITEM_MASTER             |     1 |       |     1 |
    |* 32 |     TABLE ACCESS BY INDEX ROWID     | ITEM_MASTER                |     1 |    26 |     3 |
    |* 33 |      INDEX UNIQUE SCAN              | PK_ITEM_MASTER             |  1252K|       |     2 |
    |* 34 |    FILTER                           |                            |       |       |       |
    |  35 |     NESTED LOOPS                    |                            |     9 |  1530 |    64 |
    |  36 |      NESTED LOOPS                   |                            |     6 |  1224 |    91 |
    |  37 |       NESTED LOOPS                  |                            |    76 | 14592 |    91 |
    |  38 |        NESTED LOOPS                 |                            |     9 |  1332 |    64 |
    |* 39 |         TABLE ACCESS BY INDEX ROWID | PROMSKU                    |    48 |  1680 |    15 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |* 40 |          INDEX RANGE SCAN           | PK_PROMSKU                 |    64 |       |     3 |
    |* 41 |         TABLE ACCESS BY INDEX ROWID | ITEM_MASTER                |   139K|    14M|     2 |
    |* 42 |          INDEX RANGE SCAN           | ITEM_MASTER_I1             |     1 |       |     1 |
    |  43 |        TABLE ACCESS BY INDEX ROWID  | ITEM_ZONE_PRICE            |  8610 |   184K|     3 |
    |* 44 |         INDEX RANGE SCAN            | PK_ITEM_ZONE_PRICE         |     8 |       |     2 |
    |* 45 |       INDEX UNIQUE SCAN             | PK_PRICE_ZONE_GROUP_STORE  |    61 |   732 |       |
    |  46 |      TABLE ACCESS BY INDEX ROWID    | ITEM_MASTER                |  1252K|    26M|       |
    |* 47 |       INDEX UNIQUE SCAN             | PK_ITEM_MASTER             |     1 |       |     1 |
    |* 48 |     TABLE ACCESS BY INDEX ROWID     | ITEM_MASTER                |     1 |    26 |     3 |
    |* 49 |      INDEX UNIQUE SCAN              | PK_ITEM_MASTER             |  1252K|       |     2 |
    |  50 |    NESTED LOOPS                     |                            |     6 |  1224 |    91 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |  51 |     NESTED LOOPS                    |                            |    76 | 14592 |    91 |
    |  52 |      NESTED LOOPS                   |                            |     9 |  1530 |    64 |
    |  53 |       NESTED LOOPS                  |                            |     9 |  1332 |    64 |
    |* 54 |        TABLE ACCESS BY INDEX ROWID  | PROMSKU                    |    48 |  1680 |    15 |
    |* 55 |         INDEX RANGE SCAN            | PK_PROMSKU                 |    64 |       |     3 |
    |* 56 |        TABLE ACCESS BY INDEX ROWID  | ITEM_MASTER                |   139K|    14M|     2 |
    |* 57 |         INDEX RANGE SCAN            | PK_ITEM_MASTER             |     1 |       |     1 |
    |* 58 |          TABLE ACCESS BY INDEX ROWID| ITEM_MASTER                |     1 |    26 |     3 |
    |* 59 |           INDEX UNIQUE SCAN         | PK_ITEM_MASTER             |  1252K|       |     2 |
    |  60 |       TABLE ACCESS BY INDEX ROWID   | ITEM_MASTER                |  1252K|    26M|       |
    |* 61 |        INDEX UNIQUE SCAN            | PK_ITEM_MASTER             |     1 |       |     1 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |  62 |      TABLE ACCESS BY INDEX ROWID    | ITEM_ZONE_PRICE            |  8610 |   184K|     3 |
    |* 63 |       INDEX RANGE SCAN              | PK_ITEM_ZONE_PRICE         |     8 |       |     2 |
    |* 64 |     INDEX UNIQUE SCAN               | PK_PRICE_ZONE_GROUP_STORE  |    61 |   732 |       |
    --------------------------------------------------------------------------------------------------

  3. #3
    Join Date
    Oct 2002
    Posts
    807
    I assume you're using a CBO, if plans are now different. RBO - plans don't change.

    In you're case, "when" was the DEV instance refreshed from production? That's critical in determining CBO plans. Usage patterns matter in gathering statistics. If significant time has elapsed since you refreshed the database, you can very well expect to see a difference in execution plans.

  4. #4
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178
    I just copied everything, statistics and all..

  5. #5
    Join Date
    Oct 2002
    Posts
    807
    "When" - as in how much time has elapsed between your coping the database and generating the plans?

    1) When did you copy DEV from PROD?
    2) When did you run the explain plans on both?
    3) What were the activities on DEV, PROD in the interim (between steps 1 and 2)? Was PROD up and running?
    Last edited by Axr2; 05-28-2004 at 07:17 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width