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
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