ITPub博客

首页 > Linux操作系统 > Linux操作系统 > V$SEGMENT_STATISTICS 用法

V$SEGMENT_STATISTICS 用法

原创 Linux操作系统 作者:gaopengtttt 时间:2009-07-02 23:15:47 0 删除 编辑
Using the V$SEGMENT_STATISTICS Dynamic Performance View
2007-08-05 12:45

Using the V$SEGMENT_STATISTICS Dynamic Performance View
Compliments of Brian Peasland, Pipeline SYSOP

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.

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.

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:

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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-608212/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
wxh gp_22389860

注册时间:2008-10-13

  • 博文量
    649
  • 访问量
    2858014