Home > Oracle, Performance > APPEND and PARALLEL

APPEND and PARALLEL


APPEND hint in oracle is very useful, there are many benefits

Fragmented data loading – data will clustered above the HWM so that similar data will be together. Otherwise Oracle will place the records in blocks using PCT_FREE rule

  • No undo will be generated – in large direct path insert will free up large amount of data blocks
  • Less CBC latches (because of no UNDO blocks), less CPU usage
  • Readers need not undo the records, consistent reads are less costly – just read below HWM
  • Rollback will be faster – Oracle will just discard all the blocks above the HWM and it is instant.
  • Less REDO as UNDO blocks are not generated.  In case of NOARCHIVELOG mode, very limited REDO, only to manage the internal operations.  Both instance recovery and media recovery is out of scope from REDO.
  • No UNDO  – no infamous ORA-1555 Snapshoot too old error!

 

To achieve direct path loading- we typically use /*+ APPEND */ clause and that will not generate UNDO.

SQL> insert /*+ APPEND */ into t1 select * from t;
1803136 rows created.
Elapsed: 00:00:03.09

SQL> set linesize 180
SQL> col status for a8
SQL> col undousage for 999999999999.99
SQL>  SELECT b.INST_ID,a.sid, a.username, a.status,b.xidusn, b.used_urec, b.used_ublk,flag,sql_id,b.used_ublk*8/1024/1024 undousage
 FROM gv$session a, gv$transaction b
 WHERE a.saddr(+) = b.ses_addr and a.INST_ID(+) = b.INST_ID ;
   INST_ID        SID USERNAME                       STATUS       XIDUSN  USED_UREC  USED_UBLK       FLAG SQL_ID               UNDOUSAGE
---------- ---------- ------------------------------ -------- ---------- ---------- ---------- ---------- ------------- ----------------
         2       2299 TEST                           INACTIVE         15          1          1   16782851 djzyaz5uj6vnr              .00

We just got one UNDO  record which may be used for internal operation including extent management.  While a traditional load with same data generated 43599 undo records in 950 blocks which is 7600KB in size.

<pre>SQL> insert into t1 select * from t;
1803136 rows created.
Elapsed: 00:00:03.87

SQL> set linesize 180
SQL> col status for a8
SQL> col undousage for 999999999999.99
SQL>  SELECT b.INST_ID,a.sid, a.username, a.status,b.xidusn, b.used_urec, b.used_ublk,flag,sql_id,b.used_ublk*8/1024/1024 undousage
  2    FROM gv$session a, gv$transaction b
  3    WHERE a.saddr(+) = b.ses_addr and a.INST_ID(+) = b.INST_ID ;
   INST_ID        SID USERNAME                       STATUS       XIDUSN  USED_UREC  USED_UBLK       FLAG SQL_ID               UNDOUSAGE
---------- ---------- ------------------------------ -------- ---------- ---------- ---------- ---------- ------------- ----------------
         2       2299 TEST                           INACTIVE         12      43599        950       7683 9c7qck66f77c0              .01

 

Using a direct path load is always useful.  Oracle did a wonderful thing and is documented it says – When you use PARALLEL insert it will be direct path and to enable traditional load, you should explicitly use the hint NOAPPEND.

 

https://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF50101

 

Well, may be true and it is false as well.  I am not sure what meant by DIRECT path load here.   All the above mentioned benefits of APPEND are getting while using PARALLEL hint? I thought it was true, till I test it.  It may be true that Oracle is placing all the blocks above the HWM, but is it reducing the UNDO.   One of the beauty of APPEND is reducing UNDO and if that is not achieved, a direct path load is less beneficial.

I ran the above test again in parallel –

<pre>SQL>  insert /*+ parallel (t1,5) */ into t1 select * from t;
1803136 rows created.
Elapsed: 00:00:02.75

SQL> set linesize 180
SQL> col status for a8
SQL> col undousage for 999999999999.99
SQL>  SELECT b.INST_ID,a.sid, a.username, a.status,b.xidusn, b.used_urec, b.used_ublk,flag,sql_id,b.used_ublk*8/1024/1024 undousage
  2    FROM gv$session a, gv$transaction b
  3    WHERE a.saddr(+) = b.ses_addr and a.INST_ID(+) = b.INST_ID ;
   INST_ID        SID USERNAME                       STATUS       XIDUSN  USED_UREC  USED_UBLK       FLAG SQL_ID               UNDOUSAGE
---------- ---------- ------------------------------ -------- ---------- ---------- ---------- ---------- ------------- ----------------
         2       2299 TEST                           INACTIVE         12      43743        952       7683 29hu6nmj0g66w              .01

 

To my surprise Oracle generated more UNDO records and blocks in a single thread.    So, Oracle is saying PARALLEL is APPENDing, but not all APPEND features are available in PARALLEL load.  Many times in the past I took a decision to use PARALLEL thinking it is real APPEND.   I re-tried the insert with both PARALLEL and APPEND – and it works as expected – but the it took more time to complete.

<pre>QL> insert /*+ parallel (t1,5) APPEND */ into t1 select * from t;
1803136 rows created.
Elapsed: 00:00:05.94

SQL> set linesize 180
SQL> col status for a8
SQL> col undousage for 999999999999.99
SQL>  SELECT b.INST_ID,a.sid, a.username, a.status,b.xidusn, b.used_urec, b.used_ublk,flag,sql_id,b.used_ublk*8/1024/1024 undousage
  2    FROM gv$session a, gv$transaction b
  3    WHERE a.saddr(+) = b.ses_addr and a.INST_ID(+) = b.INST_ID ;
   INST_ID        SID USERNAME                       STATUS       XIDUSN  USED_UREC  USED_UBLK       FLAG SQL_ID               UNDOUSAGE
---------- ---------- ------------------------------ -------- ---------- ---------- ---------- ---------- ------------- ----------------
         2       2299 TEST                           INACTIVE         15          1          1   16782851 avnd4v97v389t              .00

Well, it is just generated 1 UNDO block confirming my assumption.

Advertisements
  1. sampath
    November 21, 2014 at 7:34 am

    nice investigation thomas

  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

%d bloggers like this: