| My OracleGuide | Segment Level Statistics | About Feedback | Products |
|
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 CollectedUsing 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 SettingIssue 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 settingYou 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 StatisticsThe 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 NowAfter 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 |