Information to Gather During Tuning

While tune the SQL statement it is needed to determine high load sql statements along with their associated indexes. In the following section the information that is needed to gather during tuning is listed.

•Complete SQL text from V$SQLTEXT

•Structure of the table like in SQL*PLUS DESCRIBE table_name;

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

•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.

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

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

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

•Any previous optimizer plans for that SQL statement.


It is important to generate and review execution plans for all of the key SQL statements in your application. Doing so lets you compare the optimizer execution plans of a SQL statement when the statement performed well to the plan when that the statement is not performing well. Having the comparison, along with information such as changes in data volumes, can assist in identifying the cause of performance degradation.