RE: Log switches every 4 minutes with 17MB Archivelogs despite 500MB log file size

Just curious: JL already noted the small size, I’m wondering if the concern is that you’re getting delays when you wrap logs. IF so, other than disk acreage there is no penalty to having a lot of online redo log groups. The default of three is just that. I prefer to have enough online redo logs so that size of individual redo logs and the time to repair flow to the archives is dissociated by a healthy margin.  

By healthy, I really mean healthy. If you have enough online logs to handle, say, 8 hours, that should give everyone involved plenty of time to repair flow to archives after whatever you use to trigger an alarm. I’m not aware of any good reason to stick with the default of three. This makes for an environment with a lot less stress when you learn your archive area has filled and the copies are not complete yet.  

Still that would be a logs to not wrap for eight hours if you’re switching every four minutes. I further take it your investigation is to reduce switching so frequently for such small amounts. By habit I sized online redo logs at least 10 times the size of the log buffer for no particular reason, and I’ve never seen the situation you describe. That does not dig out the root cause, but it might be a workaround. (Of course I’ve never really understood why a log buffer flush couldn’t span logs as long as the logs have not wrapped.)  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Friday, February 03, 2017 3:40 AM
To: martin.decker_at_ora-solutions.net
Cc: oracle-l_at_freelists.org
Subject: Re: Log switches every 4 minutes with 17MB Archivelogs despite 500MB log file size    

Minimum online access at present. Can't check MoS.  

Sounds like an argument based on private redo threads, which aren't used in RAC. But maybe reservation still happens- bit roughly 128K * transactions/ 10 (IIRC)  

If it's only 17M of redo in 4 minutes, does it really matter or is this just curiosity ?

Regards

Jonathan Lewis

(From my iPad mini; please excuse typos and auto-correct)  

On 3 Feb 2017, at 08:21, Martin Decker <martin.decker_at_ora-solutions.net> wrote:

Dear Jonathan,

I apologize for the delay.

We have the majority of the workload on Instance 1. We saw switches with 15-18 MB archivelogs every 4 minutes on instance 1 and log switches with even smaller archivelogs around every 15 minutes on instance 2. As the workload is similar all the time, the behavior is visible all the time.

Support explained that it has to do with the "logfile space reservation algorithm" (rough explanation in MOS 1356604.1) and with the fact that Redo Buffer size is very similar compared to redo log size (both around 500M). We are waiting for a more detailed explanation from support specifically for our setup with #private strands, #cpus, log buffer, redo size. Next recommendation was to reduce log buffer from 512m to 256m and reduce redo. log file size to 700M.

Best regards,

Martin  

--
http://www.freelists.org/webpage/oracle-l