Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...


. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |


Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Performance


Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 |


Lesson 04

"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:

   PRF-VIDEO -Sizing the Buffer Cache


Sizing the Buffer Cache



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


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



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



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

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 buffer is found, the data block is read from the disk into the 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

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)



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.