9.1 Tools to Detect Problems

---

This book is intended as a step-by-step learning guide teaching tuning of Oracle installations. You should now have an understanding of SQL code tuning from the most important perspective. What is that perspective? You should have an understanding of how to tune SQL code without having to use all sorts of fancy and complicated SQL code tuning tools. Why is this approach better? This approach is better because it should always be viewed as a last resort to fall back on detailed analysis with specific tools. It is better to know how to build properly tuned SQL code in the first place without having to take drastic measures, such as use of the EXPLAIN PLAN command and more significantly tracing. Tracing is highly complex, very time consuming, produces a significant performance drain on a database and generally provides far more information than is ever necessary!

There are various Oracle Corporation and non-Oracle Corporation tools which can be used to find and tune problematic and poorly performing SQL code. What are these tools?

• EXPLAIN PLAN.

• SQL Trace with TKPROF.

•  Oracle Database 10 Grid   TRCSESS.

• Autotracing in SQL*Plus.

• Oracle Trace.

  Note 

   Oracle Database 10 Grid   Oracle Trace has been deprecated including all ORACLE_ TRACE_% configuration parameters.

• Oracle Performance views.

• Oracle Enterprise Manager and other non-Oracle tuning tools. These will be covered in later chapters depending on the type of tuning.

Now let's examine the use of some Oracle Database tuning tools in detail. Let's start with the EXPLAIN PLAN command.

---