Performance Tuning Protocol – Part 2

In Part 1 I tried to summaries why we need these tools and when to use which one. Also additional to the post at the comments I mentioned dbms_sqltune(10g), dbms_monitor(10g) packages and _trace_files_public parameter. With this additional post I will mention some more tools of course :)

Wait Interface related tools

As I mentioned in Part 1 10g introduces a number of enhancements designed to automate and simplify the performance diagnostic process. Grid control and dynamic performance views like v$session, v$session_wait, v$active_session_history etc. will simply guide you to the root causes of your performance problems. Perform Without Waiting article on Oracle Magazine July/August 2004 by Arup Nanda is a very good starting point as usual. Also Kyle Hailey’s Oracle Wait Interface Introduction presentation is another good reference.

Open source tools

There are a lot of these kind of scripts, tools around. One thing to remember is always to test them on your test databases first.

Kyle Hailey’s direct SGA access project and ASH monitor

Tanel Poder’s Oracle Session Snapper and Session Wait script

Metalink tools and references

If you have access to Oracle support site I suggest to download and try these tools on your test system, especially I love SQLTXPLAIN.SQL since it provides a summary report like pilot’s console during a flight, anything you may need related to a query is in front of you :)

Note:215187.1 – SQLTXPLAIN.SQL – Enhanced Explain Plan and related diagnostic info for one SQL statement

Note:243755.1 – Implementing and Using the PL/SQL Profiler

Note:224270.1 – Trace Analyzer TRCANLZR – Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046

Note:238684.1 – SQLAREAT.SQL – SQL Area, Plan and Statistics for Top DML (expensive SQL in terms of logical or physical reads)

Note 39817.1 – Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output

Note:62294.1 – The DBMS_SUPPORT Package

And if you still need more tools and have a budget Quest’s and are my favorites :)


  1. Oracle Performance Tools Quick Reference Guide
    Metalink Note 438452.1 – utilities like StackX, LTOM, HangFG, SQLTXPLAIN, OS_Watcher, OPDG.

Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s