Re: How to set DBWR_WRITE_PROCESSES in Oracle8i?

CC Harvest wrote:

> Hi, gurus:
> The database is oracle8.1.7.2.1 on win2k machine
> with 1 CPU and two hard drives. When I query
> v$waitstat, I got the following:
>
> CLASS COUNT TIME
> ------------------ ---------- ----------
> data block 246901435 132690159
> segment header 4869 8018
> undo block 20986 38476
> undo header 1895 1556
>
> seems like I need to set DBWR_WRITE_PROCESSES to a
> higher number, what number should I choose, 2 or
> bigger?
>
> Also, what other problems can you see from the last
> query, other than we need to add some more RBS?
>
> Thanks,
>
> Chris Harvest.

Chris,

Is most of the I/O to the datafiles reads or writes. DBWR will not help with the waits on disk reads.

Do you mean that the entire server has only 2 hard drives? If so, adding a 2nd DBWR process will at best be a wash. With a single CPU, it is not a good idea. What are your other wait events relating to I/O? I'll bet that you also see waits in the LGWR process. Assume the following config:

drive 0

        C:  4.0GB       OS, swap (pagefile is after 1 GB of OS files)
        D:  4.7GB       OraHome, online redo (redo logs are after 1 GB)
drive 1
        E:  8.7 GB      Oradata

All datafiles here are on the 2nd hard drive, OS, Oracle binaries and online redo are on the first hard drive. NOARCHIVELOG mode is used.

If the system is accessing any files on C: (like the pagefile) you have a seek operation on the drive of at least half the drive radius (>4GB) before you can write to the online redo logs.

Lets also assume that you have 5 GB of datafiles, with system being outermost (created first), and your index tablespaces being innermost, (created last) rbs and user_data in between. Anytime you have to access the system datafile, you're into a half drive radius seek again. These waits would be on the order of 1 centisecond for a queue depth of 1. Multiply that value by your average queue depth for the device.

What you need to determine is:

Is it waiting on mostly reads or writes? - If reads, increasing db_block_buffers may be of some help, but not it it leads to swapping.
- If reads, is it due to full table scans (excessive block fetches)? Maybe your code selects all columns from all tables involved in a query, when a smaller number of columns selected could be included in an index.

So you'll want to examine the I/O by datafile and by read/write characteristics. If you cannot add any hard drives (this seems like a home learning system) then you can at least put the most accessed files together on the fastest part of the hard drive (to minimize seek time). If you are not producing much redo, you might want to move some datafiles to the first hard drive, but this is likely to drive your wait events for LGWR up. If you are using the pagefile, this will not likely help performance (see above).

Back in 8.1.7.1.5 - I experienced ORA-00600s with a DBWR crash with multple DBWRn processes. I have seen both "Its supported" and "Its not supported" posts for mulitple DBWRn processes on NT/W2K - but I would refrain from using them. With only 2 hard drives - you're not solving the problem increasing the number of DBWRs.

Add drives, segregate datafiles or stripe. Your waits on RBS headers are likely due to simple I/O waits on an over-utilized storage subsystem. Different symptom, same problem.

Btw, are your statistics up to date?

hth,

Paul

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Drake
  INET: paled_at_home.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).