Intelligent Segment Resource Estimation

The DBA can use the following dynamic performance queries to find performance problems associated with individual segments. Using segment-level statistics, you can identify top tables or indexes in the database instance and pinpoint the resource-intensive database objects. This information will help to allocate more resources to the database for various processing cycles or job schedules.

• V$SEGSTAT_NAM has the segment statistics being collected, along with properties of each statistic.

• V$SEGSTAT is a real-time view that shows the statistic value, statistic name, and other basic information on all segments including the top segments causing buffer busy waits.

• V$SEGMENT_STATISTICS has all the information from V$SEGSTAT and details on segment owner and tablespace name.

The following code selects the top 20 segments by statistic name from the V$SEGSTAT since the database instance was started:

select owner, object_name, object_type, statistic_name, value
from (select dbo.owner, dbo.object_name, dbo.object_type, vss.value,
vss.statistic_name, row_number () over (order by value desc)
from dba_objects dbo, v$segstat vss
where dbo.object_id = vss.obj#  ) where rownum <= 20 ;

The V$SEGMENT_STATISTICS view was introduced in Oracle 9.2. This dynamic performance view enables you to see many different statistics on the usage of segments since the database instance startup. You do not have to turn monitoring on or perform any extra steps to use it. Listing 8.2 shows many statistics available in Oracle Database 10g.

Listing 8.2. Statistics in Oracle Database 10g

SQL>  select distinct statistic_name from v$segment_statistics;

STATISTIC_NAME
------------------------------------
ITL waits
buffer busy waits
db block changes
gc buffer busy
gc cr blocks received
gc current blocks received
logical reads
physical reads
physical reads direct
physical writes
physical writes direct
row lock waits
segment scans
space allocated
space used

15 rows selected.

Using the statistics from this view, you can identify which table has the highest physical I/O activity and whether an index has been used during a search. You can also customize the query by owner, object name, and statistic name.

select statistic_name, value from v$segment_statistics
where owner ='owner' and object_name ='objectname'
and statistic_name='statisticname';