segment와 연관된 통계정보를 제공한다.
V$OBJECT_USAGE 를 이용해 사용도를 파악할 수 있지만,
alter table(index) xxxx monitoring usage; 로 설정을 해야 한다.
SQL> select distinct STATISTIC_NAME from v$segment_statistics;
STATISTIC_NAME
----------------------------------------------------------------
ITL waits
buffer busy waits
db block changes
global cache cr blocks served
global cache current blocks served
logical reads
physical reads
physical reads direct
physical writes
physical writes direct
row lock waits
11 rows selected.
select * from
(
select
DECODE(GROUPING(a.object_name), 1, 'All Objects', a.object_name) AS "Object",
sum(case when a.statistic_name = 'ITL waits'
then a.value else null end) "ITL Waits",
sum(case when a.statistic_name = 'buffer busy waits'
then a.value else null end) "Buffer Busy Waits",
sum(case when a.statistic_name = 'row lock waits'
then a.value else null end) "Row Lock Waits",
sum(case when a.statistic_name = 'physical reads'
then a.value else null end) "Physical Reads",
sum(case when a.statistic_name = 'logical reads'
then a.value else null end) "Logical Reads"
from v$segment_statistics a
where a.owner !='SYS'
group by rollup(a.object_name)) b
where (b."ITL Waits">0 or b."Buffer Busy Waits">0) ;