NO_PARALLEL hint as overview


globe-73397_640This example will show the use of the NO_PARALLEL hints.

SQL> select owner,table_name, degree
  1  from dba_tables
  2        where table_name in ('EMPLOYEES','DEPARTMENTS')

OWNER                          TABLE_NAME                     DEGREE
------------------------------ ------------------------------ ----------------------------------------
HR                             EMPLOYEES                      1
HR                             DEPARTMENTS                    1

As we can see is the „degree“ for our tables is one, which means that we only need to change this for our example.

SQL> alter table hr.employees parallel (degree 4);

Table altered.

SQL> alter table hr.departments parallel (degree 4);

Table altered.

We now run our SQL statement, we see the parallel execution.

select e.LAST_NAME , d.DEPARTMENT_NAME
from EMPLOYEES e , DEPARTMENTS d
where  e.DEPARTMENT_ID = d.DEPARTMENT_ID;
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU) | Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |   106 |  2862 |     5  (20) | 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                 |               |       |       |             |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)           | :TQ10002      |   106 |  2862 |     5  (20) | 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED           |               |   106 |  2862 |     5  (20) | 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                  |               |    27 |   432 |     2    (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH               | :TQ10000      |    27 |   432 |     2    (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR         |               |    27 |   432 |     2    (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL        | DEPARTMENTS   |    27 |   432 |     2    (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |     PX RECEIVE                  |               |   107 |  1177 |     2    (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |      PX SEND HASH               | :TQ10001      |   107 |  1177 |     2    (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  10 |       PX BLOCK ITERATOR         |               |   107 |  1177 |     2    (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  11 |        TABLE ACCESS FULL        | EMPLOYEES     |   107 |  1177 |     2    (0)| 00:00:01 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

We can see in the Execution plan well that we have run this SQL statement in parallel.

Let’s test our parallel HINT.

select /*+ no_parallel(d) */ e.LAST_NAME , d.DEPARTMENT_NAME
from EMPLOYEES e , DEPARTMENTS d
where  e.DEPARTMENT_ID = d.DEPARTMENT_ID;
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |   106 |  2862 |     5  (20)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                |              |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10001     |   106 |  2862 |     5  (20)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    MERGE JOIN                  |              |   106 |  2862 |     5  (20)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     SORT JOIN                  |              |    27 |   432 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      BUFFER SORT               |              |       |       |            |          |  Q1,01 | PCWC |            |
|   6 |       PX RECEIVE               |              |    27 |   432 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |        PX SEND BROADCAST       | :TQ10000     |    27 |   432 |     2   (0)| 00:00:01 |        | S->P | BROADCAST  |
|   8 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |    27 |   432 |     2   (0)| 00:00:01 |        |      |            |
|   9 |      INDEX FULL SCAN           | DEPT_ID_PK   |    27 |       |     1   (0)| 00:00:01 |        |      |            |
|* 10 |     SORT JOIN                  |              |   107 |  1177 |     3  (34)| 00:00:01 |  Q1,01 | PCWP |            |
|  11 |      PX BLOCK ITERATOR         |              |   107 |  1177 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  12 |       TABLE ACCESS FULL        | EMPLOYEES    |   107 |  1177 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------

In the next execution plan, we can see that the NO_PARALLEL HINT has not meant that we did execute the statement but the serial HINT out for the DEPARTMENTS table brought about a change.

select /*+ no_parallel(e) no_parallel(d) */
    e.LAST_NAME , d.DEPARTMENT_NAME
from EMPLOYEES e , DEPARTMENTS d
where  e.DEPARTMENT_ID = d.DEPARTMENT_ID;
--------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                   |   106 |  2862 |     6  (34)| 00:00:01 |
|   1 |  MERGE JOIN                       |                   |   106 |  2862 |     6  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | DEPARTMENTS       |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN                | DEPT_ID_PK        |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                       |                   |   107 |  1177 |     4  (50)| 00:00:01 |
|   5 |    VIEW                           | index$_join$_001  |   107 |  1177 |     3  (34)| 00:00:01 |
|*  6 |     HASH JOIN                     |                   |       |       |            |          |
|   7 |      INDEX FAST FULL SCAN         | EMP_DEPARTMENT_IX |   107 |  1177 |     1   (0)| 00:00:01 |
|   8 |      INDEX FAST FULL SCAN         | EMP_NAME_IX       |   107 |  1177 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Only in this example where we have specified a NO_HINT for all tables, we can see that the complete  statement is seriel executed.

Unfortunately, there are currently (at least not known to me) no way to disable the parallel function at SQL level except the HINT.

You can control parallel query at session level  like this:

  • ALTER SESSION DISABLE PARALLEL DML;
  • ALTER SESSION DISABLE PARALLEL DDL;
  • ALTER SESSION DISABLE PARALLEL QUERY;

Alternatively, there is also the possibility to take part of influence.

Limit the parallel execution to the local instance (RAC):

System:
 alter system set "parallel_force_local" = true;
Session:
 alter session set "parallel_force_local" = true;

As always comments are welcome

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s