Re: Performance Problem on VAX
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.comReceived on Wed Jan 04 1995 - 22:50:33 CET