Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: November 04, 2017 - 3:07 pm UTC

Last updated: November 06, 2017 - 11:22 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

I came across below code spinet:

DECLARE
..
..
BEGIN
..
..
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML' ;
DELETE /*+ PARALLEL(TABLE_TEST, 4) */ FROM TABLE_TEST WHERE ID IN (SELECT ID FROM TABLE_2);

END;

Since EXECUTE IMMEDIATE process DDL in different session, will changes made by DML carried out using PARALLEL 4 here??
If not could you please assist how to achieve it.


and Connor said...

"Since EXECUTE IMMEDIATE process DDL in different session"

What makes you think that ? EXECUTE IMMEDIATE still applies to the *current* session. For example:

SQL> create table t
  2  as select rownum x
  3  from all_Objects
  4  where rownum <= 10;

Table created.

SQL>
SQL> set serverout on
SQL>
SQL> declare
  2    c int;
  3  begin
  4    select count(*) into c from t;
  5    dbms_output.put_line(c);
  6
  7    execute immediate 'delete from t';
  8
  9    select count(*) into c from t;
 10    dbms_output.put_line(c);
 11  end;
 12  /
10
0

PL/SQL procedure successfully completed.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Parallel Execution

Rajeshwaran Jeyabal, November 06, 2017 - 5:39 am UTC

....
will changes made by DML carried out using PARALLEL 4 here??
....

Starting with Oracle 11g and above you can make use of SQL Monitor to observer the parallel execution in the database.

In addition to the SQL Monitor, you can make use of these two v$ tables (v$px_session and v$px_sesstat) to montior the PX slaves while the Parallel DML/DDL executing.

demo@ORA11G> set serveroutput off
demo@ORA11G> begin
  2     execute immediate 'alter session enable parallel dml';
  3     delete /*+ parallel(b,4) */ from big_table b
  4             where owner in ('SYS','PUBLIC') ;
  5  end;
  6  /

PL/SQL procedure successfully completed.

demo@ORA11G>
demo@ORA11G> @sqlmonitortext


The SQL Monitor report shows this.

SQL Text
------------------------------
DELETE /*+ parallel(b,4) */ FROM BIG_TABLE B WHERE OWNER IN ('SYS','PUBLIC')

Global Information
------------------------------
 Status              :  DONE
 Instance ID         :  1
 Session             :  DEMO (69:147)
 SQL ID              :  4srqjuyfgn597
 SQL Execution ID    :  16777216
 Execution Started   :  11/06/2017 10:52:48
 First Refresh Time  :  11/06/2017 10:52:48
 Last Refresh Time   :  11/06/2017 10:53:31
 Duration            :  43s
 Module/Action       :  SQL*Plus/-
 Service             :  ORA11G
 Program             :  sqlplus.exe

Global Stats
=================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes |
=================================================================================
|     169 |    8.67 |       50 |        0.22 |      110 |   959K |  601 | 122MB |
=================================================================================


Parallel Execution Details (DOP=4 , Servers Allocated=4)
=====================================================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  |          Wait Events           |
|                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes |           (sample #)           |
=====================================================================================================================================================
| PX Coordinator | QC    |         |    0.39 |         |          |        0.06 |     0.33 |     10 |      |     . |                                |
| p000           | Set 1 |       1 |      42 |    2.31 |       12 |             |       28 |   235K |  148 |  30MB | log buffer space (5)           |
|                |       |         |         |         |          |             |          |        |      |       | log file switch completion (1) |
|                |       |         |         |         |          |             |          |        |      |       | db file scattered read (8)     |
|                |       |         |         |         |          |             |          |        |      |       | db file sequential read (6)    |
| p001           | Set 1 |       2 |      43 |    1.99 |       12 |        0.00 |       28 |   224K |  148 |  28MB | log buffer space (5)           |
|                |       |         |         |         |          |             |          |        |      |       | log file switch completion (1) |
|                |       |         |         |         |          |             |          |        |      |       | db file scattered read (4)     |
|                |       |         |         |         |          |             |          |        |      |       | db file sequential read (6)    |
| p002           | Set 1 |       3 |      42 |    2.20 |       14 |        0.16 |       26 |   277K |  164 |  35MB | log buffer space (7)           |
|                |       |         |         |         |          |             |          |        |      |       | log file switch completion (2) |
|                |       |         |         |         |          |             |          |        |      |       | Disk file operations I/O (1)   |
|                |       |         |         |         |          |             |          |        |      |       | db file scattered read (8)     |
|                |       |         |         |         |          |             |          |        |      |       | db file sequential read (2)    |
| p003           | Set 1 |       4 |      42 |    2.17 |       11 |             |       28 |   223K |  141 |  28MB | log buffer space (7)           |
|                |       |         |         |         |          |             |          |        |      |       | log file switch completion (2) |
|                |       |         |         |         |          |             |          |        |      |       | db file scattered read (8)     |
|                |       |         |         |         |          |             |          |        |      |       | db file sequential read (2)    |
=====================================================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=2716077150)
================================================================================================================================================================
| Id |        Operation        |   Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |        Activity Detail         |
|    |                         |           | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |          (# samples)           |
================================================================================================================================================================
|  0 | DELETE STATEMENT        |           |         |      |         1 |    +43 |     5 |        4 |      |       |          |                                |
|  1 |   PX COORDINATOR        |           |         |      |         2 |    +42 |     5 |        4 |      |       |     1.18 | Cpu (2)                        |
|  2 |    PX SEND QC (RANDOM)  | :TQ10000  |   60606 | 1174 |         2 |    +42 |     4 |        4 |      |       |          |                                |
|  3 |     DELETE              | BIG_TABLE |         |      |        43 |     +1 |     4 |        4 |  356 |   3MB |    66.86 | log buffer space (24)          |
|    |                         |           |         |      |           |        |       |          |      |       |          | log file switch completion (6) |
|    |                         |           |         |      |           |        |       |          |      |       |          | Cpu (67)                       |
|    |                         |           |         |      |           |        |       |          |      |       |          | db file sequential read (16)   |
|  4 |      PX BLOCK ITERATOR  |           |   60606 | 1174 |        42 |     +2 |     4 |     840K |      |       |          |                                |
|  5 |       TABLE ACCESS FULL | BIG_TABLE |   60606 | 1174 |        42 |     +2 |    52 |     840K |  245 | 119MB |    31.95 | Cpu (25)                       |
|    |                         |           |         |      |           |        |       |          |      |       |          | Disk file operations I/O (1)   |
|    |                         |           |         |      |           |        |       |          |      |       |          | db file scattered read (28)    |
================================================================================================================================================================


This confirms that we allocated with 4 PX Slaves for this execution.

BTW: When you upgrade to Oracle 12c and above - you dont need this ENABLE PARALLEL DML at session level, they can made at the statement level using ENABLE_PARALLEL_DML hint.

https://blogs.oracle.com/datawarehousing/new-way-to-enable-parallel-dml
Chris Saxon
November 06, 2017 - 11:22 am UTC

Yep, thanks for adding this.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library