Access Paths and Optimization

Team-Fly    

 
DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
Table of Contents
Chapter 17.  Application Performance and Optimization

Access Paths and Optimization

If you want to know how a query will be executed by DB2, you must analyze its access path , which is the method for retrieving data from a specific table. The explain facility will provide information about how DB2 accesses the data to resolve the SQL statements.

You should understand at a high level how SQL statements are processed by the DB2 database engine: Each SQL statement is analyzed by DB2, and then it is determined how to process the statement during a static bind or when executed dynamically. The method used to retrieve data from tables is called the access plan.

The component within DB2 that determines the access plan to be used is known as the optimizer. During the static preparation of an SQL statement, the SQL compiler is called on to generate an access plan. The access plan contains the data access strategy, including index usage, sort methods, locking semantics, and join methods .

The executable form of the SQL statement is stored in the system tables when a BIND command is executed. This is called a DBRM (database request module), which can then be bound into a package. For more on packages refer to Chapter 6, "Binding an Application Program."

Sometimes, the complete statement is not known at application development time. In this case, the compiler is invoked during program execution to generate an access plan for the query that can be used by the database manager to access the data. Such a SQL statement is called a dynamic SQL statement. The access plans for a dynamic SQL statement are not stored in the system catalogs. They can be cached in memory in the dynamic SQL cache and will not be re-prepared if the access plans for the dynamic SQL statements already exist in the dynamic SQL cache.

Explain

Explain is a monitoring tool that produces information about the following:

  • A plan, package, or SQL statement when it is bound. The output appears in a table you create, called PLAN_TABLE, which is also called a plan tabl e. For experienced users, you can use PLAN_TABLE to give optimization hints to DB2. We will look at access path hints later in this chapter.

  • An estimated cost of executing an SQL SELECT, INSERT, UPDATE, or DELETE statement. The output appears in a table you create, called DSN_STATEMNT_TABLE, which is also called the statement tabl e.

  • User -defined functions (UDFs) referred to in the statement, including the specific name and schema. The output appears in a table you create, called DSN_FUNCTION_TABLE, which is also called a function tabl e.

We will look at the data contained in these three tables and how to use this data to improve query performance.

Gathering Explain Data

There are two different ways to populate the PLAN_TABLE:

  • Executing the SQL statement EXPLAIN : You can populate PLAN_TABLE by executing the SQL statement EXPLAIN. In the statement, specify a single explainable SQL statement in the FOR clause. You can execute EXPLAIN either statically from an application program or dynamically, using QMF or SPUFI.

  • Binding with the option EXPLAIN(YES) : You can populate PLAN_TABLE by executing the SQL statement in a package bound EXPLAIN(YES).

  • Before you can use EXPLAIN, you must create the PLAN_TABLE to hold the results of EXPLAIN. A copy of the statements needed to create the table are in the DB2 sample library under the member name DSNTESC. (Unless you need the information they provide, it is not necessary to create a function table or statement table to use EXPLAIN.)

NOTE

graphics/note_icon.jpg

DB2 does not automatically delete rows from the PLAN_TABLE. To clear the table of obsolete rows, use DELETE, just as you would for deleting rows from any table.


NOTE

graphics/note_icon.jpg

With every release of DB2, the PLAN_TABLE is subject to change. Be sure to create a new, or alter, the existing table to pick up the new columns .


Examining Explain Data

Explains will populate this table, and from there you must interpret the data and act accordingly . Table 17-1 shows the columns in the PLAN_TABLE.

Table 17-1. PLAN_TABLE

Column Name

Description

QUERYNO

A number intended to identify the statement being explained. For a row produced by an EXPLAIN statement, specify the number in the QUERYNO clause. For a row produced by non-EXPLAIN statements, specify the number using the QUERYNO clause, which is an optional part of the SELECT, INSERT, UPDATE, and DELETE statement syntax. Otherwise , DB2 assigns a number based on the line number of the SQL statement in the source program.

FETCH statements do not each have an individual QUERYNO assigned to them. Instead, DB2 uses the QUERYNO of the DECLARE CURSOR statement for all corresponding FETCH statements for that cursor.

When the values of QUERYNO are based on the statement number in the source program, values greater than 32,767 are reported as 0. Hence, in a very long program, the value is not guaranteed to be unique. If QUERYNO is not unique, the value of TIMESTAMP is unique.

QBLOCKNO

The position of the query in the statement being explained (1 for the outermost query, 2 for the next query, and so forth). For better performance, DB2 might merge a query block into another query block. When that happens, the position number of the merged query block will not be in QBLOCKNO.

APPLNAME

The name of the application plan for the row. Applies only to embedded EXPLAIN statements executed from a plan or to statements explained when binding a plan. Blank if not applicable .

PROGNAME

The name of the program or package containing the statement being explained. For statements explained dynamically, such as QMF or SPUFI, then the associated plan/package is listed. Blank if not applicable.

PLANNO

The number of the step in which the query indicated in QBLOCKNO was processed. This column indicates the order in which the steps were executed.

METHOD

A number (0, 1, 2, 3, or 4) that indicates the join method used for the step:

0 = First table accessed, continuation of previous table accessed, or not used.

1 = Nested loop join. For each row of the present composite table, matching rows of a new table are found and joined.

2 = Merge scan join. The present composite table and the new tables are scanned in the order of the join columns, and matching rows are joined.

3 = Sorts needed by ORDER BY, GROUP BY, SELECT DISTINCT, UNION, a quantified predicate, or an IN predicate. This step does not access a new table.

4 = Hybrid join. The current composite table is scanned in the order of the join-column rows of the new table. The new table is accessed using list prefetch.

CREATOR

The creator of the new table accessed in this step; blank if METHOD is 3.

TNAME

The name of a table, created temporary table, declared temporary table, materialized view, table expression, or an intermediate result table for an outer join that is accessed in this step; blank if METHOD is 3.

Materialized results may show the temporary table name of the work file in the form DSNWFQB(qblockno) or DSN_DIM_TBLX(qblockno) for materialized snowflakes in a star schema query. Merged views show the base table names and correlation names . A materialized view is another query block with its own materialized views, tables, and so forth.

TABNO

Values are for IBM use only.

ACCESSTYPE

The method of accessing the new table:

I = By an index (identified in ACCESSCREATOR and ACCESSNAME)

I1 = By a one-fetch index scan

N = By an index scan when the matching predicate contains the IN keyword

R = By a tablespace scan

M = By a multiple index scan (followed by MX, MI, or MU)

MX = By an index scan on the index named in ACCESSNAME

MI = By an intersection of multiple indexes

MU = By a union of multiple indexes

Blank = Not applicable to the current row

MATCHCOLS

For ACCESSTYPE I, I1, N, or MX, the number of index keys used in an index scan; otherwise, 0.

ACCESSCREATOR

For ACCESSTYPE I, I1, N, or MX, the creator of the index; otherwise, blank.

ACCESSNAME

For ACCESSTYPE I, I1, N, or MX, the name of the index; otherwise, blank.

INDEXONLY

Whether access to an index alone is enough to carry out the step, or whether data too must be accessed. Y = Yes; N = No. For UPDATE/DELETE, this indicates whether an index alone is enough to determine the row(s) to be updated or deleted.

SORTN_UNIQ

Whether the new table is sorted to remove duplicate rows. Y = Yes; N = No.

SORTN_JOIN

Whether the new table is sorted for join method 2 or 4. Y = Yes; N = No.

SORTN_ORDERBY

Whether the new table is sorted for ORDER BY. Y = Yes; N = No.

SORTN_GROUPBY

Whether the new table is sorted for GROUP BY. Y = Yes; N = No.

SORTC_UNIQ

Whether the composite table is sorted to remove duplicate rows. Y = Yes; N = No.

SORTC_JOIN

Whether the composite table is sorted for join method 1, 2, or 4. Y = Yes; N = No.

SORTC_ORDERBY

Whether the composite table is sorted for an ORDER BY clause or a quantified predicate. Y = Yes; N = No.

SORTC_GROUPBY

Whether the composite table is sorted for a GROUP BY clause. Y = Yes; N = No.

TSLOCKMODE

An indication of the mode of lock to be acquired on the new table or its tablespace or tablespace partitions. If the isolation can be determined at bind time, the values are

IS = Intent share lock

IX = Intent exclusive lock

S = Share lock

U = Update lock

X = Exclusive lock

SIX = Share with intent exclusive lock

N = UR isolation; no lock

If the isolation cannot be determined at bind time, then the lock mode determined by the isolation at runtime is shown by the following values.

NS = For UR isolation, no lock; for CS, RS, or RR, an S lock.

NIS = For UR isolation, no lock; for CS, RS, or RR, an IS lock.

NSS = For UR isolation, no lock; for CS or RS, an IS lock; for RR, an S lock.

SS = For UR, CS, or RS isolation, an IS lock; for RR, an S lock.

The data in this column is right justified. For example, IX appears as a blank followed by I followed by X. If the column contains a blank, then no lock is acquired.

TIMESTAMP

Usually, the time at which the row is processed, to the last .01 second. If necessary, DB2 adds .01 second to the value to ensure that rows for two successive queries have different values.

REMARKS

A field into which you can insert any character string of 254 or fewer characters .

PREFETCH

Whether data pages are to be read in advance by prefetch. S = pure sequential prefetch; L = prefetch through a page list; blank = unknown at bind time or no prefetch.

COLUMN_FN_EVAL

When an SQL column function is evaluated. R = while the data is being read from the table or index; S = while performing a sort to satisfy a GROUP BY clause; blank = after data retrieval, after any sorts, or not applicable.

MIXOPSEQ

The sequence number of a step in a multiple index operation.

1, 2, n = For the steps of the multiple index procedure (ACCESSTYPE is MX, MI, or MU.)

0 = For any other rows (ACCESSTYPE is I, I1, M, N, R, or blank.)

VERSION

The version identifier for the package. Applies only to an embedded EXPLAIN statement executed from a package or to a statement that is explained when binding a package. Blank if not applicable.

COLLID

The collection ID for the package. Applies only to an embedded EXPLAIN statement executed from a package or to a statement that is explained when binding a package. Blank if not applicable.

ACCESS_DEGREE

The number of parallel tasks or operations activated by a query. This value is determined at bind time; the actual number of parallel operations used at execution time could be different. This column contains 0 if there is a host variable.

ACCESS_PGROUP_ID

The identifier of the parallel group for accessing the new table. A parallel group is a set of consecutive operations, executed in parallel, that have the same number of parallel tasks. This value is determined at bind time; it could change at execution time.

JOIN_DEGREE

The number of parallel operations or tasks used in joining the composite table with the new table. This value is determined at bind time and can be 0 if there is a host variable. The actual number of parallel operations or tasks used at execution time could be different.

JOIN_PGROUP_ID

The identifier of the parallel group for joining the composite table with the new table. This value is determined at bind time; it could change at execution time.

SORTC_PGROUP_ID

The parallel group identifier for the parallel sort of the composite table.

SORTN_PGROUP_ID

The parallel group identifier for the parallel sort of the new table.

PARALLELISM_MODE

The kind of parallelism, if any, that is used at bind time:

I = Query I/O parallelism

C = Query CP parallelism

X = Sysplex query parallelism

MERGE_JOIN_COLS

The number of columns that are joined during a merge scan join (Method = 2).

CORRELATION_NAME

The correlation name of a table or view that is specified in the statement. If there is no correlation name, then the column is blank.

PAGE_RANGE

Whether the table qualifies for page range screening, so that plans scan only the partitions that are needed. Y = Yes; blank = No.

JOIN_TYPE

The type of an outer join.

F = FULL OUTER JOIN

L = LEFT OUTER JOIN

S = STAR JOIN

blank = INNER JOIN or no join

RIGHT OUTER JOIN converts to a LEFT OUTER JOIN when you use it, so that JOIN_TYPE contains L.

GROUP_MEMBER

The member name of the DB2 that executed EXPLAIN. The column is blank if the DB2 subsystem was not in a data-sharing environment when EXPLAIN was executed.

IBM_SERVICE_DATA

Values are for IBM use only.

WHEN_OPTIMIZE

When the access path was determined:

blank = At bind time, using a default filter factor for any host variables , parameter markers, or special registers.

B = At bind time, using a default filter factor for any host variables, parameter markers, or special registers; however, the statement is reoptimized at runtime using input variable values for input host variables, parameter markers, or special registers. The bind option REOPT(VARS) must be specified for reoptimization to occur.

R = At runtime, using input variables for any host variables, parameter markers, or special registers. The bind option REOPT(VARS) must be specified for this to occur.

QBLOCK_TYPE

For each query block, the type of SQL operation performed. For the outermost query, it identifies the statement type. Possible values:

SELECT = SELECT

INSERT = INSERT

UPDATE = UPDATE

DELETE = DELETE

SELUPD = SELECT with FOR UPDATE OF

DELCUR = DELETE WHERE CURRENT OF CURSOR

UPDCUR = UPDATE WHERE CURRENT OF CURSOR

CORSUB = Correlated subquery

NCOSUB = Noncorrelated subquery

TABLEX = Table Expression

UNION = UNION

UNIONA = UNION ALL

BIND_TIME

The time at which the plan or package for this statement or query block was bound. For static SQL statements, this is a full-precision timestamp value. For dynamic SQL statements, this is the value contained in the TIMESTAMP column of PLAN_TABLE appended by four zeroes.

OPTHINT

A string that you use to identify this row as an optimization hint for DB2. DB2 uses this row as input when choosing an access path.

HINT_USED

If DB2 used one of your optimization hints, it puts the identifier for that hint (the value in OPTHINT) in this column.

PRIMARY_ACCESSTYPE

Indicates whether direct row access will be attempted first:

D = DB2 will try to use direct row access. If DB2 cannot use direct row access at runtime, it uses the access path described in the ACCESSTYPE column of PLAN_TABLE.

blank = DB2 will not try to use direct row access

PARENT_QBLOCK

Number that indicates the QBLOCKNO of the parent query.

TABLE_TYPE

The type of new table:

F = Table Function

Q = Temporary Intermediate Result table (not materialized). Exception is a materialized snowflake within a star join query.

T = Table

W = Work file

Access Path Evaluation

We now take a look at how to examine some of the most useful data in the PLAN_TABLE and determine what the DB2 optimizer is using to access the data.

Index Access

The following describes the different types of index access and how they represented in the PLAN_TABLE.

Index Access (ACCESSTYPE is I, I1, N or MX). If the column ACCESSTYPE in the plan table has a value I, I1, N, or MX, DB2 uses an index to access the table named in column TNAME. The columns ACCESSCREATOR and ACCESSNAME identify the index.

Multiple Index Access (ACCESSTYPE = M). This value indicates that DB2 uses a set of indexes to access a single table. A set of rows in the plan table contain information about the multiple index access. The rows are numbered in column MIXOPSEQ in the order of execution of steps in the multiple index access. (If you retrieve the rows in order by MIXOPSEQ, the result is similar to postfix arithmetic notation.)

Number of Matching Index (MATCHCOLS = n). If MATCHCOLS is 0, the access method is called a nonmatching index scan. All the index keys and their row identifiers (RIDs) are read. If MATCHCOLS is greater than 0, the access method is called a matching index scan: The query uses predicates that match the index columns.

In general, the matching predicates on the leading index columns are equal or IN predicates. The predicate that matches the final index column can be an equal, IN, or range predicate (<, <=, >, >=, LIKE, or BETWEEN). The following example illustrates matching predicates:

 SELECT *  FROM EMP WHERE JOBCODE = '5' AND LOCATION ='CA' AND SALARY > 60000 AND AGE > 21; INDEX XEMP5 on (JOBCODE,LOCATION,SALARY,AGE); 

The index XEMP5 is the chosen access path for this query, with MATCHCOLS = 3. Two equal predicates are on the first two columns, and a range predicate is on the third column. Though the index has four columns in the index, only three of them can be considered matching columns.

NOTE

graphics/note_icon.jpg

At most, one IN predicate can be a matching predicate; the exception, however, is a noncorrelated IN subquery. IN-list predicates cannot be matching predicates for MX access or list prefetch.


Index Only Access (INDEXONLY = Y). In this case, the method is called index-only access. For a SELECT operation, all the columns needed for the query can be found in the index, and DB2 does not access the table. For an UPDATE or DELETE operation, only the index is required to read the selected row.

Index-only access is not possible when returning varying-length data in the result set or when a VARCHAR column has a LIKE predicate, unless the VARCHAR FROM INDEX field of installation panel DSNTIP4 is set to YES, and plan or packages have been rebound to pick up the change.

If access is by more than one index, INDEXONLY is Y for a step with access type MX, because the data pages are not actually accessed until all the steps for intersection (MI) or union (MU) take place.

When an SQL application uses index-only access for a ROWID column, the application claims the tablespace or tablespace partition. As a result, contention may occur between the SQL application and a utility that drains the tablespace or partition. Index-only access to a table for a ROWID column is not possible if the associated tablespace or partition is in an incompatible restrictive state. For example, an SQL application can make a read claim on the tablespace only if the restrictive state allows readers.

Table Access

The following information describes the different types of table accesses and how they are represented in the PLAN_TABLE

Tablespace Scans (ACCESSTYPE=R, PREFETCH = S). Tablespace scan (R = Relational Scan) is most often used for one of the following reasons:

  • Access is through a temporary table. (Index access is not possible for temporary tables.)

  • A matching index scan is not possible because an index is not available or no predicates match the index columns.

  • A high percentage of the rows in the table are returned. In this case an index is not really useful, because most rows need to be read anyway.

  • The indexes that have matching predicates have low cluster ratios and are therefore efficient only for small amounts of data.

Assume that table DB2USER1.CANDIDATE has no index on the column identifier (CID). The following is an example that uses a tablespace scan:

 SELECT *FROM DB2USER1.CANDIDATE WHERE CID = :CIDHV; 

In this case, at least every row in the CANDIDATE table must be examined to determine whether the value of CID matches the given value.

Prefetching (PREFETCH = L, S, or blank). Prefetching is a method of determining in advance that a set of data pages is about to be used and then reading the entire set into a buffer with a single asynchronous I/O operation. If the value of PREFETCH is

  • S, the method is called sequential prefetch. The data pages that are read in advance are sequential. A tablespace scan always uses sequential prefetch. An index scan might not use it.

  • L, the method is called list prefetch. One or more indexes are used to select the RIDs for a list of data pages to be read in advance; the pages need not be sequential. Usually, the RIDs are sorted. The exception is the case of a hybrid join (METHOD = 4) when the value of column SORTN_JOIN is N.

  • Blank, prefetching is not chosen as an access method. However, depending on the pattern of the page access, data can be prefetched at execution time through a process called sequential detection (also know as dynamic prefetch).

NOTE

graphics/note_icon.jpg

DB2 always attempts to use sequential prefetch for a tablespace scan. For a segmented tablespace, if DB2 determines that less than four pages will be read at runtime, then sequential prefetch is disabled. The OPTIMIZE FOR 1 ROW also potentially disables sequential and list prefetch at bind time.


SORT (SORTN_ and SORTC_)

The plan tables show the reasons a sort was invoked. Those reasons could include a sort of data rows or a sort of RIDs in a RID list. SORTN_JOIN indicates that the new table of a join is sorted before the join. (For hybrid join, this is a sort of the RID list.) When SORTN_JOIN and SORTC_JOIN are both Y, two sorts are performed for the join. The sorts for joins are indicated on the same row as the new table access.

NOTE

graphics/note_icon.jpg

A sort of the composite table for a join (SORTC_JOIN) is beneficial in avoiding death by random I/O. Providing cluster ratio for an index and keycard statistics for multicolumn cardinalities gives DB2 the information to determine whether a query will suffer from excessive synchronous I/O. In this case sorting the composite for a nested loop or sorting both (if required) for a sort merge or hybrid join can provide sequential access to the data.


SORTC_UNIQ indicates a sort to remove duplicates, as might be needed by a SELECT statement with DISTINCT or UNION. SORTC_ORDERBY usually indicates a sort for an ORDER BY clause. But SORTC_UNIQ and SORTC_ORDERBY also indicate when the results of a noncorrelated subquery are sorted, both to remove duplicates and to order the results. A SORTC_GROUPBY would indicate a sort for processing a GROUP BY clause.

NOTE

graphics/note_icon.jpg

If more than one SORTC indicator is set to Y on the same explain output line, then DB2 is actually performing one sort to accomplish two tasks, such as sorting for uniqueness and ordering. This does not apply to SORTN_JOIN and SORTC_JOIN, as one sort is for the composite and one is for the new table.


To perform list prefetch, DB2 sorts RIDs into ascending page number order. A RID sort is usually not indicated in the PLAN_TABLE, but a RID sort normally is performed whenever list prefetch is used. The only exception to this rule is when a hybrid join is performed and a single, highly clustered index is used on the inner table. In this case SORTN_JOIN is N, indicating that the RID list for the inner table was not sorted.

NOTE

graphics/note_icon.jpg

SORTN_GROUPBY, SORTN_ORDERBY, and SORTN_UNIQ are currently not used by DB2.


Nested loop join (METHOD = 1). For a nested loop join, DB2 scans the composite (outer) table. For each row in that table that qualifies (by satisfying the predicates on that table), DB2 searches for matching rows of the new (inner) table. It concatenates any it finds with the current row of the composite table. If no rows match the current row, then

  • For an inner join, DB2 discards the current row.

  • For an outer join, DB2 concatenates a row of null values.

Stage 1 and stage 2 predicates can eliminate unqualified rows before the physical joining of rows occurs. Nested loop join is often used if

  • The outer table is small.

  • Predicates with small filter factors reduce the number of qualifying rows in the outer table.

  • An efficient, highly clustered index exists on the join columns of the inner table.

  • The number of data pages accessed in the inner table is small.

NOTE

graphics/note_icon.jpg

The nested loop join repetitively scans the inner table. That is, DB2 scans the outer table once and scans the inner table as many times as the number of qualifying rows in the outer table. Hence, the nested loop join is usually the most efficient join method when the values of the join column passed to the inner table are in sequence and the index on the join column of the inner table is clustered, or the number of rows retrieved in the inner table through the index is small. If the tables are not clustered in the same sequence, then DB2 can sort the composite to match the sequence of the inner table. Accesses to the inner table can then utilize sequential or dynamic prefetch.


Merge scan join (METHOD = 2). Merge scan join is also known as merge join or sort merge join. For this method, there must be one or more predicates of the form TABLE1.COL1 = TABLE2.COL2, where the two columns have the same data type, length, and null attributes. If the null attributes do not match, then the maximum number of merge join columns is 1. The exception is a full outer join, which permits mismatching null attributes.

NOTE

graphics/note_icon.jpg

Join columns cannot be matching columns for a merge scan join. Instead, these columns are listed as MERGE_JOIN_COLS in the plan table. Local predicates can be matching predicates and will be applied before the join.


DB2 scans both tables in the order of the join columns. If no efficient indexes on the join columns provide the order, DB2 might sort the outer table, the inner table, or both. The inner table is put into a work file; the outer table is put into a work file only if it must be sorted. When a row of the outer table matches a row of the inner table, DB2 returns the combined rows.

A merge scan join is often used if

  • The qualifying rows of the inner and outer table are large, and the join predicate does not provide much filtering; that is, in a many-to-many join.

  • The tables are large and have no indexes with matching columns.

  • Few columns are selected on inner tables. This is the case when a DB2 sort is used. The fewer the columns to be sorted, the more efficient the sort is.

NOTE

graphics/note_icon.jpg

A merge scan join is always chosen for a full outer join.


Hybrid join (METHOD = 4). The method applies only to an inner join and requires an index on the join column of the inner table. The method requires obtaining RIDs in the order needed to use list prefetch. In the successive steps, DB2

  1. Scans the outer table (OUTER).

  2. Joins the outer tables with RIDs from the index on the inner table. The result is the phase 1 intermediate table. The index of the inner table is scanned for every row of the outer table.

  3. Sorts the data in the outer table and the RIDs, creating a sorted RID list and the phase 2 intermediate table. The sort is indicated by a value of Y in column SORTN_JOIN of the plan table. If the index on the inner table is highly clustered, DB2 can skip this sort; the value in SORTN_JOIN is then N.

  4. Retrieves the data from the inner table, using list prefetch.

  5. Concatenates the data from the inner table and the phase 2 intermediate table to create the final composite table.

Hybrid join is often used if

  • A nonclustered index or indexes are used on the join columns of the inner table.

  • The outer table has duplicate qualifying rows

NOTE

graphics/note_icon.jpg

DB2 may also choose to sort the composite for a hybrid join.


Star Join (METHOD = 0, 1; JOIN TYPE = S). Star join is the access path utilized in processing a star schema. A star schema is a logical database design that is included in many data warehouse and decision-support applications. A star schema is composed of a fact table and a number of dimension tables that are connected to it. A dimension table contains several values that are given an ID, which is used in the fact table instead of all the values. You can think of the fact table, which is much larger than the dimension tables, as being in the center surrounded by dimension tables; the result resembles a star formation.

To access the data in a star schema, you write SELECT statements that include join operations between the fact table and the dimension tables; no join operations exist between dimension tables. A query must satisfy a number of conditions before it qualifies for the Star Join access path. The first requirement is detection of the fact table. Given that the access path objective is efficient access to the fact table, it is important that the fact table is correctly identified.

  1. The first fact table detection algorithm is known as the unique index check. Beginning outside-in, the optimizer will evaluate each set of join predicates. For each set of join predicates between two tables, the table with a unique index on the join predicates is considered to be the parent in a parentchild relationship. As DB2 continues outside-in, the table without any further children (and which therefore only has parents) is considered to be the fact table.

  2. The second fact table detection algorithm is based upon the values of the STARJOIN DSNZPARM, which are:

    - ENABLE: Star join is enabled and the fact table is 25 times larger than the largest dimension table.

    - DISABLE: Star join is disabled (default).

    - 1: The Fact table will be the largest in the Star Join query.

    - 232,768: This is the fact table and the largest dimension table ratio. The fact table must be n times larger than the largest dimension table.

  3. The third fact table detection algorithm is the topology check, whereby the fact table is considered to be the table with the most number of join predicates in the query.

Once a fact table is identified using any of the three fact table detection algorithms, then the following conditions must be met for DB2 to use the star join technique:

  • Number of tables in the query block must be at least 10 ( altered via DSNZPARM SJTABLES).

  • All join predicates are between the fact table and the dimension tables, or within tables of the same dimension (snowflake).

  • All join predicates between the fact and dimension tables must be equijoin (equal join) predicates.

  • All join predicates between the fact and dimension tables must be Boolean term predicates (fact to dimension join predicates cannot be ORed).

  • A local predicate on a dimension table cannot be ORed with a local predicate of another dimension table.

  • A single fact table column cannot be joined to columns of different dimension tables in join predicates. For example, fact table column F1 cannot be joined to column D1 of dimension table T1 and also joined to column D2 of dimension table T2.

  • No correlated subqueries exists across dimensions.

  • The data type and length of both sides of a join predicate are the same between the fact and dimension tables.

  • Dimensions cannot be a table function.

  • After DB2 simplifies join operations, no outer join operations can exist between the fact and dimension tables.

A successful match on all of the above star schema detection rules will immediately qualify the query for star join optimization. A failure on any of the above rules for a fact table will result in the next fact table detection algorithm to be evaluated. A failure of these rules for all of the fact table detection rules will result in the query being optimized using standard dynamic programming (exhaustive search) techniques or algorithms.

NOTE

graphics/note_icon.jpg

When star join is performed, it is identified by an S in the JOIN_TYPE column of the PLAN_TABLE, but only for dimension tables that are accessed before the fact table.


For a star schema, even though the intersection of all dimensions with the fact table can produce a small result set, the predicates applied to one single dimension table are typically insufficient to reduce the enormous number of fact table rows.

If a join based upon related tables (dimension to fact table) does not provide adequate performance, then an alternative is to join unrelated tables. Joining of unrelated tables results in a Cartesian product, whereby every row of the first table is joined with every row of the second.

Performing a Cartesian join of all dimension tables before accessing the fact table may not be efficient. DB2 must decide how many dimension tables should be accessed first to provide the greatest level of filtering of fact table rows using available indexes. This can be a delicate balance, as further Cartesian products will produce a massive increase in the size of the intermediate result sets. Alternatively, minimal prejoining of unrelated dimension tables may not provide adequate filtering for the join to the fact table. For an efficient Cartesian process, DB2 employs a logical rather than physical Cartesian of the dimension tables. Each dimension or snowflake (further normalized dimension) covered by the chosen fact table index is accessed independently before the fact table. Each qualifying dimension (and snowflake) has all local predicates applied, with the result sorted into join column order, and finally materialized into its own separate work file.

If many of the dimensions involve snowflakes, then this preprocessing and materialization significantly reduces the number of overall tables joined, since the snowflake is resolved into a single dimension.

Rather than requiring the physical work file storage involved in a physical Cartesian, DB2 simulates a Cartesian by repositioning itself within each work file to potentially join all possible combinations to the central fact table. The sequence of this simulated Cartesian join respects the column order of the selected fact table index.

The sparseness of data within the fact table implies a significant number of values generated by the Cartesian process are not to be found by a join to the fact table. To minimize the CPU overhead of joining unnecessarily derived rows to the fact table, DB2 introduces an index key feedback loop to return the next highest key value whenever a not-found condition is encountered .

A hit on the fact table index will return the matching fact table row. A miss will return the next valid fact table index key so that the data manager can reposition itself within the dimension work files, thus skipping composite rows with no possibility of obtaining a fact table match.

NOTE

graphics/note_icon.jpg

To further improve the performance of the join to the fact table, the entire join has been pushed down to data manager (stage 1); but only for star join access from the composite (dimensions) to the fact table. This ensures a reduced path length, since rows no longer need to be returned to RDS (stage 2) for the join to occur. The join method used by this process is a nested loop join.


Parallelism Usage (PARALLELISM_MODE = I, C, or X). Parallel processing applies only to read-only queries. The values are

I = Parallel I/O operations

C = Parallel CP operations

X = Sysplex query parallelism

Non-null values in columns ACCESS_DEGREE and JOIN_DEGREE indicate to what degree DB2 plans to use parallel operations. However, this can change at runtime. Parallelism is discussed later in this chapter.

DSN_STATEMNT_TABLE

This table is populated (if it exists) when EXPLAIN is run. Some of the columns are similar to those in the PLAN_TABLE, and some are new and only related to the statement cost. The columns that are unique to the DSN_STATEMENT_TABLE are

  • STMT_TYPE Type of SQL statement

  • COST_CATEGORY How much information was available

  • PROCMS Estimated processor cost in milliseconds

  • PROCSU Estimated processor cost in service units

  • REASON Reasons that COST_CATEGORY may be inaccurate

The COST_CATEGORY determination is affected by such thing as number of tables, number of rows in the tables, column cardinality, cluster ratio, first key cardinality, full key cardinality, number leaf pages, number of index levels, host variables, special registers, triggers, UDFs, RI, LOBs, and expressions. Table 17-2 describes the contents of the DSN_STATEMNT table, and Table 17-3 describes the contents of the DSN_FUNCTION_TABLE.

Table 17-2. DSN_STATEMNT Table

Column name

Description

QUERYNO

A number intended to identify the statement being explained. If QUERYNO is not unique, the value of EXPLAIN_TIME is unique.

APPLNAME

The name of the application plan for the row, or blank.

PROGNAME

The name of the program or package containing the statement being explained, or blank.

COLLID

The collection ID for the package, or blank.

GROUP_MEMBER

The member name of the DB2 that executed EXPLAIN, or blank.

EXPLAIN_TIME

The time at which the statement is processed. This time is the same as the BIND_TIME column in PLAN_TABLE.

STMT_TYPE

The type of statement being explained. Possible values:

SELECT = SELECT

INSERT = INSERT

UPDATE = UPDATE

DELETE = DELETE

SELUPD = SELECT with FOR UPDATE OF

DELCUR = DELETE WHERE CURRENT OF CURSOR

UPDCUR = UPDATE WHERE CURRENT OF CURSOR

COST_CATEGORY

Indicates if DB2 was forced to use default values when making its estimates. Possible values:

A = Indicates that DB2 had enough information to make a cost estimate without using default values.

B = Indicates that some condition exists for which DB2 was forced to use default values. See the values in REASON to determine why DB2 was unable to put this estimate in cost category A.

PROCMS

The estimated processor cost in milliseconds for the SQL statement. The estimate is rounded up to the next integer value. The maximum value for this cost is 2,147,483,647 milliseconds, which is equivalent to approximately 24.8 days. If the estimated value exceeds this maximum, the maximum value is reported.

PROCSU

The estimated processor cost in service units for the SQL statement. The estimate is rounded up to the next integer value. The maximum value for this cost is 2,147,483,647 service units. If the estimated value exceeds this maximum, the maximum value is reported.

REASON

A string that indicates the reasons for putting an estimate into cost category B.

HOST VARIABLES: The statement uses host variables, parameter markers, or special registers.

TABLE CARDINALITY: The cardinality statistics are missing for one or more of the tables used in the statement.

UDF: The statement uses user-defined functions.

TRIGGERS: Triggers are defined on the target table of an INSERT, UPDATE, or DELETE statement.

REFERENTIAL CONSTRAINTS: Referential constraints of the type CASCADE or SET NULL exist on the target table of a DELETE statement.

Table 17-3. DSN_FUNCTION_TABLE

Column name

Description

QUERYNO

A number intended to identify the statement being explained. If QUERYNO is not unique, the value of EXPLAIN_TIME is unique.

APPLNAME

The name of the application plan for the row, or blank.

PROGNAME

The name of the program or package containing the statement being explained, or blank.

COLLID

The collection ID for the package, or blank.

GROUP_MEMBER

The member name of the DB2 that executed EXPLAIN, or blank.

EXPLAIN_TIME

The time at which the statement is processed. This time is the same as the BIND_TIME column in PLAN_TABLE.

SCHEMA_NAME

The schema name of the function invoked in the explained statement.

FUNCTION_NAME

The name of the function invoked in the explained statement.

SPEC_FUNC_ID

The specific name of the function invoked in the explained statement.

FUNCTION_TYPE

The type of function invoked in the explained statement. Possible values:

S = Scalar function

T = Table function

VIEW_CREATOR

If the function specified in the FUNCTION_NAME column is referenced in a view definition, the creator of the view. Otherwise, blank.

VIEW_NAME

If the function specified in the FUNCTION_NAME column is referenced in a view definition, the name of the view. Otherwise, blank.

PATH

The value of the SQL path that was used to resolve the schema name of the function.

FUNCTION_TEXT

The text of the function reference (the function name and parameters). If the function reference is over 100 bytes, this column contains the first 100 bytes. For functions specified in fixed notation, FUNCTION_TEXT contains only the function name. For example, for a function named /, which overloads the SQL divide operator, if the function reference is A/B, FUNCTION_TEXT contains only /.

Access Path Hints (Optimization Hints)

DB2 provides a facility for telling DB2 how to process a query. This is done by giving DB2 hint s. The process of giving hints to DB2 is relatively simple, but determining what those hints should be is not. Giving optimization hints to DB2 is useful in the following situations:

  • To ensure consistency of response times across rebinds and across release migrations.

  • To temporarily bypass the access path chosen by DB2.

The facility for implementing access path hints requires many tasks to be performed as well as making sure the subsystem has been enabled for hints. Some of the items that will be affected by using hints are

  • Modifying the SQL to use the hint.

  • Updating a PLAN_TABLE with the correct access path information or using an existing one.

  • Changing the bind options for those affected.

Even after a hint has been established, it is not always possible for the optimizer to use it. As part of the normal bind process, the optimizer will have to evaluate the hint and determine if it is valid. Processes will have to be in place to make sure that rebinds do not change the process. For this reason, hints should not be used unless all else fails in establishing an acceptable access path.

Using Statistics to Model Production Environment

There are statistics in the catalog that the optimizer uses in calculating the costs of SQL to determine the best access path to pick. In building a test environment, it is often helpful to copy statistics from a production environment or to update the statistics to an approximation of what production might be like. Table 17-4 lists the catalog statistics that can be updated and are also used by the optimizer in access path selection.

Table 17-4. Catalog Statistics

Table

Statistics

SYSIBM.SYSCOLDIST

CARDF

COLGROUPCOLNO

COLVALUE

FREQUENCYF

NUMCOLUMNS

TYPE

SYSIBM.SYSCOLUMNS

COLCARDF

HIGH2KEY

LOW2KEY

SYSIBM.SYSINDEXES

CLUSTERRATIOF

FIRSTKEYCARDF

FULLKEYCARDF

NLEAF

NLEVELS

SYSIBM.SYSROUTINES

CARDINALITY

INITIAL_INSTS

INITIAL_IOS

INSTS_PER_INVOC

IOS_PER_INVOC

SYSIBM.SYSTABLES

CARDF

NPAGES

NPAGESF

PCTROWCOMP

SYSIBM.SYSTABLESPACE

NACTIVE

SYSIBM.SYSTABSTATS

CARDF

NPAGES

NOTE

graphics/note_icon.jpg

There are many other statistics used for access path selection that cannot be updated by the user.


Guidelines for Using Explain Output

There are a number of ways in which analyzing the explain data can help you to tune your queries and environment. For example, are indexes being used? Creating appropriate indexes can have a significant benefit on performance. Using the explain output, you can determine if the indexes you have created to help a specific set of queries are being used. In the explain output, you should look for index usage in the following areas:

  • Join Predicates

  • Local Predicates

  • GROUP BY clauses

  • ORDER BY clauses

  • The select list

You can also use explain to evaluate whether a different index can be used instead of an existing index or no index at all. After creating a new index, collect statistics for that index using the RUNSTATS utility and rebind the query.

NOTE

graphics/note_icon.jpg

Collecting KEYCARD statistics can enhance the optimizer's access path selection for queries involving joins.


Over time, you may notice, through the explain data, that instead of an index scan, a table scan is now being used. This can result from a change in the clustering of the table data. If the index that was previously being used now has a low cluster ratio, you want to

  • Reorganize your table to cluster the data according to that index.

  • Use the RUNSTATS command to update the catalog statistics.

  • Rebind the query.

  • Reexamine the explain output to determine whether reorganizing the table has affected the access plan.

NOTE

graphics/note_icon.jpg

The more indexes created, the more importance is placed on accurate and detailed statistics. This allows DB2 to differentiate between the indexes for access path selection. Increasing the number of indexes increases the potential access paths that DB2 must evaluate. This may lead to a less efficient access path being selected if statistics do not adequately distinguish each index for DB2.


Is the type of access appropriate for the application? You can analyze the explain output and look for types of access to the data that, as a rule, are not optimal for the type of application being executed. For example,

  • Online Transaction Processing (OLTP) Queries: OLTP applications are prime candidates to use matching index scans with range-delimiting predicates, because they tend to return only a few rows that are queries, using an equality predicate against a key column. If your OLTP queries are using a table scan or nonmatching index scan, you may want to analyze the explain data to determine the reasons a matching index scan was not used.

  • Read-Only Queries: The search criteria for a read-only query may be vague, causing a large number of rows to qualify. If the user usually looks only at a few screens of the output data, he or she may want to try to ensure that the entire answer set need not be computed before some results are returned. In this case, the goals of the user are different from the basic operating principle of the optimizer, which attempts to minimize resource consumption for the entire query, not just the first few screens of data.

For example, if the explain output shows that both merge scan join and sort operators were used in the access plan, the entire answer set will be materialized in a temporary table before any rows are returned to the application. In this case, you can attempt to change the access plan by using the OPTIMIZE FOR clause on the SELECT statement. The value specified for the OPTIMIZE clause should represent the number of rows to be processed by the application. In this way, the optimizer can attempt to choose an access plan that does not produce the entire answer set in a temporary table before returning the first rows to the application.

Visual Explain

Visual Explain is a graphical user interface (GUI) utility that gives the database administrator or application developer the ability to examine the access plan determined by the optimizer. It provides

  • An easy-to-understand display of a selected access path.

  • Suggestions for changing an SQL statement.

  • An ability to invoke EXPLAIN for dynamic SQL statements.

  • An ability to provide DB2 catalog statistics for referenced objects of an access path.

  • A subsystem parameter browser with keyword find capabilities.

Visual Explain can be used to analyze previously generated explains or to gather explain data and explain dynamic SQL statements.


Team-Fly    
Top


DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net