Home > Oracle > A small change in parallel insert with serial data access between 11.1 and 11.2

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.

About these ads
  1. June 22, 2012 at 7:50 pm | #1

    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]

    • June 22, 2012 at 8:32 pm | #2

      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)

      --------------------------------------------------------------------------
      | 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        | T2       |  Q1,01 | PCWP |            |
      |   4 |     BUFFER SORT          |          |  Q1,01 | PCWC |            |
      |   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
      |   6 |       PX SEND ROUND-ROBIN| :TQ10000 |        | S->P | RND-ROBIN  |
      |   7 |        INDEX RANGE SCAN  | IDX2     |        |      |            |
      --------------------------------------------------------------------------
      

      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 = :1
      
      • June 22, 2012 at 9:08 pm | #3

        Yep, 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]

    • June 23, 2012 at 5:14 am | #4

      There’s a bug which is fixed in 10.2.0.5 and 11.2

      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.

  2. June 25, 2012 at 4:23 pm | #5

    Tanel Poder :

    Yep, I played around with this test case a bit and also noticed that a join between two local tables produced a BUFFER SORT too.

    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.

    SQL> create table t1 as select * from all_objects;
     
    Table created
    
    SQL> create table t2 as select * from all_objects;
     
    Table created
    

    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")
    
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: