Gathering Data

Gathering Data on the SQL Identified
====================================

If you are most concerned with CPU, then examine the top SQL statements that
performed the most BUFFER_GETS during that interval. Otherwise, start with the SQL
statement that performed the most DISK_READS.

Information to Gather During Tuning
-----------------------------------
The tuning process begins by determining the structure of the underlying tables and
indexes. The information gathered includes the following:

1. Complete SQL text from V$SQLTEXT

2. Structure of the tables referenced in the SQL statement, usually by describing the
table in SQL*Plus

3. Definitions of any indexes (columns, column orderings), and whether the indexes
are unique or non-unique

4. Optimizer statistics for the segments (including the number of rows each table,
selectivity of the index columns), including the date when the segments were last
analyzed

5. Definitions of any views referred to in the SQL statement

6. Repeat steps two, three, and four for any tables referenced in the view definitions found in step five

7. Optimizer plan for the SQL statement (either from EXPLAIN PLAN, V$SQL_PLAN,
or the TKPROF output)

8. Any previous optimizer plans for that SQL statement.