AWR and SQL Optimizer

SQL Optimizer SQL Optimizer for Oracle
1

Is there anyway to get the SQL Optimizer to retrieve the SQL from AWR to be used for performance tuning and plan baselines? The reason I question this is because often enough we have performance testing that is kicked off over night and by the time we return in the morning the sql has moved out of SGA.

Regards
Tim

2

Hi Tim,

There is now a way to accomplish your goal using the recently released SQL Optimizer for Oracle version 8.5. It requires a slightly roundabout workflow for now but we are considering a more direct workflow for this task in a future version of SQL Optimizer for Oracle.

To achieve your goal in version 8.5, you must start from the new Optimize Indexes module. Here are the steps:

  1. Invoke the Optimize Indexes module from the main navigation menu (just right of the Optimize SQL module)

  2. Define a new SQL Workload (starts the Optimize Indexes workflow)

  3. Select the AWR SQL source

  4. Enter the AWR connection and filter criteria that captures your intended SQL

  5. Press the Configure Search Process button

  6. Switch to the SQL tab by clicking the SQL tab or clicking one of the two SQL links in the SQL Workload panel

  7. Find and select the SQL you wish to Optimize

  8. In the SQL Text panel, select the appropriate choice using the Send to Optimize SQL by SQL Rewrite toolbar button drop down (which includes an option to tune using Plan Control (SQL Plan Baselines))

Screen capture of the last step is included in this post.

Regards,

Greg

[cid:image001.png@01CC81DC.34F03F10]

image001.png
image001.png984×787 122 KB

3

Hi Tim,

You can refer to the attached document for another way to get the SQL Optimizer to retrieve the SQL from Inspect SGA module.

Please let us know if you have any questions for this.

Regards,
Polly
Inspect SGA.docx (839 KB)