20.1.1.1 Commit processing challenges

My customer, which I'll refer to as Bigdata Inc., needed to perform a complex data translation from one Oracle database instance to another. Approximately 20 million records in two tables were involved. It wasn't one of those all-or-nothing situations. If we could manage to get through a million records before some failure occurred, that was fine. All we had to do was come up with a mechanism for keeping track of which records had already been processed, so we didn't do them again. We used a "transfer indicator," which also led to a distributed transaction.

I've got to be honest with you: I have not spent many hours of my career (prior to Bigdata) working with this kind of volume of data. It sure puts a different spin on everything you do. When a simple SELECT could take hours to complete, you get very careful about the queries you execute. You no longer make casual statements (and take casual actions) like: "Let's just run this and see how it works. If it fails, we'll try it again." Three days later (or two weeks later), the job might crash from a "snapshot too old" error and you are back at square one -- if you didn't take precautions.

In fact, I quickly became intimate with a range of Oracle errors that earlier had been fairly academic to me: the -015NN series. Errors like:

ORA-01555 snapshot too old (rollback segment too small)
ORA-01562 failed to extend rollback segment

became a regular and unwelcome part of my life. Sure, we had big rollback segments, but one of our tables took up 2 gigabytes of storage in the database. We simply couldn't execute queries across the entire table at the same time that updates were taking place. I learned to "chunk down" by primary key ranges the rows I processed in each pass of my program. And I discovered the need to get flexible when it came to commits.

20.1.1.2 Committing every ? records

When I first started with the account, we agreed that committing every 10,000 records would be great. This is the kind of code we wrote to handle the situation:

commit_counter := 0
FOR original_rec IN original_cur
LOOP
   translate_data (original_rec);
   IF commit_counter >= 10000
   THEN
      COMMIT;
      commit_counter := 0;
   ELSE
      commit_counter := commit_counter + 1;
   END IF;
END LOOP;
COMMIT;

Of course, there were a number of different programs and each had this logic, along with a declaration of commit_counter, in each program. We soon found, however, that 10,000 was simply too high a number. We blew out rollback segments on a maddeningly occasional, but unpredictable basis. So we decided to change the number to 1,000 and off I went to each of the different programs removing that troublesome zero.

I felt dumb doing this, but of course we faced looming deadlines and had no time to reflect. The next complication I ran into was the need to run my script in "test mode:" perform the data translation for one or several records and then examine the accuracy of the data. In this situation, I found that I would rather not commit at all. Just run the program, use queries to examine the changes, and then issue a ROLLBACK. To do this, I went back into my program and commented-out the entire IF statement having to do with commits and keeping counts.

commit_counter := 0
FOR original_rec IN original_cur
LOOP
   translate_data (original_rec);
   /*
   IF commit_counter >= 1000
   THEN
      COMMIT;
      commit_counter := 0;
   ELSE
      commit_counter := commit_counter + 1;
   END IF;
   */
END LOOP;
COMMIT;

Once I got through several debug-test cycles, I reactivated my commit logic by removing the comment markers and recompiling.

20.1.1.3 What committed when?

It was then time to run the process for the full sweep of the data (in its manageable chunks). So I started the program and went home for the weekend. Saturday and Sunday were very pleasant, but I came back in on Monday and found that the job has stopped on Sunday afternoon. I had a heck of a time figuring out why it had stopped and how far it had gotten. I realized that it would have been very useful to have a log of each commit performed by the program. So I changed my basic loop (shown previously) as follows:

commit_counter := 0
FOR original_rec IN original_cur
LOOP
   translate_data (original_rec);
   IF commit_counter >= 1000
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE
        ('Commit at' ||
         original_rec.keyvalue);
      commit_counter := 0;
   ELSE
      commit_counter := 
          commit_counter + 1;
   END IF;
END LOOP;
COMMIT;

Now, every time a commit occurred, the primary key value would be displayed. I set up this job to run and after an hour or two it died -- this time because my program's output had exceeded the DBMS_OUTPUT default buffer of 2K! This was getting very frustrating. Maybe I should expand the size of the buffer. Maybe I should be writing the commit log out to a table. Or maybe it was just time for a break.

With a moment to reflect, I saw the insanity of my way. Here I was putting out little fires, patching up this hole, then that hole in my logic. In the process, the code I had written to perform commits was actually getting more complicated than the actual application logic -- and, again, it was repeated in several different programs.

20.1.1.4 Getting back on track

Time out! I declared to myself and the rest of the technical team. I had committed several grievous errors, any one of which should have raised a red flag:

1. I repeated the same code in multiple programs (declaration of commit_counter, IF statement, etc.). This should always be avoided by consolidating repetitive code behind a procedural interface.

2. I edited my code in order to move from "test mode" to "production" status -- I inserted and then removed the comment markers. You want to avoid whenever possible these kinds of last-minute, "no problem" edits of code. Any time you change your code, you really should retest. Do you want to introduce another round of testing right after you thought you finished all your testing?

No, it was time to go back to square one, do some top-down design, and do it right...the second time around.

The first thing that caught my eye is that I could simply be smarter about how to determine when to perform my commit. Rather than use an independent counter, I could take advantage of the %ROWCOUNT cursor attribute to figure out how many rows I had fetched. Combined with use of the MOD function, I could change my loop to the following, more concise implementation:

FOR original_rec IN original_cur
LOOP
   translate_data (original_rec);
   IF MOD (original_cur%ROWCOUNT, 1000) = 0
   THEN
      COMMIT;
   END IF;
END LOOP;
COMMIT;

In this approach, whenever the number of rows fetched is a multiple of 1000, the MOD function returns 0 and COMMIT is executed. No local variable counter to declare and maintain -- when working within a cursor loop anyway. This was a satisfying discovery, but it didn't address some of my other concerns: turning off commits for test purposes, changing the number to use in the call to MOD, and so on. No, I decided to press on...and come up with a package-based solution, which turned into PLVcmt.

20.1.1.5 The impact of PLVcmt

Here is what my data translation loop looks like when I use a package-based approach:

PLVcmt.init_counter;
FOR original_rec IN original_cur
LOOP
   translate_data (original_rec);
   PLVcmt.increment_and_commit;
END LOOP;
PLVcmt.perform_commit;

In other words:

1. I initialize my commit package values.

2. I commit based on the counter inside the loop.

3. Then after the loop terminates, I perform a final commit.

Notice that the commit_counter variable has disappeared. I don't want to deal with that. Also gone is the code to display the commit action and the IF statement. Nor can you find a call to COMMIT. It's all tucked away somewhere else. Ah! A sigh of relief. And -- here is where it gets really dreamy -- if I want to run the program and not perform any commits, I wish to be able to simply call another PLVcmt program to tell it not to commit, like this:

execute PLVcmt.turn_off;

Without making any changes to my program, the behavior of the PLVcmt package would change. Now that would be a wondrous thing, would it not? Let's see how we might go about building such a package, because at least in this case, my fantasies can be transformed fully into reality.

Now that you've seen the inspiration behind PLVcmt and how useful it can be, it's time for a formal introduction. These following sections explain these features of the PLVcmt package:

• Using a package-based substitute for COMMIT

• Performing incremental commits

• Controlling commit processing

• Logging commit activity

20.1.3.1 Setting the commit point

The first step in using PLVcmt to perform incremental commits is to tell the package how often you want a commit to occur. You do this with the commit_after procedure, whose header is shown below:

PROCEDURE commit_after (count_in IN INTEGER);

where count_in is the number of transactions you want to occur before a commit takes place. The default, initial value of the count is 1, which means that every time you call PLVcmt.increment_and_commit, a COMMIT is executed (unless you have turned off commit processing, which is discussed in the next section).

In the following call to commit_after, I request that a commit occur every 100 transactions.

PLVcmt.commit_after (100);

In this next call to commit_after, I set the commit point to 0.

PLVcmt.commit_after (0);

This effectively turns off the execution of a COMMIT from within the increment_and_commit program. With the "commit after" set to zero, a COMMIT occurs only when PLVcmt.perform_commit is called.

NOTE: The commit point established by the commit_after procedure does not in any way affect the behavior of the perform_commit procedure.

20.1.3.2 Initializing the counter

You have called PLVcmt.commit_after to tell PLVcmt that you want to commit every n records. Before you start running your code, you should initialize the PLVcmt counter to make sure that n transactions occur before a commit.

The header for init_counter is:

PROCEDURE init_counter;

When called, this program sets the internal PLVcmt counter to 0. The only way to modify this counter is with a call to init_counter or to increment_and_commit.

20.1.3.3 Increment and commit

When you want to commit every n records, you can simply insert a call to the increment_and_commit procedure in your code. The header is:

PROCEDURE increment_and_commit (context_in IN VARCHAR2 := NULL);

This program always increments the PLVcmt counter. If the counter exceeds the commit-after value set with a call to PLVcmt.commit_after, then the perform_commit procedure is called. Immediately after that, PLVcmt calls its own init_counter to reset the counter to 0.