Using SQL Performance Analyzer Views

You can query the following views to monitor SQL Performance Analyzer and view its analysis results:

• The DBA_ADVISOR_TASKS and USER_ADVISOR_TASKS views display descriptive information about the SQL Performance Analyzer task that was created.

• The DBA_ADVISOR_EXECUTIONS and USER_ADVISOR_EXECUTIONS views display information about task executions. SQL Performance Analyzer creates at least three executions to analyze the SQL performance impact caused by a database change on a SQL workload. The first execution collects a pre-change version of the performance data. The second execution collects a post-change version of the performance data. The third execution performs the comparison analysis.

• The DBA_ADVISOR_FINDINGS and USER_ADVISOR_FINDINGS views display the SQL Performance Analyzer findings. SQL Performance Analyzer generates the following types of findings:

  • Problems, such as performance regression

  • Symptoms, such as when the structure of an execution plan has changed

  • Errors, such as nonexistence of an object or view

  • Informative messages, such as when the structure of an execution plan in the pre-change version is different than the one stored in the SQL tuning set

• The DBA_ADVISOR_SQLPLANS and USER_ADVISOR_SQLPLANS views display a list of all execution plans.

• The DBA_ADVISOR_SQLSTATS and USER_ADVISOR_SQLSTATS views display a list of all SQL compilations and execution statistics.

• The V$ADVISOR_PROGRESS view displays the operation progress of SQL Performance Analyzer. Use this view to monitor how many SQL statements have completed or are awaiting execution in a SQL trial. The SOFAR column indicates the number of SQL statements processed so far, and the TOTAL WORK column shows the total number of SQL statements to be processed by the task execution.

You must have the SELECT_CATALOG_ROLE role to access the DBA views.