• Ask DB Specialists: Heavily Used Segments in the Database
• NoCOUG Event Draws Oracle Gurus to the Bay Area
• Keeping Up with Oracle Support
• Top Downloads from INOUG

You're invited to submit your Oracle-related questions to us at askdbspecialists07@dbspecialists.com. Include your name, telephone number and problem description. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only).

This month’s question comes from Shashi in Southern California: What is the best way to find heavily used segments in the database?

Ian Jones of the Database Specialists team responds: The answer to this question critically depends upon your Oracle database version. Prior to Oracle 9i release 2 (version 9.2.0), there is no straightforward way to obtain usage statistics for individual segments. However, there are some indirect techniques we can use. Since Oracle tracks physical I/O at the data file level, if we allocate the segments we suspect are most heavily used to different tablespaces, we can use the v$filestat view to obtain physical I/O information. E.g.:

        SELECT   c.tablespace_name,
                 a.phyblkrd + a.phyblkwrt total, 
                 a.phyrds, a.phywrts
        FROM     v$filestat a, v$datafile b, dba_data_files c
        WHERE    b.file# = a.file# 
        AND      c.file_id = b.file#
        ORDER BY total DESC;

Another approach is to monitor I/O wait events using v$session_wait, such as wait events of type 'db file scattered read' and 'db file sequential read':

        SELECT   event, p1text, p1, p2text, p2
        FROM     v$session_wait
        WHERE    event LIKE 'db file%'
        AND      state = 'WAITING';
              
        EVENT                    P1TEXT     P1 P2TEXT     P2
        ------------------------ ------ ------ ------ ------
        db file sequential read  file#       2 block#  39627

Taking the file# and block# values from the above wait event we can find the corresponding segment via:

        SELECT   segment_type, segment_name 
        FROM     dba_extents 
        WHERE    file_id = 2
        AND      39627 BETWEEN block_id AND block_id + blocks - 1;

Of course, to monitor segment activity this way over a time period you would need to capture away the wait events periodically and then analyze them. Yet another approach to the problem might be to monitor segment block usage within the buffer cache with the following query:

        SELECT   do.object_type, do.object_name, do.owner, COUNT(*)
        FROM     v$bh bh, dba_objects do
        WHERE    bh.objd = do.data_object_id
        AND      do.owner <> 'SYS'
        GROUP BY do.object_type, do.object_name, do.owner
        ORDER BY COUNT(*) DESC;

In Oracle 9i release 2, Oracle made our lives much easier by introducing the v$segment_statistics view. This makes it trivial to find segment resource usage. E.g.:

        SELECT   owner, object_name, statistic_name, value
        FROM     v$segment_statistics
        WHERE    owner <> 'SYS'
        AND      statistic_name LIKE '%reads%';

The v$segment_statistics view is used by Statspack (as long as we have the snapshot level set to at least 7) to report on segment usage. This is an ideal way to monitor which segments are used most heavily during a specific time interval.

In Oracle Database 10g the same views are available, but they have been superseded by the Automatic Workload Repository (AWR) views dba_hist_seg_stat and dba_hist_seg_stat_obj. In addition to the AWR report script available at $ORACLE_HOME/rdbms/admin/awrrpt.sql, Oracle also provides automatic analysis of the raw data via the Automatic Database Diagnostic Monitor (ADDM). The recommendations from ADDM are available using Enterprise Manager or by using the new dbms_advisor package. E.g.:

        SET LONG 500000

        SELECT   dbms_advisor.get_task_report (task_name)
        FROM     dba_advisor_tasks t
        WHERE    t.task_id = (SELECT MAX (t1.task_id)
                              FROM   dba_advisor_tasks t1,
                                     dba_advisor_log l1
                              WHERE  t1.advisor_name = 'ADDM'
                              AND    l1.task_id = t1.task_id 
                              AND    l1.status = 'COMPLETED');

Finally, the recommendations can also be seen by running the provided report script $ORACLE_HOME/rdbms/admin/addmrpt.sql

As you can see, the best way to monitor segment usage within the database depends on which version of Oracle you are using. Prior to Oracle 9i release 2 you could not monitor segment statistics directly and instead had to infer this information from datafile statistics, wait events, or buffer cache contents. Oracle 9i release 2 made this task much easier with the introduction of the v$segment_statistics view, and Oracle Database 10g has taken this to a whole new level with AWR and ADDM.

NoCOUG Event Draws Oracle Gurus to the Bay Area

The Northern California Oracle Users Group (NoCOUG) has lined up some outstanding speakers for their summer conference. It's scheduled for Thursday, August 19 in San Ramon, California. Some of the Oracle gurus who will be sharing their knowledge include authors Tom Kyte, Jonathan Lewis, and Peter Koletzke. Here is a sampling of what's in store for Oracle DBAs and developers:

• Inside Multiversioning by Tom Kyte, Oracle Corporation
• The Evolution of Optimization 8i to 10g by Jonathan Lewis
• Introduction to ADF in JDeveloper 10g—Is it Oracle Forms Developer Yet? by Peter Koletzke
• Common Performance Monitoring Mistakes to Avoid by Virag Saksena