Re: How to remove tables from KEEP Buffer Pool ?

IN order to unkeep you can try:
1) take the underlying tablespace offline, the blocks associated with it will be flushed out of the buffer cache. or 2)
 alter table testt storage( buffer_pool default);

The buffer pool hit ratio can be determined using the formula:

hit ratio=1 - physical reads/(block gets+consistent_gets)  

Where the values of physical reads, block gets, and consistent gets can be obtained for the KEEP buffer pool from the following query:

SELECT PHYSICAL_READS, BLOCK_GETS, CONSISTENT_GETS FROM V$BUFFER_POOL_STATISTICS WHERE NAME = 'KEEP'; the db cache hit rate can be calculated via SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('DB BLOCK GETS', 'CONSISTENT GETS', 'PHYSICAL READS'); Calculate the hit ratio for the buffer cache with this formula:

Hit Ratio = 1 - (physical reads / (db block gets + consistent gets))

PLease note as long as your users are happy, you don't need to care about those cache hit rate.

HTH
http://www.webyourcompany.com

hardikarm_at_yahoo.com (Mahesh Hardikar) wrote in message news:<4a1c57c2.0305282111.5c2231c1_at_posting.google.com>...
> Hi ,
>
> Oracle 8.1.7.4 on HP-UX 11.00
>
> Developes have pinned few tables in KEEP buffer pool. When I checked
> the sizes of those tables , there are few tables which are large one &
> I wish to remove those from KEEP Buffer Pool . How do I do that ?
> Currently I have only KEEP pool defined.
>
> Also wanted to know which is the safe way to check DB_Cache_Hit ratio
> ? Should I check it thru SGASTAT or I can use v$buffer_pool_statistics
> as well ?
> Both the queries are showing me differences (Buffer_pool_statistics
> shows lower value than v$sgastat )
>
> Thanks & Regards ,
> Mahesh Hardikar
Received on Fri May 30 2003 - 01:23:40 CEST