Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: lots of waiting on 'db file parallel write'
Jon,
thanks for the feedback.
We do have on average 10+ concurrrent processes that are doing lots
of IFFS and Tablescans...these
are simply large selects and hash joins...I also don't think this is
the problem because these processes
are running faster than ever..so nobody is complaining... Although we
are growing and more and
more customers are executing these types of queries..
I do have a pool of 20 parallel processes available in case a really
large request comes along...so we
do use all 20 parallel processes, but for the most part everything
else gets downgraded to serial
execution.
On the single block read front, we have lots of upload jobs... about a
month ago we switched to linux
servers and they are much faster..which I'm assuming translates to
more single block reads/waits..because these jobs lookup a single user
in 2-3 tables, then insert/update another 2-3 tables..then repeat...
and these run all day long. These jobs also need to lookup user
information in an index that is used by everything in our system... and
there is usually lots of waits on these datafiles... (this is expected
and we are working on removing this bottleneck).
We have some users that also insist on updating/clearing columns on multi-million record tables during the day and this is where the large amounts of redo comes from ... Occasionally we generate almost 1MB of redo/sec..which to me is alot.
I guess the overall picture here is that we are continuing to grow very rapidly and with that comes more and more reading/writing of data. We have a plan to reduce IO by 30-40% ..which will be removing a very large index that is shared by the entire application..this will reduce io significantly.. I'll also look at high reads/writes in V$SQL.
I have thought about increasing my buffer cache by 25% for the next 2 months until we make some changes to reduce IO. Do you see any problems with this approach? Received on Tue Feb 28 2006 - 08:46:05 CST