Introduction to Online Oracle Tuning Tools

Historically, Oracle tuning professionals used SQL*Plus scripts, and many Oracle Gurus wonder if any vendor will design a GUI that makes sense to the Oracle tuning DBA.  Tuning Oracle is all about locating and fixing bottlenecks, and most DBAs pull out their native scripts rather than try to navigate their way through a convoluted interface.

Many DBA’s will purchase a script collection such as the one at www.oracle-script.com.  Oracle tuning experts are always skeptical of GUI tools that claim to assist with Oracle tuning, usually because the tool does not give them what they want to see. 

For example, most Oracle tuning experts love the Automated Session History (ASH) cluster and the new ability to perform time-series tuning, but it only works on Oracle10g databases.  The proliferation of third party tools, such as TOAD and Ion, that were developed to compete with the Oracle Enterprise Manager (OEM) speaks for itself; however, many Oracle professionals desire a time-series tool that would allow them to do advanced trending and predictive analysis.  It appears that these new self-collecting ASH GUI tools may be the wave of the future. 

This chapter provides an overview of how popular third-party Oracle tuning tools might assist in Oracle tuning efforts.

§         Oracle Dictionary Scripts and Tools: The review will include a look at the Mike Ault Script collection from www.oracle-script.com.

§         Trend-based tools: The review of trend-based tools will provide a quick look at Ion because it’s the only tool outside OEM that will plot time-series data from the Automated Workload Repository (AWR) and ASH. It has capabilities that are move advanced than OEM because it allows users to view Oracle trends by day of the week and hour of the day.  The Ion Enterprise Edition also allows the same interface to Oracle8i and Oracle 9i databases.

§         Wait Event tuning tools: There are several online tools that focus on a wait event tuning methodology.  The DBFlash product by Confio software was chosen for this review because it is built around a wait event tuning framework.

The following section provides a review of custom Oracle dictionary scripts.

SEE CODE DEPOT FOR FULL SCRIPTS