====================
V$SEGMENT_STATISTICS
====================
这个视图的作用：
它能实时监控段级别统计项，可用于鉴定性能问题源于表或者索引。可用于展示段的使用情况，及对象的统计信息。
比如，哪些表和索引被经常使用，哪些对象上存在频繁的逻辑读和物理读。这个视图对优化有很好的作用。
视图结构
SQL> desc v$segment_statistics
Name Null? Type
----------------------------------------------------------------- -------- ---------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
TS# （表空间标识） NUMBER
OBJ# （字典标识） NUMBER
DATAOBJ# （数据对象标识） NUMBER
OBJECT_TYPE VARCHAR2(18)
STATISTIC_NAME （统计项名称） VARCHAR2(64)
STATISTIC# （统计项标识） NUMBER
VALUE （统计项值） NUMBER
统计项名称，V$SEGMENT_STATISTICS会对这些统计项采样
SQL> select distinct(STATISTIC_NAME) from v$segment_statistics;
STATISTIC_NAME
----------------------------------------------------------------
gc buffer busy
db block changes
optimized physical reads
space used
segment scans
gc cr blocks received
gc current blocks received
row lock waits
buffer busy waits
physical reads
physical reads direct
physical writes
physical write requests
space allocated
logical reads
physical read requests
physical writes direct
ITL waits
18 rows selected.
该视图的用法：
1.通过该性能视图找出gc buffer busy等待事件前10的对象
with stats as (
select owner,object_name,statistic_name,value
from v$segment_statistics
where statistic_name='gc buffer busy'
order by value desc
)
select * from stats where rownum<=10
/
OWNER OBJECT_NAME STATISTIC_NAME VALUE
------------------------------ ------------------------------ -------------------- ----------
SYS WRH$_SEG_STAT_PK gc buffer busy 0
DBSNMP BSLN_TIMEGROUPS_PK gc buffer busy 0
SYS IND$ gc buffer busy 0
DBSNMP BSLN_BASELINES gc buffer busy 0
SYS I_OBJ# gc buffer busy 0
SYS WRH$_DLM_MISC gc buffer busy 0
DBSNMP BSLN_BASELINES_PK2 gc buffer busy 0
SYS WRH$_DLM_MISC_PK gc buffer busy 0
SYS TS$ gc buffer busy 0
SYS I_TS# gc buffer busy 0
10 rows selected.
2.查出索引自启动来是否被使用。如果physical reads 统计项为0，说明这个索引从来没有使用过。
相对于监控索引，这个方法更方便快捷，同时减轻了数据库不必要的监控索引负担。
SELECT owner,object_name,statistic_name,value
from v$segment_statistics
WHERE STATISTIC_NAME='physical reads'
and object_name='PK_EMP';
OWNER OBJECT_NAME STATISTIC_NAME VALUE
------------------------------ ------------------------------ -------------------- ----------
SCOTT PK_EMP physical reads 3
在查v$segment_statistics资料时，有人说明明索引用了好几次了，但是physical reads和logical reads的value都不增加。
详见itpub：http://www.itpub.net/thread-1407139-1-1.html
我通过实验得出的结论是：
如果session 1执行一条固定SQL，A索引第一次使用，那么肯定A索引的physical reads的value肯定会增加，这时A索引的logical reads的value是0。
而后，session 1继续执行该SQL，A索引的physical reads的value不变，logical reads的value增加。但是session 1继续执行该SQL，physical reads
和logical reads的value都不会变化。这是因为A索引已经缓存到了pga中，所以无需继续读BUFFER_CACHE中的A索引缓存，故logical reads也不会增加。
但是，如果打开一个新session去执行session 1 执行的SQL，那么A索引的physical reads values不变，logical reads的value增加。
3.物理IO最多的10个表
select table_name,total_phys_io
from ( select owner||'.'||object_name as table_name,
sum(value) as total_phys_io
from v$segment_statistics
where owner!='SYS' and object_type='TABLE'
and statistic_name in ('physical reads','physical reads direct',
'physical writes','physical writes direct')
group by owner||'.'||object_name
order by total_phys_io desc)
where rownum <=10;
TABLE_NAME TOTAL_PHYS_IO
------------------------------------------------------------- -------------
SCOTT.EMP 22
DBSNMP.BSLN_BASELINES 6
XDB.XDB$ROOT_INFO 6
APEX_030200.WWV_FLOW_PLATFORM_PREFS 3
SYSTEM.SQLPLUS_PRODUCT_PROFILE 2
SYSTEM.AQ$_QUEUES 2
SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_L 1
SYSMAN.AQ$_MGMT_LOADER_QTABLE_L 1
SYSTEM.REPCAT$_REPPROP 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_L 1
10 rows selected.
4.逻辑读最高的10个对象
col OBJECT_TYPE for a10
col OBJECT_NAME for a60
select *
from (select object_type,owner||'.'||object_name as object_name, statistic_name, value
from V$SEGMENT_STATISTICS
where statistic_name = 'logical reads'
and owner not in ('SYS','SYSMAN','DBSNMP','SYSTEM')
order by 3 desc)
where rownum < 11;
OBJECT_TYP OBJECT_NAME STATISTIC_NAME VALUE
---------- ------------------------------------------------------------ -------------------- ----------
INDEX XDB.XDB$SCHEMA_URL logical reads 0
INDEX EXFSYS.RLM$SCHACTIONORDER logical reads 0
INDEX EXFSYS.RLM$JOINQKEY logical reads 0
TABLE XDB.XDB$ROOT_INFO logical reads 16
TABLE WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_L logical reads 16
INDEX WMSYS.SYS_IOT_TOP_13792 logical reads 0
TABLE SCOTT.EMP logical reads 0
INDEX SCOTT.PK_EMP logical reads 48
INDEX APEX_030200.WWV_FLOW_PLATFORM_PREFS_FKIDX logical reads 0
TABLE APEX_030200.WWV_FLOW_PLATFORM_PREFS logical reads 0
10 rows selected.
参考资料：
http://blog.sina.com.cn/s/blog_5d3da3280100n313.html
阅读(4585) | 评论(0) | 转发(0) |
