Oracle DB on Exadata. update running for hours, parallel hint isn't working

Question

We have two huge tables and trying to update around 60 million records. It used to update in 30 minutes before and now its taking hours n hours, Not even more than 20% of data growth. Here is more information. I see that there is a lot of wait on "cell single block physical read" "PX Deq: Table Q Normal" events. I don't understand why the parallel hint isn't working. Is that the reason for cell single block physical read? or is there any other reason why there is a performance degrade, any idea ? and is there a why to improve performance by getting rid of "WHERE EXISTS" clause?

table1(A) size 316 GB record count 456,365,654

table2(B) 7GB record count 31,934,956

   UPDATE /*+  parallel (a, 8) */
          table1 a
       SET (c_age,
            c_age_de,
            wa_only_ind) =
              (SELECT c_age,
                      c_age_de,
                      wa_only_ind
                 FROM table2 b
                WHERE     B.ip = A.ip
                      AND a.c_age IS NULL
                      AND b.o_type IN ('TYPE12', 'TYPE14'))
     WHERE EXISTS
              (SELECT 1
                 FROM table2 b2
                WHERE     b2.ip = a.ip
                      AND a.c_age IS NULL
                      AND b2.o_type IN ('TYPE12', 'TYPE14'));

EXLAIN PLAN

   ----------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name                    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
    ----------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                 |                         |     17M|  9599M|  5076M (20)|999:59:59 |       |       |        |      |            |
    |   1 |  UPDATE                          | TABLE1                  |        |       |            |          |       |       |        |      |            |
    |   2 |   PX COORDINATOR                 |                         |        |       |            |          |       |       |        |      |            |
    |   3 |    PX SEND QC (RANDOM)           | :TQ10002                |     17M|  9599M|  1069K  (1)| 04:09:40 |       |       |  Q1,02 | P->S | QC (RAND)  |
    |*  4 |     HASH JOIN RIGHT SEMI BUFFERED|                         |     17M|  9599M|  1069K  (1)| 04:09:40 |       |       |  Q1,02 | PCWP |            |
    |   5 |      PX RECEIVE                  |                         |     29M|   476M| 23984   (2)| 00:05:36 |       |       |  Q1,02 | PCWP |            |
    |   6 |       PX SEND HASH               | :TQ10000                |     29M|   476M| 23984   (2)| 00:05:36 |       |       |  Q1,00 | P->P | HASH       |
    |   7 |        PX BLOCK ITERATOR         |                         |     29M|   476M| 23984   (2)| 00:05:36 |       |       |  Q1,00 | PCWC |            |
    |*  8 |         TABLE ACCESS STORAGE FULL| TABLE2                  |     29M|   476M| 23984   (2)| 00:05:36 |       |       |  Q1,00 | PCWP |            |
    |   9 |      PX RECEIVE                  |                         |     57M|    30G|  1045K  (1)| 04:04:04 |       |       |  Q1,02 | PCWP |            |
    |  10 |       PX SEND HASH               | :TQ10001                |     57M|    30G|  1045K  (1)| 04:04:04 |       |       |  Q1,01 | P->P | HASH       |
    |  11 |        PX BLOCK ITERATOR         |                         |     57M|    30G|  1045K  (1)| 04:04:04 |     1 |     3 |  Q1,01 | PCWC |            |
    |* 12 |         TABLE ACCESS STORAGE FULL| TABLE1                  |     57M|    30G|  1045K  (1)| 04:04:04 |     1 |     6 |  Q1,01 | PCWP |            |
    |* 13 |   FILTER                         |                         |        |       |            |          |       |       |        |      |            |
    |* 14 |    TABLE ACCESS BY INDEX ROWID   | TABLE2                  |      1 |   210 |     4   (0)| 00:00:01 |       |       |        |      |            |
    |* 15 |     INDEX RANGE SCAN             | TABLE2_IDX1             |      1 |       |     3   (0)| 00:00:01 |       |       |        |      |            |
    ----------------------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B2"."IP"="A"."IP")
   8 - storage("B2"."O_TYPE"='TYPE1' OR "B2"."O_TYPE"='TYPE12')
       filter("B2"."O_TYPE"='TYPE1' OR "B2"."O_TYPE"='TYPE12')
  12 - storage("A"."C_AGE" IS NULL)
       filter("A"."C_AGE" IS NULL)
  13 - filter(:B1 IS NULL)
  14 - filter("B"."O_TYPE"='TYPE1' OR "B"."O_TYPE"='TYPE12')
  15 - access("B"."IP"=:B1)


 SQL_ID  dd0ah20057j37
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |  OMem |  1Mem |  O/1/M   |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                 |                              |        |       |    43T(100)|          |       |       |        |      |            |       |       |          |
|   1 |  UPDATE                          | TABLE1                   |        |       |            |          |       |       |        |      |            |       |       |          |
|   2 |   PX COORDINATOR                 |                              |        |       |            |          |       |       |        |      |            |       |       |          |
|   3 |    PX SEND QC (RANDOM)           | :TQ10002                     |     57M|    31G| 65326   (3)| 00:14:16 |       |       |  Q1,02 | P->S | QC (RAND)  |       |       |          |
|*  4 |     HASH JOIN RIGHT SEMI BUFFERED|                              |     57M|    31G| 65326   (3)| 00:14:16 |       |       |  Q1,02 | PCWP |            |  1537M|    25M|     2/0/0|
|   5 |      PX RECEIVE                  |                              |     32M|   550M|  2190  (20)| 00:00:29 |       |       |  Q1,02 | PCWP |            |       |       |          |
|   6 |       PX SEND HASH               | :TQ10000                     |     32M|   550M|  2190  (20)| 00:00:29 |       |       |  Q1,00 | P->P | HASH       |       |       |          |
|   7 |        PX BLOCK ITERATOR         |                              |     32M|   550M|  2190  (20)| 00:00:29 |       |       |  Q1,00 | PCWC |            |       |       |          |
|*  8 |         TABLE ACCESS STORAGE FULL| TABLE2 P1      |     32M|   550M|  2190  (20)| 00:00:29 |       |       |  Q1,00 | PCWP |            |  1025K|  1025K|     2/0/0|
|   9 |      PX RECEIVE                  |                              |     57M|    30G| 63100   (2)| 00:13:47 |       |       |  Q1,02 | PCWP |            |       |       |          |
|  10 |       PX SEND HASH               | :TQ10001                     |     57M|    30G| 63100   (2)| 00:13:47 |       |       |  Q1,01 | P->P | HASH       |       |       |          |
|  11 |        PX BLOCK ITERATOR         |                              |     57M|    30G| 63100   (2)| 00:13:47 |     1 |     3 |  Q1,01 | PCWC |            |       |       |          |
|* 12 |         TABLE ACCESS STORAGE FULL| TABLE1                   |     57M|    30G| 63100   (2)| 00:13:47 |     1 |     6 |  Q1,01 | PCWP |            |  1025K|  1025K|     2/0/0|
|* 13 |   FILTER                         |                              |        |       |            |          |       |       |        |      |            |       |       |          |
|* 14 |    TABLE ACCESS BY INDEX ROWID   | TABLE2          |    320K|   113M| 12787   (1)| 00:02:48 |       |       |        |      |            |       |       |          |
|* 15 |     INDEX RANGE SCAN             | TABLE2_IDX1          |    128K|       |     4  (25)| 00:00:01 |       |       |        |      |            |  1025K|  1025K|          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("U2"."IP"="I"."IP")
   8 - storage(:Z>=:Z AND :Z<=:Z AND (("U2"."OPERATION_TYPE"='TYPE1' OR "U2"."OPERATION_TYPE"='TYPE12')))
       filter(("U2"."OPERATION_TYPE"='TYPE1' OR "U2"."OPERATION_TYPE"='TYPE12'))
  12 - storage(:Z>=:Z AND :Z<=:Z AND "I"."AGE" IS NULL)
       filter("I"."AGE" IS NULL)
  13 - filter(:B1 IS NULL)
  14 - filter(("U"."OPERATION_TYPE"='TYPE1' OR "U"."OPERATION_TYPE"='TYPE12'))
  15 - access("U"."IP"=:B1)

Plan hash value: 1669240984

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                    | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |  OMem |  1Mem |  O/1/M   |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                   |                         |        |       |       |   374K(100)|          |       |       |        |      |            |       |       |          |
|   1 |  MERGE                            | TABLE1                    |        |       |       |            |          |       |       |        |      |            |       |       |          |
|   2 |   PX COORDINATOR                  |                         |        |       |       |            |          |       |       |        |      |            |       |       |          |
|   3 |    PX SEND QC (RANDOM)            | :TQ10002                |    413M|   574G|       |   374K  (2)| 01:21:50 |       |       |  Q1,02 | P->S | QC (RAND)  |       |       |          |
|   4 |     VIEW                          |                         |        |       |       |            |          |       |       |  Q1,02 | PCWP |            |       |       |          |
|*  5 |      HASH JOIN BUFFERED           |                         |    413M|   574G|  3583M|   374K  (2)| 01:21:50 |       |       |  Q1,02 | PCWP |            |  2047M|   100M|          |
|   6 |       PX RECEIVE                  |                         |     32M|    27G|       |  2199  (20)| 00:00:29 |       |       |  Q1,02 | PCWP |            |       |       |          |
|   7 |        PX SEND HASH               | :TQ10000                |     32M|    27G|       |  2199  (20)| 00:00:29 |       |       |  Q1,00 | P->P | HASH       |       |       |          |
|   8 |         PX BLOCK ITERATOR         |                         |     32M|    27G|       |  2199  (20)| 00:00:29 |       |       |  Q1,00 | PCWC |            |       |       |          |
|*  9 |          TABLE ACCESS STORAGE FULL| TABLE2           |     32M|    27G|       |  2199  (20)| 00:00:29 |       |       |  Q1,00 | PCWP |            |  1025K|  1025K|     2/0/0|
|  10 |       PX RECEIVE                  |                         |    413M|   218G|       | 67413   (9)| 00:14:44 |       |       |  Q1,02 | PCWP |            |       |       |          |
|  11 |        PX SEND HASH               | :TQ10001                |    413M|   218G|       | 67413   (9)| 00:14:44 |       |       |  Q1,01 | P->P | HASH       |       |       |          |
|  12 |         PX BLOCK ITERATOR         |                         |    413M|   218G|       | 67413   (9)| 00:14:44 |     1 |     3 |  Q1,01 | PCWC |            |       |       |          |
|* 13 |          TABLE ACCESS STORAGE FULL| TABLE1              |    413M|   218G|       | 67413   (9)| 00:14:44 |     1 |     6 |  Q1,01 | PCWP |            |  1025K|  1025K|     2/0/0|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("I"."IP"="U"."IP")
   9 - storage(:Z>=:Z AND :Z<=:Z AND (("U"."OPERATION_TYPE"='TYPE1' OR "U"."OPERATION_TYPE"='TYPE12')))
       filter(("U"."OPERATION_TYPE"='TYPE1' OR "U"."OPERATION_TYPE"='TYPE12'))
  13 - storage(:Z>=:Z AND :Z<=:Z)

Since your statement involves a join, I'd venture to guess you might need to include the `PARALLEL` hints for both joined tables. — mustaccio, Sep 10 '14 at 21:03
@mustaccio tried that already but no change in execution time, tried even merge to avoid where exists but still no performance gain. — user3570198, Sep 10 '14 at 21:12

score 3 · Answer 1 · answered Sep 11 '14 at 02:13

alter session enable parallel dml; Only part of the current plan is running in parallel. The UPDATE operation should be below the PX COORDINATOR operation, not above it. This is likely because the session does not have parallel DML enabled.

Below is a sample schema and queries demonstrating how the plans change when session parallelism is enabled.

drop table test1 purge;
create table test1(a number not null, b number);
insert into test1 select level, 1 from dual connect by level <= 100000;
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/

alter session disable parallel dml;
explain plan for update /*+ parallel(test1, 8) */ test1 set a = 1 where b <= 1000;
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |   100K|   781K|    11  (10)| 00:00:01 |        |      |            |
|   1 |  UPDATE               | TEST1    |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR      |          |       |       |            |          |        |      |            |
...

rollback;
alter session enable parallel dml;
explain plan for update /*+ parallel(test1, 8) */ test1 set a = 1 where b <= 1000;
select * from table(dbms_xplan.display);


---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |   100K|   781K|    11  (10)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |   100K|   781K|    11  (10)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    UPDATE             | TEST1    |       |       |            |          |  Q1,00 | PCWP |            |
...

MERGE Your comment says you already tried a MERGE, but it might be worth trying again in combination with other tips. MERGE allows TABLE2 to only be listed once, removing a join. And MERGE supports more join methods than UPDATE, such as a hash join between the modified table and other row sources.
Statement level parallel hint. Since 11gR2, objects should almost never be listed in the parallel hint. When objects are not listed the hint applies to the entire statement. If parallelism is used in one part of the statement it almost never hurts to use it everywhere. Replace /*+ parallel (a, 8) */ with /*+ parallel (8) */.
What changed? Even if the above tips help it is still good to know what changed and caused the performance problem. If the plan changed the different plans should be captured in AWR. Find them with this statement: select * from table(dbms_xplan.display_awr(sql_id => 'Your SQL_ID')); Plans alone are not enough, especially in a data warehouse. It may be necessary to compare runs by aggregating wait events at the operation level. All the information is in DBA_HIST_ACTIVE_SESS_HISTORY but custom queries are needed.

I updated the explain plans to te question and the merge E-Bytes seems to be a lot. not sure if adding a hint to use HASH JOIN RIGHT SEMI BUFFERED will help. I tried the update with Parallel 8 on SQL and for Merge as well no improvement is seen in the actually though the explain plan shows good. — user3570198, Sep 22 '14 at 18:23
The `UPDATE` and `MERGE` queries are still not fully running in parallel, see suggestion #1 above. I'd wager you're running into [Amdahl's law](http://en.wikipedia.org/wiki/Amdahl%27s_law), and that tuning the `SELECT` portion of the query won't get you very far. — Jon Heller, Sep 22 '14 at 19:18

Oracle DB on Exadata. update running for hours, parallel hint isn't working

1 Answers1