Oracle log_buffer size - SAP ECC

[Former Member]

Hello,

We have here the SAP ECC 6.0 running on AIX 7.1 and Oracle 11G with 24G SGA. When we was configuring the oracle parameter, we've checked the note 1627481 regarding how to size the log_buffer an it suggests us to unset this parameter since we have 80 cpu's. So, we have reset this parameter and now i've checked it and the current value is around 80MB. I've read that the log_buffer since Oracle 10G is dynamic calculated by a internal algorithm (Oracle note 604351.1). In many forums i've read that setting the log_buffer beyond 1M we have no benefits since log write writes, among other situation, when log buffer reaches 1/3 full or 1M.

In my situation, is OK let the log buffer "dynamic".

Is right the way i've interpreted when SAP  said to unset the log_buffer when systems with more the 16 cores? unset = reset in this case?

Obs.: this system is really busy with around 1500 users connected simultaneous.

Rafael M. - Br

[stefan_koehler · Accepted solution]

Hi Rafael,

> Is right the way i've interpreted when SAP  said to unset the log_buffer when systems with more the 16 cores? unset = reset in this case?

Yes, this is correct. Log buffer is allocated in granules and the whole public redo log buffer is split into several public redo buffers (threads). The number of public redo threads is also determined by cpu_count (e.g. ceiling(1 + cpu_count / 16)).

80 MB is usually pretty enough to avoid "log buffer space" waits (simplified: session that my hold private redo or goes straight to public gets redo allocation latch - not enough space is available - checking redo writing latch and post LGWR if necessary - go into "log buffer space" wait).

Regards

Stefan

[Former Member]

Thank you Stefan and Brindavan for your answers.

Log_file_sync is one of the top timed foreground events according AWR.

Is the log_buffer size causing this kind of wait?

I have 10 groups of redo with 1.5G and an average of 8-10 log switches per hour.

Regards,
Rafael

[fidel_vales]

hi,

yes 6% of the DB time is log file sync.

I do not think 6% is "A LOT" that needs to be "fiddle" changing the parameter. If you want to increase the DB "performance" you have a 45% caused by CPU and 22% by db file sequential read, but you do not ask anything about those.

Recommendatio is not to set the parameter. Please, do not set it unless there is a real reason.

Setting it big do not cause any "log file sync" problem. Oracle will flush it to disk when needed and that will (in general) not because it is full.

[Former Member]

Hey Fidel,

     yes, i have other waits and i am handling with them. We have a newly deployed SAP system here and there are a lot of missing index, poor query, z_programs, etc, etc.

     I am wondering to know what is causing this specific log wait. OK, the size of my log_buffer is not the guy, so, who's is causing this event of wait? The storage guy said that there is not any kind of enqueue. How can i have sure about that?

Regards,

Rafael Melo

[stefan_koehler]

Hi Rafael,

>> The storage guy said that there is not any kind of enqueue. How can i have sure about that?

Your storage guy is probably right as 4 ms in average (however averages are not a really good indicator at all) sounds pretty good. "Log file sync" includes the I/O and log buffer handling time. Please check the wait event "log file parallel write" for the isolated log file I/O write time.

>> OK, the size of my log_buffer is not the guy, so, who's is causing this event of wait?

Every commit is causing this wait event. So the first main question is: What time window is this AWR report about? It is necessary to set this in context to db and elapsed time.

However as Fidel already said - i would not bother about the log file sync here. It seems more like that you are suffered by "compulsive tuning disorder".

Regards

Stefan

[Former Member]

Hello Stefan,

I'm sorry but I'm sure that I'm not suffering from "compulsive tuning disorder". I'm just trying to understand my new environment and why things happen and if I can do something to become this better. Anyway, thank you for your effort and patience answering and explaining how this works. I'm glad for that!

Regards,
Rafael

[fidel_vales]

Hi Marcelo,

"log file sync" is a "normal" event (same as CPU or db file sequential read).

As mentioned by Stefan, each time there is a commit (and in other cases) the log buffer has to be written to disk.

The LGWR (background process) does that task and waits for "log file parallel write" (from the top of my head) and the equivalent event for the foreground processes (the ones doing the commit) is "log file sync". that means that it is quite normal seeing that event.

If you want to tune it, you should check if you are writing much, or if it is slow writing or if you have CPU starvation or .... maybe it is normal because the activity in your system.

looking only at the event will not tell you why it appears

you can take a look at the SAP note 619188 and also the presentation "ORACLE LGWR Analysis"

[Brindavan_M]

Hi Rafael ,

If the Log buffer set to high causing performance problem, because the writes will be performed synchronously  because of the large log buffer size (high log file sync wait).

Oracle process log file sync waits

Oracle processes log file sync waits:

"DBWn finds the highest high redo block address that needs to be synced before the batch can be written.

DBWn then takes the redo allocation latch to ensure that the required redo block address has already been written by LGWR, and if not,It posts LGWR and sleeps on a log file sync wait."

For setting the value this link might helpful  : LOG_BUFFER (Oracle) - Monitoring in the CCMS - SAP Library

Default value : 512 KB or 128 KB * CPU_COUNT, whichever is greater

Thanks,

Brindavan