Databases Reference
In-Depth Information
can have the SQL statements sorted on the basis of the physical I/Os they used; the
elapsed time spent on parsing, executing, or fetching the rows; or the total number of
rows affected.
The TKPROF utility uses a trace file as its raw material. Trace files are created for indi‐
vidual sessions. You can start collecting a trace file either by running the target appli‐
cation with a switch (if it's written with an Oracle product such as Developer) or by
explicitly turning it on with an EXEC SQL call or an ALTER SESSION SQL statement
in an application written with a 3GL. The trace process, as you can probably guess, can
significantly affect the performance of an application, so you should turn it on only
when you have some specific diagnostic work to do.
You can also view the execution plan through Enterprise Manager for the SQL state‐
ments that use the most resources. Tuning your SQL statements isn't a trivial task, but
with the EXPLAIN PLAN and TKPROF utilities you can get to the bottom of the de‐
cisions made by the cost-based optimizer. It takes a bit of practice to understand exactly
how to read an execution plan, but it's better to have access to this type of information
than not. In large-scale system development projects, it's quite common for developers
to submit EXPLAIN PLANs for the SQL they're writing to a DBA as a formal step toward
completing a form or report. While time-consuming, this is the best way to ensure that
your SQL is tuned before going into production.
SQL Advisors
Oracle Database 10
g
added a tool called the SQL Tuning Advisor. This tool performs
advanced optimization analysis on selected SQL statements, using workloads that have
been automatically collected into the Automatic Workload Repository or that you have
specified yourself. Once the optimization is done, the SQL Tuning Advisor makes rec‐
ommendations, which could include updating statistics, adding indexes, or creating a
SQL profile. This profile is stored in the database and is used as the optimization plan
for future executions of the statement, which allows you to “fix” errant SQL plans
without having to touch the underlying SQL.
The tool is often used along with the SQL Access Advisor since that tool provides advice
on materialized views and indexes. Oracle Database 11
g
introduces a SQL Advisor tool
that combines functions of the SQL Tuning Advisor and the SQL Access Advisor (and
now includes a new Partition Advisor). The Partition Advisor component advises on
how to partition tables, materialized views, and indexes in order to improve SQL
performance.
Data Dictionary Tables
The main purpose of the Oracle data dictionary is to store data that describes the struc‐
ture of the objects in the Oracle Database. Because of this purpose, there are many views
