Too much Redo generation

[kkpareek]

Hi,

I am working on a datawarehouseing project and currently developing load processes. We use SQL ( direct) to move data to staging tables & then PL/SQL procedure to insert/update data in main tables. The tables along with indexes are with logging option and DB is running in archivelog mode.

There is too much redo log generation. In on test it was about 5.5 of data size ( data size 72 MB ( num_rows*avg_row_len)) and redo generated was 370 MB. In another test, the data size was 52 MB and redo 1.5 GB!

Even if you consider that there is redo for DML on tables , indexes and entries generated for RB segments for these DML, do you not think such sizes ( specially 30 times) too high?

How is it possible to reduce redo without turning to NOLOGGING?

TIA.

[DBAtrix]

there's a pretty simple formula for redo control in oracle.
work==redo
more work==more redo

the only control you have is to do data scrubbing outside oracle and use NOLOGGING. The only reasons i can think of for creating indexes with logging is standby databases, and totally hands-off (as possible) database or instance recovery after database failure. remember the redo is not only for your work on the data, but all changes to the oracle database. this includes rollback segments and temp space that is a datafile (opposed to a "real" tempfiles--the ones that show up in dba_temp_files, not in dba_data_files).

so use nologging, direct operations where possible (that's the point of a datawarehouse), and temporary files.

d.

[jdorlon]

You can do data scrubbing in global temporary tables if you are one 8i. That will help a little.

-John

[bberg@rtnyc.com]

If you are doing these updates to the main tables in one daily run, why don't you drop the indexes... run your update statements, and then recreate them... I'm not sure of the time factor involved vs not rebuilding the indexes.

I'm doing the same project, I'd like to see how your updates go.

Are you doing MASS updates? Perhaps you can check if your data is changing before making the update (if necessary)?