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

From: Jaco Polet <jaco.polet_at_gmail.com>
Date: Tue, 31 Jan 2006 11:11:33 +0100
Message-ID: <7536f2a20601310211o6987e0e1u@mail.gmail.com>

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jan 31 2006 - 04:11:33 CST