There are a variety of factors to consider when designing and implementing a benchmark program. Since the main purpose of the program is to simulate a user application, the overall structure of the program can vary. You can use the entire application as the benchmark and simply introduce a means for timing the SQL statements to be analyzed. For large or complex applications, it may be more practical to just include blocks containing the important statements.
To test the performance of specific SQL statements, another approach would be to include these statements alone in the benchmark program along with the necessary CONNECT, PREPARE, OPEN, and other statements and a timing mechanism.
Another factor to consider is the type of benchmark to use. One option is to run a set of SQL statements repeatedly over a time interval. The ratio of the number of statements executed and this time interval would give the throughput for the application. Another option would be to simply determine the time required to execute individual SQL statements.
Regardless of the type of benchmark program, an efficient timing system is necessary to calculate the elapsed time, whether for individual SQL statements or the application as a whole. For simulating applications in which individual SQL statements would be executed in isolation, it may be important to consider times for CONNECT, PREPARE, and COMMIT statements. However, for programs processing many different statements, perhaps only a single CONNECT or COMMIT is necessary, so focusing on just the execution time for an individual statement may be the priority.
While the elapsed time for each query is an important factor in performance analysis, it may not necessarily reveal bottlenecks. For example, information on CPU usage, locking, and buffer pool I/O could show that the application is I/O bound instead of using the CPU to its full capacity. A benchmark program should allow you to obtain this kind of data for a more detailed analysis if needed.
Not all applications will need to send the entire set of rows retrieved from a query to some output device. For example, some may use the whole answer set as input for another program (that is, none of the rows are sent to output). Formatting data for screen output usually has high CPU cost and may not reflect user need. In order to provide an accurate simulation, a benchmark program should reflect the row handling of the specific application. If rows do get sent to an output device, inefficient formatting could consume the majority of CPU processing time and misrepresent the actual performance of the SQL statement itself.
The db2batch Benchmark Tool: A benchmark tool (db2batch) is provided in the misc subdirectory of your instance sqllib directory. This tool takes many of the points made above regarding the creating of a benchmark program into consideration. This tool will read SQL statements from either a flat file or standard input, dynamically describe and prepare the statements, and return an answer set. It also provides the added flexibility of allowing you to control the size of the answer set, as well as the number of rows that should be sent from this answer set to an output device.
You can also specify the level of performance-related information supplied, including the elapsed time, CPU and buffer pool usage, locking, and other statistics collected from the database monitor. If you are timing a set of SQL statements, db2batch will also summarize the performance results and provide both arithmetic and geometric means. For more information on invocation syntax, and options, type db2batch -h on a command line.
The Command Reference manual can also be referenced for more information on db2batch.
The following is an example of how db2batch could be used with an input file db2batch.sql:
Figure 60. Sample Benchmark Input File: db2batch.sql
-- db2batch.sql
-- ------------
--#SET PERF_DETAIL 3 ROWS_OUT 5
-- This query lists employees, the name of their department
-- and the number of activities to which they are assigned for
-- employees who are assigned to more than one activity less than
-- full-time.
--#COMMENT Query 1
select lastname, firstnme,
deptname, count(*) as num_act
from employee, department, emp_act
where employee.workdept = department.deptno and
employee.empno = emp_act.empno and
emp_act.emptime < 1
group by lastname, firstnme, deptname
having count(*) > 2;
--#SET PERF_DETAIL 1 ROWS_OUT 5
--#COMMENT Query 2
select lastname, firstnme,
deptname, count(*) as num_act
from employee, department, emp_act
where employee.workdept = department.deptno and
employee.empno = emp_act.empno and
emp_act.emptime < 1
group by lastname, firstnme, deptname
having count(*) <= 2;
|
Using the following invocation of the benchmark tool:
db2batch -d sample -f db2batch.sql
Produces the following output:
Figure 61. Sample Output From db2batch (Part 1)
--#SET PERF_DETAIL 3 ROWS_OUT 5 Query 1 Statement number: 1 select lastname, firstnme, deptname, count(*) as num_act from employee, department, emp_act where employee.workdept = department.deptno and employee.empno = emp_act.empno and emp_act.emptime < 1 group by lastname, firstnme, deptname having count(*) > 2 |
Figure 62. Sample Output From db2batch (Part 1)
LASTNAME FIRSTNME DEPTNAME NUM_ACT --------------------------------------------------------------------------- JEFFERSON JAMES ADMINISTRATION SYSTEMS 3 JOHNSON SYBIL ADMINISTRATION SYSTEMS 4 NICHOLLS HEATHER INFORMATION CENTER 4 PEREZ MARIA ADMINISTRATION SYSTEMS 4 SMITH DANIEL ADMINISTRATION SYSTEMS 7 Number of rows retrieved is: 5 Number of rows sent to output is: 5 Elapsed Time is: 0.074 seconds Locks held currently = 0 Lock escalations = 0 Total sorts = 5 Total sort time (ms) = 0 Sort overflows = 0 Buffer pool data logical reads = 13 Buffer pool data physical reads = 5 Buffer pool data writes = 0 Buffer pool index logical reads = 3 Buffer pool index physical reads = 0 Buffer pool index writes = 0 Total buffer pool read time (ms) = 23 Total buffer pool write time (ms) = 0 Asynchronous pool data page reads = 0 Asynchronous pool data page writes = 0 Asynchronous pool index page reads = 0 Asynchronous pool index page writes = 0 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous read requests = 0 LSN Gap cleaner triggers = 0 Dirty page steal cleaner triggers = 0 Dirty page threshold cleaner triggers = 0 Direct reads = 8 Direct writes = 0 Direct read requests = 4 Direct write requests = 0 Direct read elapsed time (ms) = 0 Direct write elapsed time (ms) = 0 Rows selected = 5 Log pages read = 0 Log pages written = 0 Catalog cache lookups = 3 Catalog cache inserts = 3 Buffer pool data pages copied to ext storage = 0 Buffer pool index pages copied to ext storage = 0 Buffer pool data pages copied from ext storage = 0 Buffer pool index pages copied from ext storage = 0 Total Agent CPU Time (seconds) = 0.02 Post threshold sorts = 0 Piped sorts requested = 5 Piped sorts accepted = 5 |
Figure 63. Sample Output from db2batch (Part 2)
--#SET PERF_DETAIL 1 ROWS_OUT 5
Query 2
Statement number: 2
select lastname, firstnme,
deptname, count(*) as num_act
from employee, department, emp_act
where employee.workdept = department.deptno and
employee.empno = emp_act.empno and
emp_act.emptime < 1
group by lastname, firstnme, deptname
having count(*) <= 2
LASTNAME FIRSTNME DEPTNAME NUM_ACT
---------------------------------------------------------------------------
GEYER JOHN SUPPORT SERVICES 2
GOUNOT JASON SOFTWARE SUPPORT 2
HAAS CHRISTINE SPIFFY COMPUTER SERVICE DIV. 2
JONES WILLIAM MANUFACTURING SYSTEMS 2
KWAN SALLY INFORMATION CENTER 2
Number of rows retrieved is: 8
Number of rows sent to output is: 5
Elapsed Time is: 0.037 seconds
Summary of Results
==================
Elapsed Agent CPU Rows Rows
Statement # Time (s) Time (s) Fetched Printed
1 0.074 0.020 5 5
2 0.037 Not Collected 8 5
Arith. mean 0.055
Geom. mean 0.052
|
The above sample output includes specific data elements returned by the database system monitor. For more information about these and other monitor elements, see the System Monitor Guide and Reference manual.
In the next example, just the summary table is produced.
db2batch -d sample -f db2batch.sql -r /dev/null,
Produces just the summary table. Using the -r option, outfile1 was replaced by /dev/null and outfile2 (which contains just the summary table) is empty, so db2batch sends the output to the screen:
Figure 64. Sample Output from db2batch -- Summary Table Only
Summary of Results
==================
Elapsed Agent CPU Rows Rows
Statement # Time (s) Time (s) Fetched Printed
1 0.074 0.020 5 5
2 0.037 Not Collected 8 5
Arith. mean 0.055
Geom. mean 0.052
|
This benchmarking tool also has a CLI option. With this option, you can specify a cache size. In the following example, db2batch is run in CLI mode with a cache size of 30 statements:
db2batch -d sample -f db2batch.sql -cli 30