Skip to Main Content
  • Questions
  • 2 database with same data but hardware diff, some query are very fast on new db, some query in new db are slower than old one

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, amin.

Asked: March 11, 2017 - 10:06 am UTC

Last updated: March 13, 2017 - 2:52 am UTC

Version: oracle 11gr2

Viewed 1000+ times

You Asked

Dear Tom,

thanks for everything that i learned from you.
i have a new power full server with oracle 11gr2 (and just for test , nobody connect to it) and another is older (Hardware are older) with oracle 10g and around 400 user on time when my query tested.
they have same data. but:
some query on new db run very very fast but some query run 1/3 slower than old one. some one before changed parameters of new db.
FYI: i can't changed query,veiw or index. just oracle db configuration i can change.


thank you so much

and Connor said...

Several possible reasons here

1) How did you load the data into the new database. If you used export/import then the data distribution could have totally changed

2) The statistics have been updated. Different stats = different plans

3) Even if the data organization and statistics are identical, its possible that system stats (ie, in aux_stats$) are different on the new box.

If you still have both boxes, you need to compare the *plans* between for the problem queries.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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  

Connor McDonald
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.