Re: archive question --- too many archive logs generated during data load

"Uchakra" <uchakra_at_aol.com> wrote in message news:20011013022053.27765.00002988_at_mb-cb.aol.com...
> I am encountering a situation while trying to load data into a production
> database.
> It's Oracle 8.1.6 , Enterprise edition on Solaris 2.7.
>
> Data is loaded into a table using PL/SQL. Approximately 1 million rows
> are inserted. Average row length is 30 bytes for the table and all columns
are
> number. Hence, approximately 30 MB of data is loaded.
>
> However, as soon as the the data load process starts, there is continuous
> archiving every few seconds. There are 3 redo logs 5 MB each.
> Eventually there are 300 archive logs are generated during the process.
> Hence 300 x 5 = 1500 MB worth of archived log is generated while only
> 30 MB of data is loaded. No other processing takes place during the load.
>
> Because of this massive archive log generation during this process,
suddenly
> the filesystem tends to fillup so fast that I have to monitor the space
> continuously during the load to ensue archiver doesn't suddenly stop
because of
> lack of space.
>
>
> Question: Is this massive archive log generation normal for this amount
of
> data.
>
> Is there any init parameter or is there anything that
can be
> checked or
> done to avoid this massive archive generation.
>
> It's understandable a lot of redo log switched will happen during data
loads.
> However, I am trying to understand what's the reason for this excessive
> archiving.
> How to justify or quantify the amount of archivig based on a certain data
> volumes or transaction
>
> Anyone with any experience or information on this . please help.
>
> Regards
> Willy
>

Redo log consists of
a log of the changed bytes in a block both for tables and indexes. As far as I know there are no free holes in a datablock, the free space is always contiguous. So your insert might force Oracle to rearrange the block. The same applies to indexes also of course. If you would insert only 1 single record in sql*plus with autotrace on, you will probably see more than 30 bytes are changed.

Morale: you need to run the sqlloader session with direct=y on the commandline, so you won't generate any redolog at all. Indexes have to be rebuild though after this act.

Hth,

Sybrand Bakker
Senior Oracle DBA Received on Sat Oct 13 2001 - 05:36:19 CDT