frequently accessed tables

[UKDBA]

Hi
Is there anyway of finding frequently accessed tables by users. i couldn't find any data dictionary table to give this information.

Cheers
UKDBA

[julian]

There are several vague queries that might help, for example:

select obj, count(*) from x$bh where tch >= 10 group by obj;

but the best way is to simply know your application. You could take also the straightforawrd approach and cache all the look-ups.

[UKDBA]

COLUMN TCH is not available in version 7344 and 805 but it is in 817. but how can i relate object number to objects and what i can do to get information in version 7344.

Cheers
UKDBA

[gpsingh]

You can use catio.sql present in ORACLE_HOME/rdbms/admin

[anandkl]

you can also query v$sqlarea and v$sqltext to know frequently executed statements which will contain the tables accessed.Query the exectution column in v$sqltext to know how many time this statement was executed

cheese
anandkl

[tamilselvan]

select b.owner "Owner",
b.segment_name "SegNm",
b.segment_type "Type",
count(*) "Num of Blocks"
from sys.x$bh a, dba_extents b
where a.dbafil = b.file_id
and a.dbablk between b.block_id and (b.block_id +b.blocks -1)
group by b.owner, b.segment_name, b.segment_type
order by 4 desc
;

[jmodic]

Querying X$BH will not necesserily show the most frequently accesed tables. It will only show segments that currently have some blocks in the buffer cache. But this does not mean they are frequently accessed segments.

Much more reliable way is to use catio.sql as suggested by gpsingh. This will indeed show quite reliable information of which segments are realy frequently accessed.

[Edited by jmodic on 02-27-2002 at 03:50 PM]

[julian]

Querying X$BH will not necesserily show the most frequently accesed tables. It will only show segments that currently have some blocks in the buffer cache. But this does not mean they are frequently accessed segments.

True. You are absolutely right. The problem with the query I just gave as an example is that if you have a table with say a 100 blocks and that table has low average touch_count, it still could produce a higher total than a 2 block table with a high average touch count.

Also touch_count is incremented every time a buffer has been accessed provided that 3 seconds have elapsed since the last touch and this definitely hinders the final coclusion that we could try to make.