After you generate performance data for the SQL statements
that are in a Java application
that is enabled for Data Access development, use the SQL Outline view
to examine the data. With this view, you can also open tools that
can help you to tune statements that are not performing well.
Procedure
To 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 Data Access Development > Show
SQL Outline.
- Click the arrow on the Show or Hide Columns button
(
) and select Performance Columns. The information might take a few moments to appear in the view,
depending on the size of your application and the amount of data that
it gathers.
- In the SQL Outline view toolbar, select Workbench
SQL performance from the Performance Data Type list.
Results
The SQL Outline view shows the following information for
each SQL statement in the application that you generated performance
data for:
- The number of times that the application ran the statement
- The longest time that the statement took to run
- The average time that the statement took to run
- The shortest time that the statement took to run
- The total time that the statement took to run, which is the sum
of the times for each run of the statement
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 your application's pureQueryXML
file contains this SQL statement:
SELECT * FROM GOSALES.INVENTORY WHERE PRODUCT_NUMBER = ?
When
you run the application to generate 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 InfoSphere™ 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
.