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.
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 ?
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.
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.
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.
Note how the 4.8million row Insert used only 1 Undo Block.
The TRUNCATE allowed the next Parallel Insert to reuse the extents.
.
.
.
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:
Post a Comment