Re: most expeditious way to update a large number of rows

> Brian,
>
> I have 5 redo log groups, each is 10MB. I cannot (easily) add more
> (the database was created with a max. of 5.)
>
> 1. Roughly 200MB of redo is generated daily.

Then I definitely would not recommend a 100MB online redo log! Assuming

   that your log switches only occur when the online redo log becomes full, a 100MB online redo log would leave you with 2 log switches in an average 24 hour period. So something much smaller is probably needed.

> 2. Maximum availability. Physical standby. ARCHIVE_LAG_TARGET set.

On my web site (http://www.peasland.net) there is a script called "archivelog_histogram.sql" which can be used to see how many archived redo logs are created in each hour. Next, use Oracle's recommendation (http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/build_db.htm#10302) to "switch logs at most once every twenty minutes". Knowing the current online redo log size and using the output from the script I referenced above, a little math from your end could come up with how much redo you generate in your busiest hour (number of log switches in the busiest hour * online redo log size). This will give you the number of megabytes. Divide this figure by 3 and you should get an estimated online redo log size. For instance, if the script shows that your busiest hour had 10 log switches and the online redo logs are 10MB, then my busiest hour is 100MB of redo generated. Dividing by 3 means an online redo log size of about 33.3MB is a minimum size to switch at most once every twenty minutes for that busiest hour. For the hours that are not your busiest hours, the ARCHIVE_LAG_TARGET parameter will ensure you switch more frequently.

> 3. Database and archived redo logs backed up nightly with RMAN (and
> written to tape).

Some people do not configure Standby databases. So if their server catches fire, the only way to get data back is from the backups (assuming they are not on the same server). If your company can only afford to lose 1 hour of transactions, then the archived redo logs should be dumped to tape every hour. This means you should either size your online redo logs so as to have at least one log switch every hour, or use the ARCHIVE_LAG_TARGET parameter to force the switch even if the log is not yet full. However, if you can only lose one hour of transactions, then you just might want to consider employing Standby, but this does illustrate the point.

> In your estimation, what's the ideal size/number of log groups?

Once you have settled on a size for the online redo logs, you'll need enough to avoid "Checkpoint not complete" messages in your Alert Log.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown