col owner format a10
set linesize 120
set pagesize 20
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;
Sample Output:
Code: Select all
SEGMENT_NAME OBJECT_TYPE TOTAL_PHYSICAL_READS
---------------------------------------- ------------------ --------------------
SYS.TYPE$ TABLE 1958
SYS.OBJ$ TABLE 976
SYS.TAB$ TABLE 856
SYS.IDL_UB1$ TABLE 470
SYS.HISTGRM$ TABLE 319
SYSMAN.MGMT_METRICS TABLE 238
SYS.WRH$_SQL_PLAN TABLE 184
SYS.IDL_UB2$ TABLE 153
SYS.I_OBJ1 INDEX 151
SYSMAN.MGMT_METRICS_RAW_PK INDEX 133