Re: How often a table is accessed?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/01/15
Message-ID: <34be9bc0.11656714@www.sigov.si>#1/1

On Thu, 15 Jan 1998 15:20:05 -0500, Joseph Sumalbag <joseph_sumalbag_at_bose.com> wrote:

>I'd be very interested to know that too ... anything beyond
>MONITOR TABLE command and short of writing triggers and stuff..
>
>Plus the frequency of access for the indexes ... That to will be
>very useful ...
>
>Any data dictionary and views for these stats ?
>
>Catcox wrote:
>>
>> Does anybody know of some nifty query which would return a list of tables and
>> the frequency with which they are accessed by users?
>>
>> If you know that this is not possible, I'd like to know that too.
>>
>> Thank you,
>>
>> Catherine Cox
>> catcox_at_aol.com

For a first iteration, take a look at CATIO.SQL script in your admin directory.

• begin quote from script's heading remarks: **** DESCRIPTION Collect I/O per table (actually object) statistics by statistical sampling NOTES This works by sampling the buffer at the end of the buffer cache LRU list. The theory is that this buffer was read in at some point and therefore counts as a an IO. All buffers that are read in will eventually find themselves at the end of the LRU list. There is a stored procedure that periodically samples the buffer at the end of the lru list and a view that generates a database object name given the block number of the buffer.

  Note that this file will tell you the distribution of IOs   between tables, but it will not tell you the exact number of IOs. **** end quote ****

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Jan 15 1998 - 00:00:00 CST