July 2004

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

July 2004
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Heavily Used Segments in the Database

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). NoCOUG Event Draws Oracle Gurus to the Bay Area

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

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.


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: Keeping Up with Oracle Support

  • 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

For details and a complete conference schedule, check out NoCOUG’s website. Be sure to check the NoCOUG website after the conference for presentations and downloads that may be available.


Like any software vendor, Oracle Corporation expects customers to upgrade their database systems periodically and, therefore, stops providing support for older database versions at a certain point. There are some interesting surprises buried deep in Oracle Support bulletins, however. So when you are planning a database upgrade, it is important to know how long Oracle will support each version. Top Downloads from INOUG

You probably know that error correction support (ECS) for Oracle 8i release 3 (8.1.7) was extended through December of this year. But did you know that ECS for Oracle 9i release 1 (9.0.1) already ended last December? And for those of you currently planning to upgrade your databases to Oracle 9i release 2 (9.2.0), did you know that Oracle has plans to end ECS next year? This last date still appears to be tentative. But, wouldn’t you hate to work so hard to upgrade your databases only to learn that you will still lose ECS in less than 18 months?

Oracle has been known to extend error correction support at the last minute—they extended ECS for Oracle 8i release 3 for one additional year for many platforms at the last minute. Your best bet is to check the Oracle Support bulletins available on Metalink on a regular basis. Here are a few document numbers to get you started:

Oracle Database Version ECS Ends Metalink Document ID
8i release 3 (8.1.7) 12/31/03 250629.1
8i release 3 (8.1.7) 12/31/04 148054.1
9i release 1 (9.0.1) 12/31/03 201685.1
9i release 2 (9.2.0) 12/31/05 190435.1
10g release 1 (10.1.0) 01/31/07 190435.1


The Indiana Oracle Users Group (INOUG) has a handy section of resources on their website. It is organized by the most popular downloads. In their top ten downloads, you’ll find topics such as:

  • Top 10 New Features in Oracle 9i
  • Debugging With Oracle Trace
  • Oracle Backup and Recovery Tips and Techniques
  • Oracle 9i New Features for Developers
  • Backup and Recovery 101
  • Intermediate SQL*Plus
  • Advanced Replication vs. Streams

To learn more about these topics and see the other presentations and papers available, visit the INOUG website.

Leave a Reply

Your email address will not be published. Required fields are marked *