通过V$SEGMENT_STATISTICS可以了解表空间内某个对象,如表、索引,从数据库启动后,统计事件发生的次数,也就是VALUE值。
其中,统计事件有:
space used -----------空间使用
space allocated -----------空间分配
segment scans ----------段扫描
row lock waits ----------行锁等待
physical writes direct -----------物理直接写入
physical writes -----------物理写入
physical write requests ------------物理写入请求
physical reads direct ------------物理直接读取
physical reads ------------物理读取
physical read requests -------------物理读取请求
optimized physical reads------------优化物理读
logical reads --------------逻辑读取
gc current blocks received
gc cr blocks received
gc buffer busy
db block changes --------------数据库块改变数
buffer busy waits ---------------数据高速缓存忙等待
ITL waits --------------ITL等待
可以解决的问题:
第一:查找出耗费物理I/O就多的对象,如表;
第二:没有被使用过的对象,如索引;
第三:那个对象被修改得最频繁,如表;
第四:到查询为止,有一个对象有多少的物理读和逻辑读;
第五:哪个对象的数据行被争用最多,如表;
第六:哪个对象的缓冲区数据块等待次数最多;
下面是V$SEGMENT_STATISTICS系统视图的结构类型:
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 |
其他两个跟段有关的统计视图为
V$SEGSTAT; V$SEGSTAT_NAME;
可以得到的统计信息其实差不多,但没有V$SEGMENT_STATISTICS的统计信息那么完全。
V$SEGSTAT
V$SEGSTAT
displays information about segment-level statistics.
Column | Datatype | Description |
---|---|---|
|
| Tablespace number |
|
| Dictionary object number |
|
| Data object number |
|
| Name of the statistic |
|
| Statistic number |
|
| Statistic value |
V$SEGSTAT_NAME
V$SEGSTAT_NAME
displays information about segment-level statistics properties.
Column | Datatype | Description |
---|---|---|
STATISTIC# | NUMBER | Statistic number |
NAME | VARCHAR2(64) | Name of the statistic |
SAMPLED | VARCHAR2(3) | Indicates whether the statistic was collected by sampling (YES ) or not (NO ) |