18.3 Index Use Ratio

---

This is a simple ratio trying to assess the percentage of index usage as compared to table scans. This value is not precise. These are the statistics I am interested in.

SELECT value, name FROM V$SYSSTAT WHERE name IN       ('table fetch by rowid', 'table scans           (short tables)', 'table scans (long tables)') OR name LIKE 'index fast full%' OR name = 'index fetch by    key';    VALUE    NAME    -----    ---------------------------------------------       75    table scans (short tables)      116    table scans (long tables)     3983    table fetch by rowid        0    index fast full scans (full)        0    index fast full scans (rowid ranges)        0    index fast full scans (direct read)     2770    index fetch by key

This query gives an idea of index usage percentages.

SELECT 'Index to Table Ratio ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT       WHERE name LIKE 'index fast full%'       OR name = 'index fetch by key'       OR name = 'table fetch by rowid') / (SELECT SUM(value) FROM V$SYSSTAT WHERE     name IN       ('table scans (short tables)', 'table scans (long           tables)') ),0)||':1' "Result" FROM DUAL;

This is the result. This number may or may not be good depending on applications. However, my highly active processing generally uses only Referential Integrity indexing, whose index hits may not be recorded. As a result this ratio could be very misleading. This is another example of a ratio which is not necessarily very useful.

Ratio                   Result ----------------------  ------ Index to Table Ratio      30:1

---