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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: disk_async_io vs. multiple db_writer_processes

Re: disk_async_io vs. multiple db_writer_processes

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 04 Mar 2006 02:01:34 -0500
Message-Id: <1141455694l.5049l.0l@medo.noip.com>

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

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Mar 04 2006 - 01:01:34 CST

Original text of this message

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