Re: disk_async_io vs. multiple db_writer_processes

  • From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
  • To: pele_00_2000@xxxxxxxxx
  • Date: Sat, 04 Mar 2006 02:01:34 -0500

On 03/03/2006 11:59:58 PM, TP wrote:
> Hello all:
> I would like to ask for your expert opinions on
> the topic of using disk_async_io, and the use of
> multiple db_writer_processes.  We're running
> Oracle E-business suites 11.5.10(9i-9205-32bit)
> on Sun E6900, 12 CPUs, 48GB RAM, Solaris 9/64bit.

Hello there. If I am not mistaken, you're using 32 bit Oracle on
64 bit OS? Why is that?

>  
> 
> By default, disk_async_io, filesystemio_options;
> db_writer_processes are default to TRUE, ASYNC,
> and 3.  The database files(redologs, datafiles,
> controlfiles) are allocated on Sun 3510 storage
> arrays(Hardware RAID10). The UFS filesystems are
> mounted with forcedirectio option.  

Great! How many buffer chain latch waits do you have? Did the 
addition of two more DB writers decrease those waits? 
How big are redo logs? How frequently are they switched?
Are waits on redo log events causing a problem? How
many random I/O requests per second can you raid fulfill?

> 
> I would like to get your suggestions or
> recommendations on which option is best for my
> environment.
> Thank you in advance.
> Tom 

Tom, it's very hard to tell you what is the best. What is the criteria 
for "good", "better" and "best"? Do you have a performance problem, apart
from @#$! snow in March?
As a long time DBA, I can tell you one thing: the main causes for the
instance performance problems are users and data. The best thing to do
is to remove users and data from the picture and your instance will never
have any performance problems. I realize that this might not be what you
wanted to hear, but this is a cruel world and I am a DBA.
You add or remove database writers based on cache buffers lru chain latch
waits. If you are waiting for a transaction enqueue, increasing the number
of database writers is unlikely to help you. In the world of the performance
analysis, there are two rules to remember:

1) You are tuning an application, not an instance.
2) In case of doubt, repeat the 1st rule.

So, when tuning an application system, you first have to ask what aspect of the 
application system is to be tuned. Usually, it's the response time, but not 
necessarily so. So, in order to decrease the time necessary for the application
to respond to the user request, you have to find out where is the time spent.
The time is usually, but not always, spent waiting. Fortunately, Oracle version
9.2.0.5 will tell you (V$SESSTSAT) how much CPU time was spent by the observed
session. Now that you know where is the time spent, you can try decreasing the
time spent waiting and processing. It usually ends with tuning SQL. It is really
strange and surprising that in tuning database applications, tuning SQL is of
an utmost importance. There is a great paper by Stephane Faroult of the Oriole 
Corp. named "It's the algorithm, stupid", published on NYOUG site in 2003:

http://www.nyoug.org/200309faroult.pdf

In the paper Stephane deals with the things like computing counts when all that 
it is needed is prof of an existence and re-writing relation logic into 
procedural
by using PL/SQL. This will probably do more for your performance then adding few
more DBWR processes and extending your shared pool.

There is also a very little known book which describes in detail Oracle 
performance analysis. The title of the book makes it sort of hard to guess
what is it all about. The title is: Optimizing Oracle Performance, by 
Millsap & Holt, published by O'Reilly. It has some flying pest with a 
stinger on the cover. In addition to some mathematical background of the 
whole performance business, the authors will also give you the philosophical 
background, starting with "cogito, ergo sum" and then building on top of 
that. I can only recommend the book.  
All of that means that I can give you one answer to to your question: 42.


-- 
Mladen Gogala
http://www.mgogala.com

--
//www.freelists.org/webpage/oracle-l


Other related posts: