15.6 How do I…Assess the impact of adding database buffers?

Problem

I executed the steps in the preceding How-To and have discovered that I should increase the size of the database buffer cache. Is there some way of determining what sort of buffer cache hit rate improvement I can expect by devoting some number of additional buffers to the database buffer cache?

Technique

Note-The UTLESTAT report does not address this topic.

The database buffer cache is so important to the overall performance level of the Oracle server that a mechanism determines the improvements in the cache hit rate as a function of adding additional buffers to the buffer cache. Suppose, for example, that you know you can afford to devote 500 more buffers to the database buffer cache. (If the database block size is 2048 bytes, then these 500 additional buffers would consume 1 megabyte of memory). Oracle provides a way for you to assess the number of additional cache hits you will generate by increasing the buffer cache size by any amount between 1 and 500 database blocks.

Set the value of initialization parameter DB_BLOCK_LRU_EXTENDED_STATISTICS to the number of database block buffers you are considering for addition to the database buffer cache. Query the table X$KCBRBH to determine the number of additional cache hits generated by each additional database buffer block. Use this number to determine the improvement in the buffer cache hit rate, and, finally, increase the size of the initialization parameter DB_BLOCK_BUFFERS accordingly. The structure of the X$KCBRBH table appears in Table 15.5.

Table 15.5 Two helpful columns in the X$KCBRBH dynamic performance table

Column Column Description INDX New buffer identifier COUNT How many additional cache hits will be generated by adding the buffer to the buffer cache Steps

1. Check the value of the initialization parameter DB_BLOCK_LRU_EXTENDED_STATISTICS by manually reviewing the INIT.ORA file or by using the SHOW PARAMETER DB_BLOCK_LRU_EXTENDED_STATISTICS command in either the server manager or SQL Worksheet utilities.

2. If you want to change the value of this parameter, shut down the database, alter the INIT.ORA file by setting DB_BLOCK_LRU_EXTENDED_STATISTICS to the number of maximum number of buffers you wish to consider adding to the buffer cache, and restart the database.

3. Allow the database to run in normal mode during what is, for your installation, a typical period of database activity.

4. Start a SQL*Plus session and connect to the database as the SYS user; only SYS has access to the X$KCBRBH table we are about to use. Use the start command to load and execute the script in CHP15_9.SQL. This script queries the X$KCBRBH table as shown in Figure 15.7 and prompts the user for the number of additional buffers to consider.

Line 3 of the script contains a substitution variable for the number of additional buffers, the impact of which you want to assess. In Figure 15.7, the script reports that the addition of 25 buffers to the database buffer cache will result in 20 additional cache hits.

5. Use the start command to load and execute the script CHP15_10.SQL. This SQL statement in this script is a modification of a statement from the last How-To. The script prompts the user for the number of additional cache hits reported in the previous step. The script and its output appear in Figure 15.8.

This script is a modification of CHP15_8.SQL. The change is in line 9, where the script reduces the number of physical disk reads by the number of additional cache hits generated in Step 4. The new cache hit rate, after the addition of 20 additional buffer blocks, will be 91%, an acceptable value.

How It Works

Oracle only populates the X$KCBRBH table if the DB_BLOCK_LRU_ EXTENDED_STATISTICS parameter is set to a non-zero value in the initialization parameter file. Steps 1 and 2 describe how to reset this parameter. The scripts in Steps 4 and 5 report the number of additional cache hits and the improvement in the overall database buffer cache hit rate that will be generated by additional cache buffer blocks.

Comments

One PL/SQL procedure can contain the functionality of each of the scripts in Steps 4 and 5. The procedure BC_GROW (for “buffer cache grow”) shows how; it appears in Listing 15.7.

Listing 15.7 The PL/SQL procedure BC_GROW created by CHP15_11.SQL and used to assess buffer cache hit improvements generated by additional buffer cache blocks

SQL> create or replace procedure bc_grow (buffers number)

2 AS

3 extra_cache_hits number;

4 new_hit_rate number;

5 BEGIN

6

6 SELECT SUM(t1.count) INTO extra_cache_hits

7 FROM x$kcbrbh t1

8 WHERE indx < buffers;

9

9 SELECT

10 (sum(decode(name,’db block gets’,value, 0)) +

11 sum(decode(name, ‘consistent gets’, value, 0)))

12 /

13 (sum(decode(name,’db block gets’,value, 0)) +

14 sum(decode(name, ‘consistent gets’, value, 0)) +

15 sum(decode(name, ‘physical reads’, value, 0))

16 - extra_cache_hits) * 100

17 INTO new_hit_rate

18 FROM v$sysstat

19 WHERE name IN (‘physical reads’, ‘db block gets’, ‘consistent

gets’);

20

20 dbms_output.put_line (‘the number of additional cache hits is: ‘

21 || to_char(extra_cache_hits));

22

22 dbms_output.put_line (‘the new hit rate is: ‘

23 || to_char(new_hit_rate));

24

24 END;

This procedure accepts one parameter as input, which contains the number of additional buffers whose impact you wish to assess. Lines 6 through 8 contain SQL that is functionally equivalent to script CHP15_9.SQL to generate the number of additional cache hits resulting from the extra buffers. Note that the use of the correlation column is necessary to avoid compilation errors. Without it, PL/SQL confuses the COUNT column name with the COUNT aggregation operator and complains about the absence of a GROUP BY clause. Lines 9 through 12 are functionally equivalent to the SQL contained in the CHP15_10.SQL and report the new buffer cache hit rate.

Run the bc_grow procedure using the EXECUTE command, as shown in Figure 15.9.

It may be tempting to set the value of DB_BLOCK_LRU_EXTENDED_ STATISTICS and forget it. This way, you can query the X$KCBRBH table at will to determine the cost effectiveness of adding more buffers to the database cache. This may not be advisable, though, because Oracle incurs processing costs when DB_BLOCK_LRU_EXTENDED_STATISTICS is not 0.