今天学习V$SEGMENT_STATISTICS 视图
看官方文档：
V$SEGMENT_STATISTICS displays information about segment-level statistics.
sql>desc V$SEGMENT_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

Using the V$SEGMENT_STATISTICS Dynamic Performance View
动态性能视图v$segment_statistics 的使用
Compliments of Brian Peasland, Pipeline SYSOP
sys 组 Brian Peasland,Pipeline 编辑
For many years, Oracle DBAs have been trying to get answers to questions on the usage of various database 
segments (i.e. tables and indexes) within their databases. They often wonder if an index is being used, or 
which table is accessed the most, or which table has the most changes applied to it. There have been many 
esoteric methods employed to try to answer these questions. The 9i release of the Oracle RDBMS finally 
gives us a definitive source to determine many statistics on any database segment, the V$SEGMENT_STATISTICS 
dynamic performance view.
很多年来，DBA们都试图对数据库的段了解的更清楚， 都疑惑我们创建的索引是否被引用了，那张表访问的最多，或者那张表变化的最多。
有很多复杂的方式去获得这些信息， 在9i 版本的时候 ORACLE RDBMS 终于给我们提供了一个确定的视图去获得数据库段的统计信息。也就是 V$SEGMENT_STATISTICS  动态性能视图。
Before Oracle 9i, there was no easy way to get information, or statistics, about the usage of various 
segments. If one wanted to know how many physical reads or writes occurred on a specific table, the only 
way to know for sure was to place that table in its own tablespace and then query the PHYRDS or PHYWRTS 
columns of the V$FILESTAT view. If other tables were in this tablespace, their physical read and write 
counts would show up for that tablespace as well. The problem with this method was that a segment must be 
the only object in that tablespace for the method to work accurately.
在9i 之前获得段的各项统计信息并不是一件容易的事情， 如果某个DBA 想要知道指定表上的物理读或者写是多少，唯一的方式是通过另外一张性能视图v$filestat的phyrds 和phywrts 字段去查询到.并且前提是要确定这张表在那个表空间，但是如果这个表空间里面还有其它的表存在 也会被同样查询出来。 通过这种方式查询出来的缺陷在于必须把一张表放在一个表空间里面。
Oracle 9i introduces the new V$SEGMENT_STATISTICS dynamic performance view. This view lets you see many 
different statistics on the usage of segments since instance startup. You do not have to turn on monitoring 
or take any special steps to begin using this view to answer your questions. The query below shows the 
statistics that you can get with the Oracle 9.2.0.2 release:
ORACLE 9i  引入了新的 V$SEGMENT_STATISTICS  动态性能视图， 通过这个视图 你可以知道 自从数据库启动以来 不同的段的使用情况，你不需要使用其它复杂的方式去找到你问题的答案。
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.
Future release of the Oracle RDBMS may include more statistics.
If you want to know if an index has ever been used since instance startup, simply query V
$SEGMENT_STATISTICS to see if there has even been a physical read on the index in question. Queries similar 
to the following can help:
SQL> select statistic_name,value
2 from v$segment_statistics
3 where wner='SDE' and object_name='LAYERS_PK'
4 and statistic_name='physical reads';
STATISTIC_NAME VALUE
---------------------------------------- ----------
physical reads 6094
SQL> select statistic_name,value
2 from v$segment_statistics
3 where wner='SDE' and object_name='LAYERS_IX1'
4 and statistic_name='physical reads';
no rows selected
The first query shows that 6,094 physical reads have been performed on the LAYERS_PK index. This index has 
obviously been used before. The second query shows that no physical reads have ever occurred on the 
LAYERS_IX1 index. If there have never been any physical reads on this index then it has never been used.
The next example demonstrates how to use V$SEGMENT_STATISTICS to determine the top 10 tables that have 
incurred the most physical I/O operations.
SQL> select table_name,total_phys_io
2 from ( select owner||'.'||object_name as table_name,
3 sum(value) as total_phys_io
4 from v$segment_statistics
5 where owner!='SYS' and object_type='TABLE'
6 and statistic_name in ('physical reads','physical reads direct',
7 'physical writes','physical writes direct')
8 group by owner||'.'||object_name
9 order by total_phys_io desc)
10* where rownum <=10;
TABLE_NAME TOTAL_PHYS_IO
----------------------------------- -------------
WEBMAP.SDE_BLK_1103 43152119
SRTM.SDE_BLK_1101 35526039
WEBMAP.SDE_BLK_1046 35155063
WEBMAP.SDE_BLK_1110 20941514
WEBMAP.SDE_BLK_1106 15487605
WEBMAP.SDE_BLK_1102 10414181
NED.SDE_BLK_1002 9247263
SRTM.SDE_BLK_1104 6386630
NED.SDE_BLK_1068 5313627
WEBMAP.SDE_BLK_804 5175899
10 rows selected.
The query above eliminated any data dictionary tables from the results. It should now be clear which exact 
table experiences the most physical I/O operations. Appropriate actions can now be taken to isolate this 
potential hotspot from other highly active database segments.
The V$SEGMENT_STATISTICS dynamic performance view gives many statistics about each and every segment that 
is used by the database. One can now accurately tell if an index has been used, and how much. One can 
easily determine the top-N "hot" segments to aid in performance tuning. There are many other questions that 
can now be answered with this dynamic performance view.