Parallel Execution -- 6 Parallel DML Restrictions

Here's link to the 11.2 documentation on Restrictions on Parallel DML

So, some of the implications are :

1.  You cannot do Parallel DML if a Trigger is present on the target table

2.  Certain integrity constraints disable Parallel DML.

3,  Parallel DML cannot be part of a Distributed Transaction.

Oracle silently converts the Parallel DML to a Serial DML without raising a warning / error.

.
.
.

Parallel Execution -- 5b Parallel INSERT Execution Plan

As noted in my previous post, Oracle does NOT enable Parallel DML by default.  You need to explicitly enable it with ALTER SESSION ENABLE PARALLEL DML.

Can you use the Execution Plan of an INSERT statement to identify if the INSERT was executed in Parallel ?

Here's a brief demonstration :

SQL*Plus: Release 11.2.0.2.0 Production on Sat May 16 22:27:49 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HEMANT>truncate table another_large_table;

Table truncated.

HEMANT>select degree from user_tables where table_name = 'ANOTHER_LARGE_TABLE';

DEGREE
----------------------------------------
         1

HEMANT>alter table another_large_table parallel 4;

Table altered.

HEMANT>insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE
  2  select /*+ PARALLEL */ * from large_table;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuuygy5k8nfrh, child number 0
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |       |       |  1314 (100)|          |        |      |         |
|   1 |  LOAD TABLE CONVENTIONAL |             |       |       |            |          |        |      |         |
|   2 |   PX COORDINATOR         |             |       |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |         |
|*  5 |      TABLE ACCESS FULL   | LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |         |
---------------------------------------------------------------------------------------------------------------------

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

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuuygy5k8nfrh, child number 1
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |       |       |  1314 (100)|          |        |      |         |
|   1 |  LOAD TABLE CONVENTIONAL |             |       |       |            |          |        |      |         |
|   2 |   PX COORDINATOR         |             |       |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |         |
|*  5 |      TABLE ACCESS FULL   | LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |         |
---------------------------------------------------------------------------------------------------------------------

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

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 16


27 rows selected.

HEMANT>
HEMANT>select count(*) from another_large_table;

  COUNT(*)
----------
   4802944

HEMANT>

Notice the "LOAD TABLE CONVENTIONAL" ?  Parallel Execution Servers were used for querying the source table LARGE_TABLE but the actual INSERT was executed as a non-parallel INSERT.  Another piece of evidence that the INSERT was not executed using PX Servers is that I was able to query the table without an ORA-12838 error.

Let me try again with the correct ALTER SESSION command.

HEMANT>truncate table another_large_table;

Table truncated.

HEMANT>alter session enable parallel dml;

Session altered.

HEMANT>insert /*+ PARALLEL */ into another_large_table tgt
  2  select /*+ PARALLEL */ * from large_table src;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9scm06z0m9vz6, child number 0
-------------------------------------
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL
*/ * from large_table src

Plan hash value: 474933689

------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT      |             |       |       |  1314 (100)|          |        |      |         |
|   1 |  PX COORDINATOR       |             |       |       |            |          |        |      |         |
|   2 |   PX SEND QC (RANDOM) | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT     |             |       |       |            |          |  Q1,00 | PCWP |         |
|   4 |     PX BLOCK ITERATOR |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |         |
|*  5 |      TABLE ACCESS FULL| LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |         |
------------------------------------------------------------------------------------------------------------------

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

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: 9scm06z0m9vz6, child number: 1 cannot be found


HEMANT>select count(*) from another_large_table;
select count(*) from another_large_table
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


HEMANT>

Here, the ORA-12838 is evidence that the INSERT was Parallel. But that evidence is only visible from the same session. What if we had to use another session to check the Execution ? We'd then use the DBMS_XPLAN.DISPLAY_CURSOR method.  Here we notice the PX COORDINATOR at ID=1 appearing *above* the LOAD AS SELECT.  There is no LOAD TABLE CONVENTIONAL step.  These are evidence that the LOAD was executed by PX Servers.
Note : Ignore the "P->S" in ID=2.



Question : Why does the first (non-parallel insert) statement have 2 child cursors.  And the 2nd child cursor actually does indicate an Auto DoP.  I have evidence that child cursor 0 is executed and child cursor 1, although parsed, shows as 0 executions in V$SQL :

SYS>select sql_id, child_number, executions, sql_text
  2  from v$sql
  3  where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
  4  order by 1,2;

SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6            0          1
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh            0          1
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh            1          0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYS>
SYS>l
  1  select sql_id, child_number, executions, px_servers_executions, sql_text
  2  from v$sql
  3  where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
  4* order by 1,2
SYS>/

SQL_ID        CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6            0          1                     0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh            0          1                     0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh            1          0                     4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

Interesting, isn't it ? This time, the PX_SERVERS_EXECUTIONS doesn't seem to be correct.  So, either EXECUTIONS is correct or PX_SERVERS_EXECUTIONS is correct.  These need to be re-verified in 11.2.0.4.

UPDATE 18-May-15 :  In 11.2.0.4,  I see 1 child cursor (not 2) with the correct EXECUTIONS and PX_SERVERS_EXECUTIONS being reported.  I will need to rerun my tests in the same 11.2.0.2 environment.


Re-testing in the same 11.2.0.2 environment :
First, the insert that does NOT get executed as a Parallel INSERT :

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Sun May 24 22:58:28 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HEMANT>truncate table ANOTHER_LARGE_TABLE;

Table truncated.

HEMANT>select degree from user_tables where table_name = 'ANOTHER_LARGE_TABLE';

DEGREE
----------------------------------------
         4

HEMANT>insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE
  2  select /*+ PARALLEL */ * from large_table;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuuygy5k8nfrh, child number 0
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |       |       |  1314 (100)|          |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL |             |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR         |             |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL   | LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------

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

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuuygy5k8nfrh, child number 1
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |       |       |  1314 (100)|          |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL |             |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR         |             |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL   | LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------

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

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 16


27 rows selected.

HEMANT>select count(*) from another_large_table;

  COUNT(*)
----------
   4802944

HEMANT>
SYSTEM>select sql_id, child_number, executions, px_servers_executions, sql_text
  2  from v$sql
  3  where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
  4  order by 1,2;

SQL_ID        CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
fuuygy5k8nfrh            0          1                     0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh            1          0                     4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>

Next, the actual Parallel Insert.

HEMANT>commit;

Commit complete.

HEMANT>alter session enable parallel dml;

Session altered.

HEMANT>insert /*+ PARALLEL */ into another_large_table tgt
  2  select /*+ PARALLEL */ * from large_table src;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9scm06z0m9vz6, child number 0
-------------------------------------
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL
*/ * from large_table src

Plan hash value: 474933689

------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT      |             |       |       |  1314 (100)|          |        |      |            |
|   1 |  PX COORDINATOR       |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000    |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT     |             |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |             |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| LARGE_TABLE |  4802K|   448M|  1314   (1)| 00:00:16 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

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

   5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: 9scm06z0m9vz6, child number: 1 cannot be found


HEMANT>select count(*) from another_large_table;
select count(*) from another_large_table
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


HEMANT>
SYSTEM>l
  1  select sql_id, child_number, executions, px_servers_executions, sql_text
  2  from v$sql
  3  where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
  4* order by 1,2
SYSTEM>
SYSTEM>/

SQL_ID        CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6            0          1                     0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh            0          1                     0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh            1          0                     4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>
SYSTEM>l
  1  select sql_id, executions, px_servers_executions, sql_text
  2  from v$sqlstats
  3  where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
  4* order by 1,2
SYSTEM>/

SQL_ID        EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6          1                     0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh          1                     4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>

The behaviour of the V$ views in the 11.2.0.2 environment doesn't seem correct. (Note : V$SQLSTATS doesn't differentiate by CHILD_NUMBER as does V$SQL).
.
.

Status Of My SlideShare Material

My  SlideShare  Material has had 7,390 views to date.

.
.
.

Parallel Execution -- 5 Parallel INSERT

Oracle permits Parallel DML (as with Parallel Query, this requires the Enterprise Edition).

Unlike Parallel Query, Parallel DML is *not* enabled by default.  You must explicitly enable it with an ALTER SESSION ENABLE PARALLEL DML.

The most common usage is Parallel INSERT.

Parallel Insert uses PX servers to execute the Insert.  Ideally, it makes sense to use Parallel Query to drive the Parallel Insert.  Each PX server doing the Insert executes a Direct Path Insert --- it allocates one or more extents to itself and inserts rows into that extent.  Effectively, the Parallel Insert creates a temporary segment.  When the whole Insert is successful, these extents of the temporary segment are merged into the target table (and the temporary segment loses it's existence).

Note that there are four consequences of this behaviour :

(a) Any empty or usable blocks in the existing extents are NOT used for the new rows.  The table *always* grows in allocated space even if there are empty blocks.

(b) Depending on the number of PX servers used, this method allocates more new extents than would a normal (Serial) Insert.

(c) The rows inserted are not visible to even the session that executed the Insert until and unless it issues a COMMIT.  Actually, the session cannot even re-query the same table (irrelevant is the possibility that the query would hit only pre-existing rows) without a COMMIT.  (This does not prevent the session from querying some other table before the COMMIT).

(d) The Direct Path Insert does not require large Undo space.  It does not track all the rowids into Undo.  It only needs to track the temporary segment and extents to be discarded should a ROLLBACK be issued.  So, it uses minimal Undo space.

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Fri May 1 22:46:46 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: hemant/hemant

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HEMANT>alter session enable parallel dml;

Session altered.

HEMANT>create table another_large_table as select * from large_table where 1=2;

Table created.

HEMANT>insert /*+ PARALLEL */ 
  2  into another_large_table
  3  select /*+ PARALLEL */ *
  4  from large_table;

4802944 rows created.

HEMANT>!ps -ef |grep ora_p0
oracle    3637     1  0 22:47 ?        00:00:00 ora_p000_orcl
oracle    3639     1  0 22:47 ?        00:00:00 ora_p001_orcl
oracle    3641     1  0 22:47 ?        00:00:00 ora_p002_orcl
oracle    3643     1  0 22:47 ?        00:00:00 ora_p003_orcl
oracle    3680  3618  0 22:50 pts/1    00:00:00 /bin/bash -c ps -ef |grep ora_p0
oracle    3682  3680  0 22:50 pts/1    00:00:00 grep ora_p0

HEMANT>
HEMANT>select count(*) from another_large_table;
select count(*) from another_large_table
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


HEMANT>

So, we see that 4 PX servers were used. We also see that the session cannot re-query the table.
What evidence do we have of the temporary segment and extents ?

SYS>select owner, segment_name, tablespace_name, extents, bytes/1048576
  2  from dba_segments
  3  where segment_type = 'TEMPORARY'
  4  /

OWNER        SEGMENT_NAME TABLESPACE_NAME    EXTENTS BYTES/1048576
------------ ------------ --------------- ---------- -------------
HEMANT       11.54579     HEMANT                 141      536.9375

SYS>

HEMANT>commit;

Commit complete.

HEMANT>

SYS>/

no rows selected

SYS>

The temporary segment no longer exists after the inserting session issues a COMMIT.  The extents of the temporary segment have been merged into the target table segment.

SYS>select owner, segment_name, tablespace_name, extents, bytes/1048576
  2  from dba_segments
  3  where segment_name = 'ANOTHER_LARGE_TABLE'
  4  /

OWNER        SEGMENT_NAME           TABLESPACE_NAME    EXTENTS BYTES/1048576
------------ ---------------------- --------------- ---------- -------------
HEMANT       ANOTHER_LARGE_TABLE    HEMANT                 142           537

SYS>

Now, let's see if another Parallel Insert would be able to reuse usable table blocks.  We DELETE (*not* TRUNCATE !) the rows in the table and re-attempt a Parallel Insert.

HEMANT>delete another_large_table;

4802944 rows deleted.

HEMANT>commit;

Commit complete.

HEMANT>

SYS>select owner, segment_name, tablespace_name, extents, bytes/1048576
  2  from dba_segments
  3  where segment_name = 'ANOTHER_LARGE_TABLE';

OWNER        SEGMENT_NAME           TABLESPACE_NAME    EXTENTS BYTES/1048576
------------ ---------------------- --------------- ---------- -------------
HEMANT       ANOTHER_LARGE_TABLE    HEMANT                 142           537

SYS>

HEMANT>insert /*+ PARALLEL */ 
  2  into another_large_table
  3  select /*+ PARALLEL */ *
  4  from large_table;

4802944 rows created.

HEMANT>

SYS>l
  1  select owner, segment_name, tablespace_name, extents, bytes/1048576
  2  from dba_segments
  3* where segment_name = 'ANOTHER_LARGE_TABLE'
SYS>/

OWNER        SEGMENT_NAME           TABLESPACE_NAME    EXTENTS BYTES/1048576
------------ ---------------------- --------------- ---------- -------------
HEMANT       ANOTHER_LARGE_TABLE    HEMANT                 142           537

SYS>

HEMANT>commit;

Commit complete.

HEMANT>

SYS>l
  1  select owner, segment_name, tablespace_name, extents, bytes/1048576
  2  from dba_segments
  3* where segment_name = 'ANOTHER_LARGE_TABLE'
SYS>
SYS>/

OWNER        SEGMENT_NAME           TABLESPACE_NAME    EXTENTS BYTES/1048576
------------ ---------------------- --------------- ---------- -------------
HEMANT       ANOTHER_LARGE_TABLE    HEMANT                 281     1073.9375

SYS>

We see that the inserted rows took another 139 extents and did NOT reuse any of the existing blocks even though they were all candidates for new rows.

This is something you must be extremely careful about !!  A Parallel Insert will "grow" the table by allocating new extents, ignoring all usable blocks in the table.  The only exception is if you have TRUNCATEd the target table.

HEMANT>truncate table another_large_table reuse storage;

Table truncated.

HEMANT>insert /*+ PARALLEL */
  2  into another_large_table
  3  select /*+ PARALLEL */ *
  4  from large_table;

4802944 rows created.

HEMANT>

SYS>select s.username, s.sql_id, t.used_ublk
  2  from v$session s, v$transaction t
  3  where s.taddr=t.addr
  4  /

USERNAME                       SQL_ID         USED_UBLK
------------------------------ ------------- ----------
HEMANT                         8g72bx3jy79gy          1

SYS>select sql_fulltext                      
  2  from v$sqlstats
  3  where sql_id = '8g72bx3jy79gy';

SQL_FULLTEXT
--------------------------------------------------------------------------------
insert /*+ PARALLEL */
into another_large_table
select /*+ PARALLEL */ *
from la


SYS>

Note how the 4.8million row Insert used only 1 Undo Block.

HEMANT>commit;

Commit complete.

HEMANT>

SYS>select s.username, s.sql_id, t.used_ublk
  2  from v$session s, v$transaction t
  3  where s.taddr=t.addr
  4  /

no rows selected

SYS>
SYS>select owner, segment_name, tablespace_name, extents, bytes/1048576
  2  from dba_segments
  3  where segment_name = 'ANOTHER_LARGE_TABLE'
  4  /

OWNER        SEGMENT_NAME           TABLESPACE_NAME    EXTENTS BYTES/1048576
------------ ---------------------- --------------- ---------- -------------
HEMANT       ANOTHER_LARGE_TABLE    HEMANT                 140           537

SYS>

The TRUNCATE allowed the next Parallel Insert to reuse the extents.

.
.
.