Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
Oracle9i High-Performance Tuning with STATSPACK Reviewed
by Marc Adlam
From the San Diego Oracle Users Group -  www.sdoug.org

5 Stars

Buy this Oracle book here


For even more monitoring scripts, check-out Burleson's latest book Creating a self-tuning Oracle Database by Rampant TechPress.  The online code depot is available immediately.


I suppose you are a little bit like me. You saw a book title purporting to cover "High-Performance Tuning" and this promising new STATSPACK utility from Oracle, and your curiosity got the best of you. Perhaps you are a DBA, or perhaps you're a so-called database tuning expert. Well, if you are in fact anything like me, you're ultimately called on to be both. After all, although the "A" in DBA might lead you to believe that your primary duties revolve solely around administration, you quickly come to realize that's not the case at all. In fact, while the "administrative" duties of backup, recovery, space management, data loading, user management (and on and on) do provide a convenient job description for the folks in HR and management, the reality in most Oracle shops has historically been much different than that. Everywhere you go, the DBA is typically expected to be part guru and part magician, a veritable miracle worker in the area of database system performance.

That being the case, you might assume that Oracle would have a long and venerable history of providing wonderful diagnostic tools for monitoring, tracking, and predicting performance. These tools would, of course, be included with the base product (at no extra cost) and would be remarkably sophisticated yet easy to use. Alas! You wake from this dream only to realize you are sorely mistaken. Instead, Oracle's baseline tools include remarkably crude utilities such as UTLBSTAT and UTLESTAT. And no, that file is not encrypted, it's just the standard report output from tkprof! If you want a fancy GUI, start seeking budget approval from your boss, because you'll have to pay Oracle extra for the Diagnostics and Tuning Packs (or else look to other reputable vendors like Quest and Embarcadero).

Well, I'm happy to report that all of that madness has finally changed! Oracle STATSPACK is here; it has evolved into a truly useful and flexible tuning framework; and prominent Oracle author Don Burleson provides over 600 pages of proof in this, his latest book from Oracle Press.

In the crazy little world of database tuning experts, the advent of STATSPACK has been nothing short of a godsend. Originally introduced as a downloadable add-on from Oracle Technet during the 8i era, STATSPACK has found its way into the standard database distribution and continues to evolve with each new release. Yet while Burleson centers his book around STATPACK, he really tries to emphasize the use of STATSPACK as part of the larger tuning process. This process needs to be comprehensive, and it needs to include host-based and network performance metrics as well as the obvious database and I/O clues. But perhaps most important for Burleson is the concept of "proactive" performance tuning, which lies in stark contrast to our traditional tendency toward reactive tuning.

It is precisely this ability to provide proactive measures that sets STATSPACK apart from its predecessors. Instead of waiting to detect a problem (or hear about it from end users), the idea is to try to predict problems and avoid them before they occur. As an added bonus, capacity planning ends up virtually taking care of itself under this method. Earlier approaches using tools like UTLBSTAT/UTLESTAT allowed you to start collecting stats, wait a period of time, then stop collecting stats and look at the cumulative results. But these were isolated windows into database performance, randomly acquired (in most cases), rarely catalogued, and almost never intended as part of an ongoing, holistic methodology for database tuning. In other words, the nature of the available tools led us toward an inherently reactive approach. With STATSPACK, it becomes much easier to define a proactive scheme, one that is easily scheduled and reliable, producing a well-documented history of database performance that can be used for predictive analysis.

So, in case it isn't blatantly obvious at this point, I'm a big fan of STATSPACK. Now let's talk about the book. I should start by noting that nobody else has published any titles focusing on this tuning utility, so that is definitely a plus. However, that's not to say there are no other books on STATSPACK. In fact, there is one other. It's also from Oracle Press, and it's called High Performance Tuning with STATSPACK. The author? You guessed it: Don Burleson. It came out in April of 2001, about a full year before this book. I've not seen a copy, but I expect it contains much of the same material but lacks details on 9i enhancements. In other words, this is more like a second edition as opposed to an entirely new book. Do yourself a favor and make sure you're buying the latest version.

One of the greatest values this book adds to your shelf is its wealth of scripts (some of which are obtainable only via download, a minor inconvenience). Many of them are SQL scripts, which are obviously platform-independent, and then many of them are shell scripts. But take note (caveat lector!) that the shell scripts are all exclusively Unix-based, primarily Korn shell. At certain points, he even breaks his discussion out into sections on HP-UX, AIX, Solaris, and Linux. If you are looking for some extensible scripts you can use on Windows NT/2000, you won't find them in this book. When Burleson looks to the OS for clues, he's calling on tools like glance, top, sar, iostat and vmstat. Windows Task Manager and Performance Monitor are not even acknowledged, so you'll have to figure out how to carry these concepts over to NT/2000 if that's what you're stuck with.

Besides his comprehensive coverage of STATSPACK itself, Burleson goes into significant detail about instance tuning, I/O tuning, network tuning, object tuning, and even SQL tuning. Add to that two chapters on Oracle parallel computing and you've got a very complete overview of all the important database tuning issues. He wraps up the book with two additional items I felt are extremely useful. The first is a set of scripts for automated alert reporting based on STATSPACK analysis, and the second is a brief walkthrough of an example where he uses Microsoft Excel as a sort of "Poor Man's SAS" to import, graph, and analyze STATSPACK output.

All in all, I think the book is going to prove quite valuable for a large majority of DBAs. You'll buy it for the STATSPACK coverage, you'll end up reading it for the tuning discussions, and you'll keep it for the included scripts.

 

Copyright 2002 Marc Adlam. All rights reserved.

 

 
 

 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.