Database News You Can Use
A monthly newsletter for Oracle users
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 email@example.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 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.
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.