 | Level: Intermediate Cliff Chu (cdchu@us.ibm.com), Advisory Software Engineer, IBM
04 Oct 2007 In a complex application or batch processing environment where many
SQL procedures and SQL statements are executed, it can be difficult for a DBA to
monitor and tune the SQL using traditional methods. Use IBM® DB2® Developer
Workbench (DWB) to profile SQL stored procedures deployed on DB2 for Linux, Unix, and
Windows, Version 8.2 or later.
Introduction
An earlier
article on developerWorks introduced an SQL Procedure Profiler tool that discovers the
SQL procedures invoked by an application in flight, profiles the procedures, and
presents the collected data side-by-side with each procedure's source code. Now,
rather than using a separate tool, use Developer Workbench to profile the execution
of SQL stored procedures on DB2 LUW, Version 8.2 or later. This functionality is available
in Developer Workbench, Version 9.1 or later and Rational Application Developer,
Version 7.0 or later.
You can use this function to profile both procedures that are created within Developer Workbench or outside of it.
The profiling data captured can include the elapsed time for a given statement, the number of times a statement was called, the number of rows read, and more.
Usage notes
This tool only captures metrics for SQL stored procedures. Data is not captured for any
other kind of stored procedure (in other words, for Java stored procedures). However, in the case of
nested stored procedure calls, any SQL stored procedures in the call stack have metrics captured.
Note that not all statements may have profiling data captured for it. There are certain statements
DB2 does not capture profiling data for.
The tool does not determine whether profiling data will be captured at all, so it is possible
that no profiling data is displayed after the stored procedure call finishes execution.
The list below summarizes the
task flow of the tool, which is explained in detail in the sections that follow:
- Launch the run profiling action on one stored procedure that is deployed on a DB2
LUW, Version 8.2 or later server, from either the Data Project Explorer or Database
Explorer views.
- Select optional event monitors to include in the report.
- Select the stored procedures, if any, to include in the report.
- View the report in the Profiling Data tab of the Data Output view.
- Optionally, print the report, save it to a text file, or both.
Launching the SQL Procedure Profiling tool
You can launch the SQL Procedure Profiling tool in two ways:
- From the context menu of one deployed stored procedure in the Data Development Project, by launching the Run Profiling action
- From the context menu of one stored procedure in the Database Explorer, by launching the Run Profiling action
Note that since profiling data can be captured for nested stored procedure calls, even non-SQL stored
procedures have the Run Profiling action active. For example, you can launch the Run Profiling
action on a Java stored procedure, which in turn calls an SQL stored procedure. If possible, profiling
data will be captured for the called SQL stored procedure (but not for the Java stored procedure).
Figure 1 shows the Run Profiling action being launched from a stored procedure in
the Database Explorer view:
Figure 1. Launching SQL Procedure Profiling in the Database Explorer view
Specifying optional event monitors
After launching the Run Profiling action, a dialog that allows you to specify
event monitors opens.
There are 15 event monitors you can select from. You can select multiple monitors from the list
in the same way you select multiple elements from a list in your operating system. For example, on the
Windows platform, you can select multiple elements by holding the Ctrl key while
left-clicking on the
desired elements.
Figure 2 shows the optional event monitor selection dialog:
Figure 2. Optional event monitor selection dialog
The event monitor names correspond to the event monitors as defined in the DB2 System Monitor
Guide and Reference.
The following list provides a brief description for the selectable event monitors
(for more details, refer
to the System Monitor Guide and Reference):
- TOTAL_SORT_TIME: The total elapsed time (in milliseconds) for all
sorts that have been executed.
- TOTAL_SORTS: The total number of sorts that have been executed.
- FETCH_COUNT: For the stmt snapshot monitoring level and the statement
event type: the number of successful fetches performed on a specific cursor.
- ROWS_READ: The number of rows read from the table.
- ROWS_WRITTEN: The number of rows changed (inserted, deleted or
updated) in the table.
- INT_ROWS_DELETED: The number of rows deleted from the database as a
result of internal activity.
- INT_ROWS_INSERTED: The number of rows inserted into the database as a
result of internal activity caused by triggers.
- INT_ROWS_UPDATED: The number of rows updated from the database as a
result of internal activity.
- POOL_DATA_L_READS: The number of logical read requests for data pages
that have gone through the buffer pool.
- POOL_DATA_P_READS: The number of read requests that required I/O to
get data pages into the buffer pool.
- POOL_INDEX_L_READS: The number of logical read requests for index
pages that have gone through the buffer pool.
- POOL_INDEX_P_READS: The number of physical read requests to get index
pages into the buffer pool.
- POOL_TEMP_DATA_L_READS: The number of data pages that have been
requested from the buffer pool (logical) for temporary table spaces.
- POOL_TEMP_DATA_P_READS: The number of data pages read from the table
space containers (physical) for temporary table spaces.
- POOL_TEMP_INDEX_L_READS: The number of index pages that have been
requested from the buffer pool (logical) for temporary table spaces.
After you select the event monitors and click on OK, a dialog appears that allows you to specify parameter
values, if the stored procedure has any input parameters. If there are no input parameters, the stored procedure
proceeds to be called so that profiling data can be captured.
Selecting the stored procedures to include in the report
After the stored procedure is executed, there can be two outcomes:
- No profiling data was captured for the stored procedure call because either there
were no SQL stored procedures in the call stack, or because there were no
statements within any stored procedure that data could be captured for. In this
case, you are presented with a dialog stating this situation, as shown in Figure 3.
Figure 3. No profiling data captured dialog
- Profiling data was captured for the stored procedure call. The user is presented with a dialog to select what stored procedures should be included in the report.
If there was data captured, you are presented with a dialog that allows selection of the procedures to
include in the report. You can filter on a particular schema or select procedures from all schemas.
Each procedure in the list has a number next to it, which specifies the number of parameters that procedure has.
Figure 4 shows the report procedure selection dialog:
Figure 4. Report procedure selection dialog
In the case of non-nested stored procedure calls, there is only one entry in the procedures list.
For nested calls, there are additional entries for each SQL stored procedures that had profiling data captured.
Select the procedures you want to include in the report, and click OK to close the dialog and to trigger
the collection of the profiling data for the report.
Viewing captured profiling data
If there was any profiling data captured, you can view it in the Data Output view, after the stored
procedure finishes executing. You can view the output in the Profiling Data tab. Regardless of what
optional event monitors you select for the report, the following columns are always present in the report:
- ROWNUM: The row number of the report.
- ROUTINESCHEMA: The schema of the stored procedure at that particular
row.
- SPECIFICNAME: The specific name of the stored procedure at that
particular row.
- LINE: The line number of the stored procedure at that particular row.
- NUM_ITERATION: The number of times that statement at that particular
row was called.
- ELAPSED TIME: The elapsed time spent running the statement (in seconds). For a
cursor, this is the elapsed time between open and close. Also note that the sum of
all elapsed times within a routine is not equal to the elapsed time of the calls invoking the routine. The reason for this mostly lies in the fact that the event monitoring itself has a significant overhead. However, elapsed time is a good indicator of relative performance between statements and routines.
- CPU (microseconds): The total CPU time (in microseconds) consumed to execute an
SQL statement. The higher the value, the more CPU-intensive the statement is. CPU
time comprises user as well as system CPU. Unlike elapsed time, in case of a declare
cursor statement the CPU time is the sum of open, all fetched, and closed. So it is
a much better indicator, along with other fields, of the cost of a cursor.
- TEXT: The text of the stored procedure at that particular line.
In general, column values of -1 or -1.0 indicate that the field is not applicable.
You may find it helpful to expand the Data Output view and stretch out the columns for better viewing.
To expand the Data Output view, double-click on the Data Output view tab.
To expand the
columns, select and drag the column dividers in the Profiling Data table. Figure 5 shows the Data
Output view Profiling Data tab:
Figure 5. Generated report in the Profiling Data tab of the Data Output view
You can also save the report to a file or print it. To do this, right-click
anywhere in the table body.
Select Save As to specify a file to write the report to, or select Print to send the output to a printer.
The Save As action produces a tabular formatted report, in plain text, within a directory and file that
you specify.
Conclusion
Using the SQL Stored Procedure Profiling feature of Developer Workbench, you can gather information on
the execution of SQL stored procedures, whether they are called directly or nested within other stored
procedure calls. The data gathered can include metrics such as the number of times a particular statement
has been executed, how much time has elapsed for a particular statement, and more. These metrics can help
you identify bottlenecks within your stored procedure call stack, so that you can optimize your stored
procedures accordingly.
Resources Learn
Get products and technologies
Discuss
About the author | | | Cliff Chu is an Advisory Software Engineer, working on the development Eclipse-based development tools for stored procedures, user-defined functions, and XML on the DB2 data servers. He has worked as a developer in various areas of data management within IBM, including the engine components of DB2 for z/OS, the Data Server Driver for JDBC and SQLJ, and Eclipse-based tooling for DB2. He is currently the technical lead for Eclipse-based XML tools for DB2 that are shipped in various IBM products such as Developer Workbench and Rational Application Developer. |
Rate this page
| |
