Performance

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:

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. You also learn what the DEFAULT, KEEP, and RECYCLE pools are.

Begin by connecting to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
 

Buffer Cache Hit Ratio
Let's calculate the Buffer Cache Hit Ratio from the V$SYSSTAT view. The V$SYSSTAT view contains the Oracle system usages such as session logical reads, physical reads direct, etc.
SQL> SELECT 1- ((p.value - l.value - d.value) / s.value)

AS "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'
/
Note that 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.

In the above query, the �physical reads� value is a number of read that Oracle physically performs from hard disk including all the �physical reads direct� and �physical read direct (lob).� You want to be sure that the �physical reads direct� values be as high as possible in a respect to the �physical reads� value. Also, you want to be sure that the �session logical reads� value is very high. The �session logical reads� value is the number of times that Oracle reads a block from the memory (Buffer Cache) rather than a disk.
 

Resize Buffer Cache
Let's first reduce the buffer cache size from 80 megabytes to 60 megabytes in order to add more buffer pool to the memory.
SQL> ALTER SYSTEM SET db_cache_size=60m
/
 

Allocation KEEP buffer pool
Then, allocate memory space to the KEEP buffer pool.
SQL> ALTER SYSTEM SET db_keep_cache_size=16m
/
 

Using KEEP buffer pool
Now, you can create a table to be kept in the KEEP buffer pool.
SQL> 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.
 

The V$BUFFER_POOL view contains the Oracle buffer pools configuration. You can use this view to query the buffer pool configurations information such as DEFAULT, KEEP, or RECYCLE pools.

Check how buffer pool was configured.
SQL> 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.
 

Buffer Cache Hit Ratio for multiple pools
Now, calculate the Hit Ratio for multiple pools using the V$BUFFER_POOL_STATISTICS dictionary view.

SQL> 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.
 

Cache an object
Now, cache the department table by hint in a SQL statement.
SQL> SELECT /*+ CACHE (iself.dept) */
*
FROM iself.dept
/
Now, the dept table should be in the memory.
 

Check FREELIST contention in Buffer Cache
The FREELIST space is an allocated space in a table that contains all the blocks� references which are candidate for more inserted records. Any contentions on the FREELIST allocation will create a performance problem.

Now, let's diagnose the FREELIST contention in the Buffer Cache.
SQL> 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.

Note that the DBA_SEGMENTS view contains all the created users� segments such as tables, indexes, etc. The V$SESSION_WAIT view contains dynamic information for that instance and for that specific time. Its content will be regenerated when you restart an instance. It contains the contentions information such as �buffer busy waits� for a file or a block, etc.
 

Increase FREELIST
If you identify a segment header that has a FREELIST contention, you can increase the number of FREELISTs for the segment.
SQL> ALTER TABLE iself.dept
STORAGE (FREELISTS 2)
/
And you would not have any more FREELIST contentions.
 

Drop a table
Drop the iself.mykeep table.
SQL> DROP TABLE iself.mykeep
/
You drop the table so you can repeat this hands-on again if you wish.

Questions:

Q: How do you measure the buffer cache hit ratio?

Q: How do you create a cache table?

Q: How do you calculate a hit ratio for multiple pools?

Q: How do you display a hit ratio for the KEEP buffer pool?

Q: How do you cache an object into the buffer pools using hint in a SQL statement?

Q: What is a FREELIST?

Q: How do you diagnose the FREELIST contentions in the buffer cache?

Q: How do you use the DEFAULT pool?

Q: How do you use the KEEP pool?

Q: When do you use the RECYCLE pool?

Q: What is the V$SYSSTAT view?

Q: What is the V$BUFFER_POOL view?

Q: What is the V$BUFFER_POOL_STATISTICS dictionary view?

Q: What is a hint in the SQL statement?

Q: How do you drop a table?

Q: Describe the session logical reads, physical reads direct, and physical reads direct (lob), and physical reads in the V$SYSSTAT view?

Q: What is an acceptable range for a buffer cache hit ratio?

Q: Cache the department table by using a hint in a SQL statement.

Q: What does the following SQL statement?

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
/