Comapring Oracle Insert Performance Using append Parallel Hints

Comapring Oracle Insert Performance Using append Parallel Hints

Conclusion:
INSERT /*+ append PARALLEL(8) */ is faster by 20s outof 123s or 18.6%
SELECT /*+ PARALLEL(8) */ is faster by 22s out of 201s or 10.9%

Insert Test Background:
NDA File Set
f173NDAnor000.txt is 1.18 GB (1,275,150,301 bytes)

Insert Test Preparation:
-- drop tables 
BEGIN 
    Droptableifexist_proc('DITTOUSER.tblf173NDAnor000'); 
    Droptableifexist_proc('DITTOUSER.extf173NDAnor000'); 
END; 
/ 
-- create normal table 
CREATE TABLE dittouser.extf173ndanor000 
  ( 
     data XMLTYPE 
  ) xmltype data store AS securefile binary xml nologging; 

Insert Test Trials:
1. With Append Parallel 8
INSERT /*+ append PARALLEL(8) */ INTO dittouser.extf173ndanor000 
VALUES      (Xmltype(Bfilename('FULLBUILDDIR', 'f173NDAnor000.txt'), 
             Nls_charset_id('UTF8'))); 
1 rows inserted.
Elapsed: 00:01:41.013

2. With Append

INSERT /*+ append */ INTO dittouser.extf173ndanor000 
VALUES      (Xmltype(Bfilename('FULLBUILDDIR', 'f173NDAnor000.txt'), 
             Nls_charset_id('UTF8'))); 

1 rows inserted.

Elapsed: 00:01:40.281

2. With Parallel 8

INSERT /*+ PARALLEL(8) */ INTO dittouser.extf173ndanor000 
VALUES      (Xmltype(Bfilename('FULLBUILDDIR', 'f173NDAnor000.txt'), 
             Nls_charset_id('UTF8'))); 

1 rows inserted.

Elapsed: 00:01:39.112

4. Nothing

INSERT INTO dittouser.extf173ndanor000 
VALUES      (Xmltype(Bfilename('FULLBUILDDIR', 'f173NDAnor000.txt'), 
             Nls_charset_id('UTF8'))); 

1 rows inserted.

Elapsed: 00:02:03.759

Loading Speed is 1,275,150,301 bytes / 122.972 s = 10369436 Bytes/s = 9.89MB/s

SelectTest Background:

-- drop tables
BEGIN
    Droptableifexist_proc('DITTOUSER.tblf173NDAnor000');
END;
/

Select Test Trials:

1. With Parallel 8

CREATE TABLE dittouser.tblf173ndanor000
nologging AS
  (SELECT /*+ PARALLEL(8) */ domain,
                             name,
                             nametype,
                             serviceid,
                             qos,
                             pe,
                             fieldlistnumber,
                             source,
                             fields
   FROM   dittouser.extf173ndanor000,
          XMLTABLE('for $i in /Instruments/Instrument return $i' passing
  dittouser.extf173ndanor000.data COLUMNS name VARCHAR2(32) path 'Name', domain
  NUMBER(6) path 'Domain/@value', nametype NUMBER(3) path 'NameType/@value',
  serviceid NUMBER(5) path 'ServiceID/@value', qos NUMBER(3) path 'QoS/@value',
  pe NUMBER(6) path 'PE/@value', fieldlistnumber NUMBER(6) path
  'FieldListNumber/@value', source VARCHAR2(16) path 'Source', fields xmltype
  path 'FieldList/Field' )); 

table DITTOUSER.TBLF173NDANOR000 created.

Elapsed: 00:02:59.434

2. Nothing

CREATE TABLE dittouser.tblf173ndanor000
nologging AS
  (SELECT                    domain,
                             name,
                             nametype,
                             serviceid,
                             qos,
                             pe,
                             fieldlistnumber,
                             source,
                             fields
   FROM   dittouser.extf173ndanor000,
          XMLTABLE('for $i in /Instruments/Instrument return $i' passing
  dittouser.extf173ndanor000.data COLUMNS name VARCHAR2(32) path 'Name', domain
  NUMBER(6) path 'Domain/@value', nametype NUMBER(3) path 'NameType/@value',
  serviceid NUMBER(5) path 'ServiceID/@value', qos NUMBER(3) path 'QoS/@value',
  pe NUMBER(6) path 'PE/@value', fieldlistnumber NUMBER(6) path
  'FieldListNumber/@value', source VARCHAR2(16) path 'Source', fields xmltype
  path 'FieldList/Field' )); 

table DITTOUSER.TBLF173NDANOR000 created.

Elapsed: 00:03:21.970