I am facing trouble in getting the most frequently used tables in oracle 10g. I'm using Oracle 10g version 10.2.0.4.0 with EBS R12.1.3 Application on it.
Please help me in sorting out the most frequently used table in my DataBase.
If possible I would like to get the TableName, owner and how many times it was accessed between a time frame.
I need this for tuning purpose.
Please provide a query to get the same.
Thanks in advance !
I know 2 ways, one of them is to turn monitoring on all table and then use that statistic, or use v$segment_statistics
to analyze block access statistic, something like this, shows you top 50 ordered by total value of access type tables, you shoul provide your target schema name < YOURSCHEMA >
in the query.
select *
from (select rownum RN, T.*
from (select stat.OBJECT_NAME, stat.STATISTIC_NAME, stat.VALUE AcsValue,
sum(value) over(partition by stat.OBJECT_NAME) Total
from v$segment_statistics stat
where owner = < YOURSCHEMA >
and stat.OBJECT_TYPE = 'TABLE'
and stat.STATISTIC_NAME in
('logical reads', 'pptimized physical reads',
'physical read requests', 'physical reads',
'physical reads direct', 'physical write requests',
'physical writes', 'physical writes direct')
order by sum(value) over(partition by stat.OBJECT_NAME) desc) T) TOrd
where TOrd.RN < 50