title: INSERT of 10 million queries under 10 minutes in Oracle?
body: <p>I am working on a file loader program.</p>
  
  <p>The purpose of this program is to take an input file, do some conversions on its data and then upload the data into the database of Oracle.</p>
  
  <p>The problem that I am facing is that I need to optimize the insertion of very large input data on Oracle.</p>
  
  <p>I am uploading data into the table, lets say ABC.</p>
  
  <p>I am using the OCI library provided by Oracle in my C++ Program.
  In specific, I am using OCI Connection Pool for multi-threading and loading into ORACLE. (<a href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci09adv.htm" rel="noreferrer">http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci09adv.htm</a> )</p>
  
  <p>The following are the DDL statements that have been used to create the table ABC –</p>
  
  <pre><code>CREATE TABLE ABC(
     seq_no         NUMBER NOT NULL,
     ssm_id         VARCHAR2(9)  NOT NULL,
     invocation_id  VARCHAR2(100)  NOT NULL,
     analytic_id    VARCHAR2(100) NOT NULL,
     analytic_value NUMBER NOT NULL,
     override       VARCHAR2(1)  DEFAULT  'N'   NOT NULL,
     update_source  VARCHAR2(255) NOT NULL,
     last_chg_user  CHAR(10)  DEFAULT  USER NOT NULL,
     last_chg_date  TIMESTAMP(3) DEFAULT  SYSTIMESTAMP NOT NULL
  );
  
  CREATE UNIQUE INDEX ABC_indx ON ABC(seq_no, ssm_id, invocation_id, analytic_id);
  /
  CREATE SEQUENCE ABC_seq;
  /
  
  CREATE OR REPLACE TRIGGER ABC_insert
  BEFORE INSERT ON ABC
  FOR EACH ROW
  BEGIN
  SELECT ABC_seq.nextval INTO :new.seq_no FROM DUAL;
  END;
  </code></pre>
  
  <p>I am currently using the following Query pattern to upload the data into the database. I am sending data in batches of 500 queries via various threads of OCI connection pool.</p>
  
  <p>Sample of SQL insert query used - </p>
  
  <pre><code>insert into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
  override, update_source)
  select 'c','b',NULL, 'test', 123 , 'N', 'asdf' from dual
  union all select 'a','b',NULL, 'test', 123 , 'N', 'asdf' from dual
  union all select 'b','b',NULL, 'test', 123 , 'N', 'asdf' from dual
  union all select 'c','g',NULL, 'test', 123 , 'N', 'asdf' from dual
  </code></pre>
  
  <p>EXECUTION PLAN by Oracle for the above query -</p>
  
  <pre><code>-----------------------------------------------------------------------------
  | Id  | Operation                | Name|Rows| Cost (%CPU) | Time     |
  -----------------------------------------------------------------------------
  |   0 | INSERT STATEMENT         |     | 4  |     8   (0) | 00:00:01 |
  |   1 |  LOAD TABLE CONVENTIONAL | ABC |    |             |          |
  |   2 |   UNION-ALL              |     |    |             |          |
  |   3 |    FAST DUAL             |     | 1  |     2   (0) | 00:00:01 |
  |   4 |    FAST DUAL             |     | 1  |     2   (0) | 00:00:01 |
  |   5 |    FAST DUAL             |     | 1  |     2   (0) | 00:00:01 |
  |   6 |    FAST DUAL             |     | 1  |     2   (0) | 00:00:01 |
  </code></pre>
  
  <p>The Run times of the program loading 1 million lines - </p>
  
  <pre><code>Batch Size = 500
  Number of threads - Execution Time -
  10                  4:19
  20                  1:58
  30                  1:17
  40                  1:34
  45                  2:06
  50                  1:21
  60                  1:24
  70                  1:41
  80                  1:43
  90                  2:17
  100                 2:06
  
  
  Average Run Time = 1:57    (Roughly 2 minutes)
  </code></pre>
  
  <p>I need to optimize and reduce this time further. The problem that I am facing is when I put 10 million rows for uploading.</p>
  
  <p>The average run time for <strong>10 million</strong> came out to be  = <strong>21 minutes</strong></p>
  
  <p><strong>(My target is to reduce this time to below 10 minutes)</strong></p>
  
  <p>So I tried the following steps as well -</p>
  
  <p>[1]
  Did the partitioning of the table ABC on the basis of <strong>seq_no</strong>.
  Used <strong>30 partitions</strong>.
  Tested with <strong>1 million rows</strong> - The performance was very poor. almost <strong>4 times more than the unpartitioned table.</strong></p>
  
  <p>[2]
  Another partitioning of the table ABC on the basis of <strong>last_chg_date</strong>.
  Used <strong>30 partitions</strong>.</p>
  
  <p>2.a) Tested with 1 million rows - <strong>The performance was almost equal to the unpartitioned table.</strong> Very little difference was there so it was not considered.</p>
  
  <p>2.b) Again <strong>tested the same with 10 million rows. The performance was almost equal</strong> to the unpartitioned table. No noticable difference.</p>
  
  <p>The following was the DDL commands were used to achieve partitioning -</p>
  
  <pre><code>CREATE TABLESPACE ts1 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts2 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts3 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts4 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts5 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts6 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts7 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts8 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts9 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts10 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts11 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts12 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts13 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts14 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts15 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts16 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts17 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts18 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts19 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts20 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts21 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts22 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts23 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts24 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts25 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts26 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts27 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts28 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts29 DATAFILE AUTOEXTEND ON;
  CREATE TABLESPACE ts30 DATAFILE AUTOEXTEND ON;
  
  CREATE TABLE ABC(
     seq_no           NUMBER NOT NULL,
     ssm_id           VARCHAR2(9)  NOT NULL,
     invocation_id    VARCHAR2(100)  NOT NULL,
     calc_id          VARCHAR2(100) NULL,
     analytic_id      VARCHAR2(100) NOT NULL,
     ANALYTIC_VALUE   NUMBER NOT NULL,
     override         VARCHAR2(1)  DEFAULT  'N'   NOT NULL,
     update_source    VARCHAR2(255) NOT NULL,
     last_chg_user    CHAR(10)  DEFAULT  USER NOT NULL,
     last_chg_date    TIMESTAMP(3) DEFAULT  SYSTIMESTAMP NOT NULL
  )
  PARTITION BY HASH(last_chg_date)
  PARTITIONS 30
  STORE IN (ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8, ts9, ts10, ts11, ts12, ts13,
  ts14, ts15, ts16, ts17, ts18, ts19, ts20, ts21, ts22, ts23, ts24, ts25, ts26,
  ts27, ts28, ts29, ts30);
  </code></pre>
  
  <p>CODE that I am using in the thread function (written in C++), using OCI - </p>
  
  <pre><code>void OracleLoader::bulkInsertThread(std::vector&lt;std::string&gt; const &amp; statements)
  {
  
      try
      {
          INFO("ORACLE_LOADER_THREAD","Entered Thread = %1%", m_env);
          string useOraUsr = "some_user";
          string useOraPwd = "some_password";
  
          int user_name_len   = useOraUsr.length();
          int passwd_name_len = useOraPwd.length();
  
          text* username((text*)useOraUsr.c_str());
          text* password((text*)useOraPwd.c_str());
  
  
          if(! m_env)
          {
              CreateOraEnvAndConnect();
          }
          OCISvcCtx *m_svc = (OCISvcCtx *) 0;
          OCIStmt *m_stm = (OCIStmt *)0;
  
          checkerr(m_err,OCILogon2(m_env,
                                   m_err,
                                   &amp;m_svc,
                                   (CONST OraText *)username,
                                   user_name_len,
                                   (CONST OraText *)password,
                                   passwd_name_len,
                                   (CONST OraText *)poolName,
                                   poolNameLen,
                                   OCI_CPOOL));
  
          OCIHandleAlloc(m_env, (dvoid **)&amp;m_stm, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
  
  ////////// Execution Queries in the format of - /////////////////
  //        insert into pm_own.sec_analytics (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value, override, update_source)
  //        select 'c','b',NULL, 'test', 123 , 'N', 'asdf' from dual
  //        union all select 'a','b',NULL, 'test', 123 , 'N', 'asdf' from dual
  //        union all select 'b','b',NULL, 'test', 123 , 'N', 'asdf' from dual
  //        union all select 'c','g',NULL, 'test', 123 , 'N', 'asdf' from dual
  //////////////////////////////////////////////////////////////////
  
          size_t startOffset = 0;
          const int batch_size = PCSecAnalyticsContext::instance().getBatchCount();
          while (startOffset &lt; statements.size())
          {
              int remaining = (startOffset + batch_size &lt; statements.size() ) ? batch_size : (statements.size() - startOffset );
              // Break the query vector to meet the batch size
              std::vector&lt;std::string&gt; items(statements.begin() + startOffset,
                                             statements.begin() + startOffset + remaining);
  
              //! Preparing the Query
              std::string insert_query = "insert into ";
              insert_query += Context::instance().getUpdateTable();
              insert_query += " (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value, override, update_source)\n";
  
              std::vector&lt;std::string&gt;::const_iterator i3 = items.begin();
              insert_query += *i3 ;
  
              for( i3 = items.begin() + 1; i3 != items.end(); ++i3)
                  insert_query += "union " + *i3 ;
              // Preparing the Statement and Then Executing it in the next step
              text *txtQuery((text *)(insert_query).c_str());
              checkerr(m_err, OCIStmtPrepare (m_stm, m_err, txtQuery, strlen((char *)txtQuery), OCI_NTV_SYNTAX, OCI_DEFAULT));
              checkerr(m_err, OCIStmtExecute (m_svc, m_stm, m_err, (ub4)1, (ub4)0, (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT ));
  
              startOffset += batch_size;
          }
  
          // Here is the commit statement. I am committing at the end of each thread.
          checkerr(m_err, OCITransCommit(m_svc,m_err,(ub4)0));
  
          checkerr(m_err, OCIHandleFree((dvoid *) m_stm, OCI_HTYPE_STMT));
          checkerr(m_err, OCILogoff(m_svc, m_err));
  
          INFO("ORACLE_LOADER_THREAD","Thread Complete. Leaving Thread.");
      }
  
      catch(AnException &amp;ex)
      {
          ERROR("ORACLE_LOADER_THREAD", "Oracle query failed with : %1%", std::string(ex.what()));
          throw AnException(string("Oracle query failed with : ") + ex.what());
      }
  }
  </code></pre>
  
  <p>While the post was being answered, I was suggested several methods to optimize my <strong>INSERT QUERY</strong>.
  I have chosen and used <strong>QUERY I</strong> in my program for the following reasons that I discovered while testing the various INSERT Queries.
  On running the SQL Queries that were suggested to me - 
  <strong>QUERY I -</strong></p>
  
  <pre><code>insert into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
  override, update_source)
  select 'c','b',NULL, 'test', 123 , 'N', 'asdf' from dual
  union all select 'a','b',NULL, 'test', 123 , 'N', 'asdf' from dual
  union all select 'b','b',NULL, 'test', 123 , 'N', 'asdf' from dual
  union all select 'c','g',NULL, 'test', 123 , 'N', 'asdf' from dual
  </code></pre>
  
  <p><strong>EXECUTION PLAN by Oracle for Query I -</strong></p>
  
  <pre><code>--------------------------------------------------------------------------
  | Id  | Operation                | Name| Rows | Cost (%CPU)   | Time     |
  --------------------------------------------------------------------------
  |   0 | INSERT STATEMENT         |     |  4   | 8   (0)       | 00:00:01 |
  |   1 |  LOAD TABLE CONVENTIONAL | ABC |      |               |          |
  |   2 |   UNION-ALL              |     |      |               |          |
  |   3 |    FAST DUAL             |     |  1   | 2   (0)       | 00:00:01 |
  |   4 |    FAST DUAL             |     |  1   | 2   (0)       | 00:00:01 |
  |   5 |    FAST DUAL             |     |  1   | 2   (0)       | 00:00:01 |
  |   6 |    FAST DUAL             |     |  1   | 2   (0)       | 00:00:01 |
  </code></pre>
  
  <p><strong>QUERY II -</strong></p>
  
  <pre><code>insert all
  into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
  override, update_source) values ('c','b',NULL, 'test', 123 , 'N', 'asdf')
  into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
  override, update_source) values ('c','e',NULL, 'test', 123 , 'N', 'asdf')
  into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
  override, update_source) values ('c','r',NULL, 'test', 123 , 'N', 'asdf')
  into ABC (SSM_ID, invocation_id , calc_id, analytic_id, analytic_value,
  override, update_source) values ('c','t',NULL, 'test', 123 , 'N', 'asdf')
  select 1 from dual
  </code></pre>
  
  <p><strong>EXECUTION PLAN by Oracle for Query II -</strong></p>
  
  <pre><code>-----------------------------------------------------------------------------
  | Id  | Operation           | Name| Rows  | Cost (%CPU)   | Time     |
  -----------------------------------------------------------------------------
  |   0 | INSERT STATEMENT    |     | 1     |     2   (0)   | 00:00:01 |
  |   1 |  MULTI-TABLE INSERT |     |       |               |          |
  |   2 |   FAST DUAL         |     | 1     |     2   (0)   | 00:00:01 |
  |   3 |   INTO              | ABC |       |               |          |
  |   4 |   INTO              | ABC |       |               |          |
  |   5 |   INTO              | ABC |       |               |          |
  |   6 |   INTO              | ABC |       |               |          |
  </code></pre>
  
  <p>As per the experiments the <strong>Query I is faster</strong>.</p>
  
  <p>Here I tested on both Oracle SQL Developer , as well as I sent insert queries by my C++ program (FILELOADER) as well.</p>
  
  <p>On Further reading about it, I found out that the cost shown by the Execution Plan is the number of CPU the query will use to process itself.
  <strong>That tells that Oracle will use more CPU to process the first query and that is why its cost goes on to be = 8.</strong></p>
  
  <p>Even by using the same insert pattern via my application, I found out that its performance it almost 1.5 times better.</p>
  
  <p>I need some insight on how I can improve the performance even further..?
  All the things that I have tried, I have summarized them in my question.
  If I find or discover anything relevant, I will add to this question.</p>
  
  <p>My target in to bring the <strong>upload time of 10 million queries under 10 minutes</strong>.</p>