Re: For the GURUS - Load Process slowing down

There are various reasons why the process might slow down:

I assume from your description that your action is:

    for r1 in (select * from load table) loop

        update base table where
        pk-cols = r1.pk-cols;
        if sql%rowcount = 0 then
            insert into load table
        end if

        ?  possible tick-off of row processed ?
        ?  possible commit ?

    end loop;
    ? possibly commit ?

Upate and insert on failure is generally quicker than insert then update on dul_val_in_index by the way unless the failure rate is higher than about 1 in 15 or 20.

If the process is slowing down as you have more rows in the target table the two main culprits are a) The index leaf levels ar becoming more fragemented

    as more rows are inserted, so it requires more physical     I/O to find the correct leaf.
b) You are running into a rollback problem, and having to

    re-read increasing amounts of rollback from disc

There are a few other options, but it there is little point in confusing the issue without seeing the exact coding method you are using.

To test for (a) - use the validate index command in the two different sets of circumstances, and see if the PCT_USED figure in INDEX_STATS has changed. A side effect of (a) is that the I/O on the relevant index tablespaces would be higher.

To test for (b) - look at v$filestat, and see if the I/O on the rollback tablespaces has increased.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Ozzy wrote in message <7ltrqp$c5o$1_at_hermes.is.co.za>...
>Thanks, but I don't have any indexes on the load table, only on the
>destination online member table.
Received on Wed Jul 07 1999 - 04:26:03 CDT