The formula seems to be (at least in 12c because this is where I have validated
it) = (transactions )*(_log_private_parallelism_mul)/100
In my case, I have two different 12c databases and this formula is consistent
with the following query:
select count(*) from x$kcrfstrand where last_buf_kcrfa = '00' ;
Thanks
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Wednesday, May 18, 2016 4:00 PM
To: 'oracle-l-freelists' <oracle-l@xxxxxxxxxxxxx>
Subject: RE: LOG_BUFFER
Amir,
The number allocated is fixed at startup, but the number in use is dynamic.
In Oracle Core I've said that it's transactions/10 but I've just checked it on
a 12c instance and either I was wrong, or it's changed, or the allocation has
become dynamic - perhaps through a segmented array) because I've got
transactions = 519 and private threads = 14 (of which only 4 have been used)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Hameed, Amir [Amir.Hameed@xxxxxxxxx]
Sent: 18 May 2016 20:52
To: Jonathan Lewis; 'oracle-l-freelists'
Subject: RE: LOG_BUFFER
How is the count of Private Strands determined at instance startup? Also, does
this count remain static during the life of an instance or is it dynamic?
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Wednesday, May 18, 2016 3:33 PM
To: 'oracle-l-freelists' <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Subject: RE: LOG_BUFFER
That should have been 13M, not 1.3M.
In part, by the way, that's because the granule size for the SGA is currently
16M. If you're running with a larger SGA you could find that the granule size
is 64MB, 256 MB or 512MB, and the value for the log_buffer parameter will
probably be reported as slightly less than one granule by default. This may
then be split up in a number of public redo threads (related to dictated by
cpu_count), with extra memory taken from the one granule for private redo
threads (related to the transactions parameter) - unless you're running RAC in
which private redo is (was, the last time I checked) disabled.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Jonathan Lewis
Sent: 18 May 2016 16:54
To: 'oracle-l-freelists'
Subject: RE: LOG_BUFFER
The documentation on the log_buffer parameter has hardly changed since 8i -
although its degree of accuracy has changed significantly.
On my 2 CPU 12c instance at present I see that log_buffer has defaulted to
1.3M, though checking x$kcrfstrand I see this is really 2 public strands of
6.5M each.
The 1MB trigger is correct, though it's not in the documentation - it's easy to
demonstrate, though - and the documentation also doesn't point out that the log
writer will start a new write immediately after it finishes processing a write
if it finds that there is still some log waiting to be written; nor does the
documentation point out that processes can wake themselves up and detect that
their bit of log buffer has been written BEFORE the log writer gets around to
posting them - which is why the old threat of "log buffer too big" is not as
important as it used to be.
The statement of the 1MB being "not useful" claim pre-dates machines with 64
CPUs and a couple of hundred (actually) concurrent sessions, so you can (and
ought to) ignore that. It's actually important in busy systems to have a large
log buffer otherwise lots of sessions can end up waiting on "log buffer space".
The log writer can write asynchronously in multiple chunks of up to 1MB each -
so a "single" write can take some time, during which there may be lots of
sessions still working to fill the log buffer, and that's what a buffer is for.
(12c allows multiple log writer slaves - lg00 to lg99 - to help with very busy
systems rather than depending on async I/O).
1GB sounds big - but if you're not seeing much time lost on "log file sync"
waits (and assuming that CPU overload is not contributing to those waits) and
the typical duration of the log file sync waits is similar to the duration of
the log file parallel writes then its size is not the cause of a problem.
(Standard caveat: I didn't write the specification or code for the Oracle
program - so I may be missing something that I might notice if I had your
system running in front of me.)
If you really wanted to get some idea of whether or not the buffer was much
bigger than it needed to be you could monitor x$kcrfstrand (extremely
frequently) to keep checking how much of the public buffer was in use at any
instant - but I've never felt it necessary to do this so I have no idea whether
trying to do this MIGHT introduce a performance problem.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx] on behalf of Ram Cheruvattath
[ram.cheruvattath@xxxxxxxxx]
Sent: 18 May 2016 16:18
To: Mark W. Farnham; Hemant-K.Chitale@xxxxxx<mailto:Hemant-K.Chitale@xxxxxx>;
'oracle-l-freelists'
Subject: Re: LOG_BUFFER
Yes. I have not come across anything that says a large log_buffer is an issue
(other than wasting memory if not being used properly). Even the Oracle
documentation says that, though, as per the documentation, a size of more than
1MB is generally not useful. On one of Tom's articles I have read that LGWR
write redo out when it get's to 1MB regardless of other factors. I have not see
that in the documentation (12c) though. I wonder if that is why more than 1M is
not useful, though I would think that the rest of the space can still be used
by DML in progress while LGWR is writing.
Configuring the Redo Log Buffer
Server processes making changes to data blocks in the buffer cache generate
redo data into the log buffer. The log writer process (LGWR) begins writing to
copy entries from the redo log buffer to the online redo log if any of the
following conditions are true:
* The redo log buffer becomes at least one-third full
* LGWR is posted by a server process performing a COMMIT or ROLLBACK
* A database writer process (DBWR) posts LGWR to do so
Sizing the Redo Log Buffer
The default size of the redo log buffer is calculated as follows:
MAX(0.5M, (128K * number of cpus))
On most systems, sizing the log buffer larger than 1 MB does not provide any
performance benefit. However, increasing the size of the redo log buffer does
not have any negative implications on performance; it merely uses more memory.
More importantly, a modestly-sized redo log buffer can significantly enhance
throughput on systems that perform many updates. Applications that insert,
modify, or delete large volumes of data may require a change to the default
size of the redo log buffer.
To determine if the size of the redo log buffer is too small, monitor the redo
log buffer statistics, as described in "Using Redo Log Buffer
Statistics<http://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#GUID-772D48A0-FE2B-4A05-8826-3ADA25F4D7C5>".
You can also check if the log buffer space wait event is a significant factor
in the wait time for the database instance. If it is not, then the log buffer
size is most likely adequately-sized.
http://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA556
From: Mark W. Farnham<mailto:mwf@xxxxxxxx>
Sent: Wednesday, May 18, 2016 9:43 AM
To: Hemant-K.Chitale@xxxxxx<mailto:Hemant-K.Chitale@xxxxxx> ;
ram.cheruvattath@xxxxxxxxx<mailto:ram.cheruvattath@xxxxxxxxx> ;
'oracle-l-freelists'<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: LOG_BUFFER
If you never come close to filling the LOG_BUFFER before a flush event, then
the downside is occupying memory you never use.
I have *not observed* Oracle performance degrade due to having a large
LOG_BUFFER if there is no essential better use for the memory such as caching
large frequently referenced look up tables.
I usually safely oversize the LOG_BUFFER so that hitting the buffer one-third
full is not routinely the flushing event, but I've never needed as much as you
are currently using.
Once your LOG_BUFFER is big enough so you cannot write one-third of it before
the time triggered flush, I *believe* that constitutes a useful upper size
limit, even if your jobs are all single commit monoliths. (There may be other
useful upper size limits that are smaller, ergo the use of "a" not "the" in the
phrase "a useful upper size limit.")
*Someone let me know if that is not still at least theoretically correct.*
So while you *might* be "wasting" some memory, I'm not aware of any other harm.
I don't believe they will let you set it bigger than the program can handle.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Chitale, Hemant K
Sent: Wednesday, May 18, 2016 2:02 AM
To: ram.cheruvattath@xxxxxxxxx<mailto:ram.cheruvattath@xxxxxxxxx>;
oracle-l-freelists
Subject: RE: LOG_BUFFER
What kind of storage do you write to @4TB/hour ? How and when do you backup
and purge/delete the archivelogs.
I've seen log buffer exceeding 1GB.
Hemant K Chitale
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ram Cheruvattath
Sent: Wednesday, May 18, 2016 3:00 AM
To: oracle-l-freelists
Subject: LOG_BUFFER
Hi
We have a 12c database that has very high redo generation (4TB/hr). Our
log_buffer is set to 1GB.
Is there a downside to setting log_bugger this big?
Thanks
Ram
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html
