amin omrani, March 12, 2017 - 5:40 am UTC
Dear Tom,
Thanks for quick response.
these are plans: What can i do? how can i change new plan:
Old Server:
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 63 | 1860K (27)| 06:12:03 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 126K| 7772K| 2327 (4)| 00:00:28 |
|* 3 | TABLE ACCESS FULL | CUST_ORD_CUSTOMER_TAB | 359 | 3590 | 6 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT SEMI | | 126K| 6539K| 2318 (4)| 00:00:28 |
| 5 | VIEW | VW_SQ_1 | 12 | 48 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | USER_ALLOWED_SITE_PK | 12 | 192 | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | CUSTOMER_ORDER_TAB | 200K| 9572K| 2310 (4)| 00:00:28 |
| 9 | SORT UNIQUE | | 4 | 304 | 15 (60)| 00:00:01 |
| 10 | UNION-ALL | | | | | |
| 11 | NESTED LOOPS | | 1 | 96 | 6 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ORDER_DELIVERY_TAB | 1 | 40 | 4 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | CUSTOMER_ORDER_DELIVERY_1_IX | 2 | | 3 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ORDER_LINE_TAB | 1 | 56 | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | CUSTOMER_ORDER_LINE_PK | 1 | | 1 (0)| 00:00:01 |
| 16 | NESTED LOOPS SEMI | | 1 | 66 | 2 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ORDER_CHARGE_TAB | 1 | 50 | 0 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | CUSTOMER_ORDER_CHARGE_PK | 1 | | 0 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ORDER_DELIVERY_TAB | 2 | 32 | 2 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | CUSTOMER_ORDER_DELIVERY_1_IX | 1 | | 2 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 74 | 1 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | ORDER_LINE_STAGED_BILLING_TAB | 1 | 41 | 0 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | ORDER_LINE_STAGED_BILLING_PK | 1 | | 0 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ORDER_LINE_TAB | 1 | 33 | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | CUSTOMER_ORDER_LINE_PK | 1 | | 1 (0)| 00:00:01 |
|* 26 | FILTER | | | | | |
| 27 | NESTED LOOPS ANTI | | 1 | 68 | 2 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_ORDER_CHARGE_TAB | 1 | 50 | 0 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | CUSTOMER_ORDER_CHARGE_PK | 1 | | 0 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_ORDER_LINE_TAB | 1 | 18 | 2 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | CUSTOMER_ORDER_LINE_PK | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
aux_stats$:
SNAME PNAME PVAL1 PVAL2
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 05/07/12 7:13
SYSSTATS_INFO DSTOP 05/07/12 7:13
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 452.7272727
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
New Server:
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 63 | 47M (1)|158:15:03 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 69571 | 4280K| 1862 (4)| 00:00:23 |
|* 3 | TABLE ACCESS FULL | CUST_ORD_CUSTOMER_TAB | 329 | 3290 | 5 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT SEMI | | 69571 | 3600K| 1856 (4)| 00:00:23 |
| 5 | VIEW | VW_SQ_1 | 12 | 48 | 12 (0)| 00:00:01 |
|* 6 | INDEX FAST FULL SCAN | USER_ALLOWED_SITE_PK | 12 | 192 | 10 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | CUSTOMER_ORDER_TAB | 185K| 8877K| 1840 (4)| 00:00:23 |
| 9 | SORT UNIQUE | | 4 | 610 | 709 (1)| 00:00:09 |
| 10 | UNION-ALL | | | | | |
| 11 | NESTED LOOPS | | 1 | 97 | 501 (1)| 00:00:07 |
| 12 | NESTED LOOPS | | 1 | 97 | 501 (1)| 00:00:07 |
|* 13 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_ORDER_DELIVERY_TAB | 1 | 41 | 401 (1)| 00:00:05 |
|* 14 | INDEX RANGE SCAN | CUSTOMER_ORDER_DELIVERY_1_IX | 2 | | 300 (0)| 00:00:04 |
|* 15 | INDEX UNIQUE SCAN | CUSTOMER_ORDER_LINE_PK | 1 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ORDER_LINE_TAB | 1 | 56 | 100 (0)| 00:00:02 |
| 17 | NESTED LOOPS SEMI | | 1 | 66 | 101 (0)| 00:00:02 |
|* 18 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ORDER_CHARGE_TAB | 1 | 50 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | CUSTOMER_ORDER_CHARGE_PK | 1 | | 1 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ORDER_DELIVERY_TAB | 2 | 32 | 100 (0)| 00:00:02 |
|* 21 | INDEX RANGE SCAN | CUSTOMER_ORDER_DELIVERY_1_IX | 1 | | 100 (0)| 00:00:02 |
| 22 | NESTED LOOPS | | 1 | 74 | 2 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 1 | 74 | 2 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID| ORDER_LINE_STAGED_BILLING_TAB | 1 | 41 | 1 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | ORDER_LINE_STAGED_BILLING_PK | 1 | | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | CUSTOMER_ORDER_LINE_PK | 1 | | 1 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ORDER_LINE_TAB | 1 | 33 | 1 (0)| 00:00:01 |
|* 28 | FILTER | | | | | |
| 29 | NESTED LOOPS ANTI | | 1 | 68 | 101 (0)| 00:00:02 |
|* 30 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_ORDER_CHARGE_TAB | 1 | 50 | 1 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | CUSTOMER_ORDER_CHARGE_PK | 1 | | 1 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_ORDER_LINE_TAB | 2 | 36 | 100 (0)| 00:00:02 |
|* 33 | INDEX RANGE SCAN | CUSTOMER_ORDER_LINE_PK | 1 | | 100 (0)| 00:00:02 |
------------------------------------------------------------------------------------------------------------------
aux_stats$ :
SNAME PNAME PVAL1 PVAL2
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 05/07/12 7:13
SYSSTATS_INFO DSTOP 05/07/12 7:13
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 452.7272727
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
March 13, 2017 - 2:52 am UTC
The that plans were different means *something* is different between the databases. The optimizer doesn't just change for no reason - its just an mathematical algorithm.
So do some research on all of the statistics on the object concerned (a common one is clustering factor on indexes because of the way data is reloaded).
But the simplest workaround is to take the plan from the old server and lock into the new server using sql plan management. That lets you do the research on the statistics at your own pace.
Data distribution
Gh.., March 12, 2017 - 7:39 am UTC
What is the optimizer_index_cost_adj value for both db?
As you see the Allowed site table pk index is chosen as Range scan in the first plan and IFFS in the second plan.
This seemed a non unique index .and some cols in the query are not the first one regarding this index cols. Since thee is different data distribution btw the old and the new oracle does not guarantee same index scan for both different optimisers. Trace 10053 will give you more clarification.