Re: issue with wait on log buffer space. Oracle recommends enable write cache??

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_at_gmail.com> 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
--
http://www.freelists.org/webpage/oracle-l