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

V$SEGSTAT_NAME

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.

V$SEGSTAT

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

V$SEGMENT_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

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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles