数据段中的数据最终在使用中会被用户访问,哪些表和索引会被频繁使用,哪些对象上存在频繁的逻辑读和物理读,这些信息对于性能优化具备极其重要的意义。Oracle数据库通过段级统计信息的收集来提供这方面的信息。
段级统计信息的收集和记录主要是通过以下几个动态性能视图实现的:V$SEGSTAT_NAME、V$SEGSTAT、V$SEGMENT_STATISTICS。
V$SEGSTAT_NAME视图记录了数据库收集段级统计信息的内容,在Oracle Database 10gR2中,以下信息会被不同程度的收集和采样:SQL> select * from v$segstat_name;
STATISTIC# NAME SAM
---------- ---------------------------------------------------------------- ---
0 logical reads YES
1 buffer busy waits NO
2 gc buffer busy NO
3 db block changes YES
4 physical reads NO
5 physical writes NO
6 physical reads direct NO
7 physical writes direct NO
9 gc cr blocks received NO
10 gc current blocks received NO
11 ITL waits NO
12 row lock waits NO
14 space used NO
15 space allocated NO
17 segment scans NO
在Oracle Database 11gR2中,可记录的段级信息增加了以下三种:STATISTIC# NAME SAM
---------- ---------------------------------------------------------------- ---
6 physical read requests NO
7 physical write requests NO
11 optimized physical reads NO
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
例如如下一条SQL可以帮助我们找到当前数据库中逻辑读最高的10个对象:SQL> select *
2 from (select object_name, statistic_name, value
3 from V$SEGMENT_STATISTICS
4 where statistic_name = 'logical reads'
5 order by 3 desc)
6 where rownum
OBJECT_NAME STATISTIC_NAME VALUE
------------------------------ ---------------------------------------- ----------
TOVERDUEDETAIL_PK logical reads 679489664
TMONTHOVERDUE_PRIMARYKEY logical reads 519297536
TLASTYE_PK logical reads 451140976
TOVERDUEDETAIL logical reads 345610752
TMONTHOVERDUE logical reads 274533968
RATIFYTASK logical reads 232831952
TLASTOVERDUE_PK logical reads 211762720
SYS_LOB0000056381C00085$$ logical reads 176265312
TLASTYE logical reads 154691600
CCODE logical reads 145472848
这部分内容在AWR报告中更为详细的展现出来(关于AWR的详细内容请参考后面章节),以下是Oracle Database 11g中生成的AWR报告展示的段级统计信息内容:
我们就可以据此分析数据库中是否有部分对象的访问过于集中,等待过于频繁,例如对于ITL等待较高的对象,则可以考虑为其增加更多的事务槽,以下是一个AWR报告段级信息摘录,这部分信息对于性能分析诊断极其重要:
上图逻辑读最高的三个对象已经占有了系统整体逻辑读的45%左右,是值得分析和注意的。