Oracle9iR2 Segment Level Statistics

Friday Nov 14th 2003 by James Koopmann

Oracle's Segment Level Statistics gives you the power to make decisions on the configuration of structures within Oracle as well as to application code. Learn how to fine tune memory structures and access methodologies using Oracle's Segment Level Statistics.

Detection of which tables or indexes are being accessed the most will allow you to fine tune memory structures and access methodologies. Let's take a look at Oracle's segment level statistics and determine which objects are being accessed the most by our applications.

If you have ever been a DBA in a shop where you knew nothing about the applications running or work with a group of developers that have no time to explain what was going on within their applications, you will quickly become agitated with wanting to know the details of the transaction mix in your database. Without being able to determine how much and how often particular data objects are being accessed, you will be unable to tune the database engine. Thankfully, Oracle has recently come out with an advisory that will tell us the performance on individual objects within the database. With this advisory, we can determine where the hot spots for particular objects reside and take appropriate action.

What are Segment Level Statistics

Segment level statistics are statistics Oracle keeps on the individual segments (read tables and indexes) that allow you to determine where performance problems may exist when accessing the segment in question. The statistics associated with segments are located in the three views explained in Listing 1. If you were to read the description of these three views you can clearly see that the one to pay most attention to is the V$SEGMENT_STATISTICS since it contains everything the V$SEGSTAT_NAME and V$SEGSTAT contain.

Listing 1.
Views used for Segment Level Statistics


This view is just a reference view that contains a description for each of the segment level statistics collected. Be warned that two of the statistics are only sampled while the others are accumulated. Take note that the STATISTICS# is not the same as in V$STATNAME.


This is what Oracle calls the "highly efficient" view for real time monitoring of segment level statistics.


This is what Oracle calls the "user-friendly" view for looking at segment level statistics. It is the same as V$SEGSTAT but with easily recognizable object properties such as owner and segment_name.

How to Ensure Segment Level Statistics are Being Collected


Using the new Oracle initialization parameter called STATISTICS_LEVEL, you can start collecting segment level statistics. This parameter has three settings BASIC, TYPICAL, and ALL and you must set STATISTICS_LEVEL to TYPICAL or ALL if you want to take advantage of segment level statistics.

BASIC - which does nothing, basically turned off.

TYPICAL/ALL - will cause Segment Level Statistics to be collected.

Check Your Current Setting

Issue the following command to determine what your setting if for STATISTICS_LEVEL.

SQL> SHOW PARAMETER statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
statistics_level                     string      TYPICAL

To change your setting

You may change the current setting of STATISTICS_LEVEL by issuing the following ALTER statement.


Use of Segment Level Statistics

The main purpose of segment level statistics is to further investigate system level performance problems. It is thus recommended that you first take a look at what performance problems you are having at the system level. Listing 2 show a snippet of output from selecting information from the V$SYSTEM_EVENT view. As you can see, the blunt of the problem here is reading and writing to files as well as buffer activity. Now that we know this, we would like to look at the segment level statistics to determine which objects are requiring the most reads, writes, and buffer activity. Listing 3 shows the SQL and output necessary to answer the previous question for determination of which objects are causing the performance bottleneck in relation to reads and writes. Listing 4 shows the SQL and output necessary to answer the question of which objects are producing performance problems in the buffer cache. Both of these listings' outputs have been reduced to show just the top five rows but you can quickly see that there is a common theme in these tables that is producing performance problems in the reads category. In addition, the writes category is causing problems in the buffer cache.

Listing 2.
System Wait Events

SQL> select event,total_waits,total_timeouts,time_waited 
      from v$system_event 
     order by time_waited desc;

------------------------------- ----------- -------------- -----------
db file scattered read              2210020              0      371904
db file sequential read             1159422              0      221027
log file sync                        212269            200      218890
log file parallel write              249552         219936      170575
db file parallel write                33238          16585      158999
control file parallel write           55521              0       97442
enqueue                                 647            123       62090
control file sequential read        1076963              0       60591
buffer busy waits                    237999             44       43843
direct path read                      47758              0       29635
latch free                            52519          36273       15263
log buffer space                        790             63       13241

Listing 3.
Determination of which objects are producing read/write performance problems

SQL> select owner,object_type,object_name,value
  3   where (statistic_name like '%read%'
  5      or  statistic_name like '%write%')
  6*  order by value desc

------- ------------- ------------------- --------
OCPOLTP  TABLE        OLTP_MANAGER        15752896
OCPOLTP  INDEX        PK_OLTP_MANAGER     10716704
OCPOLTP  INDEX        PK_OLTP_SALES       10615296
OCPOLTP  TABLE        OLTP_SALES           7050448

Listing 4.
Determination of which objects are producing buffer cache performance problems

SQL> l
  1  select owner,object_type,object_name,value
  3   where statistic_name = 'buffer busy waits'
  4*  order by value desc

-----   ------------- ------------------- ----------
OCPOLTP TABLE         OLTP_SALES              144623
OCPOLTP TABLE         OLTP_SALES_VALUE         52295
OCPOLTP TABLE         OLTP_EVENTS               1059
OCPOLTP TABLE         OLTP_SERVICES              320

What to do Now

After determining which objects are causing performance problems through the use of segment level statistics, it is now up to you as a DBA to determine what the best methodology is to reduce the bottleneck. If you have access to the code and developers, you may wish to see if there is any ill-formed logic in their code. If you are unable to gain access to the keys to the code you may be forced to tackle the problem by introducing some modification to memory structures or to the objects themselves. In my particular situation it was a combination of increasing the default buffer cache, assigning certain objects to a KEEP buffer cache, and changes to a few table structures.


This new feature of obtaining segment level statistics empowers a DBA to make decisions on the configuration of structures within Oracle as well as to application code that have never been available before. The ability to pinpoint objects that are degrading system performance and zeroing in on what areas the performance hit is impacting is vital to your ability to tune Oracle. Please remember that the over all system impact of collecting statistics is negligible compared to the benefits. There is no reason not to start using this feature.

» See All Articles by Columnist James Koopmann

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved