Re: For the GURUS - Load Process slowing down

It sounds like you are processing data as you load. What I would recommend is:

1. Do a direct load with all the indexes and triggers removed from the staging table.
2. Apply the indexes you need for your validations, updates and inserts after the load completes.
3. Process the staging table in a batch *AFTER* it is fully populated.
4. If this is a batch process with no recovery woes, turn off logging.
5. Increase your extent size in the tablespace where the load occurs and keep the size fixed.
6. Allocate enough extents to cover the expected size of the load.
7. Set your percent free to zero on the table where the load occurs, unless you are updating this table as well.

Since you have an update and insert operation, consider breaking them up. For inserts try building your insertion set using a minus operator, i.e. select (primary key fields) from staging minus select (primary key fields) from target, to produce the rows for insertion, then remove these rows from your update cursor.

I am quoting rules of thumb. However, I would recommend getting the data in first, and processing it in sets after the fact. Processing each row with PL/SQL is ssssllllooooowwww. Executing a stored procedure for each row is a tremendous amount of call overhead. Look for creative queries as opposed to detailed logic for each row.

If you are having to use several queries to process each row with EXISTS, IN and NOT IN type operators, try re-writing the process in PL/SQL using staggered cursors on your source and destination and searching in primary key order. If you are stuck doing table scans or repeatedly searching a primary key for each row of your source data, this will run faster.

If you are ambitious, my previous suggestion would probably run faster as an OCI program running on the host.

Without looking at your data, these are things that have worked for me.

Chris Weiss
Compuware Corporation
Professional Services Division

Ozzy <ozzy_at_anyinfo.co.za> wrote in message news:7ltett$62g$1_at_hermes.is.co.za...
> Hi
>
> I have written a load process (using SPs and Packages) that loads and/or
> updates live 24hr member data. The problem is that as the destination
> member table grows, the load gets progressively slower.
>
> Basically what the process is doing, is loading data, via SQL Loader, into
a
> member load table. It then compares the data in the load table with data
in
> the live member table. If the member exists and the data differs, it will
> update, if there is no match, a row is added. As the table grows, it
takes
> progressively longer to load the same amount of rows.
>
> I have spent a long time debugging and running tests and have established
> that the problem does not lie in the searches or the insertion of data
rows,
> but in the growing indexes. Eg. I have removed all the indexes (except
for
> the PK/Unique indexes) and the load becomes constant. As soon as I put
the
> necessary indexes on, the load begins to slow. I have also given the
> indexes 500K and 1M extents and no difference is noticed.
>
> Is this a common problem? Will it reach a plateau and become constant
> eventually?
>
> I currently only working with about 25 000 rows.
>
> Any help in this regard will be greatly appreciated. (Maybe my problem
will
> be solved when I go onto real serious hardware?)
>
> Thanks
> Ozzy
>
>
Received on Tue Jul 06 1999 - 23:29:17 CEST