Segment Level Statistics

Detecting which tables or indexes are being accessed the most will allow you to fine tune memory structures and access methodologies. 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. 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. If you knew nothing about the applications running, you may want to know the details of the transaction mix in the database.

Oracle has come out with an advisory that will tell us the performance on individual objects within the database. So we can determine where the hot spots for particular objects reside and take appropriate action.

The performance statistics associated with segments are available in the view V$SEGMENT_STATISTICS

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.

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 from the V$SYSTEM_EVENT view. If the problem is in Disk IO or buffer activity, then it is better to look at the segment level statistics to obtain the statistics related to objects. The waits associated with events "db file scattered read", "db file sequential read" and "buffer busy waits" has to be considered.

System Wait Events

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

Determination of which objects are producing read/write performance problems

SQL> select object_name, reads,writes, physical_reads, buffer_busy, 
block_changes, round(100*(1-physical_reads/reads)) hits
FROM(
select object_name,
max(case when STATISTIC_NAME like '%reads%' 
	THEN value else null end) reads,
max(case when STATISTIC_NAME like '%writes%' 
	THEN value else null end) writes,
max(case when STATISTIC_NAME='physical reads' 
	THEN value else null end) physical_reads,
max(case when STATISTIC_NAME='buffer busy waits' 
	THEN value else null end) buffer_busy,
max(case when STATISTIC_NAME='db block changes' 
	THEN value else null end) block_changes
FROM v$segment_statistics 
GROUP BY object_name) where reads>0;

What to do Now

After determining which objects are causing performance problems choose a methodology to reduce the bottleneck. If you have access to the code and developers, see if there is any ill-formed logic in the code. The other option is to modify the memory structures for objects