From the SQL Outline view, you can
import and view Optim™ Performance
Manager performance
data that is imported either from a Optim Performance
Manager repository database
or a file. From this view, you can also open tools that can help you
to tune SQL statements that are not performing well.
Before you begin
You must ensure that Optim Performance
Manager is monitoring the
database on which the SQL statements are running. Also, the database
must be configured in Optim Performance
Manager for
extended insight monitoring. For information about capturing performance
data with Optim Performance
Manager,
see Optim Performance Manager data in the SQL Outline view
If you are importing
and viewing SQL performance data from an Optim Performance
Manager repository database,
the workspace must have an Optim Performance
Manager profile
to the repository database For more information see Defining an Optim Performance Manager profile.
If you are viewing performance
data from a file, the file must contain Optim Performance
Manager data that was exported
from Optim Development Studio.
About this task
You import Optim Performance
Manager performance
data either from a repository database or a file that contains exported
performance data. The imported performance data is saved in your local
workspace.
After you import the data, it is displayed in the SQL
Outline view. You can view other saved performance data
sets in the view and compare two sets of data.
The SQL
Outline view displays performance data only for the SQL
statements that are listed in the view. If performance data is imported
for SQL statements that are not listed, the data is not displayed.
Procedure
To import and view performance data in the SQL
Outline view:
- If the SQL Outline view is not open,
open it by right-clicking a project and selecting pureQuery > Show
SQL Outline.
- In the toolbar for the SQL Outline view,
click the arrow on the Show or Hide Columns button
(
) and select Performance Data.
- Select Optim Performance Manager from
the Performance Data Type list.
- Click Import performance data
.
- On the Import Performance Manager Data window,
specify the name of the data set to save in the workspace.
You
can specify the name of a new or existing data set. If you specify
an existing data set, the existing data is replaced with the imported
data.
- Specify the source of information to import and display. Import from one of the following sources:
- A file that contains exported performance data
- Select From an exported Optim Performance Manager data
set.
- Click Browse to select a file and click OK.
The
file must contain Optim Performance
Manager performance
data.
- An Optim Performance
Manager repository
- Select From Optim Performance Manager.
- Specify the information to import and click OK.
Select
the Optim Performance
Manager profile
to specify the repository database that will provide the performance
data. The monitored database, aggregation interval, and time frame
determine the performance data that is imported.
When Optim Performance
Manager captures and stores
the performance data, it aggregates the data based on the time the
data was captured. The aggregation levels are the time intervals at
which Optim Performance
Manager aggregates
the data.
The aggregation level that you select changes the
available timeframe values that you can select. To improve data import
performance, select the highest Optim Performance
Manager aggregation level
that lists the timeframe values for your time interval. For example,
if aggregation levels 3 and 4 both display the timeframe values for
the time interval of interest, select level 4.
Results
The information might take a few minutes to appear in the
view, depending the amount of data that is being imported from the
repository database and how frequently Optim Performance Manager collects
data.
Note for literal substitution: If
your application is using pureQuery client optimization and you used
the
sqlLiteralSubstitution property when you captured
SQL statements to a pureQueryXML file, SQL statements that contain
literal values are nested under the versions of those statements that
contain parameter markers.
For example, suppose that pureQueryXML
file for your application contains this SQL statement:
SELECT * FROM GOSALES.INVENTORY WHERE PRODUCT_NUMBER = ?
When
you import the performance data, the SQL statement runs with the literal
value 115110. In the SQL Outline view, you must
expand the statement that has the parameter marker to see the statement
that has the literal value.
You can take any of the following
actions while viewing performance data in the SQL Outline view:
- View the access plan for an SQL statement by right-clicking it
and selecting Open Visual Explain.
- Get recommendations for tuning an SQL statement by right-clicking
the statement and selecting Get Query Tuner Report,
or open the Query Tuner editor by right-clicking an SQL statement
and selecting Open Query Tuner. See Tuning SQL statements with Optim Query Tuner.
- Save the current results by clicking
.
- If you want other people to be able to import the data into the
workbench on their computers, export the current results by clicking
.
- Import a set of performance data by clicking
.
Tip: To determine if performance data has been captured
for your SQL statements, you can view the data stored in the repository
database with Optim Performance
Manager.
Log on to Optim Performance
Manager and
connect to the monitored database. The SQL statements and performance
data are displayed as details on the Extended Insight Analysis
Dashboard.