Re: LOG FILE SYNC wait event

Hi John,

As per your notes on 32-it windows the granule size is 4M, which I verified on my system. But the log_buffer size that Oracle did set (in my example below) is not equal to the granule size. Can you please explain a bit more on the method oracle used to allocate that much of memory for log_buffer. Does Oracle use different granule sizes for different SGA components? If yes, what could be the minimum granule size?

On 9/4/07, John Hallas <john.hallas_at_bjss.co.uk> wrote:
>
> 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-state-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/initparams192.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
>
>

-- 
Regards,

Deepak
Oracle DBA

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