Friday, July 27, 2012

Configure Parallel DEGREE / INSTANCES of Table / PARALLEL execution running slow.

This is demo about Query which performing Parallel but takes lot of time.
Configuring Table parallel degree / instances degree incorrect can result adverse effect.

Platform is Exadata-X2.

Elapsed time has dropped from 2 minutes to 20 seconds.

SELECT count(1)
FROM DUMMY_TPLGY
WHERE (DUMMY_TPLGY.DVIC_TYPE = 'optical_node'
OR DUMMY_TPLGY.DVIC_TYPE = 'amplifier')
AND DUMMY_TPLGY.SNPSHT_DTM in
(SELECT SNPSHT_DTM
FROM DUMMY_TPLGY_CTRL
WHERE AVL_IND = 'Y'
AND SNPSHT_DTM >
(SELECT MAX (DAY_KEY)
FROM RCM_CTRL
WHERE TBL_NAME = 'DUMMY_DIM' AND AVL_IND = 'Y'));

Lets see What explain plan says .

set timing on 
set time on 
set long 999999
set lines 180
set pages 555

12:24:11 SQL> explain plan for
12:24:11   2  SELECT count(1)
12:24:11   3  FROM DUMMY_TPLGY
12:24:11   4  WHERE (DUMMY_TPLGY.DVIC_TYPE = 'optical_node'
12:24:11   5  OR DUMMY_TPLGY.DVIC_TYPE = 'amplifier')
12:24:11   6  AND DUMMY_TPLGY.SNPSHT_DTM in
12:24:11   7  (SELECT SNPSHT_DTM
12:24:11   8  FROM DUMMY_TPLGY_CTRL
12:24:11   9  WHERE AVL_IND = 'Y'
12:24:11  10  AND SNPSHT_DTM >
12:24:11  11  (SELECT MAX (DAY_KEY)
12:24:11  12  FROM RCM_CTRL
12:24:11  13  WHERE TBL_NAME = 'DUMMY_DIM' AND AVL_IND = 'Y'));


Explained.

Elapsed: 00:00:02.05

As you can see cost are too much high for full table scan.

12:24:15 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 231818000

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |    26 | 50332   (1)| 00:10:04 |       |       |        |         |          |
|   1 |  SORT AGGREGATE               |                     |     1 |    26 |            |          |       |       |        |         |          |
|   2 |   NESTED LOOPS                |                     |  3434K|    85M| 49646   (1)| 00:09:56 |       |       |        |         |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| DUMMY_TPLGY_CTRL     |     3 |    30 |     1   (0)| 00:00:01 |       |       |        |         |          |
|*  4 |     INDEX RANGE SCAN          | XPK_DUMMY_TPLGY_CTRL |     1 |       |     1   (0)| 00:00:01 |       |       |        |         |          |
|   5 |      SORT AGGREGATE           |                     |     1 |    26 |            |          |       |       |        |         |          |
|   6 |       PX COORDINATOR          |                     |       |       |            |          |       |       |        |         |          |
|   7 |        PX SEND QC (RANDOM)    | :TQ10000            |     1 |    26 |            |          |       |       |  Q1,00 |    P->S | QC (RAND)|
|   8 |         SORT AGGREGATE        |                     |     1 |    26 |            |          |       |       |  Q1,00 |    PCWP |          |
|   9 |          PX BLOCK ITERATOR    |                     |   382 |  9932 |    24   (0)| 00:00:01 |       |       |  Q1,00 |    PCWC |          |
|* 10 |           TABLE ACCESS FULL   | RCM_CTRL            |   382 |  9932 |    24   (0)| 00:00:01 |       |       |  Q1,00 |    PCWP |          |
|  11 |    PARTITION LIST ITERATOR    |                     |  1174K|    17M| 16548   (1)| 00:03:19 |   KEY |   KEY |        |         |          |
|* 12 |     TABLE ACCESS FULL         | DUMMY_TPLGY          |  1174K|    17M| 16548   (1)| 00:03:19 |   KEY |   KEY |        |         |          |
--------------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("AVL_IND"='Y')
   4 - access("SNPSHT_DTM"> (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("DAY_KEY")),0)) FROM "RCM"."RCM_CTRL" "RCM_CTRL" WHERE
              "TBL_NAME"='DUMMY_DIM' AND "AVL_IND"='Y'))
  10 - filter("TBL_NAME"='DUMMY_DIM' AND "AVL_IND"='Y')
  12 - filter(("DUMMY_TPLGY"."DVIC_TYPE"='amplifier' OR "DUMMY_TPLGY"."DVIC_TYPE"='optical_node') AND
              "DUMMY_TPLGY"."SNPSHT_DTM"="SNPSHT_DTM")

Note
-----
   - dynamic sampling used for this statement (level=6)

33 rows selected.

Lets see actual execution Time .

Elapsed: 00:00:01.01
12:24:28 SQL>
12:24:28 SQL>
12:28:14 SQL> SELECT count(1)
12:28:15   2  FROM DUMMY_TPLGY
12:28:15   3  WHERE (DUMMY_TPLGY.DVIC_TYPE = 'optical_node'
12:28:15   4  OR DUMMY_TPLGY.DVIC_TYPE = 'amplifier')
12:28:15   5  AND DUMMY_TPLGY.SNPSHT_DTM in
12:28:15   6  (SELECT SNPSHT_DTM
12:28:15   7  FROM DUMMY_TPLGY_CTRL
12:28:15   8  WHERE AVL_IND = 'Y'
12:28:15   9  AND SNPSHT_DTM >
12:28:15  10  (SELECT MAX (DAY_KEY)
12:28:15  11  FROM RCM_CTRL
12:28:15  12  WHERE TBL_NAME = 'DUMMY_DIM' AND AVL_IND = 'Y'));

  COUNT(1)
----------
   3574152

Elapsed: 00:02:07.34
12:30:23 SQL>

Almost 2 minutes.

What are these tables and why it takes so long even if its running PARALLEL.
Culprit is Table DUMMY_TPLGY, which is going for FULL TABLE SCAN and is very big.

SQL> SELECT OWNER,TABLE_NAME,DEGREE,INSTANCES,PARTITIONED,NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME IN ('DUMMY_TPLGY','RCM_CTRL','DUMMY_TPLGY_CTRL');

OWNER      TABLE_NAME           DEGREE     INSTANCES  PARTITIONED       NUM_ROWS
---------- -------------------- ---------- ---------- --------------- ----------
RCM        RCM_CTRL                DEFAULT    DEFAULT NO                    4597
TNC        DUMMY_TPLGY                    1          1 YES              277101036
TNC        DUMMY_TPLGY_CTRL               1          1 NO                      59

----------Now Tables are in PARALLEL but degree is one Only.
So lets change it default and let oracle decide how to execute it.

SQL> ALTER TABLE TNC.DUMMY_TPLGY PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT);

Table altered.

SQL> ALTER TABLE TNC.DUMMY_TPLGY_CTRL PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT);

Table altered.

SQL> SELECT OWNER,TABLE_NAME,DEGREE,INSTANCES,PARTITIONED,NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME IN ('DUMMY_TPLGY','RCM_CTRL','DUMMY_TPLGY_CTRL');

OWNER      TABLE_NAME           DEGREE     INSTANCES  PARTITIONED       NUM_ROWS
---------- -------------------- ---------- ---------- --------------- ----------
RCM        RCM_CTRL                DEFAULT    DEFAULT NO                    4597
TNC        DUMMY_TPLGY              DEFAULT    DEFAULT YES              277101036
TNC        DUMMY_TPLGY_CTRL         DEFAULT    DEFAULT NO                      59

---New execution plan has low cost as compare to previous plan even Execution time also dropped drastically .

12:34:38 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3873636908

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib|
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     1 |    26 |  1746   (1)| 00:00:21 |       |       |        |      |           |
|   1 |  SORT AGGREGATE                     |                     |     1 |    26 |            |          |       |       |        |      |           |
|   2 |   PX COORDINATOR                    |                     |       |       |            |          |       |       |        |      |           |
|   3 |    PX SEND QC (RANDOM)              | :TQ20001            |     1 |    26 |            |          |       |       |  Q2,01 | P->S | QC (RAND) |
|   4 |     SORT AGGREGATE                  |                     |     1 |    26 |            |          |       |       |  Q2,01 | PCWP |           |
|   5 |      NESTED LOOPS                   |                     |  3434K|    85M|  1723   (1)| 00:00:21 |       |       |  Q2,01 | PCWP |           |
|   6 |       BUFFER SORT                   |                     |       |       |            |          |       |       |  Q2,01 | PCWC |           |
|   7 |        PX RECEIVE                   |                     |       |       |            |          |       |       |  Q2,01 | PCWP |           |
|   8 |         PX SEND BROADCAST           | :TQ20000            |       |       |            |          |       |       |        | S->P | BROADCAST |
|*  9 |          TABLE ACCESS BY INDEX ROWID| DUMMY_TPLGY_CTRL     |     3 |    30 |     1   (0)| 00:00:01 |       |       |        |      |           |
|* 10 |           INDEX RANGE SCAN          | XPK_DUMMY_TPLGY_CTRL |     1 |       |     1   (0)| 00:00:01 |       |       |        |      |           |
|  11 |            SORT AGGREGATE           |                     |     1 |    26 |            |          |       |       |        |      |           |
|  12 |             PX COORDINATOR          |                     |       |       |            |          |       |       |        |      |           |
|  13 |              PX SEND QC (RANDOM)    | :TQ10000            |     1 |    26 |            |          |       |       |Q1,00   | P->S | QC (RAND) |
|  14 |               SORT AGGREGATE        |                     |     1 |    26 |            |          |       |       |Q1,00   | PCWP |           |
|  15 |                PX BLOCK ITERATOR    |                     |   382 |  9932 |    24   (0)| 00:00:01 |       |       |Q1,00   | PCWC |           |
|* 16 |                 TABLE ACCESS FULL   | RCM_CTRL            |   382 |  9932 |    24   (0)| 00:00:01 |       |       |Q1,00   | PCWP |           |
|  17 |       PX BLOCK ITERATOR             |                     |  1174K|    17M|   574   (1)| 00:00:07 |   KEY |   KEY |Q2,01   | PCWC |           |
|* 18 |        TABLE ACCESS FULL            | DUMMY_TPLGY          |  1174K|    17M|   574   (1)| 00:00:07 |   KEY |   KEY |Q2,01   | PCWP |           |
-------------------------------------------------------------------------------------------------------------------------------------------------------

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

   9 - filter("AVL_IND"='Y')
  10 - access("SNPSHT_DTM"> (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("DAY_KEY")),0)) FROM "RCM"."RCM_CTRL" "RCM_CTRL" WHERE "TBL_NAME"='DUMMY_DIM'
              AND "AVL_IND"='Y'))
  16 - filter("TBL_NAME"='DUMMY_DIM' AND "AVL_IND"='Y')
  18 - filter(("DUMMY_TPLGY"."DVIC_TYPE"='amplifier' OR "DUMMY_TPLGY"."DVIC_TYPE"='optical_node') AND "DUMMY_TPLGY"."SNPSHT_DTM"="SNPSHT_DTM")

---Now Lets run and Measure execution time.

SQL> ALTER SESSION ENABLE PARALLEL QUERY;

Session altered.

SQL> SET TIME ON
12:51:00 SQL> SET TIMING ON;
12:51:03 SQL>
12:51:15 SQL> SELECT count(1)
12:51:16   2  FROM DUMMY_TPLGY
12:51:16   3  WHERE (DUMMY_TPLGY.DVIC_TYPE = 'optical_node'
12:51:16   4  OR DUMMY_TPLGY.DVIC_TYPE = 'amplifier')
12:51:16   5  AND DUMMY_TPLGY.SNPSHT_DTM in
12:51:16   6  (SELECT SNPSHT_DTM
12:51:16   7  FROM DUMMY_TPLGY_CTRL
12:51:16   8  WHERE AVL_IND = 'Y'
12:51:16   9  AND SNPSHT_DTM >
12:51:16  10  (SELECT MAX (DAY_KEY)
12:51:16  11  FROM RCM_CTRL
12:51:16  12  WHERE TBL_NAME = 'DUMMY_DIM' AND AVL_IND = 'Y'));

  COUNT(1)
----------
   3574152

Elapsed: 00:00:20.89
12:51:37 SQL>


Here we go only 20 seconds from 2 minutes.






No comments:

Post a Comment