Latches and Tuning:Buffer Cache

1.  The buffer pool is structured into pools; RECYCLE, KEEP, and DEFAULT.  Unless otherwise specified at create time or altered, an object will reside in the DEFAULT pool.

a. Suppose you have a table that is frequently updated, but is a relatively small table.  You would want to ensure the blocks for this table are stored in the KEEP pool of the buffer cache.  Execute the following command to create the table TEST1 in the KEEP pool.> create table TEST1 (n number) storage (buffer_pool KEEP); b.  Suppose you have a large table that is randomly accessed.  You want to ensure the blocks for this table are stored in the RECYCLE pool.  Execute the following command to create the table TEST2 in the RECYCLE pool: > create table TEST2 (n nmber) storage (buffer_pool RECYCLE); c.  Suppose you have a table that is subjected to normal access. You want to ensure the blocks for this table are stored in the DEFAULT pool. Execute the following command to create the table TEST3 in the DEFAULT pool: > create table TEST3 (n number) storage (buffer_pool DEFAULT); d.  Query the USER_TABLES view to see the table names and the pools to which they are assigned: > select table_name, buffer_pool from user_tables where table_name like 'TEST%'; You can use the ALTER TABLE command to change the buffer pool for a table.

2. Various statistics are incremented during buffer management. a.  Execute the following query to view the relevants statistics: > select * from v$sysstat where name in ('dirty buffers inspected','free buffer inspected'); - dirty buffers inspected: incremented when dirty buffers are read and moved to the LRUW list. - free buffers inspected: incremented when free buffers are detected during the search for a free buffer on the LRU list. 3. The parameter DB_BLOCK_LRU_LATCHES defines the number of working sets for the instance.

a.   Determine the value of DB_BLOCK_LRU_LATCHES by executing the following query:> select name,value from v$system_parameter where name = 'db_block_lru_latches';

4.  There is one working set per DB_BLOCK_LRU_LATCHES.

a.  Query the X$KCBWDS view to determine the number of working sets for the instance.> select count(*) from x$kcbwds; The returned values should be the same as the value for DB_BLOCK_LRU_LATCHES.

5.  Each working set also has its own checkpoint latch.

a.  Execute the following query to view the checkpoint latch and set id's for the instance:> select addr,set_id,ckpt_latch,set_latch,dbwr_num from x$kcbwds; b.  Using one of the ckpt_latch values, execute the following query: > select * from v$latch_children where addr = <address from query>; With this you can see that there is one child latch for each checkpoint latch.

6.  The maximum number of DBWR cannot be more than the number of working sets. a.  Set the number of DBWR processes to a value greater than DB_BLOCK_LRU_LATCHES.  Do this by changing the DB_WRITER_PROCESSES value in the init.ora. b.  Bounce your database instance. c.  Execute the following query: > select dbwr_num from x$kcbwds; You will see that the number of DBWR processes is equal to the number DB_BLOCK_LRU_LATCHES. 7. The number of DBWR processes are set using the DB_WRITER_PROCESSES parameter.

a.  View your init.ora file and determine if the number of DB_WRITER_PROCESSES has been explicitly set.

8.  Two parameters effect the way DBWR scans for purposes of making free buffers. a  Execute the following query: > select ksppinm from x$ksppi where ksppinm like '_db%scan%'; _DB_WRITER_SCAN_DEPTH - Sets the scan depth for DBWR _DB_BLOCK_MAX_SCAN_CNT - Sets the foregrond scan depth The maximum number of dirty buffers in the write list, which will not cause DBWR to write is max(DB_BLOCK_BUFFERS/8, 2*write-batch-size 9. A target value for the buffer cache hit% is 90.

a.  Determine the buffer cache % by executing the following query:> select 1 - (phy.value / (log.value + con.value )) "Buffer Cache Hit%" from v$sysstat phy, v$sysstat log, v$sysstat con where phy.name = 'physical reads' and log.name = 'db block gets' and con.name = 'consistent gets'; Descriptions of the buffer cache statistics used are: - db block gets: tracks the number of blocks obtained in CURRENT mode. - physical reads: stores the number of physical blocks when the OS retrieves a database block from disk - consistent gets: number of times a consistent read was requested for a block. Other buffer cache statistics include: - physical writes - db block changes - free buffer requested - dirty buffers inspected - free buffer inspected - CR blocks created

10.  Numerous statistics are associated with the DBWR process.

a.  Execute the following query to view the statistics associated with the DBWR process:> select name from v$sysstat where name like 'DBWR%'; Of the statistics listed two of the most important are DBWR checkpoints and DBWR make free requests.

11.  The number of cache buffers chains latches is equal to prime(db_block_buffers/4).  The number of cache buffers lru chain latches is equal to the value of DB_BLOCK_LRU_LATCHES.

a.  Execute the following query to determine the approximate number of cache buffers chains latches:> select (value/4) from v$system_parameter where name = 'db_block_buffers'; b.  The value from the above should be approximately equal to the row count returned from the following query: > select count(*) from v$latch_children where name = 'cache buffers chains'; c.  Execute the following query to determine the number of cache buffers lru chain latches: > select count(*) from v$latch_children where name = 'cache buffers lru chain'; d.  Check the value of DB_BLOCK_LRU_LATCHES and it should equal the row count returned from the first query.

12.  Wait events are also important for diagnosing buffer cache problems.  It's the time_waited for each event that is used for diagnostics. a.  Execute the following query to view the two primary events associated with tuning the buffer cache: > select * from v$system_event where name in ('buffer busy waits','free buffer waits') order by time_waited; You may not see free buffer waits in the result set if there has been no wait time for this event.  It is listed in V$EVENT_NAME. b.  Additional wait information can be derived from the views V$SESSION_WAIT and V$WAITSTAT views, and the X$KCBFWAIT table.  Query the V$WAITSTAT view to list the different types of blocks: > select class from v$waitstat; Some of the most likely reasons for each block type are: Segment Header - not enough free lists and high number of inserts Data Blocks - too many rows in each block Freelist Blocks - not enough free lists Undo Header - too many transactions per rollback segment Undo Blocks - not enough rollback segment blocks