Learn how DB2's query parallelism features help speed query run time.
Editor's Note: This article is an excerpt from Chapter 17, "Performance Monitoring and Tuning," of DB2 10 for z/OS Database Administration: Certification Study Guide.
To reduce elapsed time for a query, DB2 can give queries parallel resources, such as several I/O paths or processors. By taking advantage of these resources, queries can run in a shorter period of time, enabling more work to be pushed through the system. Parallelism can help improve the performance of I/O- and CPU-bound read-only queries. It can help queries that are reading large amounts of data, regardless of the filtration.
Some overhead in terms of CPU is associated with the use of parallelism. DB2 scales processor-intensive work across all available processors. Parallelism can average less than 1 percent additional CPU overhead for long-running queries and less than 10 percent for short-running queries. Even if you select query parallelism at bind or prepare time, there is no guarantee that it will be used at execution time. DB2 may reduce or eliminate the degree of parallelism if it determines at run time that insufficient resources (memory, CPU, and so on) exist to support the parallelism. You can use the DB2 Accounting Report to determine whether parallelism was used at the thread level, and you can run a DB2 performance trace to determine the actual degree of parallelism used for a query.
I/O and CPU Parallelism (PARALLELISM_MODE = I or C)
DB2 can use two different methods for achieving query parallelism: I/O or CPU. With I/O parallelism, the goal is to move elapsed time toward CPU time by splitting data access into equal, sequential prefetch streams to bring I/O time down to estimated CPU time. If CPU is estimated at one second and I/O at three seconds, the three I/O parallel streams of approximately equal size will be started. Each I/O stream should cost about one second. This type of parallelism is implemented with a round-robin type of GET paging. With current releases of DB2, I/O parallelism is very infrequently chosen; instead, the preferred method is CPU parallelism.
The goal of CPU parallelism is to move elapsed time toward CPU time by splitting queries into multiple equal, smaller queries and processing those queries in multiple execution units, or parallel tasks. At execution time, DB2 considers the number of CPUs available, and if there are not enough CPUs to support the degree of parallelism initially chosen by the optimizer, the degree will be degraded.
DB2 parallelism is decided both at the time of the bind and at run time. If parallelism is not chosen at bind time, there is no possibility of it being chosen at run time. Even if parallelism is chosen at bind time, it may not be used at run time due to several factors. If insufficient space exists in the virtual buffer pool to support the requested degree of parallelism, DB2 can reduce the degree from that chosen at bind time, or it can turn off parallelism altogether. An SQL query that uses host variables can prevent DB2 from determining which partitions will qualify in a query; therefore, the degree of parallelism chosen will be decided at run time. If the machine on which DB2 is running does not have hardware sort at run time, parallelism will be disabled. If DB2 determines that an ambiguous cursor can be updated, parallelism will be disabled. If parallelism is disabled, the query does not fail; DB2 simply uses a sequential plan to access the data.
During BIND or PREPARE, DB2 chooses the access path best suited to the query and then does a post-optimization step to identify the sections of the access path that will benefit most from parallelism. It then identifies the part of the actual query that can be executed in parallel and finally determines the degree of parallelism to be used.
For dynamic SQL, parallelism can be enabled by using the following statement:
SET CURRENT DEGREE = 'ANY'
Queries Best-Suited for Parallelism
Queries with the following characteristics can take advantage of parallelism:
- Aggregate functions—Joins: hybrid, merge scan, and nested loop
- Long-running, read-only queries, both static and dynamic SQL, from both local and remote sites
- Table space scans and index scans—Joins: nested loop, merge scan, and hybrid without sort on new tables
- Multi-row fetch for read-only cursors
Queries that will process large amounts of data against a partitioned table space are excellent candidates for parallelism. There are only a few places where DB2 will not consider using parallelism:
- Queries that use multi-index access to return a DOCID list
- Queries that use an XML data type
- Queries with a common table expression (CTE) or table function
- Queries with created global temporary tables
The following circumstances will cause only Sysplex parallelism not to be considered:
- Queries with list prefetch and multiple index access
- Queries performing direct row access via a row identifier (RID)
- Queries accessing a table with a security ID column
- Queries accessing large object (LOB) data
Parallelism should not be used if a system is already CPU-constrained because parallelism would only add to the problem in most situations.
CPU parallelism cannot be used when a cursor is defined WITH HOLD, because this cursor's use is potentially interrupted by a commit, which causes a stoppage in processing.
Short-running queries usually will not see a great benefit from parallelism (in general, a short-running query is one that is subsecond). But how often are long-running queries separated from short-running queries? Well, if you are trying to obtain the benefits of parallelism without placing unnecessary overhead where it does not belong, you will need to give consideration to this type of granularity of processing.
You could separate the long-running queries into a separate package and bind the package using DEGREE(ANY) in a different collection; then use the SET CURRENT PACKAGESET statement to switch between the package and a program bound with DEGREE(1) for shorter queries that are better off run sequentially.
Or you could set the macro SPRMPTH to disable parallelism for short-running queries. The default value for SPRMPTH is 120, which causes parallelism to be disabled for any query with an estimated cost of less than 120 milliseconds; parallelism is enabled for any query above this threshold.
For dynamic SQL, you could consider use of the SET CURRENT DEGREE statement to switch parallelism on and off. Also, for dynamic queries, you can use the resource limit facility to disable the various types of parallelism for certain users, plans, packages, collections, or locations.
Sysplex Query Parallelism (PARALLELISM_MODE = X)
Sysplex query parallelism works in much the same multitasking way as CPU parallelism; in addition, it gives us the ability to take a complex query and run it across multiple members in a data sharing group. Sysplex query parallelism is best used with isolation level UR (uncommitted read) to avoid excess lock propagation.
In this scenario, a query is issued by a coordinator, who sends the query to the assistant members in the group. The members then process the data and return it to the coordinator either via a work file (the coordinator will read each assistant's work files) or by cross-system coupling facility (XCF) links when a work file is not necessary.
Want to learn more? Check out DB2 10 for z/OS Database Administration: Certification Study Guide at the MC Press Bookstore.