Tried this but it didn't improve
2006/1/31, Vlad Sadilovskiy <vlovsky_at_gmail.com>:
>
> Solaris on UFS file systems may cause aync IO to be slow. Even though you
> have set it to "forcedirectio"
>
> In that case it is not enough just to turn off disk_async_io. You might
> want to try "_lgwr_ async_io"=FALSE
>
>
> On 1/31/06, Jaco Polet <jaco.polet_at_gmail.com> wrote:
> >
> > Anand
> >
> > Thanks for your feedback.
> > My answers are inline
> > I will test with a 2Mb log buffer
> >
> > Jaco
> >
> >
> > 2006/1/31, Anand Rao <panandrao_at_gmail.com>:
> > >
> > > *Hi,
> > >
> > > what was the size of your log_buffer and what is the current size? you
> > > are only inserting about 32000 rows, so it shouldn't be a big deal. how
> > > frequent is this data load job? *
> >
> >
> > We doubled the log buffer from 0.5 Mb to 1Mb. No effect.
> > We only copied the data from one table to another. As the log shows it's
> > only 32000 rows stored in +/- 10 Mb.
> >
> > *'log buffer space' waits are generally caused due to a small log buffer
> > relative to the amount of redo you are generating.
> >
> > there is no direct reason why db_writer_processes has to be
> > increased.....for 'log buffer space' waits. If you have configured AsyncIO on the OS side, then you should use disk_async_io
> > = TRUE. Are you using RAW volumes for your redologs and datafiles? else, it
> > doesn't make sense to turn on async IO.
> > *
> > **
> > We changed disk_async_io to FALSE, set db_writer_processes to 2. We are
> > not using
> >
> > *I can't remember too many problems with Solaris 8 Async driver, but you
> > are better of checking it with your sysadmin as well searching Metalink for
> > any async IO related patches.
> >
> > If you are using UFS or VxFS for redo and datafiles, then what you need
> > to do is reduce your Filesystem Buffer Cache. Are you using Direct IO or
> > some new version of this 'near RAW performance Filesystem booster package' ?
> > you need to consider all these filesytem config options before deciding to
> > use Async IO.
> > *
> > We use Direct IO
> > * *
> > *When changed **you need to see if your Disk IO thoroughput is able to
> > handle the workload generated. you may have very few and slow disks causing
> > LGWR to be slow writing data from the log buffer to the redolog files. *
> > *
> >
> > use a 1MB log buffer and see what you get. it all depends on your redo
> > rate.
> >
> > regards
> > anand *
> >
> > >
> > >
> > > On 31/01/06, Jaco Polet < jaco.polet_at_gmail.com > wrote:
> > > >
> > > > Hello,
> > > >
> > > > I have a general issue with loading data into a Oracle 9.2.0.4database 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.4database 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 - 08:21:29 CST
