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'; 



    Oracle Database 10g Insider Solutions
    SUSE LINUX Enterprise Server 9 Administrators Handbook
    ISBN: 672327910
    EAN: 2147483647
    Year: 2006
    Pages: 214

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net