 | | | LOG FILE SYNC wait event | LOG FILE SYNC wait event 2007-09-04 - By DBA Deepak
Back
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
<div>Hi Alberto,</div>
<div> </div>
<div>Thanks for you help.</div>
<div> </div>
<div>Did the following experiment</div>
<div> </div>
<div>
<p>SQL> sho parameter sga_target</p>
<p>NAME
TYPE
VALUE<br>-- ---- ---- ---- ---- ---- ---- --- -- ---- --- -- ------
-- ---- ---- ---- ---<br>sga_target
big integer 100M</p>
<p>SQL> sho parameter log_buffer</p>
<p>NAME
TYPE
VALUE<br>-- ---- ---- ---- ---- ---- ---- --- -- ---- --- -- ------
-- ---- ---- ---- ---<br>log_buffer
integer 2899456<br
>SQL> alter system set log_buffer=500000 scope=spfile;
</p>
<p>System altered.</p>
<p>SQL> shutdown immediate<br>Database closed.<br>Database dismounted.<br
>ORACLE instance shut down.<br>SQL> startup<br>ORACLE instance started.</p>
<p>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.</p>
<p>SQL> sho parameter log_buffer</p>
<p>NAME
TYPE
VALUE<br>-- ---- ---- ---- ---- ---- ---- --- -- ---- --- -- ------
-- ---- ---- ---- ---<br>log_buffer
integer <strong
>2899456<br></strong>
SQL></p>
<p>==================================================================</p>
<p>Just want to whether Oracle automatically increases the log_buffer value?
<br></p></div>
<div><span class="gmail_quote">On 9/4/07, <b class="gmail_sendername">Alberto
Dell'Era</b> <<a href="mailto:alberto.dellera@(protected)">alberto.dellera
@(protected)</a>> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">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)
.com">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"
<br></blockquote></div><br><br clear="all"><br>-- <br>Regards,<br><br>Deepak<br
>Oracle DBA
|
|
|