In scope of resolving aborted batch, support ask me to take a look at the following code that has to be executed to resolve the issue:


update price_hist ph1
set ph1.ACTION_DATE=ph1.ACTION_DATE-1
where ph1.tran_type = 0
and ph1.action_date >= to_date('10.11.2014','dd.mm.yyyy')
and exists (select 1
from price_hist ph2
where ph2.item = ph1.item
and ph2.loc = ph1.loc
and ph2.tran_type in (0, 4, 11)
and ph2.action_date < to_date('10.11.2014','dd.mm.yyyy'));

To be able to simulate the execution plan that I had at that time, you can modify the original statement in the following way (two hints have been added):

 
update /*+ index(ph1, price_hist_i1) */ price_hist ph1
set ph1.ACTION_DATE=ph1.ACTION_DATE-1
where ph1.tran_type = 0
and ph1.action_date >= to_date('10.11.2014','dd.mm.yyyy')
and exists (select /*+ index(ph2, price_hist_i1) */ 1
from price_hist ph2
where ph2.item = ph1.item
and ph2.loc = ph1.loc
and ph2.tran_type in (0, 4, 11)
and ph2.action_date < to_date('10.11.2014','dd.mm.yyyy'));

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                     |               |       |       |   282K(100)|          |       |       |
|   1 |  UPDATE                              | PRICE_HIST    |       |       |            |          |       |       |
|   2 |   NESTED LOOPS SEMI                  |               | 18150 |   602K|   282K  (1)| 00:56:27 |       |       |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| PRICE_HIST    | 18150 |   673K|   263K  (1)| 00:52:47 | ROWID | ROWID |
|   4 |     INDEX SKIP SCAN                  | PRICE_HIST_I1 | 18150 |       |   245K  (1)| 00:49:11 |       |       |
|   5 |    INDEX RANGE SCAN                  | PRICE_HIST_I1 |    95M|  2378M|     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------

If you look at the following picture, notice the wait activity on the price_hist_i1 index (index full scan), which is the main reason why query will take hours to finish.

015_original_execution_plan

Main reason for slow performance of the first query are table PRICE_HIST and index which are both very large (table has more than 200 million rows).

To resolve the issue, I’ve forced full table scan for the update part, and left only index range scan for the second part of the SQL.


update /*+ full(ph1) */ price_hist ph1
set ph1.ACTION_DATE=ph1.ACTION_DATE-1
where ph1.tran_type = 0
and ph1.action_date >= to_date('11.11.2014','dd.mm.yyyy')
and exists (select /*+ index(ph2, price_hist_i1) */ 1
from price_hist ph2
where ph2.item = ph1.item
and ph2.loc = ph1.loc
and ph2.tran_type in (0, 4, 11)
and ph2.action_date < to_date('11.11.2014','dd.mm.yyyy'));

New actual execution plan is the following:


------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |               |       |       |  3078K(100)|          |       |       |
|   1 |  UPDATE              | PRICE_HIST    |       |       |            |          |       |       |
|   2 |   NESTED LOOPS SEMI  |               |   860K|    27M|  3078K  (1)| 00:01:20 |       |       |
|   3 |    PARTITION HASH ALL|               |   860K|    31M|   495K  (2)| 00:00:13 |     1 |    64 |
|*  4 |     TABLE ACCESS FULL| PRICE_HIST    |   860K|    31M|   495K  (2)| 00:00:13 |     1 |    64 |
|*  5 |    INDEX RANGE SCAN  | PRICE_HIST_I1 |    92M|  2292M|     3   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------

As you can see from the next picture, the wait is now present on FTS part of the query, but the query finish within 3 minutes.

015_optimized_execution_plan

This is still not perfect, but it’s the fastest way to get the job done.

Main reason of such poor performance lays in huge segments (both table and index), which should be partitioned, but this is for one of the future blogs.


Comments

CarlYNindorf
2016-02-21 04:51:00
You must take part in a contest for just one of the best sites online. I most certainly will recommend this web site!
ChetARonco
2016-03-01 04:21:18
Hi my relative! I want to point out that this article is awesome, nice written and feature approximately all important infos. I might like to peer more posts like this .

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.