Re: issue with wait on log buffer space. Oracle recommends enable write cache??
- From: Jaco Polet <jaco.polet@xxxxxxxxx>
- To: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- Date: Wed, 1 Feb 2006 11:11:18 +0100
Christo,
The basic problem was the async_io. By using the problem with waiting for
log buffer space was solved.
Everybody thanks for yout input
Jaco
2006/1/31, Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>:
>
> Are you using dedicated redo disk drive?
> How much redo are you generating for this 1 statement ?
>
> 2mb is still pretty small. I usually set it quite large to handle
> spikes. I usually target 1-2 seconds of the disk speed where my redo
> is sitting.
>
> Remeber that redo buffer is "zoned" based on the number of CPUs. You
> want each zone to be at least 500 kb to allow some nice big write
> requests.
>
> For reference, a modern single disk drive is capable at doing 50
> Mb/sec sequencially.
>
> The specific machine I have in mind has 8gb RAM, 2 15k rpm disk drives
> in mirror, and produces 58 Mb/sec. my log_buffer is 45mb and I have 8
> cpus (4 with ht). Usually my write request is in the 1 mb range when
> doing data loads (when not CPU limited).
>
> --
> Christo Kutrovsky
> Senior Database/System Administrator
> The Pythian Group
>
> On 1/31/06, Jaco Polet <jaco.polet@xxxxxxxxx> wrote:
> >
> > Hello,
> >
> > I have a general issue with loading data into a Oracle 9.2.0.4 database
> 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: 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
> >
> ********************************************************************************
> >
>
>
> --
> Christo Kutrovsky
> Senior Database/System Administrator
> The Pythian Group
>
Other related posts:
