Christian Bilien’s Oracle performance and tuning blog

March 28, 2007

Log buffer issues in 10g (2/2)

Filed under: Oracle — christianbilien @ 7:29 pm

 

The first post on this topic was a quick recap of log buffer generalities. The second will address what’s new in 10gR2.

Part 2: Destaging the log buffer

First bullet: the log buffer is NOT tunable anymore in 10gR2 (whatever the LOG_BUFFER initialization parameter)

From Bug 4592994:

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”. The log buffer size and granule size can be read from v$sgainfo:

SQL> select * from v$sgainfo;

NAME BYTES RES

——————————– ———- —
Fixed SGA Size 2033832 No
Redo Buffers 14737408 No
Buffer Cache Size 3405774848 Yes
Shared Pool Size 520093696 Yes
Large Pool Size 16777216 Yes
Java Pool Size 33554432 Yes
Streams Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 3992977408 No
Startup overhead in Shared Pool 234881024 No

 

The log buffer can also be read from:

sql> select TOTAL_BUFS_KCRFA from x$kcrfstrand;

or from the lgwr log file (not the same instance):

ksfqxc:ctx=0x79455cf0 flags=0×30000000 dev=0×0 lbufsiz=1048576 bp=0×6497928

As a matter of fact, 10 or 16MB are huge values. Given the LGWR writes rule (see first post), LGWR will only begin performing background writes when the 1MB threshold is reached if no user transaction commits in the mean time. Log file parallel write are bound to appear.

This circumstance often appears in PL/SQL which contains COMMIT WORK: PL/SQL actually does commit, but does not wait for it. In other words, a COMMIT WAIT within PL/SQL is actually a COMMIT NOWAIT, but the wait is performed when the PL/SQL block or procedure exits to the calling program. This is where _LOG_IO_SIZE shows its usefulness: _LOG_IO_SIZE is a divisor of the log buffer size, and will trigger the LGWR to write whenever (log buffer size divided by _LOG_IO_SIZE > size of pending redo entries). The LGWR write threshold defaults in 9i to 1MB or 1/3 of the log buffer, whichever is less. kcrfswth from “oradebug call kcrfw_dump_interesting_data” is the threshold: it is expressed in OS block size, thus the current _LOG_IO_SIZE can be derived from kcrfswth.

LEBSZ from x$kccle is the operating system block size (512 bytes here).

SQL> oradebug setospid 3868;

Oracle pid: 11, Unix process pid: 3868, image: oracle@mymachine (LGWR)

SQL> oradebug unlimit

Statement processed.

SQL> oradebug call kcrfw_dump_interesting_data

SQL> oradebug tracefile_name

/oracle/10.2.0/admin/MYSID/bdump/eptdb1_lgwr_3868.trc

SQL> !grep kcrfswth /oracle/10.2.0/admin/MYSID/bdump/eptdb1_lgwr_3868.trc

kcrfswth = 2048

SQL> select max(LEBSZ) from x$kccle;

MAX(LEBSZ)

———-

512

SQL> select value from v$parameter where name = ‘log_buffer’;

VALUE

——————————————————————————–

14289920

SQL> select 14289920/512/2048 from dual;

14289920/512/2048

—————–

13.6279297

The threshhold will be about 1/13e of the log buffer size (roughly 1MB here).

About these ads

3 Comments »

  1. [...] problems for highly intensive DB. The first post is a quick recap of log buffer generalities, the second will focus on 10gR2 changes and offer some [...]

    Pingback by Log buffer issues in 10g (1/2) « Christian Bilien’s Oracle performance and tuning blog — April 10, 2007 @ 10:55 am

  2. Did you ever find out how Oracle calculated the LOG_BUFFER size in 10gR2 in case you do not specify it in the init.ora file?

    Because these are nt true:

    1. From Bug 4592994: 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.
    2. Documentation: Default value 512 KB or 128 KB * CPU_COUNT, whichever is greater

    Example:

    SQL> select * from v$sgainfo;

    NAME BYTES RESIZEABL
    ———————————————————————————————— ———- ———
    Fixed SGA Size 2046056 No
    Redo Buffers 6340608 No
    Buffer Cache Size 683671552 Yes
    Shared Pool Size 369098752 Yes
    Large Pool Size 4194304 Yes
    Java Pool Size 8388608 Yes
    Streams Pool Size 0 Yes
    Granule Size 4194304 No
    Maximum SGA Size 1073741824 No
    Startup overhead in Shared Pool 88080384 No
    Free SGA Memory Available 0

    11 rows selected.

    SQL> select STRAND_SIZE_KCRFA from X$KCRFSTRAND;

    STRAND_SIZE_KCRFA
    —————–
    3074048
    3074048

    SQL> show parameter CPU

    NAME TYPE VALUE
    ———————————— ——————————— ———
    cpu_count integer 8
    parallel_threads_per_cpu integer 2
    SQL>

    Why is it 6340608? How did Oracle calculate it?

    Comment by Julian Dontcheff — September 13, 2008 @ 10:42 am

  3. [...] regardless of the value of the value of the _LOG_IO_SIZE parameter. (pages 158-160 reference reference2 reference3 [...]

    Pingback by Book Review: Oracle Database 11g Performance Tuning Recipes « Charles Hooper's Oracle Notes — September 10, 2011 @ 9:14 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: