Oracle SQL Tuning Information Views

The following views display the information that has been gathered for tuning the SQL statements. DBA privileges are needed to access these views.

• Advisor information views, such as DBA_ADVISOR_TASKS, DBA_ADVISOR_EXECUTIONS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, and DBA_ADVISOR_RATIONALE views.
• SQL tuning information views, such as DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS, and DBA_SQLTUNE_PLANS views.
• SQL Tuning Set views, such as DBA_SQLSET, DBA_SQLSET_BINDS, DBA_SQLSET_STATEMENTS, and DBA_SQLSET_REFERENCES views.
• Information on captured execution plans for statements in SQL Tuning Sets are displayed in the DBA_SQLSET_PLANS and USER_SQLSET_PLANS views.
• SQL Profile information is displayed in the DBA_SQL_PROFILES view.

The TYPE parameter shows if the SQL profile was created manually by the SQL Tuning Advisor (if TYPE = MANUAL) or automatically by automatic SQL tuning (if TYPE = AUTO).

• Advisor execution progress information is displayed in the V$ADVISOR_PROGRESS view.
• Dynamic views containing information relevant to the SQL tuning, such as V$SQL, V$SQLAREA, V$SQLSTATS, and V$SQL_BINDS views.

Oracle data dictionary views

Oracle dynamic performance views

Last updated: July 6, 2009