Problem solve Get help with specific problems with your technologies, process and projects.

Oracle segment statistics

The V$SEGMENT_STATISTICS dynamic performance view gives many statistics about each and every segment that is used by the Oracle database. Here's how to use it.

Introduction

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.

Segments and Statistics

In the Oracle database, a segment is any database object that requires physical storage space, aside from the DDL of that object. Segments include tables, indexes, clusters, partitions, temporary segments, undo segments, and LOB segments. Objects like views, procedures, triggers are not included in the list of segments. These objects do not need to allocate an extent to reside in the database. They are only stored in the data dictionary. While the definition of all segments reside in the data dictionary, the segment itself lives outside the data dictionary, hopefully in a non-SYSTEM tablespace.

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.

Many people also wanted to answer the question of whether a particular index was ever used. If an index is not being used, then we want to remove it so that it does not unnecessarily hamper performance of DML statements. The answer to this question often was to query V$BH, which shows the blocks currently in the database buffer cache. If the index had any block in the buffer cache then it must have been used at one time. But what happens when the index block gets aged out of the cache? One had to make sure to query V$BH with a short interval between samples to see if any index block was in the cache. This method could never guarantee that the index block was not aged out of the cache between queries on V$BH. Oracle 9i addressed this issue with the ALTER INDEX MONITORING USAGE command and the V$OBJECT_USAGE view. This is a much better solution, but you do have to turn on monitoring for that index some time before you can query V$OBJECT_USAGE to see if the index has ever been used.

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.

Answering Questions

Oracle 9i has included the V$SEGMENT_STATISTICS view to help give definitive answers to many questions. We can now know exactly if an index has been used, without turning on monitoring. We can also know which tables have the highest physical I/O activity.

If you want to know if an index has ever been used since instance startup, the solution is quite easy. 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 owner='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 owner='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.

Next, we'll see 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 what the exact table is that experiences the most physical I/O operations. Appropriate actions can now be taken to isolate this potential hotspot from other highly active database segments.

If you've ever dealt with wait events, you may have seen the 'buffer busy waits' event. This event occurs when one session is waiting on another session to read the buffer into the cache, or some other session is changing the buffer. This even can often be seen when querying V$SYSTEM_EVENT. If I query my database, I have approximately 13 million waits on this specific event.

GASP SQL> select event,total_waits from v$system_event
  2  where event='buffer busy waits';

EVENT                                    TOTAL_WAITS
---------------------------------------- -----------
buffer busy waits                           12976210

The big question is to determine which segments are contributing to this overall wait event. Querying V$SEGMENT_STATISTICS can help us determine the answer.

SQL> select segment_name,object_type,total_buff_busy_waits
  2  from ( select owner||'.'||object_name as segment_name,object_type,
  3                value as total_buff_busy_waits
  4         from   v$segment_statistics
  5         where  statistic_name in ('buffer busy waits')
  6         order by total_buff_busy_waits desc)
  7* where rownum <=10
GASP SQL> /

SEGMENT_NAME                        OBJECT_TYPE   TOTAL_BUFF_BUSY_WAITS
----------------------------------- ------------- ---------------------
WEBMAP.SDE_BLK_1103                 TABLE                      10522135
WEBMAP.SDE_BLK_804                  TABLE                       1176185
SRTM.SDE_BLK_1101                   TABLE                        651175
WEBMAP.SDE_BLK_804_UK               INDEX                        100242
SYS.DBMS_LOCK_ALLOCATED             TABLE                         64695
NED.SDE_BLK_1002                    TABLE                         48582
WEBMAP.BTS_ROADS_MD                 TABLE                         27068
WEBMAP.SDE_BLK_1103_UK              INDEX                         25707
ARCIMS.SDE_LOGFILE_DATA_IDX1        INDEX                         24618
NED.SDE_BLK_62                      TABLE                         14710

10 rows selected.

From the query above, we can see that one specific table contributed 10.5 million, or approximately 80%, of the total waits. Without V$SEGMENT_STATISTICS, we would have not been able to get this accurate of a picture. It would have been very difficult to get determine that one table was contributing 80% of the buffer busy wait events.

Conclusions

Hopefully, I've shown how the Oracle 9i database has gone another step forward by giving DBAs the information that they need to solve some of their problems. 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.

About the Author

Mr. Peasland has been in the IT field for 15 years working as a Computer Operator, Operations Analyst, Systems Administrator, Application Developer, and finally a Database Administrator. He holds a B.S. in Computer Science and a M.S. in Computer Science specializing in Database Systems. Additionally Mr. Peasland holds OCP DBA credentials for Oracle 7.3, 8, and 8i. He currently works for SGT, Inc. as a Database Administrator.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close