Performance

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:

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

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.

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.