RE: LOG FILE SYNC wait event

From: John Hallas <john.hallas_at_bjss.co.uk>
Date: Tue, 4 Sep 2007 16:37:35 +0100
Message-ID: <E02CB9B2777CF8459C86C49B48C48EC60235685C@exchange.bjss.co.uk>

Log_buffer sometimes cannot be specified exactly as it depends on what chunk or granule of memory is available. See the notes below re granules

With dynamic SGA, the unit of allocation is called a granule. Components, such as the buffer cache, the shared pool, the java pool, and the large pool, allocate and free SGA space in units of granules. Oracle tracks SGA memory use in integral numbers of granules, by SGA component. All information about a granule is stored in a corresponding granule entry. Oracle maintains the state of each granule in the granule entry and the granule type.

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 128 MB, and it is 16 MB for larger SGAs. There may be some platform dependency, for example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 128 MB.

The granule size that is currently being used for SGA can be viewed in the view V$SGA_DYNAMIC_COMPONENTS. The same granule size is used for all dynamic components in the SGA.

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of DBA Deepak Sent: 04 September 2007 16:22
To: Alberto Dell'Era
Cc: oracle-l
Subject: Re: LOG FILE SYNC wait event

Hi Alberto,

Thanks for you help.

Did the following experiment

SQL> sho parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
sga_target                           big integer 100M

SQL> sho parameter log_buffer

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
log_buffer                           integer     2899456

SQL> alter system set log_buffer=500000 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 104857600 bytes

Fixed Size                  1246492 bytes
Variable Size              71305956 bytes
Database Buffers           29360128 bytes
Redo Buffers                2945024 bytes

Database mounted.
Database opened.

SQL> sho parameter log_buffer

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
log_buffer                           integer     2899456

SQL>

Just want to whether Oracle automatically increases the log_buffer value?

On 9/4/07, Alberto Dell'Era <alberto.dellera_at_gmail.com> wrote:

I've committed to memory this great explanation of LGWR processing:

http://kevinclosson.wordpress.com/2007/07/21/manly-men-only-use-solid-st ate-disk-for-redo-logging-lgwr-io-is-simple-but-not-lgwr-processing/

since you say that you can't lessen the commit frequency neither move to faster disks,
you might focus on reducing CPU starvation for the LGWR process, something that the blog entry (actually more a paper than a blog entry) discusses in detail. The author (Kevin Closson) suggests that this is very frequently one of the major contributor to the "log file sync" event - in the author's final test case, it was the *only* contributor (look at what happens when He disables logging at all at the end!)

BTW The log buffer is not managed by the Automatic Shared Memory Management
in 10gR2:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparam s192.htm

"The following pools are manually sized components and are not
affected by Automatic Shared Memory Management:

Log buffer ...
"
Anyway, an undersized log_buffer would make the processes wait for
"log buffer space" and not "log file sync". The former means "the
log buffer is full and I cannot write the changes I've made to the datafile blocks into it, so I'm waiting for some free log buffer space", the latter means "I've written the changes into the log buffer, and I'm waiting for

LGWR to persist them in the online redo logs files".

HTH
Alberto

On 9/3/07, DBA Deepak <oracle.tutorials_at_gmail.com> wrote:

> Hi All,
>
> We are having a lot of Log file sync waits because of frequent commits
> issued from the third party application. What are the solutions to fix

this

> apart from the follwing one...
>
> > To move the redo logs to faster disks(Not feasible in our case). 
>
> We are using AUTO SGA (10g R2) which can tune log buffer on its

own(Please

> correct me if I am wrong).
>
>
> --
> Regards,
>
> Deepak
> Oracle DBA

--
Alberto Dell'Era


"the more you know, the faster you go"





-- 
Regards,

Deepak
Oracle DBA 




BJSS Limited, 1st Floor Coronet House, Queen Street, Leeds LS1 2TW.
Registered in England with company number 2777575.
http://www.bjss.co.uk


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

Received on Tue Sep 04 2007 - 10:37:35 CDT