The development environment has production amount of data in it.
There is only one table which is a bit skewed - the H table has much
more data in production - could this be causing such a drastic
difference in execution plan?
On development, (2 CPUs, 2GB RAM) the Cost 25 query runs in about 10
seconds. In production, (8 CPUs, 4GB RAM) the same query, with a Cost
of 158, runs in about 45 minutes!!
Here's the Cost 25 plan ->
Plan Table
| Operation | Name | Rows | Bytes| Cost |
Pstart| Pstop |
| SELECT STATEMENT | | 1 | 266 | 25 |
| |
| SORT GROUP BY | | 1 | 266 | |
| |
| NESTED LOOPS | | 1 | 266 | 25 |
| |
| NESTED LOOPS | | 1 | 256 | 25 |
| |
| NESTED LOOPS | | 1 | 252 | 24 |
| |
| NESTED LOOPS | | 1 | 241 | 22 |
| |
| NESTED LOOPS | | 1 | 230 | 21 |
| |
| NESTED LOOPS | | 1 | 226 | 20 |
| |
| NESTED LOOPS | | 1 | 215 | 19 |
| |
Plan Table
| NESTED LOOPS | | 1 | 204 | 18 |
| |
| NESTED LOOPS | | 1 | 200 | 17 |
| |
| NESTED LOOPS | | 1 | 189 | 16 |
| |
| NESTED LOOPS | | 1 | 178 | 15 |
| |
| NESTED LOOPS | | 1 | 174 | 15 |
| |
| NESTED LOOPS| | 1 | 163 | 13 |
| |
| NESTED LOOP| | 1 | 152 | 13 |
| |
| NESTED LOO| | 74 | 9K| 11 |
| |
| NESTED LO| | 1 | 127 | 10 |
| |
| NESTED L| | 1 | 122 | 9 |
| |
| NESTED | | 1 | 114 | 8 |
| |
Plan Table
| NESTED| | 1 | 88 | 8 |
| |
| NESTE| | 1 | 79 | 7 |
| |
| NEST| | 1 | 53 | 5 |
| |
| NES| | 1 | 36 | 4 |
| |
| NE| | 1 | 33 | 4 |
| |
| N| | 1 | 30 | 2 |
| |
| |A | 1 | 13 | 1 |
| |
| |PK_A | 2 | | 2 |
| |
| |E | 475K| 7M| 1 |
| |
| |PK_E | 475K| | 1 |
| |
| I|PK_A | 117 | 351 | |
| |
Plan Table
| IN|PK_E | 252 | 756 | |
| |
| IND|PK_E | 970K| 15M| 1 |
| |
| VIEW|VW_G | 1 | 26 | |
| |
| SOR| | 1 | 9 | 1 |
| |
| IN|IX2_G | 81 | 729 | 4 |
| |
| INDEX|PK_G | 83 | 747 | 1 |
| |
| VIEW |VW_H | 1 | 26 | |
| |
| SORT | | 1 | 9 | 1 |
| |
| INDE|IX5_H | 90 | 810 | 4 |
| |
| TABLE A|D | 36K| 284K| 1 |
| |
| INDEX |PK_D | 36K| | |
| |
Plan Table
| INDEX UN|PK_I | 3 | 15 | |
| |
| TABLE ACC|J | 221 | 2K| 1 |
| |
| INDEX RA|IX2_J | 221 | | |
| |
| INDEX UNIQ|PK_K | 3 | 45 | |
| |
| INDEX RANGE|PK_D | 36K| 391K| 4 |
| |
| TABLE ACCESS|C | 416 | 4K| 1 |
| |
| INDEX UNIQU|PK_C | 416 | | |
| |
| INDEX UNIQUE |PK_B | 71K| 278K| |
| |
| INDEX RANGE SC|PK_D | 36K| 391K| 4 |
| |
| TABLE ACCESS BY|C | 416 | 4K| 1 |
| |
| INDEX UNIQUE S|PK_C | 416 | | |
| |
Plan Table
| INDEX UNIQUE SCA|PK_B | 71K| 278K| |
| |
| INDEX RANGE SCAN |PK_D | 36K| 391K| 4 |
| |
| TABLE ACCESS BY IN|C | 416 | 4K| 1 |
| |
| INDEX UNIQUE SCAN|PK_C | 416 | | |
| |
| INDEX UNIQUE SCAN |PK_B | 71K| 278K| |
| |
| INDEX RANGE SCAN |PK_D | 36K| 391K| 4 |
| |
| TABLE ACCESS BY INDEX|C | 416 | 4K| 1 |
| |
| INDEX UNIQUE SCAN |PK_C | 416 | | |
| |
| INDEX UNIQUE SCAN |PK_B | 71K| 278K| |
| |
| INDEX UNIQUE SCAN |PK_A | 5 | 50 | 1 |
| |
Received on Thu Jun 06 2002 - 09:00:22 CDT
