18.9 Latch Hit Ratio | Oracle High Performance Tuning for 9i and 10g

< Day Day Up >

As already stated, latches protect memory buffers from concurrent usage. This is a healthy ratio for the Accounts schema active database at well over 99%. However, this ratio does not necessarily show much because not only are the numbers hidden, individual hit rates for specific latches are not shown.

SELECT 'Latch Hit Ratio ' "Ratio" , ROUND( (SELECT SUM(gets) - SUM(misses) FROM V$LATCH) / (SELECT SUM(gets) FROM V$LATCH) * 100, 2)||'%' "Percentage" FROM DUAL;     Ratio              Percentage ----------------   ---------- Latch Hit Ratio    99.96%

That's enough ratios for now. There are many others. Obviously all the examples above would be best written into a stored procedure. Executing all the ratios together gives the following result.

Ratio                               Percentage ---------------------------------   ---------- Chained Rows                         0% Database Buffer Cache Hit Ratio     95% Dictionary Cache Hit Ratio          99.86% Efficient Table Access              99.97% Hard Parses                          0.04% Index to Table Ratio                30:1 Latch Hit Ratio                     99.96% Library I/O Reloads                  0% Library Lock Requests               96.31% Library Pin Requests                96.1% Library Reparses                     0% Long Table Scans                     0.03% Parse Failures                       0% Short Table Scans                   31.16% Short to Long Full Table Scans      99.92% Soft Parses                         44.98% Sorts in Memory                     98.89% Table by Index                      68.81%

There are numerous potential ratios that can be used to attempt to assess symptoms of database health. Once again be aware that a ratio divides one number by another. This division of two numbers can hide the values in those numerator and denominator values, which can be independently significant. The next chapter will look at wait events. Many things can happen in the database potentially causing one task to wait for another to complete.

< Day Day Up >