Session 5: Using SQL Performance Analysis in Performance Analyst for Oracle

From DBArtisan
Jump to: navigation, search

Go Up to Performance Analyst for Oracle Tutorial

SQL-based performance analysis is really a subset of Workload Analysis. Understanding current and historical SQL execution patterns lets you to have the second set of data points necessary to properly perform workload analysis. You may find that optimizing SQL code produces some of the best performance-enhancing boosts available for a database.

Uncovering Resource-intensive SQL in Performance Analyst for Oracle

Performance Analyst offers plenty of insight into SQL performance and execution patterns. Each one of the home pages depicts the most costly SQL statements as determined by a variety of different metrics for that performance category (most physical, logical I/O, etc.) For example, Performance Analyst might show that a single SQL statement is responsible for almost 60 percent of all the physical I/O on the database.

Getting Details on Top SQL with Performance Analyst for Oracle

Getting details on identified SQL statements is easy in Performance Analyst. For example, if you would like to see further information on a SQL statement identified on the Performance Analyst Home page, do the following:

  1. In the SQL Analysis section, position the pointer over Hash Values.
  2. Double-click or right-click, and then select Details.
    Performance Analyst opens the Top SQL summary page that shows a breakdown of all top SQL statements along with their execution statistics

If you would like to see the full SQL text of a SQL statement, do the following

  1. In the Top SQL Details section, double-click a statement.
    Performance Analyst opens the SQL Details tab of the Top SQL view to display the entire SQL statement and the execution metrics. While on this pane, you can also perform an EXPLAIN of the SQL statement and examine its access path information.
    Note: To automatically tune a problem SQL statement if you have IDERA SQL Tuner installed on your workstation, do the following:
  2. On the SQL Details tab, select a problem statement, and then click the Tune SQL button.
    SQL Tuner opens and you can begin a tuning session.


Proceed to Session 6: Using Operating System Performance Analysis in Performance Analyst for Sybase ASE

Retrieved from "http://docwiki.embarcadero.com/DBArtisan/170/e/index.php?title=Session_5:_Using_SQL_Performance_Analysis_in_Performance_Analyst_for_Oracle&oldid=6003"