Chapter 18: Ratios: Possible Symptoms of Problems

---

Overview

There is much to be said for not using various ratios as tuning indicators, in fact for not using any of them. Many of these ratios can conceal some very large and ugly numbers since they divide one number by another. The denominator, or the number used to divide by, simply disappears. Ratios can be useful to indicate a possible problem or problems. Well in fact a low or high ratio could indicate a possible problem but it could also conceal a possible problem. Additionally a ratio could indicate no problem at all. Ratios are best used as possible symptoms of possible problems. "Hmmmm…"

Ratios have received a lot of bad press recently. Much of this is founded in truth. Ratios should not be completely ignored. I find even the database buffer cache hit ratio useful. However, I do not necessarily think it is a bad thing if it is low or high. So what! That could be because of application behavior. A possible mistake made with ratios in the past was that an unacceptable value was assumed to be tunable. A ratio is not tunable. However, it can indicate that something else could be tunable such as an ugly SQL statement in an application full of very well-tuned SQL code. If the database buffer cache is "doing something weird" like going up and down say between 60% and 90% every 5 s then something odd may be going on. Increasing the size of the database buffer cache will not help you to find that ugly SQL code statement, in fact nasty things might be further concealed. The whole point of tuning is finding things and solving them not attempting to conceal and bury them behind meaningless percentages.

The database buffer cache and the shared pool can actually suffer from tuning of their respective ratios not only because problems causing unpalatable ratios are not being solved but because extremely large buffers are much more difficult for Oracle Database to manage.

Tip 

Use ratios as symptoms or indicators of potential problems. Tuning the ratios themselves by changing buffer sizes is often a complete waste of time! It also might make things worse. On the contrary, I disagree completely with Oracle database administrators who consider ratios to be completely useless. Ratios can give clues as to where to look in the Oracle Database Wait Event Interface when trying to solve a performance problem. The solution is perhaps to use both ratios and the Oracle Database Wait Event Interface.

The V$SYSSTAT performance view is the source of many ratios. What are some of these ratios?

• Database Buffer Cache Hit Ratios.   Cached database table and index blocks versus those read from disk.

• Table Access Ratios.   Full table scanning small static tables can help performance. Full table scanning large tables can completely devastate database performance. The best performance method for accessing tables is by index ROWID pointers and full scans on small tables.

• Index Use Ratio.   Compare index key reads to table reads.

• Dictionary Cache Hit Ratio.   Database metadata in the shared pool.

• Library Cache Hit Ratios.   Library cache parsing activity in the shared pool.

• Disk Sort Ratio.   How much is temporary disk sort space used?

• Chained Rows Ratio.   Proportion of rows fetched involving row chaining. Row chaining is overflow of blocks to other blocks since information cannot fit into a single block and can make data retrieval inefficient. It is extremely likely that chained blocks are spread across disk storage haphazardly.

• Parse Ratios.   Parsing is expensive. The less parsing the better.

• Latch Hit Ratio.   Latches protect memory buffers from concurrent use.

---