 |
|
Oracle Tips by Burleson |
STATSPACK benefits
In addition to using these views and
scripts, it is possible to use STATSPACK to gather much, if not all,
of the same information. STATSPACK has advantages such as built in
snapshot statistics gathering instead of the process shown earlier.
It also has a ready-made query in the form of a report called
spreport.sql which can be found in most $ORACLE_HOME/rdbms/admin
directories.
The install is pretty straightforward, and it must be
done before any data can be retrieved using it. This could be a
definite disadvantage in a reactive performance tuning situation
where a solution is needed NOW. Another problem could be that the
report it produces is rather general in nature. While using tools
like oraperf.com can help navigate this report, nothing beats a
simple query that tells where a given session is waiting when the
user is asking why this report is taking so long. That will be
examined more with the use of STATSPACK in Chapter 7.
Benefits
In order to wrap up the discussion of the
Wait Interface, it is important to highlight the benefits of using
it instead of, or in addition to, other tuning methodologies. The
nature of the Wait Interface makes analysis more precise than
ratios. The analysis shows the specific area of concern, which
allows for a more direct resolution. Whereas ratios take details of
the activity of the database and summarize them into a single
metric, the Wait Interface uses the details to let the Remote DBA see what
is slowing the database down. Ratios have a “one-trick pony”
solution – the typical solution to a bad ratio is to throw more
memory at the problem, which doesn’t always work. The Wait
Interface simply provides more accurate information, which may lead
to adding more memory if warranted, but could lead elsewhere.
The above book excerpt is from:
Oracle Wait Event Tuning
High Performance with Wait
Event Interface Analysis
ISBN 0-9745993-7-9
Stephen Andert
http://www.rampant-books.com/book_2004_2_wait_tuning.htm |
