Xref: alice comp.databases.oracle.server:56314
Path: alice!news-feed.fnsi.net!netnews.com!newshub.northeast.verio.net!dispose.news.demon.net!demon!news.demon.co.uk!demon!jlcomp.demon.co.uk!not-for-mail
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.server
Subject: Re: For the GURUS - Load Process slowing down
Date: Wed, 7 Jul 1999 10:26:03 +0100
Message-ID: <931341362.28143.1.nnrp-02.9e984b29@news.demon.co.uk>
References: <7ltett$62g$1@hermes.is.co.za>  <NKtg3.569$Wu4.7351@petpeeve.ziplink.net>  <37826c74@discussions> <7ltrqp$c5o$1@hermes.is.co.za>
X-Trace: news.demon.co.uk 931341362 nnrp-02:28143 NO-IDENT jlcomp.demon.co.uk:158.152.75.41
X-Complaints-To: abuse@demon.net
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Lines: 55

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@hermes.is.co.za>...
>Thanks, but I don't have any indexes on the load table, only on the
>destination online member table.