Performance Issues with DML Error Logging and Conventional Path Inserts

December 4th, 2005 by

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:

INSERT /*+ APPEND */
INTO   table_a
SELECT *
FROM   table_b
LOG ERRORS
REJECT LIMIT UNLIMITED;

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
Revisited”
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
version?

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
insert.

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
    entry

It’s worth saying here that I’m working with 10.2.0.1 on Windows XP SP2 in
noarchivelog mode (MR – I originally had this as 10.1.0.2, 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.

Comments

  1. Jaromir D.B. Nemec Says:

    Hi Mark,
    nice to see you on such low level of BI:)
    A small addendum: a care should be taken if the number of exceptional rows is not very low, i.e. if a non trivial number of rows is written in the err$ table. See test case below comparing the LOG ERRORS feature with a self implemented check on a 200k rows table with 100k exceptions. (Note the difference in recursive calls – one per exception row – and db block gets statistics; in addition to redo size).
    Yes, I know exceptions *should* be rare, but this could be a negative feedback, you have a really bad data and you get a really slow running job.
    Regard,
    Jaromir
    – table src has 200k rows; the half of then is not valid and is written in the err$ table
    – process high number of exceptions is relatively expensive

  2. Mark Says:

    Hi Jaromir
    Good to hear from you!
    Thanks for the heads up on the number of exception rows issue – I kind of expected that this might be an issue, thanks for the test case.
    I don’t know why the comments were rejected – they all got through and I uploaded one of them this morning. I’ve got an issue with the software used to run my site, this probably sounds like it playing up again. Thanks for persevering though.
    all the best
    Mark

  3. David Aldridge Says:

    These reverse engineering articles are pretty tricky stuff, i find. I didn’t realise how much work and thought they require before I started on them myself.
    Good job Mark: have you had a look through trace files for the differences between the scenarios? I always find them illuminating.

  4. Rittman Mead Consulting » OWB11g, DML Error Logging and Data Rules Says:

    [...] wrote about DML Error Logging in a blog post last year and in this article for Oracle Magazine. One of the drawbacks in the 10gR2 implementation of DML [...]

Website Design & Build: tymedia.co.uk