Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
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 Jaco Polet

Reply:     1     2     3     4     5     6     7     8     9  

 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 (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
********************************************************************************

<font size="2">&nbsp;</font>
<div>Hello,</div>
<div>&nbsp;</div>
<div>I have a general issue with loading data into a Oracle <a onclick="return
top.js.OpenExtLink(window,event,this)" href="http://9.2.0.4/" target="_blank">9
.2.0.4</a> database on solaris 8. I reduced it down to a simple statement which
&nbsp;takes 1 second on my PC (oracle
<a onclick="return top.js.OpenExtLink(window,event,this)" href="http://9.2.0.6/
" target="_blank">9.2.0.6</a>) but takes 20 seconds on the <a onclick="return
top.js.OpenExtLink(window,event,this)" href="http://9.2.0.4/" target="_blank">
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&nbsp;value of log_buffer and&nbsp;set disk_async_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&nbsp;&nbsp;(the
redologs are on local disks)&nbsp;? 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>&nbsp;</div>
<div>Hope someone can help me with this... Jaco Polet</div>
<div>&nbsp;</div>
<div>The formatted trace:</div>
<div>&nbsp;</div>
<div>insert into t_jpo select * from test_tabel</div>
<p>call&nbsp;&nbsp;&nbsp;&nbsp; count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cpu
&nbsp;&nbsp;&nbsp; elapsed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; disk&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp; query&nbsp;&nbsp;&nbsp; current&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp; rows<br>-- ---- -- ---&nbsp; -- ----- -- ---- -- -- ---- -- -- ----
--- -- ---- --&nbsp; -- ---- --<br>Parse&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0
<br>Execute&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.90
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20.08&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2920&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp; 7664&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32114<br>Fetch&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.00&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp; 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp; 0<br>-- ---- -- ---&nbsp; -- ----- -- ---- -- -- ------
- -- ---- -- -- ---- --&nbsp; -- ---- --
<span></span> <br>total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp; 0.91&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20.09&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2920
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7664&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
32114</p>
<p>Misses in library cache during parse: 1<br>Optimizer goal: CHOOSE<br>Parsing
user id: 1340&nbsp; (PERF)</p>
<p>Rows&nbsp;&nbsp;&nbsp;&nbsp; Row Source Operation<br>-- ----&nbsp; -- ------
-- ---- ---- ---- ---- ---- ---- ---- ----<br>&nbsp; 32114&nbsp; 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>&nbsp; Event waited on
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp; Times&nbsp;&nbsp; Max. Wait&nbsp; Total Waited<br>&nbsp; -- ---- --
-- ---- ---- ---- ---- ---- --&nbsp;&nbsp; Waited&nbsp; -- ---- --&nbsp; -- ----
-- --<br>&nbsp; log buffer space&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 43&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;
1.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19.18<br>&nbsp; SQL*Net
message to client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp; 0.00<br>&nbsp; SQL*Net message from client&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.49&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.49<br>***********************
*********************************************************
<br></p>