Note : This test is in a 19c database
In my previous post, I have demonstrated how a Parallel or Direct Path INSERT blocks a Conventional INSERT.
Does a Conventional INSERT block a Parallel or Direct Path INSERT ? OR is there a different effect on the session attempting the Parallel or Direct Path ?
Taking the same target table, this is the first session, with a Conventional INSERT :
22:30:18 SQL> insert into target_objects_list 22:30:20 2 select * from dba_objects 22:30:32 3 where rownum lessthan 2 -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML 22:30:43 4 / 1 row created. 22:30:44 SQL>
With the Conventional INSERT not having issued a COMMIT yet, the second session attempts a Parallel INSERT
22:32:14 SQL> alter session enable parallel dml; Session altered. 22:32:20 SQL> insert /*+ PARALLEL (t 2) */ into target_objects_list t 22:32:30 2 select /*+ PARALLEL (s 2) */ * from objects_list s 22:32:37 3 /
With the second session waiting, let's check what the wait is
WAITING_SESSION ------------------------------------------------------------------------------------------------------------------------------------ LOCK_TYPE MODE_REQUE MODE_HELD LOCK_ID1 LOCK_ID2 ----------------- ---------- ---------- ---------- ---------- 325 None w*82 DML Exclusive Row-X (SX) 78449 0 2 rows selected. SQL> select sql_text from v$sql where sql_id = 2 (select sql_id from v$session 3 where sid=82) 4 / SQL_TEXT ------------------------------------------------------------------------------------------------------------------------------------ insert /*+ PARALLEL (t 2) */ into target_objects_list t select /*+ PARALLEL (s 2) */ * from objects_list s 1 row selected. SQL>
Note how the MODE_REQUESTED and MODE_HELD are different from the previous blog post.
What happens when the Conventional INSERT issues a COMMIT ?
22:30:18 SQL> insert into target_objects_list 22:30:20 2 select * from dba_objects 22:30:32 3 where rownum lessthan 2 -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML 22:30:43 4 / 1 row created. 22:30:44 SQL> 22:35:54 SQL> 22:35:55 SQL> commit; Commit complete. 22:35:56 SQL>
And here is the second session now :
22:32:20 SQL> insert /*+ PARALLEL (t 2) */ into target_objects_list t
22:32:30 2 select /*+ PARALLEL (s 2) */ * from objects_list s
22:32:37 3 /
289641 rows created.
22:36:00 SQL>
22:36:50 SQL> select count(*) from target_objects_list;
select count(*) from target_objects_list
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
22:36:58 SQL> commit;
Commit complete.
22:37:13 SQL> select count(*) from target_objects_list;
COUNT(*)
----------
289642
22:37:20 SQL>
The Parallel INSERT succeeded after the COMMIT by the Conventional INSERT.
If you compare the MODE_REQUESTED and MODE_HELD (from dba_locks) in the two blog posts, the Parallel, Direct Path INSERT requests an Exclusive Lock.
FYI, LOCK_ID1=78449 is :
Here's an experiment you can try :
SQL> l
1 select object_id, object_name
2 from dba_objects
3* where object_id = 78449
SQL> /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
78449 TARGET_OBJECTS_LIST
1 row selected.
SQL>
Here's an experiment you can try :
What if the target is not a table but a Partition of a Table ?
What if the target is an explicitly named Partition ?
2 comments:
insert /*+ APPEND PARALLEL (t 2) */ into target_objects_list t
select /*+ PARALLEL (s 2) */ * from objects_list s
/
Would that be faster with an APPEND and also change the Table LOGGING to NOLOGGING during insert and revert back after insert finishes.
Assuming FORCE_LOGGING is not TRUE at DB or TABLESPACE Level.
Mukhtar,
In order for the INSERT to be executed in Parallel, you also need an "ALTER SESSION ENABLE PARALLEL DML" to be issued first. (The SELECT will executed as a Parallel Select).
A Parallel Insert is always implicitly executed as an APPEND, so the APPEND is unnecessary (as long as you ENABLE PARALLEL DML).
The operation will NOT "change the Table LOGGING to NOLOGGING" but the advantage is that an APPEND / PARALLEL Insert generates minimal Undo. That is, Redo will be generated for the INSERT but with minimal Undo, there is no Redo for the Undo. You will have to watch out for Redo on the Indexes that also need to be updated.
So, if you want the Parallel Insert to be with reduced Redo, you need to explicitly ALTER TABLE .. NOLOGGING first.
Both the APPEND and PARALLEL will cause the Table to be locked from any concurrent DML being executed.
Parallel Operations are available in the Enterprise Edition, not the Standard Edition / SE-2.
If you do have FORCE LOGGING enabled, Redo Generation will happen, irrespective of the Logging attribute at the Table level.
Post a Comment