V$SEGMENT_STATISTICS

V$SEGMENT_STATISTICS displays information about segment-level statistics.

Column	Datatype	Description
OWNER	VARCHAR2(30)	Owner of the object
OBJECT_NAME	VARCHAR2(30)	Name of the object
SUBOBJECT_NAME	VARCHAR2(30)	Name of the subobject
TABLESPACE_NAME	VARCHAR2(30)	Name of the table space to which the object belongs
TS#	NUMBER	Tablespace number
OBJ#	NUMBER	Dictionary object number of the object
DATAOBJ#	NUMBER	Data object number of the object
OBJECT_TYPE	VARCHAR2(18)	Type of the object
STATISTIC_NAME	VARCHAR2(64)	Name of the statistic
STATISTIC#	NUMBER	Statistic number
VALUE	NUMBER	Statistic value

Note:

1. Segment Waits

select      object_name ,     obj# ,     statistic_name ,     value from  v$segment_statistics where owner like '&owner' and   statistic_name like '%waits%' and   value > 0 order by statistic_name ,     value desc;

-- find the database table that is accessed the most: query the V$SEGMENT_STATISTICS view for this information by looking for tables that have a high number of physical reads and logical reads SELECT t.owner,t.table_name,lr.value+pr.value AS total_reads FROM  (SELECT owner, object_name, value FROM v$segment_statistics         WHERE statistic_name='logical reads') lr,       (SELECT owner, object_name, value FROM v$segment_statistics         WHERE statistic_name='physical reads') pr,        dba_tables t WHERE lr.owner=pr.owner AND lr.object_name=pr.object_name   AND lr.owner=t.owner AND lr.object_name=t.table_name ORDER BY 3 desc;

Oracle dynamic performance views

Oracle data dictionary views

Last updated: 2009-11-05 Thursday