Hands-On 05 (Measuring the Buffer Cache Hit Ratio)

As a DBA, you are responsible for monitoring and calculating the Buffer Cache Hit Ratio in the SGA memory in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Measuring the Buffer Cache Hit Ratio

Creating a cache table

Calculating the Hit Ratio for Multiple Pools

Displaying the Hit Ratio for the KEEP buffer pool

Caching the Oracle objects in the Buffer Pools

Diagnosing the FREELIST contentions

Adding a FREELIST to a table

Using the DEFAULT pool

Using the KEEP pool

Using the RECYCLE pool

Using the V$SYSSTAT view

Using the V$BUFFER_POOL view

Using the V$BUFFER_POOL_STATISTICS dictionary view

Using the DBA_SEGMENTS view

Using the V$SESSION_WAIT view

Dropping a table

Commands:

ALTER SYSTEM SET db_cache_size=60m

ALTER SYSTEM SET db_keep_cache_size=16m

CREATE TABLE STORAGE (BUFFER_POOL KEEP)

SELECT /*+ CACHE (iself.dept) */

ALTER TABLE STORAGE (FREELISTS 2)

-- Hands-On 05 (Measuring the Buffer Cache Hit Ratio)
-- Measuring the Buffer Cache Hit Ratio.
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a40
col parameter format a40
meter format a40
col username format a10
pause

--Start

CLEAR SCR
-- In this exercise you will learn how to: measure the Buffer Cache
-- Hit Ratio, create a table to keep in the KEEP buffer pool,
-- calculate the Hit Ratio for multiple pools, cache the Oracle
-- objects in the Buffer Pools, diagnose the FREELIST contentions,
-- and add a FREELIST to a table.

-- We also learn what the DEFAULT, KEEP, and RECYCLE pools are.

-- Begin by connecting to SQLPlus as the SYSTEM/MANAGER user.

pause

CONNECT system/manager AS SYSDBA

pause


CLEAR SCR
-- Let's calculate the Buffer Cache Hit Ratio from the V$SYSSTAT view.

pause

SELECT 1- ((p.value - l.value - d.value) / s.value) "Buffer Cache Hit Ratio"
FROM v$sysstat s, v$sysstat l, v$sysstat d, v$sysstat p
WHERE s.name = 'session logical reads'
AND d.name = 'physical reads direct'
AND l.name = 'physical reads direct (lob)'
AND p.name = 'physical reads'
/

-- If the Buffer Cache Hit Ratio is more than 90% then there is no problem.
-- If the Buffer Cache Hit Ratio is between 70% and 90% then there
-- could be a problem.
-- And if the Buffer Cache Hit Ratio is less than 70%, there is definitely
-- a problem and the Buffer Cache size needs to be increased.

pause

CLEAR SCR
-- Let's first reduce the buffer cache size from 80 megabytes 
-- to 60 megabytes in order to add more buffer pool to the memory.

pause

ALTER SYSTEM SET db_cache_size=60m
/

pause

CLEAR SCR
-- Then, allocate memory space to the KEEP buffer pool.

pause

ALTER SYSTEM SET db_keep_cache_size=16m
/

pause



CLEAR SCR
-- Now, you can create a table to be kept in the KEEP buffer pool.

pause

CREATE TABLE iself.mykeep
(col1 NUMBER,
col2 VARCHAR2(10))
STORAGE (BUFFER_POOL KEEP)
/

-- Notice that if we don't specify a BUFFER_POOL, the DEFAULT pool 
-- is used.

pause

CLEAR SCR
-- Check how buffer pool was configured.

pause

SELECT name, buffers
FROM v$buffer_pool
/

-- The name column values can be DEFAULT, KEEP, or RECYCLE.
-- The DEFAULT pool is the same thing as the standard block size Buffer Cache.

-- The KEEP buffer pool, is used to keep buffers in the pool as long as 
-- possible for data blocks that are likely to be reused.

-- The RECYCLE buffer pool is used as a temporary host block from segments 
-- that you don't want to interfere with blocks in the DEFAULT Buffer Pool.

pause

CLEAR SCR
-- Now, calculate the Hit Ratio for multiple pools 
-- using the V$BUFFER_POOL_STATISTICS dictionary view.

pause

SELECT name, 
1-(physical_reads/(db_block_gets + consistent_gets)) "Hit Ratio"
FROM v$buffer_pool_statistics
WHERE db_block_gets + consistent_gets > 0
/

-- Notice that the Hit Ratio for the KEEP buffer pool is very high.

pause

CLEAR SCR
-- Now, cache the department table by hint in a SQL statement.

pause

SELECT /*+ CACHE (iself.dept) */
* 
FROM iself.dept
/

-- Now, the dept table is in the memory.

pause

CLEAR SCR
-- Now, let's diagnose the FREELIST contention in the Buffer Cache.

pause

SELECT s.segment_name, s.segment_type, s.FREELISTs, w.wait_time,
w.seconds_in_wait, w.state
FROM dba_segments s, v$session_wait w
WHERE w.event='buffer busy waits'
AND w.p1=s.header_file
ANd w.p2=s.header_block
/

-- Note that there is no segment name. Normally that is what you get
-- when you have no FREELIST contention problem. If we find records,
-- we should increase the number FREELIST on the table in the question.

pause

CLEAR SCR
-- If you identify a segment header that has a FREELIST contention, 
-- you can increase the number of FREELISTs for the segment.

pause

ALTER TABLE iself.dept
STORAGE (FREELISTS 2)
/

-- And you would not have any more FREELIST contentions.
pause

CLEAR SCR
-- Drop the iself.mykeep table.

pause


DROP TABLE iself.mykeep
/

pause


CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause