Tuning SQL with Optim Query Tuner, Part 1: Understanding access paths

Learn how to monitor and tune queries and workloads to improve application performance

If you are a developer, DBA, or query tuning specialist, it is critical that you understand the basics of access paths so that you can precisely tune queries and query workloads before they cause problems in your production environment. This basic understanding, coupled with the visualization and tuning advice provided by IBM® Optim® query tuning solutions, can help make you more efficient at this task. This article provides conceptual background on access paths, shows you how to read an access path graph, and walks through the access path graph to demonstrate critical information regarding access path selection. The article concludes with a sample scenario that demonstrates how to use query annotation, a feature in Optim Query Tuner that helps you with query analysis by providing vital statistical information directly in the SQL statement.

Share:

Gene Fuh (fuh@us.ibm.com), IBM Distinguished Engineer, IBM

Gene Fuh photoGene Fuh has been working in database technologies for IBM since 1994. He joined the DB2 for z/OS organization in 2000 after he had worked in the DB2 LUW organization for six and a half years. In 2004, Gene started up a team for the development of DB2 Optimization Expert and Optimization Service Center (DB2 OE/OSC). He was chief architect and overseer of the project until 2007, when the product became available simultaneously with DB2 9 for z/OS. In 2008, Gene initiated the transition of DB2 OE/OSC technology into the Optim tuning solutions, which is now known as Optim Query Tuner and Optim Query Workload Tuner. During his 17 years with IBM, Gene filed 48 patent applications and published more than 20 technical papers in both academic and IBM conferences.



Kendrick Ren (kren@ca.ibm.com), Advisory Software Engineer, IBM

Kendrick Ren photoKendrick Ren is the technical lead of the IBM Optim Query Tuner and Optim Query Workload Tuner products, working out of the IBM Toronto lab. He has worked with these products in their previous incarnations as the DB2 Optimization Expert and Optimization Service Center products since the team was founded in 2004. Kendrick works closely with the customers and business partners who are using these products, assisting them in the area of query optimization. Before joining the Optimization Expert team, Kendrick worked two years on the IBM WebSphere Commerce Server product.



Kathryn Zeidenstein (krzeide@us.ibm.com), InfoSphere Guardium Evangelist, IBM

Photo of Kathryn ZeidensteinKathy Zeidenstein has worked at IBM for a bazillion years. Currently, she is working as a technology evangelist for InfoSphere Guardium data activity monitoring, based out of the Silicon Valley Lab. Previously, she was an Information Development Manager for InfoSphere Optim data lifecycle tools. She has had roles in technical enablement, product management and product marketing within the Information Management and ECM organizations at IBM.



17 June 2010

Also available in Chinese Russian Vietnamese Spanish

Introduction

SQL is a declarative language in the sense that only the data of interest is described in the program, not the algorithm for obtaining the data. Therefore, there are often multiple ways to satisfy a given SQL statement. These different ways are known as access paths or access plans. For simplicity, this article refers to a particular way of satisfying an SQL statement as the access path. Although different access paths for the same SQL statement produce the same result set, they most likely do not perform the task with the same level of performance. The SQL compiler uses query optimization to select the best performing access path for any given SQL statement (within a reasonable amount of time, of course).

If you are a developer, DBA, or query tuning specialist, it is critical that you understand the basics of access paths so that you can precisely tune queries and query workloads before they cause problems in production. This basic understanding, coupled with the visualization and tuning advice provided by Optim query tuning solutions, can help make you more efficient at this task.

After introducing access paths and query execution, this article describes the most common access methods and join methods supported by DB2® for z/OS. Next, the article describes an access path selected by the DB2 for z/OS optimizer and walks through the access path graph to demonstrate critical information regarding access path selection. The article concludes with a sample scenario that demonstrates how to use query annotation, a feature in Optim Query Tuner that helps you with query analysis by providing vital statistical information directly in the SQL statement.

If you want the opportunity to interact with the access path diagrams yourself, they are included in a sample project file that you can obtain from the download section of this article. You can import the project file into Data Studio (stand-alone package with Fix Pack 1 or later) or any of the Optim Query Tuner products. You do not need to have a connection with the database to interact with the analysis results, which are labeled to correspond with the figure numbers in this article.

To import the sample project do the following:

  1. Open the Data Perspective of your Data Studio stand-alone or Optim Query Tuner product.
  2. Select File > Import....
  3. In the Import Wizard, navigate to Query Tuner > Projects, then click Next.
  4. Click Browse.. and select the directory that contains the downloaded zip file. This causes a list of projects to appear in the Projects window.
  5. Select sampleaccesspathproject, and click Finish.
  6. The sample project should now appear in your Project Explorer. If you do not see a Project Explorer Window, make sure you are in the Data Perspective and select Window > Reset Perspective. Alternatively, you can select Window > Show View > Project Explorer.

About Optim query tuning solutions

Optim query tuning solutions provide an environment to identify and tune poorly performing SQL statements with advisors and tools that help guide you to a solution. Query tuning capabilities are delivered in the following products:

  • Basic, single-query tuning and query formatting capabilities are available in Data Studio stand-alone 2.2.0.1. This product is available at no charge for both DB2 for z/OS and DB2 for Linux®, UNIX®, and Windows®. Be aware that while the information in this article series explains how you can use Data Studio to interpret the access path graphs, not all the capabilities that are described are available in Data Studio.
  • Single-query tuning and query formatting, as well as the larger set of advisors, are available in Optim Query Tuner. This product is available for both DB2 for z/OS and DB2 for Linux, UNIX, and Windows.
  • Query workload tuning, single-query tuning, and the full set of advisors are available in Optim Query Workload Tuner. This product is only available for DB2 for z/OS (as of the time this article was written).

For brevity, this article series uses the name Optim Query Tuner to refer to the set of advisors and tools that provide Optim query tuning solutions. Where applicable, specific product names are provided when describing capabilities that may not be available in all the products listed above.


How to read an access path graph

An access path graph not only describes the "operational details" of query execution, it also describes how the data flows. A leaf node of an access path graph is either a table node, a workfile node, or an index node that represents a data source in the query execution plan (Figure 1 shows examples of each of these types of nodes). Data flows from the bottom up (as displayed in the graph) and is processed by operation nodes in the access path graph. Table 1 describes the input, output, and function of several operation nodes that typically appear in an Optim Query Tuner access path graph.

Table 1. Typical operations displayed in Optim Query Tuner
Operation nodeFirst inputSecond inputOutputDescription of the function
IXSCANIndex node N/AA set of qualifying record identifiers (RIDs) Scan the index to retrieve the qualifying record identifiers within a given index key range.
FETCHA set of RIDs Table nodeA set of qualifying records Fetch data records and the corresponding data pages based on RIDs. Apply predicates if there are any.
TBSCANTable node N/AA set of qualifying recordsScan the target table space sequentially to fetch data pages and apply predicates to the records if there are any.
SORTA set of records or RIDsN/A A set of sorted records or RIDsSort the input into page number order (for RIDs) or sort key order (for records).
WFSCANWorkfile node N/AA set of recordsWork file scan. Scan the target workfile table space sequentially to fetch data pages and apply predicates to the records if there are any.
NLJOINA set of recordsA set of recordsA set of records Nested loop join. For each qualifying records from the first input (outer), scan the second input (inner) to find the matching records and return the joined records.
MSJOIN A set of sorted recordsA set of sorted recordsA set of recordsMerge scan join. Scan both inputs to find the matching records and return the joined records.

Access paths and query execution

The easiest way to describe access paths is to use an example. The SQL statement in Listing 1 joins three tables to produce a sales report. The sales amount is aggregated based on customers' gender and marital status.

Listing 1. Sample three-way join used to illustrate access path graph shown in Figure 1
select c.gender_code, c.marital_status_code, sum(od.unit_cost * cust_quantity)
from cust_customer c, cust_order_header oh, cust_order_detail od
where c.cust_code = oh.cust_code
  and oh.cust_order_number = od.cust_order_number
  and c.cust_prov_state_code = 'CA'
  and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by c.gender_code, c.marital_status_code

Intuitively, an access path is a procedural description of query execution that consists of three components:

  • The join sequence of the tables
  • The algorithm by which a table is scanned (access method)
  • The algorithm by which a join operation is performed (join method)

Figure 1 is the access path graph generated by Optim Query Tuner for the query shown in Listing 1.

Figure 1. Sample access path graph for Listing 1 (three-way join)
Access path graph of a three-way join. Text below figure provides a detailed description.

(See a larger version of Figure 1.)

Each of the three tables referenced in the query are shown in the graph as table nodes with their corresponding table names.

The leading table in the join sequence is the CUST_ORDER_DETAIL table, which is scanned through an index defined over the PRODUCT_NUMBER column. This index is represented in the graph by the index node labeled with the index name SQT01_CUST_ORDER_DETAIL.

Directly above the SQT01_CUST_ORDER_DETAIL index node is the IXSCAN node. This means that the index is traversed to obtain the qualifying record identifiers (RIDs). The qualifying RIDs are then taken by the FETCH node to fetch the corresponding records and data pages from the table space into the DB2 buffer pool.

The second table in the join sequence is the CUST_ORDER_HEADER table, which is scanned through an index on the join column named CUST_ORDER_NUMBER. The access method for CUST_ORDER_HEADER, index scan, is represented in the same way as the CUST_ORDER_DETAIL table.

The last table in the join sequence, CUST_CUSTOMER, is also scanned through an index defined over the CUST_CODE column.

Now that you have an overview of the tables and how the information is accessed (via indexes in this case), you are ready take a closer look at the join methods that are used. The join node in the graph shows that the DB2 optimizer selects a nested loop join (NLJOIN) for the first join operation between CUST_ORDER_DETAIL and CUST_ORDER_HEADER. The join takes the result set of the first table (CUST_ORDER_DETAIL) and the result set of the second table (CUST_ORDER_HEADER) as inputs. The result of that join operation then becomes the first input to the second join operation (also a nested loop join). It is joined to the result set of the last table in the join sequence, CUST_CUSTOMER.

Understanding data flow is important because it is the most influential factor in terms of the query performance aspect of query execution. The rest of this section takes you through the access path graph one more time from the data flow perspective so that you can gain a full understanding of the performance aspect of query execution.

The number shown in each node is the optimizer's estimate for the cardinality of the underlying data source or operation node. For example, the number 273 on the index node for SQT01_CUST_ORDER_DETAIL indicates that there are 273 distinct index keys. CUST_ORDER_HEADER_PK is a unique index; therefore, the number of index keys matches exactly the number of records (539,526) in the corresponding table. The same is true for the IDX_CUST_CUSTOMER index and CUST_CUSTOMER table, with 31,284 index keys and records, respectively.

For indexes that are not unique, the number of index keys is less than the number of records. Moreover, the ratio between the number of records and the number of index keys indicates, on average, the number of RIDs associated with each index key. The numbers that appear in the three table nodes in the graph indicate the size of the table in terms of number of records.

Here's what happens at execution time for the first leg of the nested loop join:

  1. The index scan (IXSCAN) operation over the index BQT01_CUST_ORDER_DETAIL goes first, because it's the first operation for the first table in the join sequence. Based on the optimizer's estimate, there are eight qualifying RIDs within the target index key range.
  2. These eight qualifying RIDs are used by the fetch node to locate the corresponding records and page in the table space, which contains 560273 records. Because there are no additional predicates to be applied by the fetch operation, the output cardinality of the fetch node is also eight. This means that there will be eight qualifying records produced by the fetch operation.
  3. These eight qualifying records are then taken as the first input to the NLJOIN node immediately above the FETCH node. Because of how nested loop joins work, the second leg (there are four nodes) is executed per outer table record. That is, the inner table of the join operation will be scanned eight times, once for each of the eight qualifying records from the outer input.

The second leg of the NLJOIN node is similar to that of the first table:

  1. The optimizer selects a unique index (CUST_ORDER_HEADER_PK) to access the records in the CUST_ORDER_HEADER table.
  2. As indicated by the number in the IXSCAN node and the FETCH node, one record is found in CUST_ORDER_HEADER each time the index scan is performed. Therefore, a total of eight records will be produced by the NLJOIN node.

Similarly, the last leg in the access path graph is evaluated eight times, once for each record produced by the first NLJOIN node. Because a unique index is selected for the IXSCAN node (IDX_CUST_CUSTOMER), only one record will match in each scan of the CUST_CUSTOMER table. Therefore, a total of eight records are produced in the final result set.

From a performance point of view, a good performing access path should touch the least amount of data with respect to the final result set. In the example shown above, the number of records fetched from each table is kept to the minimum. For the CUST_ORDER_DETAIL table, only eight records are fetched from the table space using the index scan. Also, each probe into the inner input of both NLJOIN operations matches only one record. Therefore, if the optimizer's estimate is accurate, this access path should be a very efficient access path. (The A case study using query annotation section of the article describes how you can determine if the estimate is accurate.)


Other access methods and join methods

In the previous section you learned about one type of access method, index scan (IXSCAN), and one type of join method, nested loop join (NLJOIN). This section covers a few more access methods, join methods, and sort operations. The description of each operation includes a Query Tuner access path graph that explains its operational semantics.

Table space scan (TBSCAN) with stage-1 and stage-2 predicates

The SQL statement in Listing 2 creates the access path graph shown on the right side of the Optim Query Tuner screenshot in Figure 2.

Listing 2. Sample query to illustrate table scan in Figure 2
select count(*)
from cust_order_header
where cust_total_quantity > 3
  and cust_sales_tax > cust_total * 0.03
Figure 2. Access path graph for table scan (TBSCAN) with stage-1 and stage-2 predicates
Access path graph of table scan with stage-1 and stage-2 predicates. Text below figure provides a detailed description.

(See a larger version of Figure 2.)

Stage-1 and stage-2 predicates (SARGable and residual)

Stage-1 predicates are applied when the column or columns that it depends on are in the buffer pool. Stage-2 predicates are applied when the column or columns that it depends on are copied from the buffer pool to the private memory pool. The Resources section contains a link to a topic in the DB2 for z/OS information center where you can find more information on this subject.

For DB2 for Linux, UNIX, and Windows, the same basic concepts are known as SARGable and residual predicates, respectively.

Figure 2 shows an example of how you can display detailed descriptions of two nodes (the CUST_ORDER_HEADER table and the table scan (TBSCAN) operation) from the access path graph. You can get detailed descriptions from any node on the graph simply by right-clicking on the node and selecting Show Description from the context menu. In Figure 2, the descriptor for the table node and the descriptor for the TBSCAN node are shown to the left of the access path graph. The SQL statement is shown at the bottom of the diagram.

The access path shows that a TBSCAN operation is performed over the table CUST_ORDER_HEADER. This means that DB2 fetches every data page in the table space sequentially into the buffer pool. It then scans each record in the buffer pool to apply the stage-1 predicate. The qualifying records (only the relevant columns) are copied to a private memory pool to be filtered by a stage-2 predicate. The aggregate function, COUNT, is applied to the records that remain after the stage-2 predicate filtering.

The details regarding the data flow can be found in the attributes section of the TBSCAN descriptor. There are 539,526 records scanned by DB2 in the buffer pool (Input Cardinality value on TBSCAN descriptor). The DB2 optimizer estimates that 473,785.44 records would survive the stage-1 predicate (see the Stage 1 Returned Rows attribute in the descriptor) and 4,737.9688 records would survive the stage-2 predicate (see Stage 2 Returned Rows attribute in the descriptor). There is only one record produced, because of the COUNT function. The Prefetch attribute in the descriptor contains the value 'S', which indicates that sequential prefetch will begin at execution time to improve the I/O performance for fetching the data pages from the table space.

Table scan (TBSCAN) with partition pruning

The SQL statement in Listing 3 creates the access path graph shown in Figure 3.

Listing 3. Sample query to illustrate table scan with partition pruning in Figure 3
select count(*)
from cust_order_header
where cust_total_quantity > 3 
  and cust_sales_tax > cust_total * 0.03
  and cust_order_number between 100000 and 580000
Figure 3. Access path graph for table scan (TBSCAN) with partition pruning
Access path graph of table scan with partition pruning. Text below figure provides a detailed description.

(See a larger version of Figure 3.)

Limit keys

Tables that are partitioned by keys use limit keys to indicate the boundaries of the partitions; one limit key for each partition.

As shown in the table node descriptor, there are 10 partitions in the table space, and the limit key for the ninth partition is 625,000. Because there is a predicate, cust_order_number between 100000 and 580000, on the partitioning column, the optimizer is able to limit the TBSCAN to the first nine partitions. This optimization is described in the TBSCAN descriptor. The Page_Ranges attribute contains one range, range 1, which covers partition 1 to partition 9. At execution time, partition 10 is skipped completely for better performance.

Index scan (IXSCAN) with matching predicates

The SQL statement in Listing 4 creates the access path graph shown in Figure 4.

Listing 4. Sample query to illustrate matching index scan in Figure 4
select crdt_method_code, cust_total_quantity, count(*)
from cust_order_header 
where cust_total_quantity - 2 > 1 
  and crdt_method_code > 20 
  and cust_order_date > '2007-01-01-01.24.58.017000' 
group by crdt_method_code, cust_total_quantity

(Note that in the example above, the cust_order_date column is defined with a type of TIMESTAMP.)

Figure 4. Access path graph for index scan (IXSCAN) with matching predicates
Access path graph of index scan with matching predicates. Text below figure provides a detailed description.

(See a larger version of Figure 4.)

An index scan is typically shown in Query Tuner as a group of four nodes in the access path graph. As shown in Figure 4, CUST_ORDER_HEADER is accessed through a matching index scan with the index BQT01_CUST_ORDER_HEADER, which is defined on column CRDT_METHOD_CODE (you can see this by expanding the IXSCAN descriptor a bit wider than what is shown in Figure 4). The IXSCAN descriptor details show that there is one matching predicate with a 7.32% filter factor that leads to the estimate of 114 index pages and 3,9471 RIDs qualifying out of a total of 1,558 index pages and 539,526 RIDs, respectively.

These RIDs are used by the FETCH node to position the corresponding data pages in the table space. Once fetched into DB2 buffer pools, records in these pages are scanned and the stage-1 predicate is applied. The detailed description of the FETCH node shows that 18,515.602 records are estimated to survive the stage-1 predicate. The stage-2 predicate is applied after the relevant columns of these records are copied to private buffers in DB2. Eventually, there are 6,172.508 records returned by the FETCH node.

Non-matching index scan (IXSCAN)

The SQL statement in Listing 5 creates the access path graph shown in Figure 5.

Listing 5. Sample query to illustrate non-matching index scan in Figure 5
select count(*) as count
from cust_order_header
Figure 5. Access path graph for non-matching index scan (IXSCAN)
Access path graph of non-matching index scan. Text below figure provides a detailed description.

(See a larger version of Figure 5.)

An index scan can be non-matching, which means that there are no restrictions in the scan of the underlying index. That is, DB2 scans every single leaf page. Typically, a non-matching index scan is chosen by the optimizer when the underlying index provides benefits such as index-only access, avoids sort, or some similar benefit.

In the example shown in Figure 5, the optimizer chooses to exploit the non-matching index scan because the aggregate function, count(*), can be evaluated by scanning the index only. Also, the index is generally smaller than the table and therefore, more efficient to scan. The IXSCAN node detailed description shows that all of the 1,680 leaf pages are scanned due to the lack of a matching predicate. There is no FETCH or table node shown in the access path graph because it is an index-only scan.

Nested loop join (NLJOIN)

The SQL statement in Listing 6 creates the access path graph shown in Figure 6.

Listing 6. Sample query to illustrate nested loop join in Figure 6
select oh.cust_code, sum(od.cust_quantity * od.cust_unit_price)
from cust_order_header oh, cust_order_detail od
where oh.cust_order_number = od.cust_order_number
  and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by oh.cust_code
Figure 6. Access path graph for nested loop join (NLJOIN)
Access path graph for nested loop join. Text below figure provides a detailed description.

(See a larger version of Figure 6.)

Nested loop joins (NLJOIN) take two data sources as input, join the records from the outer (left side) with the matching records on the inner (right side) that qualify the join predicates, and then return the joined records as the output. For more information about nested loop joins, refer to the appropriate information center link in the Resources section for your DB2 platform.

As shown in the sample query's access path graph in Figure 6, the qualifying records from the outer table (CUST_ORDER_DETAIL) are joined to the qualifying records of the inner table (CUST_ORDER_HEADER) using NLJOIN and an EQUAL join predicate. At execution time, for each record flowing from the FETCH node on the outer side of the join, the inner table is scanned using an index scan (IXSCAN) on index CUST_ORDER_HEADER_PK.

The detailed information in the NLJOIN descriptor (also shown in Figure 6) shows that according to optimizer's estimate:

  • There are eight qualifying records after applying the local predicate (Outer Input Cardinality value is 8).
  • One record of the inner table will match each qualifying record from the outer table (Inner Input Cardinality value is 1).
  • The NLJOIN node will eventually produce eight joined records as the output (Output Cardinality value is 8).

From the performance perspective, this is considered a very efficient join operation, because the cost for scanning the inner table is optimal via unique access through a unique index.

Nested loop join (NLJOIN) with sort composite

Notice that The SQL statement in Listing 7 is the same as the one in Listing 6 in the previous section. However, for this example a different access path is selected, which is shown in Figure 7.

Listing 7. Sample query to illustrate nested loop join with sort composite in Figure 7
select oh.cust_code, sum(od.cust_quantity * od.cust_unit_price)
from cust_order_header oh, cust_order_detail od
where oh.cust_order_number = od.cust_order_number
  and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by oh.cust_code
Figure 7. Access path graph for nested loop join (NLJOIN) with sort composite
Access path graph for nested loop join with sort composite. Text below figure provides a detailed description.

(See a larger version of Figure 7.)

The difference between Figure 6 and Figure 7 is that in Figure 7, a SORT node is added on the outer side of the join. This type of access path is referred to as a nested loop join (NLJOIN) with sort composite.

The following explains why the optimizer would add a SORT node. Notice that there is a significant difference between the cardinality shown in the fetch node on the outer side of the join in Figure 7. The example in Figure 6 only had eight records, but in Figure 7, the optimizer's estimate of the number of qualifying records is 8,708.988. The higher the cardinality, the more probes into the inner table, one per qualifying (outer) record. The index exploited for the inner table happens to be a clustered index. Therefore, the order of consecutive probes into the inner table could impact the I/O performance of the inner table significantly. More precisely, if the consecutive probes of the inner table are in CUST_ORDER_NUMBER order, the inner index will be scanned sequentially across different probes that, in turn, will render the scan of the inner table sequential. The benefit obtained from more efficient I/O outweighs the cost for sorting approximately 8,708 records. Therefore, in Figure 7 the optimizer chooses to sort the composite for the nested loop join.

In general, a nested loop join with sort composite is favored by the optimizer if the inner index is well-clustered and the inner table is expected to be probed many times.

Merge scan join (MSJOIN) with sort for outer and inner

The SQL statement in Listing 8 creates the access path graph shown in Figure 8.

Listing 8. Sample query to illustrate merge scan join in Figure 8
select *
from cust_order_header oh, cust_order_detail od
where oh.cust_total_quantity = od.cust_quantity
  and oh.cust_total = od.cust_unit_price
  and oh.cust_order_date > '2009-01-16-01.00.00.000000'
  and od.product_number > 150000

(Note that in the example above, the cust_order_date column is defined with a type of TIMESTAMP.)

Figure 8. Access path graph for merge scan join (MSJOIN) with sort for outer and inner
Access path graph for merge scan join with sort for outer and inner. Text below figure provides a detailed description.

(See a larger version of Figure 8.)

Merge scan join is also known as a sorted merge join. The abbreviation MSJOIN is used in Optim Query Tuner to denote this join algorithm. For more information about merge scan joins, refer to the appropriate information center link in the Resources section for your DB2 platform.

A merge scan join always involves one or more Equal join predicates. Figure 8 indicates that there are two Equal join predicates exploited for the sample query's merge scan join. For the merge scan join to perform properly, both inputs are expected to be in the order of join columns. More precisely, for the outer table of the join (CUST_ORDER_DETAIL), the qualifying records are supposed to be in the order of the columns CUST_QUANTITY and CUST_UNIT_PRICE. Likewise, the qualifying records of the inner table are supposed to be in the order of CUST_TOTAL_QUANTITY and CUST_TOTAL. Because the inner table is scanned with TBSCAN, which does not guarantee the expected order for MSJOIN, a SORT node is added to enforce the order.

As shown in the IXSCAN descriptor in Figure 8, the leading column of the index exploited by optimizer is PRODUCT_NUMBER, which does not provide the order required for merge scan join. Therefore, a SORT node is also added to the outer of the join to enforce the order.

As previously stated, ordered input is essential to a merge scan join, but it is not required for nested loop joins. So when compared to a nested loop join, the merge scan join may incur the cost for sorting the inputs. However, the benefit of the merge scan join is that the inner table does not have to be scanned repeatedly as it does for nested loop joins. Therefore, when deciding which join method to choose, the optimizer balances the sort cost and the performance benefit for the inner table scan to drive the decision. One notable thing regarding merge scan join is that the optimizer would aggressively avoid SORT by keeping track of the "order property" of the data. For example, the SORT on the inner table could be avoided if the inner table is scanned through an index with CUST_TOTAL and CUST_TOTAL_QUANTITY as the leading columns. Typically, the optimizer chooses merge scan join when the cardinality of the outer input is not obviously small and there is no efficient access method for the inner table.


A case study using query annotation

This section illustrates how the Query Tuner query annotation and access path graph can help you analyze access paths and SQL performance. Listing 9 contains the same three-way join SQL statement that was used in Listing 1 to introduce the concept of access paths and query execution.

Listing 9. Sample query to illustrate query annotation capabilities shown in Figure 9
select c.gender_code, c.marital_status_code, sum(od.unit_cost * cust_quantity)
from cust_customer c, cust_order_header oh, cust_order_detail od
where c.cust_code = oh.cust_code
  and oh.cust_order_number = od.cust_order_number
  and c.cust_prov_state_code = 'CA'
  and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)
group by c.gender_code, c.marital_status_code

Figure 9 illustrates the query annotation function in Optim Query Tuner that formats an SQL statement so that each table reference in the FROM clause and each predicate in the WHERE clause takes a new line. Predicates are reordered and regrouped based on the kind of predicate (local or join) and the table reference.

Figure 9. Query format and annotation example
Access path graph showing formatting and annotation functions. Text below figure provides a detailed description.

(See a larger version of Figure 9.)

Figure 9 demonstrates how the formatting and annotation functions makes the underlying statement easier to understand. The three table references (CUST_ORDER_DETAIL, CUST_CUSTOMER, and CUST_ORDER_HEADER) are displayed on three different lines. Each of the four predicates are also displayed on different lines, with the two local predicates displayed before the two join predicates.

In addition to the easy-to-read formatting, you also have access to annotation that provides critical statistical information to facilitate your analysis of SQL performance. To the right of each table reference is the table cardinality (in terms of both number of records and number of pages) and the estimated number of qualified rows.

The number associated with QUALIFIED_ROWS represents the estimated number of records that would qualify all the local predicates of the underlying table reference. For example, the CUST_ORDER_DETAIL table is estimated to return 8.000001 out of 560,273 records after applying the local predicates. Likewise, only 729.99976 out of 31,284 records would qualify the local predicates for table CUST_CUSTOMER. Because there are no local predicates for CUST_ORDER_HEADER, all 539,526 rows will survive. This information can lead to an understanding of how a lead table in a join sequence is chosen — the smaller the number of qualified rows, the more likely it is that the optimizer will select that table as the leading table in the join sequence.

As shown in Figure 1, table CUST_ORDER_DETAIL is selected by the optimizer as the leading table of the join sequence because it is supposed to produce only eight records after applying the local predicates. In the access path, the second table reference (CUST_ORDER_HEADER) and the third table reference (CUST_CUSTOMER) in the join sequence are accessed through a fully matched unique index. That is, each probe into the second table reference and the third table reference would efficiently locate the only record that matches the join condition. Therefore, this access path could actually be the best performing one as long as the estimate for QUALIFIED_ROWS of the first table reference (CUST_ORDER_DETAIL) is accurate.

To get an idea of how the optimizer calculates QUALIFIED_ROWS and how accurate it is, refer again to Figure 9. Notice that there is only one local predicate for CUST_ORDER_DETAIL, which is an IN list predicate on column PRODUCT_NUMBER, with six elements in the list (od.product_number in (154110, 129170, 129150, 129110, 129140, 130130)).

The query annotation for this predicate shows that the predicate filter factor (FF) is 0.000014, or 0.0014%. That is, the estimated percentage of rows that would qualify with this predicate is 0.0014%. The estimate for QUALIFIED_ROWS is obtained by multiplying the table cardinality (560,273 records) by the predicate filter factor (0.0014%).

To understand how the optimizer ends up with 0.0014% as the filter factor, first look at the six elements in the IN list predicate. The query annotation for the predicate shows that there are 273 distinct values on column PRODUCT_NUMBER. Assuming uniform distribution, the optimizer should normally conclude that 2.197% (6/273 = 0.02197) of the records would qualify this predicate. But instead, it is using 0.0014%. This suggests a much better predicate selectivity than it appears to be!

To understand this, look at MAXFREQ in the query annotation for this predicate. When the necessary statistics are collected and available, MAXFREQ shows the frequency of the most frequently appearing value on the column. In this example, it is 3.32%, which is much higher than the average percentage per value (1/273 = 0.366%). This suggests that some of the 273 values could be skewed low as well. The available statistics on this column can help reveal what is actually taking place. You can obtain the statistics from the table descriptor of the table node in the underlying access path graph.

To see the table descriptor, right-click the table node in the access path graph. Figure 10 shows the table descriptor for CUST_ORDER_DETAIL.

Figure 10. Fragment of the access path graph shown in Figure 1
Fragement of access path graph of a three-way join. Text below figure provides a detailed description.

(See a larger version of Figure 10.)

By drilling down to the column statistics in the table descriptor, you can discover that the six elements in the IN list predicate happen to be the bottom six values in the frequency statistics. Adding up these frequency values, you end up with exactly the same (estimated) filter factor, 0.0014%, for the IN list predicate. So this explains how the optimizer determined the filter factor to be 0.0014%.

Also, notice that the timestamp for the statistics collection displayed at the bottom of the table descriptor shows a very recent date relative to the time this query is explained. The optimizer's estimate could be very close to the actual value as long as the data distribution has not changed since the last RUNSTATS. Assuming this to be the case, the underlying access path is considered optimal with high confidence.


Conclusion

This article described the basic concepts of access paths and how to read an access path graph. The access path graph and query annotation capabilities of Optim Query Tuner were used to review real-world queries and explain the reasoning of how and why the DB2 optimizer chose particular access paths. This information should provide you with the building blocks you need to get started with tuning queries. Future articles in this series will provide more information on methodologies that you can use for query tuning.


Download

DescriptionNameSize
Sample project file for this articlesampleaccesspathproject.zip212KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=495573
ArticleTitle=Tuning SQL with Optim Query Tuner, Part 1: Understanding access paths
publish-date=06172010