Analyzing summary monitor information

 

Once data has been collected in the monitor, it can be analyzed. You can analyze information in a summary monitory by right-clicking the summary monitor in the right pane and selecting Analyze. A summary monitor must be ended or paused in order to analyze the data.

The following is an overview of the information that you can obtain from the predefined reports.

General Summary

Contains information that summarizes all SQL activity. This information provides the user with a high level indication of the nature of the SQL statements used. For example, how much SQL is used in the application? Are the SQL statements mainly short-running or long running? Is the number of results returned small or large?

Job Summary

Contains a row of information for each job. Each row summarizes all SQL activity for that job. This information can be used to tell which jobs on the system are the heaviest users of SQL, and hence which ones are perhaps candidates for performance tuning. The user may then want to start a separate detailed performance monitor on an individual job to get more detailed information without having to monitor the entire system.

Operation Summary

Contains a row of summary information for each type of SQL operation. Each row summarizes all SQL activity for that type of SQL operation. This information provides the user with a high level indication of the type of SQL statements used. For example, are the applications mainly read-only, or is there a large amount of update, delete, or insert activity. This information can then be used to try specific performance tuning techniques. For example, if a large amount of INSERT activity is occurring, perhaps using an OVRDBF command to increase the blocking factor or perhaps use of the QDBENCWT API is appropriate.

Program Summary

Contains a row of information for each program that performed SQL operations. Each row summarizes all SQL activity for that program. This information can be used to identify which programs use the most or most expensive SQL statements. Those programs are then potential candidates for performance tuning. Note that a program name is only available if the SQL statements are embedded inside a compiled program. SQL statements that are issued through ODBC, JDBC, or OLE DB have a blank program name unless they result from a procedure, function, or trigger.

Additionally, you can select more Detailed Results:

Basic statement information

This information provides the user with basic information about each SQL statement. The most expensive SQL statements are presented first in the list so at a glance the user can see which statements (if any) were long running.

Access plan rebuild information

Contains a row of information for each SQL statement that required the access plan to be rebuilt. Reoptimization will occasionally be necessary for one of several reasons such as a new index being created or dropped, the apply of a PTF, and so on. However, excessive access plan rebuilds may indicate a problem.

Optimizer information

Contains a row of optimization information for each subselect in an SQL statement. This information provides the user with basic optimizer information about those SQL statements that involve data manipulation (Selects, opens, updates, and so on) The most expensive SQL statements are presented first in the list.

Index create information

Contains a row of information for each SQL statement that required an index to be created. Temporary indexes may need to be created for several reasons such as to perform a join, to support scrollable cursors, to implement ORDER BY or GROUP BY, and so on. The created indexes may only contain keys for rows that satisfy the query (such indexes are known as sparse indexes). In many cases, the index create may be perfectly normal and the most efficient way to perform the query. However, if the number of rows is large, or if the same index is repeatedly created, you may be able to create a permanent index to improve performance of this query. This may be true whether an index was advised.

Index used information

Contains a row of information for each permanent index that an SQL statement used. This can be used to quickly tell if any of the permanent indexes were used to improve the performance of a query. Permanent indexes are typically necessary to achieve optimal query performance. This information can be used to determine how often a permanent index was used by in the statements that were monitored. Indexes that are never (or very rarely) used should probably be dropped to improve the performance of inserts updates and deletes to a table. Before dropping the index you may also want to look at the last used date in the Description information for the index.

Open information

Contains a row of information for each open activity for each SQL statement. The first time (or times) a open occurs for a specific statement in a job is a full open. A full open creates an Open Data Path (ODP) that will be then be used to fetch, update, delete, or insert rows. Since there will typically be many fetch, update, delete, or insert operations for an ODP, as much processing of the SQL statement as possible is done during the ODP creation so that same processing does not need to be done on each subsequent I/O operation. An ODP may be cached at close time so that if the SQL statement is run again during the job, the ODP will be reused. Such an open is called a pseudo open and is much less expensive than a full open. You can control the number of ODPs that are cached in the job and then number of times the same ODP for a statement should be created before caching it.

Table scan

Contains a row of information for each subselect that required records to be processed in arrival sequence order. Table scans of large tables can be time-consuming. If the SQL statement is long running, it may indicate that an index might be necessary to improve performance.

Sort information

Contains a row of information for each sort that an SQL statement performed. Sorts of large result sets in an SQL statement may be a time consuming operation. In some cases, an index can be created that will eliminate the need for a sort.

Temporary file information

Contains a row of information for each SQL statement that required a temporary result. Temporary results are sometimes necessary based on the SQL statement. If the result set inserted into a temporary result is large, you may want to investigate why the temporary result is necessary. In some cases, the SQL statement can be modified to eliminate the need for the temporary result. For example, if a cursor has an attribute of INSENSITIVE, a temporary result will be created. Eliminating the keyword INSENSITIVE will typically remove the need for the temporary result, but your application will then see changes as they are occur in the database tables.

Data conversion information

Contains a row of information for each SQL statement that required data conversion. For example, if a result column has an attribute of INTEGER, but the variable the result is being returned to is DECIMAL, the data must be converted from integer to decimal. A single data conversion operation is very inexpensive, but repeated thousands or millions of times can add up. In some cases, it is a simple task to change one of the attributes so a faster direct map can be performed. In other cases, the conversion is necessary because there is no exact matching data type available.

Subquery information

Contains a row of subquery information. This information can indicate which subquery in a complex SQL statement is the most expensive.

Finally, you can select the Composite view.

Summary data

Contains resource and other general information about monitored jobs.

Statement text

Contains the SQL text that monitored jobs call.

Table scan

Contains the table scan data for the monitored jobs.

Data sorts

Contains details of data sorts that monitored jobs perform.

Host variable use

Contains the values of host variables that monitored jobs use.

Optimizer time out/access paths considered

Contains details of any occurrences of time outs of monitored jobs.

Indexes used

Contains details of how indexes are used by monitored jobs.

Index creation

Contains details of the creation of indexes by monitored jobs.

Subselect processing

Contains information about each subselect in an SQL statement.

Temporary file use

Contains details of temporary files that monitored jobs created.

 

Parent topic:

Using iSeries Navigator with summary monitors