Importing and viewing Optim Performance Manager SQL performance data in the SQL Outline view

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:

  1. If the SQL Outline view is not open, open it by right-clicking a project and selecting pureQuery > Show SQL Outline.
  2. In the toolbar for the SQL Outline view, click the arrow on the Show or Hide Columns button (Show or Hide Columns) and select Performance Data.
  3. Select Optim Performance Manager from the Performance Data Type list.
  4. Click Import performance data Import Performance Data.
  5. 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.

  6. Specify the source of information to import and display. Import from one of the following sources:
    • A file that contains exported performance data
      1. Select From an exported Optim Performance Manager data set.
      2. Click Browse to select a file and click OK.

        The file must contain Optim Performance Manager performance data.

    • An Optim Performance Manager repository
      1. Select From Optim Performance Manager.
      2. 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:

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.

Feedback