Database News You Can Use
A monthly newsletter for Oracle usersMay 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: SMON Activity
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 Chris Lawson’s book, The Art and Science of Performance Tuning. (US shipping addresses only). Get Oracle 9i Up and Running on Linux—A Step-by-Step Guide
This month’s question comes from Yuri in St. Petersburg, Russia: I see a huge amount of SMON activity even when there are only a few database connections. What’s going on?
Chris Lawson of the Database Specialists team responds: A runaway SMON process can be very damaging to performance. Fortunately, the problem can usually be isolated to one of three causes. Consider these three main possibilities:
1. Temporary segment cleanup: This can occur if a transaction aborts while building lots of temporary extents. If this is the reason for the SMON activity, you can query dba_extents and observe the number of temporary extents decrease as SMON deallocates them:
SELECT COUNT(*) FROM dba_extents WHERE segment_type = 'TEMPORARY';
Note that, since Oracle 7.3, it has been possible to designate the content type for a temporary tablespace as TEMPORARY instead of PERMANENT. And since Oracle 8i, it has been possible to create a locally managed temporary tablespace using temp files. SMON cleanup of extents and segments in these types of temporary tablespaces is less resource-intensive.
2. Coalesce of free extents: Similarly, SMON might be hogging resources while it coalesces free space in a tablespace. If this is the case, you will see a decreasing value for the number of free extents:
SELECT COUNT(*) FROM dba_free_space;
Many DBAs switched to uniform extent sizing methodologies around the time of Oracle 7.3, making tablespace coalescing unnecessary. Locally managed tablespaces have furthered the obsolescence of coalescing free space within a tablespace.
Oracle Support has posted some useful recommendations pertinent to cases 1 and 2 in Metalink note 61997.1. This note explains in detail how to avoid these SMON problems, as well as some useful troubleshooting hints.
3. Rollback in progress: If you recently aborted a very large transaction, SMON may run for a long time to coordinate the rollback. (If an Oracle process crashes in the middle of a transaction, SMON will clean up the aborted sessions.) I recently encountered this problem and found it helpful to query v$session_wait in order to pinpoint exactly which files SMON was reading as it performed the rollback. (Database Specialists has published several popular papers on using Oracle’s wait event facility. You can download these papers and others at http://dbspecialists.wpengine.com/presentations.html.)
If you see SMON consuming a lot of resources on your system, the situation will probably be one of the three identified above. Hopefully, this information will help you better understand and track what SMON is doing, as well as avoid these situations in the future.
We know there are a lot of Linux fans out there, and we’re making it easy for you to get Oracle 9i up and running smoothly! (And while we’re all waiting for Oracle 10g to get established.) Database Specialists, Inc. has published a step-by-step Oracle installation guide for Oracle9i Release 2 on RedHat Linux ES release 3. Authors Roger Schrag and Robert Townsend walk you through all of the details of installing Oracle9i on the Linux platform, including many tips, tricks, and “gotchas” to be aware of. Oracle Database 10g New Features: Easy Management
This detailed guide provides hands on, roll up-your-sleeves-and-get-busy material for Oracle users who want to get an Oracle9i database up and running quickly without reading hundreds of pages of documentation and “readme” files. Our white papers on installing Oracle are among our most popular free resources. Check out this new white paper today at http://dbspecialists.wpengine.com/presentations/oracle920linux.html.
Oracle has been promoting the concept of automatic self-management or “easy management” with Oracle Database 10g. If you’d like a summary of the new features aimed at database management, check out Mike Ault’s article on orafaq.com. Ault recently co-authored a book titled: Oracle Database 10g New Features, and his article provides an overview of Oracle Database 10g new features in areas such as: Weekly Series on Oracle 10g
After you get an overview of Oracle 10g new features, you may want to drill down to more detail. Arup Nanda, Oracle Magazine’s “DBA of the Year” for 2003, is doing a weekly series on 10g features. His goal is to lead readers through the ins and outs of Oracle Database 10g by presenting what he considers the top 20 new features for database administration tasks. This list ranges from the rudimentary, such as setting a default tablespace for new users, to the advanced, such as the new Automatic Storage Management feature. So far, Nanda has covered topics such as:
You’ll find the series, week by week, at http://otn.oracle.com/pub/articles/10gdba/index.html.