Re: is it possible to flush data block buffer without restart the database?

In article <19971212192000.OAA21390_at_ladder01.news.aol.com>, MarkP28665 <markp28665_at_aol.com> wrote:
>From: dshi_at_magpage.com (David Shi) >>
>I know you can flush sql shared area, but what about data block
>buffer? <<
>
>All dirty (changed) blocks are written at check-point time. You can force a
>log switch manually to make these blocks available sooner to be reused via the
>LRU list. But the database writer process should be freeing blocks for reuse
>as data is writen to disk.

I'm sure you've seen by now the reason he wanted to do this is performance testing.

I would like to comment that until 7.3, subsequent access to a committed data block would cause Oracle to try to generate a read-consistent view because of the leftover inactive rollback segment. You can probably recreate this behavior in 7.3 by setting DELAYED_LOGGING_BLOCK_CLEANOUTS to FALSE.

Performance testing of the data area is extremely difficult because of the large number of variables that impact it. All of the usual "hit-ratio" methods will be extremely misleading when starting with a clean buffer set. I would advise David to take the opposite approach - try to simulate a steady, known load, and then test a specific set of cases under load.

>
>You might want to look into multiple db writers via init.ora parameter if you
>are getting a lot of lru_buffer_cache latches, but otherwise why would you even
>consider trying to flush the buffer pool. If you have a buffer pool problem
>look into async i/o first and multiple db writers second if adding buffers is
>not feasible.
>
>
>Mark Powell -- Oracle 7 Certified DBA
>- The only advise that counts is the advise that you follow so follow your own
>advise -
>

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!