EXPERT RESPONSE
Tuning SQL and PL/SQL begins with an understanding of how Oracle processes SQL and PL/SQL. The "Oracle9i Database concepts" manual provides an overview in "Part V data access: SQL, PL/SQL and Java." You should also review the "Oracle9i Database performance tuning guide and reference."
Most of the decisions about SQL processing can and should be left to
Oracle's Cost-Based Optimizer. Make sure that you have accurate statistics
on your tables and indexes so that the optimizer has the information it
needs to do its job.
When you are ready to tackle the tuning of individual SQL statements, you
will want to use the EXPLAIN PLAN command to see the optimizer plan for a
given query, the DBMS_SUPPORT package to trace Oracle sessions, and the
TKPROF utility to format trace information. The Oracle documentation
contains information on all of these.
|
