Analyzing SQL Performance Using Oracle Enterprise Manager

This section describes how to analyze SQL performance before and after the system change using Oracle Enterprise Manager.

To analyze SQL performance using Enterprise Manager:

  1. On the Guided Workflow page, click the Execute icon for Compare Step 2 and Step 3.

    The Run SQL Trial Comparison page appears.

    Description of the illustration spa_run_trial_compare.gif follows
    Description of the illustration spa_run_trial_compare.gif

    In this example, the SQL_TRIAL_1241213421833 and SQL_TRIAL_1241213881923 trials are selected for comparison.

  2. To compare trials other than those listed by default, select the desired trials in the Trial 1 Name and Trial 2 Name lists.

    Note that you cannot compare a statistical trial with a trial that tests the explain plan only.

  3. In the Comparison Metric list, select the comparison metric to use for the comparison analysis:

    • Elapsed Time

    • CPU Time

    • User I/O Time

    • Buffer Gets

    • Physical I/O

    • Optimizer Cost

    • I/O Interconnect Bytes

    Optimizer Cost is the only comparison metric available if you generated execution plans only in the SQL trials.

    To perform the comparison analysis by using more than one comparison metric, perform separate comparison analyses by repeating this procedure with different metrics.

  4. In the Schedule section:

    1. In the Time Zone list, select your time zone code.

    2. Select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

  5. Click Submit.

    The Guided Workflow page appears when the comparison analysis begins.

    The status icon of this step changes to an arrow icon while the comparison analysis is in progress. To refresh the status icon, click Refresh. Depending on the amount of performance data collected from the pre-change and post-change executions, the comparison analysis may take a long time to complete. After the comparison analysis is completed, the Status icon changes to a check mark and the Execute icon for the next step is enabled.

  6. Once SQL Performance Analyzer has analyzed the pre-change and post-change performance data, generate a SQL Performance Analyzer report that you can use for further analysis.

    On the Guided Workflow page, click the Execute icon for View Trial Comparison Report.

    The SQL Performance Analyzer Task Report page appears. Review the report, as described in "Reviewing the SQL Performance Analyzer Report Using Oracle Enterprise Manager".