Oracle DB on Exadata. update works for several hours, parallel hint doesn't work

We have two huge tables and trying to update about 60 million records. It used to be updated 30 minutes before, and now it took n hours, even no more than 20% data growth. Here's some more information. I see there is a lot of expectation on the "physical read" single block "PX Deq: Table Q Normal". I don't understand why the parallel hint is not working. Is this the reason that the physical reading of individual blocks of a cell? or is there any other reason why performance is degrading, any idea? and is there any reason to improve performance by getting rid of the WHERE EXISTS clause?

table1 (A) size 316 GB record score 456 365 654

table2 (B) 7GB record score 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'));

      

OPERATING 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)

      

+3


source to share


1 answer


  • alter session enable parallel dml;

    Only part of the current plan is executed in parallel. The operation UPDATE

    must be below the operation PX COORDINATOR

    , not above. This is probably because the session does not support concurrent DML.

The following is an example schema and queries that show plan changes when parallelism session 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 |            |
...

      

  1. MERGE Your comment says you've tried it already MERGE

    , but it might be worth trying again in conjunction with other tips. MERGE

    allows TABLE2 to be specified only once, removing the connection. And it MERGE

    supports more join methods than UPDATE

    , for example, hash join between modified table and other row sources.
  2. A document-level prompt parameter . Since 11gR2, objects should almost never appear in a parallel tooltip. When no objects are specified, the hint is applied to the entire operator. If parallelism is used in one part of a statement, it is almost never afraid to use it throughout. Replace /*+ parallel (a, 8) */

    with /*+ parallel (8) */

    .
  3. What changed? Even if the tips above help, it's still good to know what changed and caused the performance issue. If the plan changes, different plans must be recorded in the 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 activity level. All information is in DBA_HIST_ACTIVE_SESS_HISTORY

    , but custom queries are needed.
+2


source







All Articles