Oracle9iR2 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

How to Ensure Segment Level Statistics are Being Collected

STATISTICS_LEVEL

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.

SQL> ALTER SYSTEM SET statistics_level=TYPICAL SCOPE=SPFILE;

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;
EVENT                           TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
——————————- ———– ————– ———–
…
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
  2    from V$SEGMENT_STATISTICS
  3   where (statistic_name like ‘%read%’
  5      or  statistic_name like ‘%write%’)
  6*  order by value desc
OWNER   OBJECT_TYPE   OBJECT_NAME            VALUE
——- ————- ——————- ——–
OCPOLTP  TABLE        OLTP_MANAGER        15752896
OCPOLTP  INDEX        PK_OLTP_MANAGER     10716704
OCPOLTP  INDEX        PK_OLTP_SALES       10615296
OCPOLTP  TABLE        OLTP_SALES_VALUE     8491008
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
  2    from V$SEGMENT_STATISTICS
  3   where statistic_name = ‘buffer busy waits’
  4*  order by value desc
OWNER   OBJECT_TYPE   OBJECT_NAME              VALUE
—–   ————- ——————- ———-
OCPOLTP TABLE         OLTP_SALES              144623
OCPOLTP TABLE         OLTP_SALES_VALUE         52295
OCPOLTP TABLE         OLTP_EVENTS               1059
OCPOLTP TABLE         OLTP_SERVICES              320
OCPOLTP INDEX         OLTP_SALES_CLASS_IDX       300

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.

Conclusion

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