Re: How often a table is accessed?

Joseph Sumalbag wrote:
>
> John P. Higgins wrote:
> >
> > Joseph Sumalbag wrote:
> > >
> > > Anything like a querry for a cummulative data rather than a
> > > current one , Not time based sampling ?????
> > >
> > > I believe we were looking for some info like
> > > This table has been accessed n times since the database has been
> > > started ...
> > > This index has been used n time since the databse has been
> > > started ...
> > -------------snip----------
> >
> > The V$SQLAREA view contains this for tables. It has a row for every
> > cursor opened in the database. (Note: if the shared pool is to small, a
> > LRU algorithm overwrites old entries.) You would have to parse the SQL
> > statement to determine which table(s) the statement uses. You could sum
> > all the uses of a table by command_type.
> >
> > I didn't say this is easy!
>
> I'm very much familiar with V$SQLAREA ... It contains cursors
> that is CURRENT and active...... NOT CUMMULATIVE .....
>
> Again ... this doesn't contain the info that is wanted ....
> Please read the INFO that the original poster wanted .
>
> And thanks for the reply .
>

The V$SQLAREA data IS CUMULATIVE. As I noted, it is subject to being aged out of the shared pool. But the executions, disk_reads, buffer_gets, rows_processed DO cumulate for the statement.

Strictly speaking, what the poster wants is not available. I just tried to provide a hint as to how to get half a loaf.

Now, about the LRU aging out the statements:

Why not create a structure to save the data? You could use address and hash_value as a key. If your update routine finds the same key, replace the cumulative fields. If you find a new key, insert a row. Run the stats update often enough to avoid missing single-use statements. Received on Fri Jan 16 1998 - 00:00:00 CST