 | | | issue with wait on log buffer space. Oracle recommends enable write cache?? | issue with wait on log buffer space. Oracle recommends enable write cache?? 2006-01-31 - By Li-Shan Cheng
Can you try and see how fast is the disk where you placed the redo logs? Do
some dd to carry the test...
On 1/31/06, Jaco Polet <jaco.polet@(protected)> wrote:
>
> Tried this but it didn't improve
>
> 2006/1/31, Vlad Sadilovskiy <vlovsky@(protected)>:
> >
> > Solaris on UFS file systems may cause aync IO to be slow. Even though
> > you have set it to "forcedirectio"
> >
> > In that case it is not enough just to turn off disk_async_io. You might
> > want to try "_lgwr_ async_io"=FALSE
> >
> >
> > On 1/31/06, Jaco Polet <jaco.polet@(protected) > wrote:
> > >
> > > Anand
> > >
> > > Thanks for your feedback.
> > > My answers are inline
> > > I will test with a 2Mb log buffer
> > >
> > > Jaco
> > >
> > >
> > > 2006/1/31, Anand Rao <panandrao@(protected)>:
> > > >
> > > > *Hi,
> > > >
> > > > what was the size of your log_buffer and what is the current size?
> > > > you are only inserting about 32000 rows, so it shouldn't be a big deal.
how
> > > > frequent is this data load job? *
> > >
> > >
> > > We doubled the log buffer from 0.5 Mb to 1Mb. No effect.
> > > We only copied the data from one table to another. As the log shows
> > > it's only 32000 rows stored in +/- 10 Mb.
> > >
> > > *'log buffer space' waits are generally caused due to a small log
> > > buffer relative to the amount of redo you are generating.
> > >
> > > there is no direct reason why db_writer_processes has to be
> > > increased.....for 'log buffer space' waits. If you have configured
> > > Async IO on the OS side, then you should use disk_ async_io = TRUE.
> > > Are you using RAW volumes for your redologs and datafiles? else, it doesn
't
> > > make sense to turn on async IO.
> > > *
> > > **
> > > We changed disk_async_io to FALSE, set db_writer_processes to 2. We
> > > are not using
> > >
> > > *I can't remember too many problems with Solaris 8 Async driver, but
> > > you are better of checking it with your sysadmin as well searching
Metalink
> > > for any async IO related patches.
> > >
> > > If you are using UFS or VxFS for redo and datafiles, then what you
> > > need to do is reduce your Filesystem Buffer Cache. Are you using Direct IO
> > > or some new version of this 'near RAW performance Filesystem booster
> > > package' ? you need to consider all these filesytem config options before
> > > deciding to use Async IO.
> > > *
> > > We use Direct IO
> > > * *
> > > *When changed **you need to see if your Disk IO thoroughput is able to
> > > handle the workload generated. you may have very few and slow disks
causing
> > > LGWR to be slow writing data from the log buffer to the redolog files.
> > > * *
> > >
> > > use a 1MB log buffer and see what you get. it all depends on your redo
> > > rate.
> > >
> > > regards
> > > anand *
> > >
> > > >
> > > >
> > > > On 31/01/06, Jaco Polet < jaco.polet@(protected) > wrote:
> > > > >
> > > > > Hello,
> > > > >
> > > > > I have a general issue with loading data into a Oracle 9.2.0
.4database on solaris 8. I reduced it down to a simple statement which takes 1
> > > > > second on my PC (oracle 9.2.0.6) but takes 20 seconds on the
> > > > > 9.2.0.4 database on solaris.
> > > > > I traced it and the trace showed a 19 second wait on log buffer
> > > > > space. (see trace output)
> > > > > I increased the value of log_buffer and set disk_async_io=FALSE
> > > > > (increasing the db_writers) but this didn't make a difference
> > > > > On metalink I found note 263652.1 saying that this was a general
> > > > > issue on all platforms which could be fixed by switching on write
cache. (It
> > > > > doesn't state that is is fixed in a next version)
> > > > > It sounds strange to me that Oracle recommends to write the redo
> > > > > log to cache. Is this correct and if so how do I switch this on (the
> > > > > redologs are on local disks) ? I am also surprised that when
searching the
> > > > > internet I don't get any hits on this although it looks like a general
> > > > > issue. What am I missing?
> > > > >
> > > > > Hope someone can help me with this... Jaco Polet
> > > > >
> > > > > The formatted trace:
> > > > >
> > > > > insert into t_jpo select * from test_tabel
> > > > >
> > > > > call count cpu elapsed disk query
> > > > > current rows
> > > > > -- ---- -- --- -- ----- -- ---- -- -- ---- -- -- ---- --
> > > > > -- ---- -- -- ---- --
> > > > > Parse 1 0.01 0.00 0 0
> > > > > 0 0
> > > > > Execute 1 0.90 20.08 0 2920
> > > > > 7664 32114
> > > > > Fetch 0 0.00 0.00 0 0
> > > > > 0 0
> > > > > -- ---- -- --- -- ----- -- ---- -- -- ---- -- -- ---- --
> > > > > -- ---- -- -- ---- --
> > > > > total 2 0.91 20.09 0 2920
> > > > > 7664 32114
> > > > >
> > > > > Misses in library cache during parse: 1
> > > > > Optimizer goal: CHOOSE
> > > > > Parsing user id: 1340 (PERF)
> > > > >
> > > > > Rows Row Source Operation
> > > > > -- ---- -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
> > > > > 32114 TABLE ACCESS FULL TEST_TABEL (cr=1182 r=0 w=0 time=96619
> > > > > us)
> > > > >
> > > > > error during execute of EXPLAIN PLAN statement
> > > > > ORA-00942 (See ORA-00942.ora-code.com): table or view does not exist
> > > > >
> > > > > parse error offset: 109
> > > > >
> > > > > Elapsed times include waiting on following events:
> > > > > Event waited on Times Max. Wait
> > > > > Total Waited
> > > > > -- ---- ---- ---- ---- ---- ---- ---- -- Waited -- ---- --
> > > > > -- ---- ----
> > > > > log buffer space 43 1.00
> > > > > 19.18
> > > > > SQL*Net message to client 1 0.00
> > > > > 0.00
> > > > > SQL*Net message from client 1 2.49
> > > > > 2.49
> > > > > *********************************************************************
***********
> > > > >
> > > > >
> > > >
> > > >
> > >
> >
>
Can you try and see how fast is the disk where you placed the redo logs? Do
some dd to carry the test...<br><br><br><br><div><span class="gmail_quote">On 1
/31/06, <b class="gmail_sendername">Jaco Polet</b> <<a href="mailto:jaco
.polet@(protected)">
jaco.polet@(protected)</a>> wrote:</span><blockquote class="gmail_quote" style
="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding
-left: 1ex;">Tried this but it didn't improve<br><br>
<div><span class="gmail_quote">2006/1/31, Vlad Sadilovskiy <<a href="mailto
:vlovsky@(protected)" target="_blank" onclick="return top.js.OpenExtLink(window
,event,this)">vlovsky@(protected)</a>>:</span>
<div><span class="e" id="q_10920db69e419f88_1"><blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0px 0px 0px 0.8ex;
padding-left: 1ex;">Solaris on <span style="padding: 0pt; color: black;
background-color: yellow;">
UFS</span> file systems may cause aync IO to be slow. Even though you have set
it to "forcedirectio" <br><br>In that case it is not enough just to
turn off disk_async_io. You might want to try "_lgwr_<span style="padding:
0pt; color: black; background-color: yellow;">
async</span>_io"=FALSE
<div><span><br><br><br>
<div><span class="gmail_quote">On 1/31/06, <b class="gmail_sendername">Jaco
Polet</b> <<a href="mailto:jaco.polet@(protected)" target="_blank" onclick=
"return top.js.OpenExtLink(window,event,this)">jaco.polet@(protected)</a>
> wrote:</span>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204
); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>Anand</div>
<div> </div>
<div>Thanks for your feedback.</div>
<div>My answers are inline</div>
<div>I will test with a 2Mb log buffer</div>
<div> </div>
<div>Jaco</div>
<div><br> </div>
<div><span class="gmail_quote">2006/1/31, Anand Rao <<a href="mailto
:panandrao@(protected)" target="_blank" onclick="return top.js.OpenExtLink(window
,event,this)">panandrao@(protected)</a>>:</span> <span>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204
); margin: 0px 0px 0px 0.8ex; padding-left: 1ex;"><em>Hi,<br><br>what was the
size of your log_buffer and what is the current size? you are only inserting
about 32000 rows, so it shouldn't be a big deal. how frequent is this data load
job?
</em></blockquote>
<div> </div></span>
<div>We doubled the log buffer from 0.5 Mb to 1Mb. No effect. </div>
<div>We only copied the data from one table to another. As the log shows it's
only 32000 rows stored in +/- 10 Mb.</div><span>
<div> </div>
<div><em>'log buffer space' waits are generally caused due to a small log
buffer relative to the amount of redo you are generating. <br><br>there is no
direct reason why db_writer_processes has to be increased.....for 'log buffer
space' waits. If you have configured
<span style="padding: 0pt; color: black; background-color: yellow;">Async</span
> IO on the OS side, then you should use disk_<span style="padding: 0pt; color:
black; background-color: yellow;">
async</span>_io = TRUE. Are you using RAW volumes for your redologs and
datafiles? else, it doesn't make sense to turn on <span style="padding: 0pt;
color: black; background-color: yellow;">
async</span> IO. <br></em></div><em></em></span></div>
<div>
<div>We changed disk_<span style="padding: 0pt; color: black; background-color:
yellow;">async</span>_io to FALSE, set db_writer_processes to 2. We are not
using
</div><span>
<div><br><em>I can't remember too many problems with Solaris 8 <span style=
"padding: 0pt; color: black; background-color: yellow;">Async</span> driver, but
you are better of checking it with your sysadmin as well searching Metalink for
any
<span style="padding: 0pt; color: black; background-color: yellow;">async</span
> IO related patches.<br><br>If you are using UFS or VxFS for redo and datafiles
, then what you need to do is reduce your Filesystem Buffer Cache. Are you using
Direct IO or some new version of this 'near RAW performance Filesystem booster
package' ? you need to consider all these filesytem config options before
deciding to use
<span style="padding: 0pt; color: black; background-color: yellow;">Async</span
> IO. <br></em></div></span>
<div>We use Direct IO</div>
<div><em> </em></div>
<div><em>When changed </em><em>you need to see if your Disk IO thoroughput is
able to handle the workload generated. you may have very few and slow disks
causing LGWR to be slow writing data from the log buffer to the redolog files.
</em>
<div><span><em><br><br>use a 1MB log buffer and see what you get. it all
depends on your redo rate. <br><br>regards<br><span>anand</span> </em></span><
/div></div></div>
<div><span>
<div>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204
); margin: 0px 0px 0px 0.8ex; padding-left: 1ex;">
<div><span><br><br>
<div><span class="gmail_quote">On 31/01/06, <b class="gmail_sendername">Jaco
Polet</b> <<a href="mailto:jaco.polet@(protected)" target="_blank" onclick=
"return top.js.OpenExtLink(window,event,this)"> jaco.polet@(protected)
</a>> wrote:</span>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204
); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><font size="2"> </font>
<div>Hello,</div>
<div> </div>
<div>I have a general issue with loading data into a Oracle <a href="http://9.2
.0.4/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">9
.2.0.4</a> database on solaris 8. I reduced it down to a simple statement which
takes 1 second on my PC (oracle
<a href="http://9.2.0.6/" target="_blank" onclick="return top.js.OpenExtLink
(window,event,this)">9.2.0.6</a>) but takes 20 seconds on the <a href="http://9
.2.0.4/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
9.2.0.4</a> database on solaris.</div>
<div>I traced it and the trace showed a 19 second wait on log buffer space.
(see trace output)</div>
<div>I increased the value of log_buffer and set disk_<span style=
"padding: 0pt; color: black; background-color: yellow;">async</span>_io=FALSE
(increasing the db_writers) but this didn't make a difference
</div>
<div>On metalink I found note 263652.1 saying that this was a general issue on
all platforms which could be fixed by switching on write cache. (It doesn't
state that is is fixed in a next version)</div>
<div>It sounds strange to me that Oracle recommends to write the redo log to
cache. Is this correct and if so how do I switch this on (the
redologs are on local disks) ? I am also surprised that when searching the
internet I don't get any hits on this although it looks like a general issue.
What am I missing?
</div>
<div> </div>
<div>Hope someone can help me with this... Jaco Polet</div>
<div> </div>
<div>The formatted trace:</div>
<div> </div>
<div>insert into t_jpo select * from test_tabel</div>
<p>call count cpu
elapsed disk
query current
rows<br>-- ---- -- --- -- ----- -- ---- -- -- ---- -- -- ----
--- -- ---- -- -- ---- --<br>Parse
1 0.01
0.00 0
0
0 0
<br>Execute 1 0.90
20.08
0 2920
7664 32114<br>Fetch
0 0.00
0.00
0 0
0
0<br>-- ---- -- --- -- ----- -- ---- -- -- ------
- -- ---- -- -- ---- -- -- ---- --
<span></span><br>total 2
0.91 20.09
0 2920
7664
32114</p>
<p>Misses in library cache during parse: 1<br>Optimizer goal: CHOOSE<br>Parsing
user id: 1340 (PERF)</p>
<p>Rows Row Source Operation<br>-- ---- -- ------
-- ---- ---- ---- ---- ---- ---- ---- ----<br> 32114 TABLE ACCESS
FULL TEST_TABEL (cr=1182 r=0 w=0 time=96619 us)</p>
<p>error during execute of EXPLAIN PLAN statement<br>ORA-00942 (See ORA-00942.ora-code.com): table or view
does not exist</p>
<p>parse error offset: 109</p>
<p>Elapsed times include waiting on following events:<br> Event waited on
Times Max. Wait Total Waited<br> -- ---- --
-- ---- ---- ---- ---- ---- -- Waited -- ---- -- -- ----
-- --<br> log buffer space
43  
;
1.00 19.18<br> SQL*Net
message to client
1
0.00
0.00<br> SQL*Net message from client
1 2.49
2.49<br>***********************
*********************************************************
<br></p></blockquote></div><br></span></div></blockquote></div><br></span></div
></blockquote></div><br></span></div></blockquote></span></div></div><br>
</blockquote></div><br>
|
|
|