 | | | LOG FILE SYNC wait event | LOG FILE SYNC wait event 2007-09-04 - By DBA Deepak
Back
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@(protected)> 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@(protected) [mailto:
> oracle-l-bounce@(protected)] *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@(protected)> 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@(protected)> 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
<div>Hi John,</div>
<div> </div>
<div>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?
</div>
<div>If yes, what could be the minimum granule size?</div>
<div><br><br> </div>
<div><span class="gmail_quote">On 9/4/07, <b class="gmail_sendername">John
Hallas</b> <<a href="mailto:john.hallas@(protected)">john.hallas@(protected)<
/a>> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">
<div lang="EN-US" vlink="blue" link="blue">
<div>
<p><font face="Arial" color="navy" size="2"><span style="FONT-SIZE: 10pt; COLOR
: navy; FONT-FAMILY: Arial">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
</span></font></p>
<p><font face="Arial" color="navy" size="2"><span style="FONT-SIZE: 10pt; COLOR
: navy; FONT-FAMILY: Arial"> </span></font></p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">With
dynamic SGA, the unit of allocation is called a <em><i><font face="Times New
Roman">granule</font></i></em>. <em><i><font face="Times New Roman">
Components</font></i></em>, 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
<em><i><font face="Times New Roman">granule entry</font></i></em>. Oracle
maintains the state of each granule in the granule entry and the granule type.<
/span></font></p>
<p><a name="114d12b6e5870dbb_22304"></a><font face="Times New Roman" size="3">
<span style="FONT-SIZE: 12pt">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.
</span></font></p>
<p><a name="114d12b6e5870dbb_22394"></a><font face="Times New Roman" size="3">
<span style="FONT-SIZE: 12pt">The granule size that is currently being used for
SGA can be viewed in the view </span></font><code><font face="Courier New" size
="2">
<span style="FONT-SIZE: 10pt">V$SGA_DYNAMIC_COMPONENTS</span></font></code>.
The same granule size is used for all dynamic components in the SGA.</p>
<p><font face="Arial" color="navy" size="2"><span style="FONT-SIZE: 10pt; COLOR
: navy; FONT-FAMILY: Arial"> </span></font></p>
<p><font face="Arial" color="navy" size="2"><span style="FONT-SIZE: 10pt; COLOR
: navy; FONT-FAMILY: Arial"> </span></font></p>
<div>
<div style="TEXT-ALIGN: center" align="center"><font face="Times New Roman"
size="3"><span style="FONT-SIZE: 12pt">
<hr align="center" width="100%" size="2">
</span></font></div>
<p><b><font face="Tahoma" size="2"><span style="FONT-WEIGHT: bold; FONT-SIZE:
10pt; FONT-FAMILY: Tahoma">From:</span></font></b><font face="Tahoma" size="2">
<span style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> <a onclick="return top.js
.OpenExtLink(window,event,this)" href="mailto:oracle-l-bounce@(protected)"
target="_blank">
oracle-l-bounce@(protected)</a> [mailto:<a onclick="return top.js.OpenExtLink
(window,event,this)" href="mailto:oracle-l-bounce@(protected)" target="_blank"
>oracle-l-bounce@(protected)</a>] <b><span style="FONT-WEIGHT: bold">
On Behalf Of </span></b>DBA Deepak<br><b><span style="FONT-WEIGHT: bold">Sent:<
/span></b> 04 September 2007 16:22<br><b><span style="FONT-WEIGHT: bold">To:<
/span></b> Alberto Dell'Era<br><b><span style="FONT-WEIGHT: bold">
Cc:</span></b> oracle-l<br><b><span style="FONT-WEIGHT: bold">Subject:</span><
/b> Re: LOG FILE SYNC wait event</span></font></p></div>
<div><span class="q" id="q_114d12b6e5870dbb_1">
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt"> <
/span></font></p>
<div>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">Hi
Alberto,</span></font></p></div>
<div>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt"> <
/span></font></p></div>
<div>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">Thanks
for you help.</span></font></p></div>
<div>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt"> <
/span></font></p></div>
<div>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">Did the
following experiment</span></font></p></div>
<div>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt"> <
/span></font></p></div>
<div>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">SQL>
sho parameter sga_target</span></font></p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">NAME
TYPE
VALUE<br>-- ---- ---- ---- ---- ---- ---- --- -- ---- --- -- ---- ---- --
-- ---- ---- --<br>sga_target
big integer 100M
</span></font></p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">SQL>
sho parameter log_buffer</span></font></p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">NAME
TYPE
VALUE<br>-- ---- ---- ---- ---- ---- ---- --- -- ---- --- -- ---- ---- --
-- ---- ---- --<br>log_buffer
integer 2899456
<br>SQL> alter system set log_buffer=500000 scope=spfile; </span></font></p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">System
altered.</span></font></p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">SQL>
shutdown immediate<br>Database closed.<br>Database dismounted.<br>ORACLE
instance shut down.<br>SQL> startup<br>ORACLE instance started.</span>
</font></p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">Total
System Global Area 104857600 bytes<br>Fixed Size
1246492 bytes<br>Variable Size
71305956 bytes<br>Database Buffers
29360128 bytes
<br>Redo Buffers
2945024 bytes <br>Database mounted.<br>Database
opened.</span></font></p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">SQL>
sho parameter log_buffer</span></font></p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">NAME
TYPE
VALUE<br>-- ---- ---- ---- ---- ---- ---- --- -- ---- --- -- ---- ---- --
-- ---- ---- --<br>log_buffer
integer
<strong><b><font face="Times New Roman">2899456</font></b></strong><b><span
style="FONT-WEIGHT: bold"><br></span></b>SQL></span></font></p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">========
==========================================================</span></font></p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">Just
want to whether Oracle automatically increases the log_buffer value? <
/span></font></p></div>
<div>
<p><span><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">On
9/4/07, <b><span style="FONT-WEIGHT: bold">Alberto Dell'Era</span></b> <
<a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:alberto
.dellera@(protected)" target="_blank">
alberto.dellera@(protected)</a>> wrote:</span></font></span> </p>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt">I've
committed to memory this great explanation of LGWR processing:<br><br><a
onclick="return top.js.OpenExtLink(window,event,this)" href="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/" target="_blank">
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/</a><br><br
>since you say that you can't lessen the commit frequency<br>neither move to
faster disks,
<br>you might focus on reducing CPU starvation for the LGWR<br>process,
something that the blog entry (actually more a paper than<br>a blog entry)
discusses in detail. The author (Kevin Closson)<br>suggests that this is very
frequently one of the major contributor
<br>to the "log file sync" event - in the author's final test
case,<br>it was the *only* contributor (look at what happens when<br>He
disables logging at all at the end!)<br><br>BTW The log buffer is not managed
by the Automatic Shared Memory Management
<br>in 10gR2:<br><br><a onclick="return top.js.OpenExtLink(window,event,this)"
href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14237
/initparams192.htm" target="_blank">http://download.oracle.com/docs/cd/B19306_01
/server.102/b14237/initparams192.htm
</a><br><br>"The following pools are manually sized components and are not
<br>affected by Automatic Shared Memory Management:<br>
* Log buffer<br> ...<br>"<br>Anyway, an undersized
log_buffer would make the processes wait for
<br>"log buffer space" and not "log file sync". The former
means "the <br>log buffer is full and I cannot write the changes I've
made to the datafile<br>blocks into it, so I'm waiting for some free log
buffer space", the latter
<br>means "I've written the changes into the log buffer, and I'm
waiting for <br>LGWR to persist them in the online redo logs files".<br>
<br>HTH<br>Alberto<br><br>On 9/3/07, DBA Deepak <<a onclick="return top.js
.OpenExtLink(window,event,this)" href="mailto:oracle.tutorials@(protected)" target
="_blank">
oracle.tutorials@(protected)</a>> wrote:<br>> Hi All,<br>><br>> We
are having a lot of Log file sync waits because of frequent commits<br>>
issued from the third party application. What are the solutions to fix this
<br>> apart from the follwing one...<br>><br>> > To move the redo
logs to faster disks(Not feasible in our case). <br>><br>> We are using
AUTO SGA (10g R2) which can tune log buffer on its own(Please<br>
> correct me if I am wrong).<br>><br>><br>> --<br>> Regards,<br>
><br>> Deepak<br>> Oracle DBA<br><br><br>--<br>Alberto Dell'Era<br>
"the more you know, the faster you go"</span></font>
</p></div>
<p><font face="Times New Roman" size="3"><span style="FONT-SIZE: 12pt"><br><br
clear="all"><br>-- <br>Regards,<br><br>Deepak<br>Oracle DBA </span></font></p><
/span></div></div>
<div>
<p>
<hr>
<br>BJSS Limited, 1st Floor Coronet House, Queen Street, Leeds LS1 2TW.<br
>Registered in England with company number 2777575.<br><a onclick="return top.js
.OpenExtLink(window,event,this)" href="http://www.bjss.co.uk/" target="_blank">
http://www.bjss.co.uk</a><br>
<p></p></p></div></div></blockquote></div><br><br clear="all"><br>-- <br
>Regards,<br><br>Deepak<br>Oracle DBA
|
|
|