Interpreting performance profile for a Db2 allied address space
This section describes the Strobe Performance Profile produced when you measure a Db2 allied address space. It includes an overview of Db2 batch reports and describes specific reports associated with SPUFI address spaces.
Choosing the reports you need to examine is dependent on the characteristics of your Db2 environment and where you want to improve performance. For example, you might want to focus on Db2 system modules or on a specific DBRM; or an overall reduction in CPU usage may be your goal.
Some reports show CPU usage. Another set of reports shows the time a job waits. You can set various reporting parameters to customize what data appears in your Performance Profile. This information can provide a comprehensive picture of all factors influencing how your application is running. For a complete description of the Strobe Performance Profile, refer to the Using-the-Strobe-Application-Performance-Measurement-System.
Reports That Show CPU Execution
The Performance Profile shows CPU execution in Db2 allied address spaces in four types of reports:
- Program module reports
- Attribution reports
- SQL, DBRM, and Procedure reports
- Transaction reports for batch jobs.
Each type of report shows a different view of CPU execution activity during a measurement session. You can use various combinations of the reports to trace the cause of heavy CPU usage. After each type of report is briefly described, examples of these report combinations are provided to show how to follow the report findings to locate the source of the CPU consumption.
Program Module Reports
The Program Section Usage Summary and Program Usage by Procedure reports show CPU usage in all programs, including application programs, Db2 system modules, and other system service routines. The Program Section Usage Summary report shows where the majority of the CPU usage occurred: in Db2 service modules or user programs. After you identify where the heavy CPU usage is taking place, refer to the Program Usage by Procedure report for these program sections to see more specifically what is responsible for the CPU usage.
The Most Intensively Executed Procedures report highlights the information in the module reports. It lists the ten heaviest users of CPU time and quickly shows you whether service modules or user programs are responsible for a majority of the CPU usage.
Throughout the Strobe Performance Profile, Strobe reports activity in Db2 system modules to pseudo-sections within the pseudo-module .SYSTEM, as shown below.
Module Name Prefix | Pseudo-Section | Function |
|---|---|---|
DSN | .DB2 | Db2 system services execution and SQL execution within Db2 |
DXR | .IRLM | Db2 resource locking |
You can obtain detailed reporting on activity within a Db2 module or control section by specifying the module name in the DETAIL parameter when you generate the Performance Profile. For more information, refer to the Using-Strobe-to-measure-online-applications-and-batch-programs. For more information on Strobe pseudo-entities, refer to the Using-the-Strobe-Application-Performance-Measurement-System manual.
Attribution Reports
Attribution of CPU Execution Time reports for batch programs identify the callers of Db2 system modules by:
- Transaction name
- SQL statement within DBRM.
When a Db2 service module shows high CPU usage, its Attribution of CPU Execution Time report identifies all statements that invoked the service.
SQL, DBRM, and Procedure Reports
The SQL CPU Usage Summary report shows which DBRMs, and Procedures were responsible for SQL in_DB2 execution CPU activity. The CPU Usage by SQL Statement reports show the distribution of the SQL in_DB2 execution CPU activity among the SQL statements that make up those DBRMs, and Procedures. Turn to these reports when most of the CPU activity occurs in Db2 service modules.
Transaction Reports
The Transaction Summary and Transaction Usage by Control Section reports identify which transactions are responsible for CPU activity in online address spaces. These reports are helpful when:
- You want to see transaction and CPU usage in CICS response time statistics
- Db2 service modules do not account for a majority of the CPU activity
- You want to see plan names for batch jobs.
Reports That Show Wait Time
Four Strobe reports show wait time:
- The Wait Time by Module report shows all the modules, control sections, pseudo-modules and pseudo-sections in which Strobe found the application it was measuring in wait state.
- The Attribution of CPU Wait Time identifies the percentage of wait time spent in an invoked system routine as a result of the routine that invoked it.
- The SQL Wait Time Summary report shows the distribution of wait time among Procedures and DBRMs.
- The Wait Time by SQL Statement report shows the distribution of wait time among SQL statements in a Procedure or DBRM.
Choosing Between the Execution and Wait Reports
To determine whether to examine the CPU execution or wait reports in the Performance Profile, determine from the Measurement Session Data report (See the following figure) whether the value shown for EXEC TIME PERCENT (the percentage of time that one or more CPUs were active) is greater than the value shown for WAIT TIME PERCENT, in the MEASUREMENT STATISTICS section of the report.
Measurement Session Data Report for a Batch Job
The report shows the WAIT TIME PERCENT is 86.10; therefore, Strobe found a significant amount of wait time. For more information about reports that analyze wait time, see Reports for Analyzing Wait Time.
If Strobe found a high EXEC TIME PERCENT instead, then you would look at ways to reduce CPU usage.
To reduce CPU time consumption, first determine what is using CPU time by examining the Program Section Usage Summary and the Transaction reports. If you determine that the majority of time is being consumed by application programs, the Using-the-Strobe-Application-Performance-Measurement-System manual provides a detailed example of how to use the Performance Profile to reduce application program CPU usage.
CPU Reports for Analyzing Batch Jobs
This section describes Performance Profile reports you can use to determine which parts of your application are consuming the most CPU time and if improvement can be made in the application itself, its utilization of system resources, or in both areas.
The Program Section Usage Summary Report, the Program Usage by Procedure Report, and the Attribution of CPU Execution Time Report do not account for any parallel SQL execution. For reports which account for parallel SQL in_DB2 SQL CPU execution, refer to CPU Usage Reports for SQL.
Program Section Usage Summary Report
The Program Section Usage Summary report shows the distribution of CPU time used by each active control section of each active module in the Db2 address space. From it, you can determine whether Db2 system modules or user programs are responsible for most of the CPU activity.
The report shows that Db2 system modules used 87.27% of the execution time. Strobe compresses Db2 and other IBM system module information and shows pseudo-sections under the pseudo-module .SYSTEM as highlighted on the example below.
When Db2 system modules are the heaviest CPU users, refer to either the SQL CPU usage reports to see which SQL statements are affecting performance or the Program Usage by Procedure report to see what system functions Db2 is using. If user programs show high activity, also refer to the Program Usage by Procedure reports.
Program Section Usage Summary Report
Program Usage By Procedure Reports
The Program Usage by Procedure reports present a detailed accounting of CPU time spent by each Procedure within each program that was active during a measurement session. The format of an application program report differs slightly from the report format for system modules.
- For application programs, Strobe displays the module name, control section name, and starting location. If the control section was indexed, Strobe shows the line number and Procedure name as well.
- For system modules, Strobe displays the module and control section name and a function descriptor. Both types of reports show the solo and total CPU time used by each control section within the module. (For a complete description of this report, see the Using-the-Strobe-Application-Performance-Measurement-System.)
Activity in System Modules
If the Program Usage Summary report indicates that Db2 system modules accounted for most of the CPU activity, the Program Usage by Procedure in the following figure shows the Db2 modules Strobe found to be utilized during the measurement session.
Program Usage by Procedure Report for Db2 Modules
As the highlighted area of the report shows, Db2 section DSNISFED of module DSNIDM, accounts for 43.45% of the CPU activity. To determine which transactions and SQL statements caused the activity, examine the Attribution reports for this module and control section.
Attribution of CPU Execution Time Report
The Attribution of CPU Execution Time report (See the following figure) identifies the sites of invocation of system routines. Use this report to identify which SQL statements caused activity in a system service routine. This report contains the following sections:
- Header lines
- Detail lines
- Total line.
Attribution of CPU Execution Time Report
Header Lines
The report header identifies the invoked routine, showing:
- Its pseudo-module, module, and control section name (when available)
- A function descriptor for either the control section or the module.
Detail Lines
Each report detail line for a Db2 service routine identifies the SQL statement that invoked the module and displays:
- A transaction name, if one is available
- The name of the SQL statement, DBRM, or Query
- The time that the module was created
- Its SQL statement number (if available)
- The executed SQL statement’s type and the target statement’s number and type
- The solo and total CPU time spent on behalf of the invoker.
Total Line
The total line shows the total time attributed to the invokers of the module. It may be less than the time shown in the Program Usage by Procedure or Most Intensively Executed Procedures reports because Strobe cannot always identify an invoker of a service routine.
If you want to examine an SQL statement on the Attribution of CPU Execution Time Report in more detail, see .
CPU Usage Reports for SQL
SQL activity reports (especially those in iStrobe) are now produced from the instrumentation data. Within SQL activity reports a specific SQL statement % is determined by the CPU seconds used by a specific statement over the total of CPU seconds used by all SQL. Thus the % of all SQL statements is 100%.
The Program Section Usage Summary report and Program Usage by Procedure report are still derived from sampling. The Db2 percentages seen in these reports has no direct correlation to the CPU% seen in the SQL activity reports where the total of all SQL CPU is 100%.
SQL Activity reports are generally viewed for the following two reasons:
- If a problem application is measured by Strobe and the Program Section Usage Summary report .SYSTEM .DB2 % is high, then the problem is likely SQL performance, and the SQL Activity reports should be viewed to find the application problem (preferably in iStrobe).
- If an application was measured to tune Db2 SQL performance, then the SQL Activity reports should be viewed.
Instead of beginning with the Program Usage by Procedure reports, you can start your analysis with the SQL CPU Usage Summary report. It presents an overview of the in_DB2 SQL execution CPU activity for each DBRM. For DBRMs, the report also shows total statement counts. The CPU Usage by SQL Statement reports show a detailed account of the in_DB2 SQL CPU usage by SQL statement. This report displays statement counts and the CPU percents for each SQL statement in a DBRM that was active during the measurement.
SQL CPU Usage Summary Report
The SQL CPU Usage Summary report shows the distribution of in_DB2 SQL execution CPU activity among DBRMs that invoked Db2 for SQL execution. Each detail line in this report shows the executing SQL type, its name, the time and date it was executed (or, for DBRMs, the time and date it was created), and its total in_DB2 SQL CPU usage. Also for DBRMs, Strobe provides the total executed statements for each DBRM and the average amount of execution time for all of the statements.
The following limitations apply to Strobe SQL statement count and Execution time reporting:
- For add active requests, Strobe cannot report count or execution information on an instance of a SQL statement processing at the time of the add active request. Strobe will report on subsequent executions of the statement.
- For queued requests, Strobe may not collect a count and service time for the first execution of a SQL statement if the program does not make an explicit connection to Db2 before it issues the statement. Strobe will begin collecting a count and service time for all subsequent statements.
- To increment the count and adjust the service time of dynamic SQL statements, Strobe must have measured the PREPARE statement SQL to correlate it with the corresponding subsequent statements, such as EXECUTE, OPEN, FETCH, or CLOSE. A higher sampling rate will produce more complete counts.
- When execution time is negligible for a SQL statement, Strobe rounds the amount to zero and a service time of .0000 is reported.
SQL CPU Usage Summary Report
SQL TYPE
The SQL TYPE column of this report will contain DBRMs sorted by name, time, and date.
SQL NAME
In this column, Strobe displays the actual plan or package name.
STATEMENT COUNT
In this column, Strobe displays the total number of statement executions during the measurement session.
EXECUTION AVG TIME
In this column, Strobe displays a weighted average for the number of seconds it took for all statements to execute.
TIMESTAMP
Strobe displays the time and date the DBRM/Package was created in this column.
% CPU TIME TOTAL
This column contains the in_DB2 SQL CPU utilization of a DBRM/Packages as a percentage of the total in_DB2 SQL CPU utilized by all SQL execution in this measurement. (If there is only one DBRM/Package for a measurement, it will be 100%.) The next step is to examine the SQL statements within this DBRM.
CPU Usage by SQL Statement Report
The CPU Usage by SQL Statement report (See the following figure) shows the distribution of in_DB2 SQL CPU activity among SQL statements within a DBRM and displays the target SQL statements that caused the activity.
The report includes a description section that displays the SQL statement text for all target statements, and a CPU usage section that details the amount of in_DB2 SQL CPU activity consumed by each executing statement.
CPU Usage by SQL Statement Report for DBRMs
Report Header Description
Strobe displays a header showing the DBRM name and the creation date and time stamp.
SQL Statement Identification
SQL statements are listed under the following headings: Static, Non-cursor SQL, Static, Cursor SQL, Dynamic, Non-cursor SQL, or Dynamic, Cursor SQL. If Strobe encounters Static, Non-cursor SQL statements, each detail line begins with the precompiler listing statement number followed by statement type. The statement number generated for nonexecutable statements is not made available to Db2 during execution. Nonexecutable statements can only be embedded in an application program and include precompiler declaratives such as DECLARE CURSOR and WHENEVER.
In the CPU activity section of the report, Strobe begins detail lines with the precompiler statement number followed by the SQL verb. Under the heading STATEMENT TEXT, for SQL verbs with target SQL statements, Strobe displays the generated statement number and the SQL verb of the target statement.
For nonexecutable SQL statements, Strobe shows a uniquely generated statement number. The statement number is based on the sequence in which activity is detected for a statement. This generated number is used to correlate the executable statement, for example OPEN or FETCH, with the target DECLARE CURSOR statement.
Strobe also replaces any literals in the WHERE predicate of dynamic statements and aggregates activity in each SQL statement that has the same row/column name and operand. Literals are replaced with values determined by literal type as follows:
Numeric literals | ???? |
String literals | ‘????’ |
String literals beginning with % | ‘%????’ |
Date literals | ‘????-??-??’ |
Time literals | ‘??:??:??’ |
Timestamp literals and timestamps with timezone | ‘????-??-??-??.??.??.??????’ |
Timezone literal | ‘+/-??:??’ |
For example, Strobe combines activity in the statements SELECT…WHERE NAME='William' and SELECT…WHERE NAME='Paul', and reports it as SELECT…WHERE NAME='????'. Literals found within an IN clause will be reduced to improve aggregation; that is, WHERE NAME IN ('William', 'Paul') and WHERE NAME IN ('Bob', 'Mark', 'Joe') will both reduce to WHERE NAME IN ('????').
Optionally, Strobe retains the literal data (for the most CPU intensive occurrence of each SQL statement) that it replaced when the Strobe PARMLIB option COLLECT_DB2_PRED_DATA is set to ENABLE and L is entered into the Collect Db2 Predicate Data field on the Db2/DDF Data Collector Capture Option ISPF or the Collect DB2 Predicate Data box is checked on the iStrobe Db2 Measurement option window. If enabled and requested, the literal information that is retained is put back into its original place within the SQL text prior to SQL Analysis and iStrobe reporting. This feature enables Strobe's SQL Analysis Explain feature (SQLAF) to obtain a more accurate access path by using the data that was actually present at execution time. Care should be used when enabling this feature as sensitive customer data may be captured and there is no built-in security in Strobe or iStrobe.
The example in CPU Usage by SQL Statement Report for DBRMs illustrates a report for a DBRM that uses both static and dynamic SQL statements. In the Query description, SQL statements are grouped by their type: Static, Non-cursor SQL, Static, Cursor SQL, Dynamic, Non-cursor SQL, or Dynamic, Cursor SQL). For more information on how to control the length of the text shown for a SQL statement, see Creating the Strobe Performance Profile.
LOCATION
Strobe for Db2 shows the location of the Db2 subsystem the application is connected to.
PARALLEL
If the Db2 SQL capture reports that the Db2 Optimizer has determined an SQL statement meets the requirements for using parallel processing, Strobe displays a “PARALLEL” indicator for that SQL statement. This indicator appears on the line following the SQL text, and is followed by one or more fields that provide more specific information about the type of parallel processing observed. The PARALLEL indicator is not displayed for SQL statements that are not eligible. The following table lists the fields that appear with the PARALLEL indicator.
Field | Indicates whether or not the SQL statement |
ELIG=YES|NO | was eligible for parallel processing. |
PIO=YES|NO | used parallel I/O |
CPU=YES|NO | was split and processed by multiple CPUs |
STMT EXECUTION CNT
In this column, Strobe displays the number of times the statement executed.
STMT EXECUTION AVG-TIME
In this column, Strobe displays the average time the statement took to execute.
% CPU TIME TOTAL
This column contains the percentage of in_DB2 SQL CPU time used by an SQL statement. This value is the in_DB2 SQL CPU time consumed by an SQL statement during the measurement divided by the in_DB2 SQL CPU time used by the entire address space during the measurement. These in_DB2 SQL CPU times include in_DB2 CPU utilization from any group member the SQL was executing on if the application was connected to a Db2 group.
Execution of Stored Procedures
When you measure a job that issues calls to Db2 stored procedures, the CPU Usage by SQL Statement reports (See the following figure) shows the CALL statement executed, as well as in_DB2 SQL CPU usage associated with its execution.
CPU Usage by SQL Statement Report for Stored Procedures
The CPU time shown reflects the in_DB2 execution time utilized in executing the call statement. This time does not include in_DB2 SQL execution CPU usage associated with the actual SQL statements contained and executed within the stored procedure. Create an iStrobe profile for this measurement, and the iStrobe Stored Procedure SQL Activity report will contain information for SQL statements within the stored procedure.
Batch Transaction Reports
Transaction reports in the Strobe Performance Profile vary according to the type of address space and the Strobe options that were active during the measurement session. Transaction reports for TSO address spaces or batch processing programs show CPU activity by Db2 plan or package (DBRM) name. When Strobe for Db2 cannot attribute CPU activity in Db2 service modules to a Db2 plan name, it assigns that activity to the pseudo-transaction .DB2. When Strobe for Db2 observes CPU activity in non-Db2 programs, it assigns that activity to the pseudo-transaction .NONDB2.
When you measure an online subsystem, the transaction reports show which transactions were responsible for most of the CPU activity. These reports are helpful when:
- You want to see CPU usage for the transaction and CICS response time statistics
- The major CPU activity is not in Db2 service modules
- DBRMs are shared among transactions.
Transaction Usage by Control Section Report
Reports for Analyzing Wait Time
The Performance Profile shows wait time in measurements of Db2 allied address spaces in the Wait Time by Module report and the Attribution of CPU Wait Time report. Two reports also show wait time detected in allied address spaces for SQL statements. The SQL Wait Summary and the Wait by SQL Statements reports use a format similar to the equivalent reports for CPU usage. These reports are generated for DBRMs.
Wait Time By Module Report
The Wait Time by Module report (See the following figure) shows the sites of wait time. In this TSO address space, the Wait Time by Module report shows wait time due to Db2 system module DSNVSR.
Wait Time by Module Report
Attribution of CPU Wait Time Report
The Attribution of CPU Wait Time report identifies the sites of invocation of system routines. When wait time is attributed to a Db2 service routine, the report header displays the invoked routine and shows the following:
- A transaction name, if one is available
- The SQL statement or DBRM name
- The time it was invoked or created
- Its SQL statement number, if available
- The executed SQL statement’s type and the target statement’s number and type
- Total wait time caused by the invoker.
When wait time is attributed to DSNVSR, the report also displays the module and section name for the module that called DSNVSR, and provides a function descriptor for that module. The function descriptor can be helpful in identifying the resource for which the suspended SQL statement is waiting.
If you have identified an SQL statement on the Attribution of CPU Wait Time report that you want to examine in more detail, go the section .
Attribution of CPU Wait Time Report
SQL Wait Time Summary Report
The SQL Wait Summary report (See the following figure) shows the distribution of wait time among the DBRMs that invoked Db2 system services. Each detail line in this report shows the SQL type, its name, the time and date of execution (or, for DBRMs, the time and date it was created), and its total run time. Also, Strobe provides the total number of executed statements and a weighted average for the amount of time it took for all statements to execute. The report fields are described in the section called SQL CPU Usage Summary Report.
SQL Wait Summary Report
Wait By SQL Statement Report
The Wait by SQL Statement report attributes wait time to individual statements. The example in Wait by SQL Statement Report for DBRMs shows how Strobe presents this information. Refer to CPU Usage by SQL Statement Report for a description of the report fields other than RUN TIME PERCENT, which is described next.
RUN TIME PERCENT
RUN TIME PERCENT is the percentage of time during the measurement session that the address space was in wait state. TOTAL measures all causes of wait time, programmed I/O operations, and timer requests.
Wait by SQL Statement Report for DBRMs
