MYTH? – The log_buffer should remain small.
***********************************************************

This is perpetuated with several Metalink notes that have become somewhat obsolete:

“In a busy system, a value 65536 or higher is reasonable for log_buffer.

“It has been noted previously that values larger than 5M may not make a difference.”

Metalink notes that in 10gr2, we see a case where a customer cannot reduce the log_buffer size from 16 meg:

https://metalink.oracle.com/metalink/plsql/f?p=130:15:1634364682833660660::::p15_database_id,p15_docid,p15_show_header,p15_show_help,p15_black_frame,p15_font:BUG,4930608,1,1,1,helvetica

“In 10G R2, Oracle combines fixed SGA area and redo buffer log buffer together. If there is a free space after Oracle puts the combined buffers into a granule, that space is added to the redo buffer. Thus you see redo buffer has more space as expected. This is an expected behavior.. .

In 10.2 the log buffer is rounded up to use the rest of the granule. The granule size can be found from the hidden parameter "_ksmg_granule_size" and in your case is probably 16Mb. The calculation for the granule size is a little convoluted but it depends on the number of datafiles”

MYTH? – Disk I/O speed is not the primary concern for tuning the log_buffer
**************************************************************************************
Below, Jonathan Lewis confirms that disk I/O speed is critical to redo throughput:

http://www.jlcomp.demon.co.uk/not_proof.html

http://www.jlcomp.demon.co.uk/ops_01.html

“Your main worry is how fast the I/O subsystem might be, and what happens with the log buffer and log files.”

“Each instance has only one redo log buffer, and the ability to generate redo can ultimately be what defines the maximum speed of your database.”

TO THOSE WHO LIKE TO HURL INSULTS, PLEASE NOTE THAT I HAVE CONTRIBUTED NOTHING PERSONAL, CITING ONLY JONATHAN LEWIS, THE ORACLE DOCUMENTATION AND METALINK!

By the way, your article says: "The number of CPUs is important to the value of log_buffer, because multiple log writer (LGWR) processes may be spawned by Oracle to asynchronously offload the redo information."

How does that use of "because" work? Paraphrasing the Metalink note: if you set dbwr_io_slaves to a non-zero value, then _lgwr_io_slave defaults to 4, which allows up to 4 extra I/O slaves to be started for LGWR to hand off its i/o load. That statement has no logical connection to the fact that the number of CPUs affects the value of log_buffer.

"I like your snarky word trick"
You mean the trick of making a true statement - but expressing it in a way that might make people think it means more than it does ? I learned that one from someone on the Internet - I can't think who it was right now, but I'm sure it will come back to me.

"The Metalink references"
So what do you do when you find two Metalink references that contradict each other ?

you have "copied" loads of content onto your domain from my web sites
What's the difference between "copied" with and without quotes ? I've certainly copied a few bits from your website from time to time - but only to point out the mistakes. I didn't think the volume had got up to "loads" yet. Since you've clearly been tracking the volume better than I have, give me a list and I'll add the appropriate URL - and include an apology for failing to include it in the first place.

"it’s irresponsible to make such a bold assertion with such little data. (in the instance example, his “guess” is completely incorrect, but that’s not surprising, given the tiny amount of data available to him)."

But you presented the data with the lead in: "Here is an an example of an Oracle 10g database with an undersized log buffer, in this example 512k:" in one case and "Here is a AWR report showing a database with an undersized log_buffer, in this case where the DBA did not set the log_buffer parameter in their init.ora file:" in the other.
Apparently you thought the data you published was sufficient to demonstrate to the rest of the world the symptoms of a log buffer that was too small. Are you now saying that the data wasn't intended to show people what the symptoms of a small log buffer would show up as in their 'Top 5 timed events' ? If so, why print the data at all ? If they're not truely symptomatic of anything why not introduce them with a comment like: "Here is a completely irrelevant set of figures from a database which (I promise you, despite apparent indications to the contrary) has a log buffer which is too small ?"

But what’s confusing is the fact that Lewis admits that it’s “not sensible” to draw conclusions using only the top-5 timed events (unless, of course, it’s to try to insult me!). But don’t take my word for it, see Lewis’ own words here:

http://www.jlcomp.demon.co.uk/statspack_01.html

“Even though the top-5 timed events numbers look unusual, the first thing you should notice is that I haven’t supplied enough information for you to make a sensible decision. .

The error in the argument here is known as "arguing from the particular to the general". For example, if I say: "It's a lovely sunny day here and the sky is blue", you would note be wise to report this as "Lewis says it never rains in the UK".

By the way, in one case you are ignoring my use of the word "probably": “If you have a performance problem with this system it's probably in the slow I/O subsystem - not in the log buffer.”. It's almost as if you're trying to put together a few true statements in a way that is intended to lead to the worst possible interpretation. I can argue the case that slow writes by LGWR can lead to log buffer space waits - not to mention log file syncs - and you have log writes of 30 milliseconds. It's a pity that you only included the log buffer space waits in one of the data sets and didn't publish some "before and after" figures for the two cases, ; I'd be interested to see what improvements in I/O times and reduction in use of CPU you got from increasing the log buffer size

"Here we go again, another incomplete proof that contradicts..."
You're repeating yourself - you made this claim further up the page. It's the same
counter-argument to the same demonstration. Repeating something doesn't make it true.

"MYTH?: There is no relationship between log_buffer size and CPU count."
It took me a moment to understand why you wanted to include this as a heading when I had quoted the Oracle manual for the default value Operating system specific. Maximum: 500K or 128K * CPU_COUNT, whichever is greater

But of course you're trying to avoid the discussion of multiple LGWR processes by employing a logical fallacy. Let me demonstrate this by analogy. Your published statement starts. "The number of CPU is important to the log_buffer size, because ..." compare this with the following statement: "The pope is a catholic because it always rains in the UK."
* The pope is a catholic - true
* It always rains in the UK - not true
* The pope is a catholic because it always rains in the UK - not true.
At this point in your posting, you are trying to prove that you must be in the right by arguing about the pope and ignoring the weather.

"The obsolete Oracle8 docs note that the value for the parameter log_simultaneous_copies is dependent on the number of CPU’s on the server"

An attempt to direct attention away from the basic criticism

Why does the value of log_simultaneous_copies have any relevance to the argument, the discussion is about multiple LGWR processes, that parameter is about the number of redo allocation latches used to allow processes to acquire space in the log buffer.

"Metalink note 34583.1 “WAITEVENT: "log file parallel write" Reference Note”, shows that the log_buffer size is related to parallel writes (i.e. the number of CPU’s)"

I don't see anything in that note about the log buffer size, or the number of CPUs. Moreover, the behaviour of "log file parallel writes" does not depend on the number of CPUs, it depends on the capacity for some form of asynchronous I/O. And what has "super-fast solid-state disk" got to do with how many LGWR processes exist ? Again this is just obfuscation to direct attention from the basic questions.

"MYTH? – The log_buffer should remain small."
Another attempt to direct attention away from the basic criticism. The default size for the ... no, wait, I've already said that - it's still true - I don't need to say it again. But note that the default size for the log buffer isn't "small".

"This is perpetuated with several Metalink notes that have become somewhat obsolete"
But you said that the Metalink references are authoritative - so why are you
allowed to decided which ones are really authoritative and which ones are obsolete ?

"Quoting from Metalink In 10.2 the log buffer is rounded up to use the rest of the granule. The granule size can be found from the hidden parameter "_ksmg_granule_size" and in your case is probably 16Mb"
I know that Metalink is "authoritative" when it suits you, but the author of this one doesn't seem to know (or, perhaps, doesn't want to say) much about In Memory Undo, private redo threads, and the possibility of multiple shared redo threads. There's a lot more to it than just the granule size. But, as I said in my original note: That's not particularly helpful for some one asking about a 9i database. And what's it got to do with the basic criticisms anyway ?

"MYTH? – Disk I/O speed is not the primary concern for tuning the log_buffer"
Yet another attempt to direct attention away from the basic criticism. But what a strange creation. (Does it make you think, by the way, that my comment about the slow I/Os from one of the AWR reports is on the mark ?)

And finally:
Don't you have anything to say about my comments on the bit you copied (I said "quoted" last time - so I thought I'd take the opportunity to say "copied" this time) from Osamu Kobayashi about transactions_per_rollback_segment. And what about my demonstration that the log buffer does not have to be a multiple of 2048 if you are using multiple block sizes in 9i ?

So let's summarize:
* Oracle doesn't recommend a maximum of 500KB or 128KB * CPU - that's just the default
* Oracle has recommended fairly large log buffers in the past - but even the 10g reference manual makes an inane comment about "In a busy system, a value 65536 or higher is reasonable."
* You don't get multiple LGWR processes
* You can get IO slave processes to assist LGWR, but only if you have configured your system to run with dbwr_io_slaves, or fiddle with a hidden parameter.
* transaction_per_rollback_segment is irrelevant as far as redo is concerned.
* the log buffer prior to 10.2 had to be a multiple of the redo block size, which was based on the O/S unit I/O size.
* Metalink is not "authoritative" - and if it contradicts itself it's a good idea to test its claims; and even when it hasn't contradicted itself it may be incomplete or wrong.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

Footnote: In the past, other members of the Oracle community have taken my technical criticisms of material written by Don Burleson as an excuse to pass rude, personal, and non-technical remarks about him. Accurate technical criticism is something to be encouraged. Abuse should not be tolerated.

Regarding the multiple log writer processes, I got this, from Steve Karam
(OCM):

According to Metalink Note 109582.1, you will see multiple log writer slaves as
ixnn processes. The note is for Oracle 8, but it’s a good description,
confirming that multiple log writer processes exist.

The representation of I/O Slaves is as follows: ora_ixnn_SID where

i= slave,
x= adaptor number and
nn is the slave number within the adaptor.

For example:

ora_i105_mul is a background I/O slave process.

Here:

i stands for a slave
1 stands for the adaptor number for this slave
05 is the slave number within the adaptor.

The valid ranges are:

x (adaptor number) -> 1 to 9 and A through F
nn (Slave number) -> 01..0z, 10..zz (Once slave number 0z is spawned, next
slaves are 10,11 and so on.)

An adaptor is a pool of memory allocated to a thread and the pool is allocated

a handle to be identified. The adaptor number is the handle returned by the
internal Oracle code.

There are different classes of adaptors and the pools by these classes are
totally exclusive to each other. For example, the IO pool for data buffers and

log buffers are different and hence these have separate adaptor numbers.

(Note: It is NOT necessary for the same process to use the same adaptor number
always. What this implies is that the adaptor numbers are not 'reserved' for
processes.)

Some of the classes for which adaptors exist are:

DBWR
LGWR
ARCH
Backup_tape_io
backup_disk_Io

...

For the other adaptors in 8i:
-----------------------------

These start on demand.

Thus a typical Unix ps -ef listing after instance startup may look like :

oracle 27582 1 0.0 11:16:40 ?? 0:44.03
ora_pmon_mul
oracle 27584 1 0.0 11:16:40 ?? 0:28.44 ora_ckpt_mul
oracle 27586 1 0.0 11:16:40 ?? 0:58.65 ora_dbw0_mul
oracle 27588 1 0.0 11:16:40 ?? 0:05.12 ora_lgwr_mul
oracle 27593 1 0.0 11:16:41 ?? 0:32.35 ora_smon_mul
oracle 27594 1 0.0 11:16:41 ?? 0:11.59 ora_arc0_mul
oracle 27596 1 0.0 11:16:41 ?? 0:00.35 ora_reco_mul
oracle 27598 1 0.0 11:16:48 ?? 0:17.42 ora_i201_mul
oracle 28408 1 0.0 11:35:25 ?? 0:06.47 ora_i102_mul
oracle 28411 1 0.0 11:35:25 ?? 0:06.59 ora_i101_mul
oracle 28412 1 0.0 11:35:25 ?? 0:06.10 ora_i103_mul
oracle 28414 1 0.0 11:35:25 ?? 0:06.03 ora_i104_mul
oracle 28416 1 0.0 11:35:25 ?? 0:06.14 ora_i105_mul
oracle 28420 1 0.0 11:35:25 ?? 0:06.01 ora_i106_mul

Here DBWR_IO_SLAVES=6.

The processes ora_i101 to ora_i106 are DBWR IO slaves and ora_i201
is the I/O slave for LGWR.

Note:

1. As there may not be substantial log writing taking place, only one LGWR IO
slave has been started initially. This may change when the activity increases.

2. The DBWR IO slaves may also not be immediately started with the instance, as
is clear from the timestamps of the processes.