-
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 | |
--------------------------------------------------------------------------------------------------
-
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 | |
--------------------------------------------------------------------------------------------------
-
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.
-
I just copied everything, statistics and all..
-
"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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|