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@(protected)>: > > 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@(protected)> 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 (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 > > > ***************************************************************************** *** > > > > > -- > Christo Kutrovsky > Senior Database/System Administrator > The Pythian Group >
<div>Christo,</div> <div> </div> <div>The basic problem was the async_io. By using the problem with waiting for log buffer space was solved.</div> <div> </div> <div>Everybody thanks for yout input</div> <div> </div> <div>Jaco</div> <div><br><br> </div> <div><span class="gmail_quote">2006/1/31, Christo Kutrovsky <<a href="mailto :kutrovsky.oracle@(protected)">kutrovsky.oracle@(protected)</a>>:</span> <blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0 .8ex; BORDER-LEFT: #ccc 1px solid">Are you using dedicated redo disk drive?<br >How much redo are you generating for this 1 statement ?<br><br> 2mb is still pretty small. I usually set it quite large to handle<br>spikes. I usually target 1-2 seconds of the disk speed where my redo<br>is sitting.<br> <br>Remeber that redo buffer is "zoned" based on the number of CPUs. You <br>want each zone to be at least 500 kb to allow some nice big write<br >requests.<br><br>For reference, a modern single disk drive is capable at doing 50<br>Mb/sec sequencially.<br><br>The specific machine I have in mind has 8gb RAM, 2 15k rpm disk drives <br>in mirror, and produces 58 Mb/sec. my log_buffer is 45mb and I have 8<br >cpus (4 with ht). Usually my write request is in the 1 mb range when<br>doing data loads (when not CPU limited).<br><br>--<br>Christo Kutrovsky<br> Senior Database/System Administrator<br>The Pythian Group<br><br>On 1/31/06, Jaco Polet <<a href="mailto:jaco.polet@(protected)">jaco.polet@(protected)</a> > wrote:<br>><br>> Hello,<br>><br>> I have a general issue with loading data into a Oracle <a href="http://9.2.0.4">9.2.0.4</a> database on<br>> solaris 8. I reduced it down to a simple statement which takes 1 second on<br>> my PC (oracle <a href="http://9.2.0.6">9.2.0.6</a>) but takes 20 seconds on the <a href="http:/ /9.2.0.4"> 9.2.0.4</a> database on<br>> solaris.<br>> I traced it and the trace showed a 19 second wait on log buffer space. (see<br>> trace output)<br>> I increased the value of log_buffer and set disk_async_io=FALSE (increasing <br>> the db_writers) but this didn't make a difference<br>> On metalink I found note 263652.1 saying that this was a general issue on<br>> all platforms which could be fixed by switching on write cache. (It doesn't <br>> state that is is fixed in a next version)<br>> It sounds strange to me that Oracle recommends to write the redo log to<br>> cache. Is this correct and if so how do I switch this on (the redologs are<br>> on local disks) ? I am also surprised that when searching the internet I <br>> don't get any hits on this although it looks like a general issue. What am I<br>> missing?<br>><br>> Hope someone can help me with this.. . Jaco Polet<br>><br>> The formatted trace:<br>><br>> insert into t _jpo select * from test_tabel <br>><br>> call count cpu elapsed disk query   ;current<br>> rows<br>> -- ---- -- --- -- ----- -- ---- -- ---- -- --- -- ---- -- -- ---- --<br>> -- ---- --<br>> Parse 1 0.01 0.00 0 0 0<br> > 0<br>> Execute 1 0.90 20.08  ; 0 2920 7664<br>> 32114<br>> Fetch 0 0.00 0.00   ; 0 0 0 <br>> 0<br>> -- ---- -- --- -- ----- -- ---- -- -- -- ----- -- ---- -- -- ---- --<br>> -- ---- --<br>> total   ; 2 0.91 20.09 0 2920 7664<br>> 32114<br>><br>> Misses in library cache during parse: 1 <br>> Optimizer goal: CHOOSE<br>> Parsing user id: 1340 (PERF) <br>><br>> Rows Row Source Operation<br>> -- -- --<br>> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---<br>> 32114 TABLE ACCESS FULL TEST_TABEL (cr=1182 r=0 w=0 time=96619 us) <br>><br>> error during execute of EXPLAIN PLAN statement<br>> ORA -00942: table or view does not exist<br>><br>> parse error offset: 109<br> ><br>> Elapsed times include waiting on following events:<br> > Event waited on   ; Times Max. Wait Total<br>> Waited<br>> -- ---- ---- ---- ---- ---- ----- -- ---- Waited<br>> -- ---- -- -- ---- ----<br>> log buffer space 43 <br>> 1.00 19.18<br>> SQL*Net message to client 1 0.00<br>> 0.00 <br>> SQL*Net message from client 1 2.49<br>> 2.49 <br>> *********************************************************************** ********* <br>><br><br><br>--<br>Christo Kutrovsky<br>Senior Database/System Administrator<br>The Pythian Group<br></blockquote></div><br>
