 | | | LOG FILE SYNC wait event | LOG FILE SYNC wait event 2007-09-04 - By John Hallas
Back
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-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@(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
<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft
-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http:
//www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@(protected)
{font-family:Tahoma;
panose-1 (See http://ose-1.ora-code.com):2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:blue;
text-decoration:underline;}
p
{mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman";}
code
{font-family:"Courier New";}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
p.bp, li.bp, div.bp
{mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman";}
@(protected) Section1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=blue>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>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<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=bp><font size=3 face="Times New Roman"><span style='font-size:12.0pt'
>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.<o:p></o:p></span></font></p>
<p class=bp><a name=22304></a><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>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.<o:p></o:p></span></font></p>
<p class=bp><a name=22394></a><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>The granule size that is currently being used for SGA
can be viewed in the view </span></font><code><font size=2 face="Courier New">
<span
style='font-size:10.0pt'>V$SGA_DYNAMIC_COMPONENTS</span></font></code>. The
same granule size is used for all dynamic components in the SGA.<o:p></o:p></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <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><o:p></o:p></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Hi Alberto,<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Thanks for you help.<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Did the following experiment<o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>SQL>
sho parameter sga_target<o:p></o:p></span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>NAME 
;
TYPE VALUE<br>
-- ---- ---- ---- ---- ---- ---- --- -- ---- --- -- ---- ---- ---- ---- ---- --
<br>
sga_target
big integer 100M<o:p></o:p></span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>SQL>
sho parameter log_buffer<o:p></o:p></span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>NAME 
;
TYPE VALUE<br>
-- ---- ---- ---- ---- ---- ---- --- -- ---- --- -- ---- ---- ---- ---- ---- --
<br>
log_buffer
integer 2899456<br>
SQL> alter system set log_buffer=500000 scope=spfile; <o:p></o:p></span><
/font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>System
altered.<o:p></o:p></span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>SQL>
shutdown immediate<br>
Database closed.<br>
Database dismounted.<br>
ORACLE instance shut down.<br>
SQL> startup<br>
ORACLE instance started.<o:p></o:p></span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>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.<o:p></o:p></span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>SQL>
sho parameter log_buffer<o:p></o:p></span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>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><o:p></o:p></span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>=========
=========================================================<o:p></o:p></span><
/font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>Just want
to whether Oracle automatically increases the log_buffer value? <o:p></o:p
></span></font></p>
</div>
<div>
<p class=MsoNormal><span class=gmailquote><font size=3 face="Times New Roman">
<span
style='font-size:12.0pt'>On 9/4/07, <b><span style='font-weight:bold'>Alberto
Dell'Era</span></b> <<a href="mailto:alberto.dellera@(protected)">alberto
.dellera@(protected)</a>>
wrote:</span></font></span> <o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>I've committed to memory this great explanation of LGWR processing:<br>
<br>
<a
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/">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
href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14237
/initparams192.htm">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 href="mailto:oracle.tutorials@(protected)">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"<o:p></o:p></span></font></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><br>
<br clear=all>
<br>
-- <br>
Regards,<br>
<br>
Deepak<br>
Oracle DBA <o:p></o:p></span></font></p>
</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>
http://www.bjss.co.uk<BR>
</P></DIV>
</body>
</html>
|
|
|