Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: lots of waiting on 'db file parallel write'

Re: lots of waiting on 'db file parallel write'

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 28 Feb 2006 12:57:55 +0000 (UTC)
Message-ID: <du1hcj$dt9$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com>

> Folks,
> I'm currently trying to get to the bottom of why my 4 database
> writters spend so much time
> waiting on 'db file parallel write'... even when it appears that the
> system is not terribly busy.
>
> Environment:
> Oracle 10.1.0.4
> Solaris 8 - 64bit
> IBM SAN T700
> Veritas filesystem (mostly everything is 0+1 raid with a few luns at
> raid 5).
> 4 db writters
>

You can't ignore the physical reads when reporting a problem about physical writes.

Your report shows 5,400 reads per second, and 500 writes per second. Call that 6,000 I/O requests per second, and assume they are extremely random. A typical modern disc may be able to cope with 100 I/Os per second. Do you have at least 60 different spindles dedicated to your system. If not, you have to address the reads.

Your comment in another post about mostly tablescans and index fast full scans doesn't seem to be consistent with this extract - you have FAR more single block waits than multiblock waits, and scans tend to be multiblock.

Of course, you also have PX Deq waits - which might indicate that you are also doing a lot of parallel operation - which can chew up CPU, but could be yet another load on your I/O. It is possible that the direct path read scans are using user-level async reads, and therefore never (or rarely) showing up with wait times on the stats.

Try to pick on a couple of individual sessions, preferably based on the program they operate, and see if you can identify extreme behaviour patterns worth investigating. Alternatively, fall back on v$sql and look for statements which are using far more elapsed time than CPU time, or doing very large numbers of I/Os for the volume of data processed.

I would also check for sessions generating a lot of redo. It is possible that your batch-like loads are doing something very inefficient that is making matters worse than they need to be.

Just as a side thought - keep an eye on any Veritas processes that are running. I did once see an unlikely configuration issue cause a large number of CPU-intensive veritas processes kill the system by competing with each other for direct I/O access.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Received on Tue Feb 28 2006 - 06:57:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US