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

mylesv_at_gmail.com wrote:
> Brian Peasland wrote:
> > How can you make any assessment of this individual's online redo log
> > size? Did I miss where the redo generation rate was specified? In one
> > system where a 100MB online redo log size is appropriate, other systems
> > may find 1MB more appropriate. The size of the online redo logs should
> > be predicated by the following:
> >
> > 1. What is the redo generation rate?
> > 2. If a Standby database is employed, the type of Standby configuration
> > which dictates how transactions are shipped to the Standby database. How
> > does this play into your online redo log size?
> > 3. How often will archived redo logs be written to tape or other
> > destination not residing on the database server?
> > 4. In the absence of Standby configuration, what is the SLA for data loss.
> >
> >
> > One wants to size their redo logs appropriately so that the redo gets
> > archived and copied to a safe location frequently enough so as to meet
> > the defined SLA for that system. However, one does not want to switch
> > too frequently so as to put unnecessary stress on the system. I have
> > some systems which only generate 50MB of archived redo each day. Why
> > would I want a 100MB online redo log on that system? I have other
> > systems which can generate 1GB of redo per minute. IMO, no one size fits
> > all, which is why I enjoy the fact that Oracle lets me determine an
> > optimal size for the online redo logs on a database-by-database basis. :)
> >
> >
> > Cheers,
> > Brian
>
> 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.

I think here we may want to know the rate during the maximum generation of logs. Look in your alert log for the busiest times and find the smallest interval between log switches. If they are not more frequent then archive_lag_target, they are probably sized properly. Another way to check this is to list your log archive destination and see when logs are equal to 10MB in size. Again, if none ever get that big, you don't need to change anything (unless you want to change your service level agreement).

> 2. Maximum availability. Physical standby. ARCHIVE_LAG_TARGET set.
> 3. Database and archived redo logs backed up nightly with RMAN (and
> written to tape).
>
> In your estimation, what's the ideal size/number of log groups?

In addition to the above, look for log buffer waits. If you don't have any, nothing to worry about. We don't know if the 200MB is generated evenly during the day, or, say, during some load all at once. So we need to be told.

We also need to be told the exact version, ie 9.2.0.6, and also your platform and exact operating system version. "Maximum availability" is a hint, but not a good one.

As far as depending on FAL, my advice would be very version dependent.

jg

--
@home.com is bogus.
Fraud or risk?
http://www.signonsandiego.com/uniontrib/20061015/news_lz1b15fraud.html