Performance Issues with DML Error Logging and Conventional Path Inserts

December 4, 2005 Oracle Database

I’ve recently been putting together an article for Oracle Magazine on the new
DML Error Logging feature in Oracle 10gR2, which allows you to add a LOG ERRORS
clause to most DML statements so that rows that would otherwise cause the
statement to fail are instead copied to an error logging table. A typical use of
LOG ERRORS is like this:

INTO   table_a
FROM   table_b

The rows that would cause an error are instead written to a table called
ERR$_TABLE_A that gets created for you by the DBMS_ERRLOG.CREATE_ERROR_LOG
procedure. I won’t go into the full details here but Tim Hall has put together a
nice write-up that you can view here.

Anyway, for the article I put a set of examples together, where I used the
data in the SH.SALES table to create a “source table” copy in a new
schema, created another copy based off of this as a “target table”,
then introduced some errors in to the source table and constraints into the
target so that I could show how the feature worked. Finally I used the
DBMS_ERRLOG package to create the error logging table.

Next then I demonstrated a direct path insert from the source into the target
table, using the LOG ERRORS clause.

I then compared it to a PL/SQL routine that you’d normally use to handle this
sort of situation. 

The example results came back as you’d expect – the direct path insert using DML error logging took about 5 seconds for 900k rows, whilst the PL/SQL routine, which used BULK COLLECT, FORALL and SAVE EXCEPTIONS, took about 26 seconds. As an experiment, I also ran the LOG ERRORS version using just a conventional path insert, like this:

The conventional path version came back in just under a minute – considerably
longer than the PL/SQL version. I must admit I put this down to the fact that I
wasn’t in direct path mode, coupled with the amount of rows I was loading, and
just mentally filed it away as one of those things.

I passed the article out for review to Tim Hall, Doug Burns and Tom Kyte as
this was the first “server tech” article I’d written for Oracle
Magazine, and I thought it worth getting a couple of extra opinions before
submitting it for publishing. The reviews were OK except that Tom picked up on
the fact that the conventional path version ran so slow – this seemed too slow
for him and something didn’t look right. Tom said he’d take a look at it but
also suggested that I contact Jonathan Lewis, who might have an idea on this.

I got in touch with Jonathan who said that he’d not had a chance to take a
proper look at DML error logging yet, but said that the figures suggested that
the conventional path load might have managed to fall back into some single-row processing mode for undo, redo and latching.
Jonathan suggested that I run the tests again, but this time using a view and a
statistics gathering package that he’d written to get a better idea of the
activity during the DML execution. I’ve had a look on Jonathan’s site and I
think that the view and code that I used is on this “Snapshots
page, so you can take a look and run the tests yourselves if
you like.

Going back to SQL*Plus, I installed the view and package from Jonathan’s code
and ran the tests again, but this time snapshotting the stats to see if clues
suggested themselves:

Well that was the direct path version. What about the conventional path

Note the difference in the redo generated between the two versions – 178Kb of
redo for the direct path insert, compared to 263Mb with the conventional path

I also then ran a couple of extra tests; firstly I removed the constraints
from the target table and ran the conventional path version again, this time
without using LOG ERRORS:

This time the conventional path insert generated 43Mb of redo – this should
be considered the benchmark figure for a conventional path insert, therefore the
LOG ERRORS clause is generating just under 200Mb of extra redo, which explains
why it takes so much longer to run. What about then if I remove all the errors
from the data, then reinstate the LOG ERRORS clause – will having this clause in
itself cause the extra redo to be generated?

So yes, just adding the LOG ERRORS clause causes the extra redo to be
generated, even though no errors have been written to the error logging table. According
to Jonathan, what is in effect happening is that when you switch to conventional
path with LOG ERRORS, the “block at a time” optimisation for undo and
redo dissappears. In effect, the conventional path switches to single row mode,
which you can tell by

  • the session logical I/Os in the stats = number of rows
  • redo entries = number of rows
  • db block changes = 2* number of rows (one for the row, one for the undo

It’s worth saying here that I’m working with on Windows XP SP2 in
noarchivelog mode (MR – I originally had this as, a typo I corrected on 5/11). I’ll raise it as a bug on metalink on Monday (assuming it’s a
bug and not an intended feature) and we’ll see what happens. Thanks again to
Jonathan and Tom for pointing it out, very interesting and it shows the value of
getting your article peer reviewed.