Re: Cache Hit Ratio

From: Phil Herring <revdoc_at_uow.edu.au>
Date: 1998/01/07
Message-ID: <6911sg$mlr$1@wyrm.its.uow.edu.au>#1/1

In article <34B3FCFB.CC36B06C_at_menlosoftware.com> Paul Osborn, posborn_at_menlosoftware.com writes:
>Look at the following SQL statements - especially #11 and #77. If you run them
>periodically and archive the results, you should be able to develop a record of
>who causing the most I/O on your system and what SQL statements they are running
>to cause that I/O. Statement #9 will return the end user SQL accessing the most
>db block buffers, i.e. let you be able to determine the data blocks saved into
>the db block buffer area.

This sort of thing will help, *but* the SQL statements in the SGA usually won't have substituted values in them - they'll just have the names of whatever variables the applications use. Therefore, you'll find out what SQL statements are being used most often, but not which specific rows they have accessed. Similarly, sorting I/O by table/user/datafile/whatever won't tell you which rows in those tables are being accessed.

What you really want to know are the rowids of the data in the SGA. Is there a dictionary view that holds that information? (I'm thinking not.)

Received on Wed Jan 07 1998 - 00:00:00 CST