Previous | Table of Contents | Next

Page 785

There is a way in Oracle to test the effect of adding more buffers. Why would you want to do this as opposed to simply increasing DB_BLOCK_BUFFERS? It is often the case that you need to add more buffers to your database buffer cache, but don't have enough real memory to support it. Hence, this technique can be used as a justification for purchasing more memory. To do this, shut down the instance and set

DB_BLOCK_LRU_EXTENDED_STATISTICS =  <n>

where <n> is the number of buffers you want to add.

Then start up the instance again. Let your application run normally for a reasonable amount of time, just as you would for utlbstat.sql/utlestat.sql. The table X$KCBRBH contains the information you need to make a prognosis. Do the following query:

SQL> SELECT SUM(COUNT)
      2> FROM X$KCBRBH
      3> WHERE INDEX < <n>;

This returns the number of additional cache hits you would gain by adding these <n> buffers. To then determine what your new, hypothetical database buffer cache hit ratio would be, add the <n> to the original equation:

1 - (physical reads - <n>) / (logical reads)

As you can see, this will result in a higher cache hit ratio because the numerator being subtracted from 1 will be smaller as long as <n> is greater than 0. As an example, suppose from report.txt you have physical reads = 40000, consistent gets = 100000, and db block gets = 30000. Then, your cache hit ratio, before adding any buffers is

1 - (40000/(100000+30000) ) = 1 - (40000/130000) = 1 - (.31) = .69

which is woefully below .90. Suppose you want to try adding 10,000 more buffers. You set your extended statistics on and queried your X$CBRBH table, and it indicated that you would gain 30,000 more hits. Your new, hypothetical cache hit ratio (with accompanying improved performance) would then be

1- ((40000 - 30000) / (130000)) = 1 - (10000 / 130000) = 1 - (.08) = .91

which is a considerable improvement. Of course, this example has very well-rounded numbers, but it should be sufficient to emphasize the point that it is very useful when you have run out of core memory and need a justification to purchase more, in particular to bolster the database buffer cache. As might not be immediately apparent, the hardest thing is to determine what number of buffers to add to give you the additional gain in the number of hits you need. I suggest that you try moving up in percentages; first add 10 percent, then go up or down depending on the result. There is a counterpart way of testing the effect of subtracting "unnecessary" buffers. However, any DBA knows that an unnecessary buffer is a mythical beast, so this book won't cover this technique because it is rarely, if ever, used.

You can consider specific system wait events as other guidelines to help measure the performance of the database buffer cache. You may gather `buffer busy waits' and `latch free' from the System wide wait events section of report.txt or from V$SYSTEM_EVENT and

Page 786

V$SESSION_WAIT, as shown earlier in tuning the library cache. Also, examine `free buffer inspected' from V$SYSSTAT or report.txt. In particular, a latch contention problem may be indicated if any of the following are much greater than 0:

The init.ora parameter DB_BLOCK_LRU_LATCHES is set by default to 1/2 the number of CPUs on an SMP machine. If this seems insufficient based on the previous measures, increase it up to twice the number of CPUs.

CAUTION
Don't increase DB_BLOCK_LRU_LATCHES unless you have evidence indicating to do so, and then only increment it in degrees—for example, from 1/2 (# CPUs) to 3/4 (# CPUs), and so forth. If a heavier workload is predicted than was sampled for, you may increase this a little more.

As mentioned, you can counteract the phenomenon of full table scan blocks being placed at the LRU end of the LRU list if you cache whole tables at the MRU end. How do you do this? Either use CREATE TABLE with the nondefault CACHE clause or embed a CACHE hint in the first query that will reference the table. Set the init.ora parameter CACHE_SIZE_THRESHOLD to the maximum number of blocks allowed to be cached per table. You should generally use these techniques when you expect to frequently reuse the same tables, such as with lookup tables. Be careful not to cache too many tables, thereby defeating the purpose of the cache itself. The situation can deteriorate rapidly, especially if most non-lookup, large tables don't get a chance to get at least partially cached.

If you run the $ORACLE_HOME/rdbms/admin/catparr.sql script, it creates the V$CACHE view, along with other views germane only to Oracle Parallel Server (OPS). However, the V$CACHE view can be very useful because it offers a mapping of blocks to datafiles by object. Hence, you can determine which objects, such as tables, have blocks currently in the database buffer cache. This can tell you, for example, whether your attempts to cache your lookup tables worked. The view is not dynamic, as many of the V$ views are, so you must rerun the catparr.sql script whenever you CREATE new objects or ALTER your current objects' storage parameters.

Tuning Sorts

Sorting is the process of putting something in a particular order, such as alphabetical or numerical, ascending or descending, and so forth. As with any RDBMS, Oracle requires sorting to take place for various reasons, sometimes implicitly due to the nature of a SQL statement, and other times explicitly through a user request. In any case, sorting consumes significant amounts of CPU time, memory, and disk.

Previous | Table of Contents | Next