|
"He can compress
the most words into the smallest idea of any man I know." -
Abraham Lincoln (1809-1865) |
Read
first then play the video:
PRF005(VIDEO)-Measuring
the Buffer Cache Hit Ratio
More on Resizing and
Measuring the Buffer Cache Hit Ratio
Introduction
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
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.
DEFAULT buffer pool
The DEFAULT pool is the
same thing as the standard block size Buffer Cache.
KEEP buffer pool
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.
RECYCLE buffer pool
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.
|
"It's not the
size of the dog in the fight, it's the size of the fight in the
dog." - Mark Twain (1835-1910) |
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?
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
/
|