Oracle Backfill Performance

By | In Blog, Oracle | May 05th, 2011

Most of us have had the need to backfill some data at one point or another. Perhaps we needed to create some data to test out a new application we’re building, or we wanted to test a new Oracle feature. Backfilling can be as easy as writing some INSERT and UPDATE commands, or coding up a simple PL/SQL script. It probably doesn’t matter how efficient your SQL is when you’re dealing with 10’s of rows or even 10’s of thousands of rows, but what happens when you start dealing with millions of rows? At Datavail we follow three main principles when backfilling:

  1. Design to Handle Failure
  2. Operate on Manageable Chunks
  3. Use Bulk Operations

Design to Handle Failure

When dealing with large volumes of data, backfills can consume a fair amount of resources and can often take an extended period of time to execute. The last thing we want to happen is for the backfill to be 99% complete and then run into an error that causes the whole operation to be rolled back. Instead, we should design our backfill in such a way that we expect it to fail before it completes. In doing so, we can then just start the backfill up again and it should resume where it left off.

Operate on Manageable Chunks

Operating on manageable chunks of data can help us in our quest for failure tolerant backfills. However, it’s not all about resuming the backfill after a failure. Ok, so we’re not going to wait until the very end and issue one commit. On the other hand, we don’t want to issue a commit for every row we are backfilling. That would slow us down and potentially impact other users on our database. So what is a “manageable chunk” then? We usually pick a starting point of around one thousand rows and go up from there as needed or as testing allows. That’s three orders of magnitude up from backfilling one row at a time, which will buy us some nice performance increases.

Use Bulk Operations

Using bulk operations is another principle that works hand-in-hand with the other two. Oracle has some useful features in PL/SQL that let you operate on things one row at a time and then save your work in bulk. Oracle also gives us the ability to SELECT or FETCH rows in bulk as well. This reduces the number of executions and round-trips between our backfill code and the database.

Example INSERTS

Lets work through an example where we want to INSERT a million rows into a table. We’ll see how long each backfill takes and examine the 10046 trace output to determine where our time is being spent. Lets use the following table definition:

Our first attempt might look something like the following, where we’re doing a simple loop, inserting and committing every row.

Incorporating our principles of Designing to Handle Failure and Operating on Manageable Chunks, we might modify our PL/SQL script to the following:

If we further enhance this by Using Bulk Operations, we get the following:

Looking at the tkprof output from the 10046 traces on each PL/SQL block above, we see the following results for our 1 million row inserts. We should note that the act of tracing the backfill slowed down the backfill in many cases, so I used the Elapsed time from multiple executions that were not traced and determined the percentages from the trace files.

Example UPDATES

Lets take the same 1 million rows that we just inserted and run an UPDATE backfill on them. Again, we’ll wee how long each backfill takes and examine the 10046 trace output to see where our time is being spent. Our starting backfill will update all rows in one transaction.

We can refine this a bit by issuing a commit every 1000 rows. We can see that this takes a little longer, but we’ve Designed to Handle Failure with this new PL/SQL and we’ve started to Operate on Manageable Chunks.

In this next backfill, we’ve modified the script to use Use Bulk Operations for both our SELECT/FETCH and our UPDATE statements.

Well, that wasn’t much faster than the previous example that looped through and updated one row at a time. However, if we change the size of the chunks we’re working on from 1000 to 10000, we see an improvement. The elapsed time with “LIMIT 10000” was: Elapsed: 00:01:36.99, which is fairly close to our original, plain SQL UPDATE statement. For comparison, I also wanted to trace a Create Table As Select (CTAS). One should note that it is not always feasible to recreate the table instead of updating rows. This requires a downtime where no applications are modifying data and that may not be possible on a production system. Not only is CTAS intrusive, but I’m also not taking into account the time required to recreate indexes and constraints on the new table. However, we see that it can speed up the process quite a bit.

Here are the results from the 10046 traces on the update backfills. Again, we note that the act of tracing the backfill slowed down the backfill in many cases, so I used the Elapsed time from multiple executions that were not traced and determined the percentages from the trace files.

 

Contact Us

Leave a Reply

Your email address will not be published.
Required fields are marked (*).