|
"The man who
goes alone can start today; but he who travels with another must
wait till that other is ready." - Henry David Thoreau
(1817-1862) |
Read
first then play the video:
PRF004(VIDEO)-Sizing
the Buffer Cache
Sizing the Buffer Cache
Introduction
As a DBA, you are
responsible for monitoring and changing the memory space allocation
for the Buffer Cache 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:
Resizing the BUFFER CACHE
memory allocation
Setting the Dynamic Buffer
Cache Advisory parameter
ON
OFF
READY
The Least Recently Used (LRU)
list
The Dirty list
The DB Writer processes (DBWn)
Check the SGA memory
maximum allocation size
Using the V$PARAMETER view
Checking the Buffer Cache
size
Checking the Shared Pool
size
Checking the Redo Log
buffer size
Checking the JAVA Pool size
Decreasing the Shared Pool
memory size
Increasing the Buffer Cache
memory size
Commands:
SHOW PARAMETER
ALTER SYSTEM SET
db_cache_advice=ON;
ALTER SYSTEM SET
db_cache_size=80M
ALTER SYSTEM SET
shared_pool_size=60M
ALTER SYSTEM SET
db_cache_size=80M
Hands-on
In this exercise we will
learn how to: re-size the BUFFER CACHE memory allocation, and use the
Dynamic Buffer Cache Advisory parameter. We will learn what the Least
Recently Used (LRU) list, and the Dirty lists are.
Connect to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
Buffers
The buffers in the buffer cache are organized in two lists:
1 -- The Least Recently
Used (LRU) list, and
2 -- the Dirty list.
Dirty Buffers
The dirty buffers are
blocks in the buffer cache that have been changed. The dirty buffers
are moved to the dirty list and written to data files by DB Writer
processes (DBWn). When a free buffer is found, the data block is read
from the disk into the free buffer and the buffer is moved higher in
the LRU list. The Least Recently Used (LRU) list is a list of blocks
that have been used at the least amount of time recently at the SGA
memory.
When you first configure an Oracle instance, you may set the buffer
size value too high or too low. That will result in too much I/O or
poorly utilized real memory. To assist you in the proper
configuration, Oracle provided you with the DB_CACHE_ADVICE parameter.
When this parameter sets to ON, Oracle begins collecting statistics
about cache utilization and projects the physical I/O for 20 cache
sizes, ranging from 10 to 200 percent of the current size.
Using Buffer Cache Advisory
Now, let's turn on the Dynamic Buffer Cache Advisory parameter.
SQL> ALTER SYSTEM SET db_cache_advice=ON;
Notice that there are three possible values:
1 -- ON - allocates memory and gathers statistics.
2 -- OFF - disables advice statistic gathering.
3 -- READY - allocates memory, but statistics are not gathered.
Determine a potential
Buffer Cache problem
Query the V$DB_CACHE_ADVICE view to determine potential physical
I/O that would result from using a different sized buffer cache.
SQL> SELECT size_for_estimate
"Estimated Cache size (Mb)",
buffers_for_estimate "Buffers",
estd_physical_reads "Estimated Reads"
FROM v$db_cache_advice
ORDER BY 1
/
Reading V$DB_CACHE_ADVICE
view
From the preceding list, we
can see that increasing the buffer cache from 53 to 61 Megbytes does
not reduce the "estimated reads" column. Therefore, the
cache buffer size of 55 MB (any thing between 53 and 61) is the best
candidate for database configuration instead of 77 Megabytes. Notice,
that the first entry is 10 percent of the current buffer size which is
about 77 Megabytes.
Estimated Cache size (Mb)
Buffers Estimated Reads
-------------------------
---------- ---------------
7.6836 1967 350,769,900
15.3672 3934 237,452,764
23.0508 5901 132,658,845
30.7344 7868 104,758,765
38.418 9835 96,765,231
46.1016 11802 74,765,034
53.7852 13769 64,980,630
61.4688 15736 987,902
69.1523 17703 4727
76.8359 19670 4727
84.5195 21637 4727
92.2031 23604 4727
99.8867 25571 4727
107.5703 27538 4727
115.2539 29505 4727
122.9375 31472 4727
130.6211 33439 4727
138.3047 35406 4727
145.9883 37373 4727
153.6719 39340 4727
20 rows selected.
To reduce the cache buffer size from 77 to 55 megbytes and increase
the shared pool size from 60 to 80 Megabytes.
Resize Buffer Cache
First check the SGA memory maximum allocation size.
SQL> SHO PARAMETER sga_max_size
Now you should be able to see the maximum size that the SGA
that can grow on.
Compare the SGA parameter size with the calculated size from the
Shared Pool, Buffer Cache, and Redo Log sizes along with Java pool.
SQL> SHOW PARAMETER sga_max_size
SQL> SELECT SUM(value) as "SGA Size"
FROM v$parameter
WHERE name in
('shared_pool_size','db_cache_size','log_buffer','java_pool_size')
/
Notice that if the sizes are very close, then we should
decrease one size in order to increase the others.
Check the Shared Pool, and Buffer Cache sizes individually.
SQL> SHOW PARAMETER shared_pool_size
SQL> SHOW PARAMETER db_cache_size
Take a note on the Shared Pool and DB Cache sizes.
Turn OFF the Dynamic Buffer Cache Advisory parameter.
SQL> ALTER SYSTEM SET db_cache_advice=OFF;
Then, decrease the Buffer Cache size to 55 Megabytes.
SQL> ALTER SYSTEM SET db_cache_size=55M
/
Increase the Shared Pool size to 80 Megabytes.
SQL> ALTER SYSTEM SET shared_pool_size=80M
/
Check the Shared Pool, and Buffer Cache sizes individually again.
SQL> SHOW PARAMETER shared_pool_size
SQL> SHOW PARAMETER db_cache_size
Notice that the shared pool size and db buffer cache size
were adjusted based on the Granule unit. That is the reason their
adjusted sizes are more than their assigned sizes.
|
"After I'm dead
I'd rather have people ask why I have no monument than why I
have one." - Cato the Elder (234-149 BC, AKA Marcus Porcius
Cato) |
Questions:
Q: What does the buffer
cache contain in the Oracle SGA memory?
Q: How do you change the
size of buffer cache in the SGA memory?
Q: What is the Dynamic
Buffer Cache Advisory parameter?
Q: What is the Least
Recently Used (LRU) list in the buffer cache memory?
Q: What is a Dirty Buffer
in the Buffer cache memory?
Q: How do you perform
tuning on the Buffer Cache Memory?
Q: How do you check a SGA
memory size?
Q: How do you use the
V$PARAMETER view?
Q: How do you check the
Buffer cache, Shared Pool, Redo Log buffer, and JAVA Pool sizes?
Q: How do you decrease a
shared pool memory size?
Q: How do you increase a
buffer cache memory size?
Q: What does the SHOW
PARAMETER command?
Q: How many lists are the
buffers organized in the buffer cache?
Q: Describe the
DB_CACHE_ADVICE parameter.
Q: Describe the Buffer
Cache Advisory method.
|