Large amount of data loading into ORACLE database

[Alex Lavrov.]

Hello,
we have a process that loads huge amonts of data into oracle. I'm talking about millions of rows everyday. The problem is, that the tables now are huge, so we had to build different indexes so we can fetch the data, but now every inserts takes longer, because it has to rebuild indexes every time and some of the data is dropped, because the insert speed into the dataase is much smaller than the speed we get the data (from outside).

We totaly lost. We thought about breaking the tables into smaller tables, because we want the data only for 30 days, so we ould build 30 tables, table for each day, so every day insert will insert to another table. Another think is instead of inserting the data into oracle, write it in files and the, another process will load it into the database (I'm not sure it's gonna improve something).


Any ideas will be much appriciated.

[harry d brown jr]

If the data is pre-validated (meaning it's pure and correct) then drop your constraints and triggers when loading the data.

We did this years ago in another life and our load process went from 22 hours to less than 5 minutes.

We made the DB unavailable to other users and applications, but it was well worth it!

live free or die
harry d brown jr

[Alex Lavrov.]

Hey,
it's not good enough because the data is loaded for 24/7 and we must fetch the data too ....

[Alzhy]

If your server is already fast enough (CPU/Memory Wise) - then look at increasing the throughput of your IO infrastructure. Thhis means, increasing I.O channels (FC or SCSI), getting a faster disk array subsystem and reviewing the storage layout of your instance.

[Steven E. Protter]

Performance of this process can be improved by stopping of archive logging just prior to the upload.

This requires the database to be bounced.

At the end of the upload, you can bounce the database again and turn on archive logging.

There is an obvious recovery risk in this scenario.

SEP

[Alex Lavrov.]

The database is not in archive mode. We disabled it along ago ...

[TwoProc]

Alex, You can do loads from sqlloader with a special keyword(can't think of it right now) that loads above the high water mark. These loads can be much faster than the conventional.

You could use partitioned indexes (on date columns) but that's only going to help queries - not loads, and only if the queries that you form actually query with limits by date.

Your idea of a table with live 30 days of data idea is probably the most solid I've heard (although the one with the most work).
You may want to leave the current day's table name static and roll the other day's data into another table partitioned by date. That way you've only got two tables to manage - current and old. Not one for each day. Have another job that runs every day that takes data out of the "old" table that is older than 30 days. You probably would want to put these two tables in a separate tablespace with uniform allocation sizes so as to limit fragmentation.

Another one that I've done is drop indexes before loads and re-create using parallel threads afterwards. But, since you're loading all day - that's not going to fly either.

Have you run a statspack? Are you spending a lot of time waiting for redo-log buffers? I've seen cases where increasing this number until the waits taper off makes a big difference. Also, are the redo-logs running on raid 0/1 drives? How about the data drives that you're writing to? They really should be. Are there lots of sorts on disk? You may be able to increase the sort_area_size to increase throughput (if it's way too small now, it will make a difference). Also, have you tried greatly increasing the size of the db_buffer_cache? If you can get to a point where only a small percentage of this type of I/O (the inserts) are running all the down to the drives, it could greatly increase throughput. How big is your SGA?

[Alex Lavrov.]

Hello John, thanx for the ideas, I'll answer to all your question as quick as possible.

[Jean-Luc Oudart]

Hi Alex,

not to sure about your application details therefore I may make some asumptions.

If you want to insert a huge number of rows into an existing table sqlldr direct mode would be the fastest way of doing it.
The constraint is that the indexes will be rebuilt at the end.
If you don't care about about logging the change you can use the UNRECOVERABLE option to speed up the operation.

if you break the table into smaller tables, would you use the partitioning option ?

If the data is not updated (other than this load) you may use the compression option (from oracle9i onward). You can also use compressed indexes.

Regards
Jean-Luc

[Julio Yamawaki]

Hi,

I have a table with 150 Million rows, without partitioning and we load a lot of rows every day. This table has no issues in performance.
So, to help your process, there are a couple of things that we must know:
1. What version of Oracle are you using
2. How many rows do you have in your table and how many rows do you insert every day. Are this rows insert during all day or at the same moment?
3. How are your database configured: rule or choose?
4. How is the tablespace that contains the table configured: local or dictionary management?
5. Is there any row migration? What about the indexes fragmentation?