While the super-expensive SQLAccess advisor will
examine a SQL Tuning set and recommend missing indexes and
materialized views, a multi-thousand dollar tool is not needed!
Remember, while not all full scans are evil, it is
a sign of a missing index! The first thing I examine is an
unnecessary large-table full-table scan, a sure sign of a missing
index.
I use my free plan9i.sql and
plan10g.sql scripts to summarize SQL workloads and count the
number of large table-full table scans, and I have loads of other
scripts to aid in finding missing indexes in my
Oracle script
download with over 600 tuning scripts for Oracle.
You
can query v$sql
_plan (using the
plan9i.sql script from
our code depot) revealed a huge number of large-table, full-table
scans, which is one symptom of suboptimal SQL.
Sub-optimal index
access to a table occurs when the optimizer cannot find an
index or the most restrictive where clause in the SQL is not matched
with an index.
With a missing index, the optimizer cannot find an
appropriate index to access table rows, the optimizer will always
invoke a full-table scan, reading every row in the table. Hence, a
large-table full-table scan might indicate a sub-optimal SQL statement
that can be tuned by adding an index that matches the
where clause of the query.
Case study in a missing
index
I had a client who reported slow SQL and looking into
v$sql revealed that
the rows returned by each query were small, with this common
where
clause for their SQL:
WHERE
customer_status = ‘:v1’ and customer_age > :v2;
T
