Oracle9iR2 Segment Level Statistics
November 14, 2003
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.
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 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.
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.