01 May, 2015

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.

.
.
.

No comments: