If you're serious about tuning an Oracle server, you'll probably be familiar with STATSPACK, a set of Oracle-provided SQL, PL/SQL and SQL*Plus scripts which help you collect, store and analyze performance data over a period of time. Statspack primarily queries the performance v$ views and collects information on memory usage, I/O usage patterns, high resource usage patterns, and so on. It replaces the old BSTAT/ESTAT utilities, and can be set up to collect data over a period of time so that you can see how changes to the server parameters affect how the database performs.
Whilst STATSPACK comes with a report generator to help make sense of the data that's being collected, and there's information out there on how to interpret STATSPACK reports, it still is a bit daunting for new DBAs to deduce what's going on and what you should do now. To address this, there's a couple of useful utilities i've come across that can help interpret STATSPACK reports.
First up is a free web-based service from http://www.oraperf.com that analyzes uploaded STATSPACK report files and provides a web report, highlighting areas that are of concern and offering advice on what parts of the database to tune first. The standard service provides a set of recommendations based on the contents of the STATSPACK report, but if you register (which is free) you get a far more detailed report, explaining what each of the STATSPACK statistics mean and giving you some idea of the figures you should be aiming at.
Statspack Viewer Professional is a commercial tool that takes care of the installation and configuration of STATSPACK, and provides a desktop tool to analyze and report on the collected statistics. I haven't tried the tool myself, so I can't tell at this point how different it is to the Oracle OEM Diagnostics Pack, but it certainly looks like a good alternative to poring over STATSPACK reports.
More information on STATSPACK can be found at OTN and this excellent article at the Capital District Oracle User Group website.
9:38:41 PM 
|
