Re: Reducing redolog switch frequence

From: Boaz Laufer <lauferb_at_adit-tec.com>
Date: 1 Jun 2002 04:21:05 -0700
Message-ID: <1bacd337.0206010321.2134d82b@posting.google.com>

100.17706_at_germanynet.de wrote in message news:<abr8ho$5cf$1_at_news.netmar.com>...
> Hello
>
> We use a data warehouse application (PL/SQL) and produce over 100 redo log
> files 10 MB in size each per hour. In the init parameter file, we have
> log_buffer = 20979712.
>
> What can I do to try to reduce the redo log switch frequence to a healthy
> value of about one per half an hour?
>
> Because this application runs very slow, we assume that harddisk I/O due
> to redo log operations could be a bottleneck.
>
> How can I improve performance by somehow tuning parameters related to
> the redo log files?
>
> The same question would apply to parameters related to rollback segments,
> I guess.
>
> ANY suggestion will be welcome!
>
> Thanks
> Rick
>

enlarge your redo logs and create more groups.
if you use 'DELETE' Sql's in your process (Delete's write a lot to the redologs), try to change it to 'TRUNCATE' commands (use partitions to separate your data to different partitions and than truncate a whole partition)
use 'NOLOGGING' on tables and indexes - this will save you a lot of writes to the redologs BUT only in certain cases like in 'INSERT AS SELECT', you can use temporary tables like this: Copy the data to the temporary table, do the work on the tables and than truncate the original table use 'INSERT AS SELECT' with NOLOGGING to copy back the data. this will redure significantly the redolog writes!

Regards,
Boaz.

Boaz Laufer.
Oracle DBA and Consultant.
Adit-Information Technologies.
Adanet business group.

>
>
> ----- Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web -----
> http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups
> NewsOne.Net prohibits users from posting spam. If this or other posts
> made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net
Received on Sat Jun 01 2002 - 06:21:05 CDT