title: How to calculate the optimal Sequence Cache size for a multithreaded application inserting a large amount of data?
body: <p>I have a multi-threaded application that uses 10 threads, each of which on average result in an insert of 40K rows into a table. These inserts will occur 24/7 around the clock with no pauses.</p>
  <p>I ran some performance tests and noted the following:</p>
  <ul>
  <li>With CACHE 20 and single threaded, each insert took about 3.5 seconds on average</li>
  <li>With CACHE 20 and 10 threads, each insert took 100 seconds on average</li>
  <li>After removing the primary key and the sequence, each insert, regardless of the number of threads used, took 3.1 seconds.</li>
  <li>With CACHE 400000 and 10 threads, each insert took 5.6 seconds on average. (Incidentally, the average originally was 8, then dropped down to 5.6 over time)</li>
  </ul>
  <p>I'm performing an INSERT like this:</p>
  <pre>
  INSERT INTO foo (id, bar, baz)
  SELECT (foo_id_seq.nextval, bar, baz)
  FROM (
      SELECT bar, baz
      FROM ...
  )
  </pre>
  <hr />
  <p>Given my constraints of 10 threads processing 40K records each on average, how can I calculate the optimal cache size of a sequence?</p>
  <p>I'm tempted to set the cache size = (10 threads * 40K records) == 400,000, but I would be worried about any trade offs that I haven't read about in the docs.</p>
  <p>Moreover, the insert with 400K cache size is still 100% worse than the insert with no sequence/pk. Granted this is an acceptable time.</p>
  <hr />
  <p>The <a href="https://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm#ADMIN11794" rel="nofollow noreferrer">docs</a> say:</p>
  <blockquote>
  <p>The CACHE clause preallocates a set of sequence numbers and keeps them in memory so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used, the database reads another set of numbers into the cache.</p>
  <p>Sequence numbers can be kept in the sequence cache in the System Global Area (SGA). Sequence numbers can be accessed more quickly in the sequence cache than they can be read from disk.</p>
  <p>Follow these guidelines for fast access to all sequence numbers: Be sure the sequence cache can hold all the sequences used concurrently by your applications. Increase the number of values for each sequence held in the sequence cache.</p>
  </blockquote>