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?

[Indira Aramandla]

Hi Alex,

Yes as Jean mentioned using sqlldr utility with direct=true option is a method to load data into table. Direct path load is much faster than conventional path load, but entails several restrictions.

A direct path load parses the input records according to the field specifications, converts the input field data to the column datatype, and builds a column array. The column array is passed to a block formatter, which creates data blocks in Oracle database block format. The newly formatted database blocks are written directly to the database, bypassing most RDBMS processing. During a direct path load, some integrity constraints are automatically disabled. When the load completes, the integrity constraints will be reenabled automatically if the REENABLE clause is specified
During a direct path load, performance is improved by using temporary storage. After each block is formatted, the new index keys are put to a sort (temporary) segment. The old index and the new keys are merged at load finish time to create the new index. The old index, sort (temporary) segment, and new index segment all require storage until the merge is complete. Then the old index and temporary segment are removed.
You can improve the performance of direct path loads by presorting your data on indexed columns. Presorting minimizes temporary storage requirements during the load. Presorting also allows you to take advantage of high-performance sorting routines that are optimized for your operating system or application

You can use the index maintenance options availabe with sqlldr
SKIP_INDEX_MAINTENANCE={YES | NO} stops index maintenance for direct path loads but does not apply to conventional path loads. It causes the index partitions that would have had index keys added to them instead to be marked Index Unusable because the index segment is inconsistent with respect to the data it indexes. Index segments that are not affected by the load retain the Index Unusable state they had prior to the load. Avoiding index maintenance is quite reasonable when the number of rows to be loaded is large compared to the size of the table

And you said that you are loading the data and at the same time retrieving the data. And you also mentioned that you had truned off the archive logging off. So I assume that the users are only quering the data from say 7 AM to 6 PM during the day, then you can collect the data from your source into flat / ascii files and schedule the dataload at different intervals of time. Say early in the morning, then one set of data in the afternoon and then later on in the evening and night. Ro you could also use johnâ s method of current and old data in two tables instead of 10 tables.

[Yogeeraj_1 · Accepted solution]

hi alex,

have you considered partitioning the table?

Rebuilding of index is a very costly activity, unless you have real good reason, it is not advisable. If you have properly set the PCTFREE for the table and use Locally Managed tablespaces, there should not be any major problems.

Are you also, recalculating your statistics (if you are using the Cost-based optimizer, of course)?

There are also new features in Oracle 9i and 10g conserning data upload...

hope this helps too!

regards
yogeeraj

[Alex Lavrov.]

Thatnk you for your help, we'll try all these things.