Re: Performance Problem on VAX

From: Lee E Parsons <lparsons_at_world.std.com>
Date: Wed, 4 Jan 1995 21:50:33 GMT
Message-ID: <D1wI09.Dw1_at_world.std.com>


>After turning our Oracle appl over a year ago, the statistic DBWR FREE NEEDED
>has always been zero or very close to zero. Suddenly, performance has slowed
>noticeable and DBWR FREE NEEDED has jumped to 500,000.

From Kim Powell's Bstat/Estat paper in the Aug 92 Oracle Integrator: (I knew I was saving this for a reason)

  DBWR Free Needed is the number of times DBWR is invoked because a user   process scanned DB_BLOCK_MAX_SCAN_CNT buffers with out finding a free on.   DB_BLOCK_MAX_SCAN_CNT is an init.ora parameter that specifies the number   of unavailable buffers a process scans before signaling DBWR to write   dirty buffers from the buffer cache to disk.

  If DBWR Free Needed has a non-zero value, the init.ora parameter   DB_BLOCK_WRITE_BATCH should be increased. [...] This parameter should   only be increased until the statistics Write Complete Waits and Write   Wait Time increase. Write Complete Waits is the number of times a process   waits for DBWR to write a current block before making a change to   a buffer.

I think you may want to consider DBWR Free Needed a symptom of your performance problem not the cause. The thing that has changed here is likely a user/appl function not a OS/init.ora parameter. Do you see 500k Free Needed over the course of many days? Have you bounced the database? If so look for a new user process that is doing a lot of inserts/updates.

If I were in your shoes the first thing I would look for is a user process that has gone batshit and is off in the ozone. Any high CPU processes out there you cant ID?

-- 
Regards, 

Lee E. Parsons                  		
Systems Oracle DBA	 			lparsons_at_world.std.com
Received on Wed Jan 04 1995 - 22:50:33 CET

Original text of this message