A small change in parallel insert with serial data access between 11.1 and 11.2
There is an interesting small change in parallel insert between 11.1 and 11.2 in queries with parallelized insert, but serialized data access. On a practice the simplest case where we can see this it is CTAS or parallel insert as select from remote table.
As an example I will use CTAS from remote table.
create table tbllocal parallel 2
as select *
from tblremote@dblink;
Note: even if remote part of this query (select from tblremote@dblink) will be parallelized, it will be run in parallel on remote side. Communication between remote and local servers will be performed in one thread: local query coordinator – remote query coordinator. So, in our case it does not matter if query part parallelized or not.
11.1.0.7
SQL> select * from v$version where rownum = 1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production SQL> show parameter optimizer_features_enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_features_enable string 11.1.0.7 SQL> explain plan for 2 create table tbllocal parallel 2 3 as select * from tblremote@dblink; SQL> @plan ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ/Ins |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | 41 | 82 | 3 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 41 | 82 | 2 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) | | 3 | LOAD AS SELECT | TBLLOCAL | | | | | Q1,01 | PCWP | | | 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | | | 5 | PX RECEIVE | | 41 | 82 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | | 6 | PX SEND ROUND-ROBIN| :TQ10000 | 41 | 82 | 2 (0)| 00:00:01 | | S->P | RND-ROBIN | | 7 | REMOTE | TBLREMOTE | 41 | 82 | 2 (0)| 00:00:01 | DBLINK | R->S | | ------------------------------------------------------------------------------------------------------------------- Remote SQL Information (identified by operation id): ---------------------------------------------------- 7 - SELECT "DUMMY" FROM "TBLREMOTE" "TBLREMOTE" (accessing 'DBLINK.WORLD' )
This execution plan means (a little bit simlified):
Query Coordinator:
[step 7] get result from remote side,
[step 6] put data into special structures in SGA
Slave process:
[step 5] gets data from SGA
[step 4] put the data in workarea (PGA)
[step 3] performes direct-path insert
Pay attention on the step 4 – BUFFER SORT.
BUFFER SORT it is buffering technic using sort area size, without actual sorting.
Note: additional details: Jonathan Lewis – buffer-sorts
The problem here is that BUFFER SORT should get ALL data from child rowsource before parent operation will be performed. Before inserts will be started all data from remote side will be read and put into workareas of slave processes. As the result if you are going to carry out CTAS from huge remote table you need the same huge amount of memory (sort area size) and if you do not have enough memory the data will be spilled in temp.
As a result there is probability to get many “direct-path read/write temp” wait events and even “ORA-01652: unable to extend temp segment .. in tablespace TEMP” before the first row will be inserted.
11.2.0.2
Fortunately Oracle 11.2 does not have this step in execution plan.
Note: Oracle 11.2 does not have this step in execution plan even optimizer_features_enable = 11.1.0.7, 10.2.0.5, and adds this step when optimizer_features_enable = 10.2.0.4
SQL> select * from v$version where rownum = 1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production SQL> show parameter optimizer_features_enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_features_enable string 11.2.0.2 SQL> explain plan for 2 create table tbllocal parallel 2 3 as select * from tblremote@dblink; Explained SQL> @plan ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ/Ins |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------ | 0 | CREATE TABLE STATEMENT | | 41 | 82 | 3 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 41 | 82 | 2 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) | | 3 | LOAD AS SELECT | TBLLOCAL | | | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 41 | 82 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | | 5 | PX SEND ROUND-ROBIN| :TQ10000 | 41 | 82 | 2 (0)| 00:00:01 | | S->P | RND-ROBIN | | 6 | REMOTE | TBLREMOTE | 41 | 82 | 2 (0)| 00:00:01 | DBLINK | R->S | | ------------------------------------------------------------------------------------------------------------------ Remote SQL Information (identified by operation id): ---------------------------------------------------- 6 - SELECT "DUMMY" FROM "TBLREMOTE" "TBLREMOTE" (accessing 'DBLINK.WORLD' )
Here slave process will inserts data immediately after get a portion from query coordinator.
Thus, here you do not need huge amount of memory and temp space to perform CTAS from huge remote table.
Alexander Anokhin 
Hi Alexander,
There’s a bug which is fixed in 10.2.0.5 and 11.2:
Bug 7708267 – Extra BUFFER SORT row source allocated for parallel insert from remote table [ID 7708267.8]
Hi Tanel,
Thanks, looks like it is exaclty this bug.
Unfortunately the description of the bug is not accurate enough. It can be not only remote table, but also something like that (10.2.0.5 on Solaris)
Here datasource is local.
upd:
this execution plan for a query
create table t parallel 2 select indexed_column1, indexed_column2, ... from t where indexed_column1 = :1Yep, I played around with this test case a bit and also noticed that a join between two local tables produced a BUFFER SORT too.
Note that there’s this one too:
Bug 8251486 – Unnecessary BUFFER SORT for PDML Insert as SELECT with sequence [ID 8251486.8]
This issue is fixed in
11.2.0.1 (Base Release),
10.2.0.5 Patch 2 on Windows Platforms
My 10.2.0.5 on Solaris still has this bug.
Yes, in some cases BUFFER SORT is involved in parallel joins.
Several examples are below. There is no difference between 10.2 and 11.2 for these execution plans.
parallel hash join
select --+ parallel(t1) parallel(t2) t1.owner, t2.object_name from t1, t2 where t1.object_id = t2.object_id; -------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN | | Q1,01 | PCWP | | | 4 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | | 5 | TABLE ACCESS FULL | T1 | Q1,01 | PCWP | | | 6 | BUFFER SORT | | Q1,01 | PCWC | | | 7 | PX RECEIVE | | Q1,01 | PCWP | | | 8 | PX SEND BROADCAST | :TQ10000 | Q1,00 | P->P | BROADCAST | | 9 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") -------------------------------------------------------------------------serial CTAS with parallel data access
create table t as select --+ parallel(t1) parallel(t2) t1.owner, t2.object_name from t1, t2 where t1.object_id = t2.object_id; --------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | | | | | 1 | LOAD AS SELECT | T | | | | | 2 | PX COORDINATOR | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | |* 4 | HASH JOIN | | Q1,01 | PCWP | | | 5 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | | 6 | TABLE ACCESS FULL | T1 | Q1,01 | PCWP | | | 7 | BUFFER SORT | | Q1,01 | PCWC | | | 8 | PX RECEIVE | | Q1,01 | PCWP | | | 9 | PX SEND BROADCAST | :TQ10000 | Q1,00 | P->P | BROADCAST | | 10 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 11 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") -------------------------------------------------------------------------parallel CTAS with parallel data access
create table t parallel 2 as select --+ parallel(t1) parallel(t2) t1.owner, t2.object_name from t1, t2 where t1.object_id = t2.object_id; -------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | | 3 | LOAD AS SELECT | T | Q1,01 | PCWP | | |* 4 | HASH JOIN | | Q1,01 | PCWP | | | 5 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | | 6 | TABLE ACCESS FULL | T1 | Q1,01 | PCWP | | | 7 | PX RECEIVE | | Q1,01 | PCWP | | | 8 | PX SEND BROADCAST | :TQ10000 | Q1,00 | P->P | BROADCAST | | 9 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") -------------------------------------------------------------------------HASH JOIN BUFFERED with three slavesets
select --+ parallel(t1) parallel(t2) PQ_DISTRIBUTE(@"SEL$1" "T1"@"SEL$1" HASH HASH) t1.owner, t2.object_name from t1, t2 where t1.object_id = t2.object_id; ------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | Q1,02 | PCWP | | | 4 | PX RECEIVE | | Q1,02 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | | 8 | PX RECEIVE | | Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | | 11 | TABLE ACCESS FULL| T1 | Q1,01 | PCWP | | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") -------------------------------------------------------------------------HASH JOIN BUFFERED with two slavesets, one table is read by QC
select --+ parallel(t1) noparallel(t2) PQ_DISTRIBUTE(@"SEL$1" "T1"@"SEL$1" HASH HASH) t1.owner, t2.object_name from t1, t2 where t1.object_id = t2.object_id; ------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | Q1,02 | PCWP | | | 4 | BUFFER SORT | | Q1,02 | PCWC | | | 5 | PX RECEIVE | | Q1,02 | PCWP | | | 6 | PX SEND HASH | :TQ10000 | | S->P | HASH | | 7 | TABLE ACCESS FULL| T2 | | | | | 8 | PX RECEIVE | | Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | | 11 | TABLE ACCESS FULL| T1 | Q1,01 | PCWP | | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")