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@xxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *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@xxxxxxxxx> 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@xxxxxxxxx> 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