When I tune, I always use SQL*Plus scripts. I sometimes wonder if any
vendor will design a GUI that makes sense to the DBA. Tuning Oracle is all-about
locating and fixing bottlenecks, and most DBA's pull-out their native scripts
rather then try to navigate their way through a convoluted interface. Things
appear to be changing.
Our premier Oracle monitoring software is
Ion for Oracle, a truly
proactive tool that gets right to the heart of any Oracle performance
problem. Ion for Oracle uses STATSPACK or AWR to analyze for hidden
trends and signatures, making it easy to monitor Oracle.
Also see the valuable notes on
Oracle Monitoring Best Practices.
I know dozens of DBA consultants
who tune databases everyday. When you do it for a living, you
have to use whatever "tool" that your client has purchased. If
the client has an Oracle EE license, and has paid for the extra-cost
Oracle Tuning Pack and Oracle Diagnostic packs, you can use the
Oracle new automation features. Remember, even
though the Oracle 10g Active Session History tables are built-in
to the kernel, the time-series wait event tables can only be read if
you have the proper licensing.
I'm always skeptical of any GUI tool that claims to assist me when
tuning, usually because the tool doesn't give me what I want to see.
I recently investigated a new offering called DBFlash that surprised
me. I love the ASH cluster and the new ability to perform
time-series tuning, but it only works on Oracle. The DBFlash
product mimics the ASH, and you don't have to pay for the Oracle
Performance Pack to use it. The proliferation of 3rd-party tools to
compete with OEM speaks for itself, but I needed a time-series tool
that would allow me to do advanced trending and predictive analysis.
It appears that these new self-collecting ASH GUI tools may be the
wave of the future.
When you choose a tuning tool, its all-about functionality. When
your manager chooses a tool, its all about saving money.
Oracles offering (OEM), requires Oracle Enterprise Edition (EE),
just to get the right to purchase the OEM performance packs. Oracle is unclear about the costs
of the Performance Pack (PP) and Diagnostic Pack (DP).
Oracle Tuning Tool
When it comes to a tool that can
do advanced tuning (i.e. identifying performance trends), there are
only a few tools that provide time-series capability for the
professional Oracle DBA. These are presented in cost-order,
with the most expensive first:
Extra-cost Solutions (Oracle Performance Pack,
Diagnostic Pack, Automatic Database Diagnostic Monitor) Prices
vary, depending on your Oracle license. - The OEM
interface has come a long way since Oracle, but the licensing
issues require Oracle EE, and it's not an option for SE users.
Obviously, Oracle 10g OEM ASH and AWR components are not an
option for shops running Oracle databases.
Quest Spotlight The oldest of the
third-party Oracle Performance tuning GUI tools and has a
sophisticated interface to the Oracle Wait Interface x$ views:
Ignite A nice, on-target tool
that can support time-series wait event tuning on Oracle databases. A great drill-down too, based on Oracle Wait
Tuning approach, and it makes sense. I suspect that the
interface was written by an experienced Oracle tuning expert:
(Workload Interface Statistics Engine) Ion is the only tool outside OEM
that will plot time-series data from AWR and ASH. It is move
advanced than OEM (IMHO) because it allows you to view Oracle
trends by day-of-the-week and hour-of-the-day. The Ion EE
also allows the same interface to Oracle8i and Oracle databases.
Collection ($79.95) - I'm still not a GUI guy yet,
and I still rely on my trusty script collection to help me see
what's happening inside Oracle. Many people use Mike
Ault's script collection too:
Are there other Oracle tuning
tools out there? Sure. Are we likely so see Oracle
tuning experts using them? Looks like that's about to change.
. . .