 | Level: Intermediate Scott L. Forstie (forstie@us.ibm.com), Senior Software Engineer, IBM
25 Jan 2007 DB2 for i5/OS on V5R4 has been enhanced to include an index advice condenser feature to simplify the analysis of index advice from the query optimizer. Index advice is accumulated as it occurs based upon query and SQL activity. Much of the raw index advice generated by DB2 has some redundancy and overlap that should be considered when creating indexes based on the advice. This article explains the new index advisor condensing feature, as well as demonstrating how it can be used from the iSeries Navigator graphical interface or accessed directly with a custom user query.
The DB2 for i5/OS Index Advisor
The Index Advisor, a feature introduced to DB2 for i5/OS V5R4, records statistics on a system's indexing needs.
Each time a query or SQL statement is executed, the Index Advisor records any indexes suggested by the query optimizer to possibly improve performance. Those new to the topic of index advice on DB2 for i5/OS, can
refer to "OnDemand Index Advice for DB2 for i5/OS." That basic knowledge will be helpful in understanding this new Index Advice Condenser.
Each time an index is recommended, a new row is added
to the system index advice table, SYSIXADV in schema QSYS2. This assumes that the index has not been advised
previously. The Index Advisor examines the attributes in the following columns to determine if an index has already been advised by the optimizer:
- LEADING_COLUMN_KEYS
- KEY_COLUMNS_ADVISED
- TABLE_NAME
- TABLE_SCHEMA
- INDEX_TYPE
- PARTITION_NAME
- NLSS_TABLE_NAME
- NLSS_TABLE_SCHEMA
The complete layout of the SYSIXADV table can be found in Table 3. If matching advice
exists within the table, the existing row is updated. For example,
the "Number of Times Advised" entry is incremented, and the "Last Advised for Query Use" is updated with the current time.
In its raw form, index advice can be unnecessarily verbose.
Although all entries in the table are unique, some entries can be redundant. One good example of this occurs when the same columns are advised but in a different order and the order of these keys is unimportant. In this case, the columns can be arranged to create one index that covers both instances of advice. This opportunity for reordering of the advised keys is indicated via the "Leading Keys Order Independent" column. The keys listed in this column are the leading keys which could be reordered while still satisfying the index being advised.
Advised index examples
Review some index advice examples to set the stage for the simplification work done by the condenser.
The following examples assume all advice is on the same table, same partition, with the same index type and sort sequence.
Table 1. Advised index example 1
| Keys advised | Leading keys, Order independent |
|---|
| C1, C2, C3 | C1, C2, C3 | | C1, C3, C2 | C1 |
In this example, the second row of advice is not pliable.
To satisfy the index advice, a permanent index would need to have keys in the order { C1, C3, C2 }.
However, the first row of advice has great flexibility. The leading key order independent detail shows that the keys could be in any order, so you can condense the two rows of advice into a single permanent index by reordering { C1, C2, C3 } into { C1, C3, C2 }.
If you extend the example in Table 2 to include a third row of advice, you are still able to conclude that the addition
of a single permanent index { C1, C3, C2, C4 } should provide the optimizer all it needs to permanently improve the performance of these queries.
Table 2. Advised index example 2
| Keys advised | Leading keys, order independent |
|---|
| C1, C2, C3 | C1, C2, C3 | | C1, C3, C2 | C1 | | C1, C3, C2, C4 | C1, C2, C3, C4 |
Contrasting Index Advisor and condensed index advice
The condense index advice technology requires the following V5R4M0 5722-SS1 PTFs to be installed on the server: SI25391, SI25469, and SI25470.
When index advice is condensed, the raw index advice is placed into a summary form. Some fields
within the raw advice are specific to the instance of advice and have no value in a summarized form.
Other fields are quite important to preserve and weight the importance of the condensed advice.
Table 3 explains, per column, what happens to the raw index advice during index advice condensation.
The condensed index advice permits the same iSeries Navigator actions as those found under the Index Advisor,
with one exception. Since the condensed index advice does not exist in a permanent form, there is no "Remove from List" action.
Table 3. Condenser column transitions
| Column name | Column heading | Condensing action |
|---|
| TABLE_NAME | Table over which an index is advised | Preserved without change | | TABLE_SCHEMA | Schema containing the table | Preserved without change | | SYSTEM_TABLE_NAME | System table name on which the index is advised | Preserved without change | | PARTITION_NAME | Partition detail for the index | Preserved without change | | KEY_COLUMNS_ADVISED | Column names for the advised index | Generated | | LEADING_COLUMN_KEYS | Leading, order independent keys, the keys at the beginning of the Key_Columns_Advised field that could be reordered and still satisfy the index being advised | Consumed and discarded | | INDEX_TYPE | Radix (default) or encoded vector index (EVI) | Preserved without change | | LAST_ADVISED | Last time this row was updated | Most recent advice timestamp used | | TIMES_ADVISED | Number of times this index has been advised | Generated through summation | | ESTIMATED_CREATION_TIME | Estimated number of seconds for index creation | Largest value used | | REASON_ADVISED | Coded reason why index was advised | Discarded | | LOGICAL_PAGE_SIZE | Recommended page size for index | Largest value used | | MOST_EXPENSIVE_QUERY | Execution time in seconds of the query | Largest value used | | AVERAGE_QUERY_ESTIMATE | Average execution time in seconds of the query | Generated through average | | TABLE_SIZE | Number of rows in table when the index was advised | Value from most recent advice used | | NLSS_TABLE_NAME | Sort sequence table to use for the index | Preserved without change | | NLSS_TABLE_SCHEMA | Library name of the sort sequence table | Preserved without change | | MTI_USED | Number of times an maintained temporary index (MTI) (autonomic index) that matched the advised definition was used because a matching permanent index did not exist | Discarded | | MTI_CREATED | Number of times this specific index advice was used to create a MTI | Discarded | | LAST_MTI_USED | Last time an MTI was used because a matching permanent index did not exist | Discarded |
The following SQL statements were executed to generate the raw index advice contained in Figure 1a
and Figure 1b.
Listing 1. Database and queries used to generate index advice
-- Create sample database in CONDENSE schema
CALL QSYS.CREATE_SQL_SAMPLE('CONDENSE');
SET SCHEMA Condense;
SELECT e.firstnme, d.deptnmae FROM department d, employee e
WHERE e.job = 'DESIGNER' AND YEAR(e.birthdate) > 1950 AND e.sex = 'M';
SELECT e.firstnme, d.deptnmae FROM department d, employee e
WHERE YEAR(e.birthdate) = 1953 AND e.job = 'DESIGNER' AND e.sex IN ('M');
SELECT e.firstnme, d.deptnmae FROM department d, employee e
ORDER BY e.job, e.sex, e.birthdate;
|
Figure 1b just contains the remaining index advice attributes that could not fit
in Figure 1a. Note that the order of the columns under 'Keys Advised' is different.
Figure 1a. Advised index output
Figure 1b. Advised index output (continued)
In this case, two of the advised indexes have enough
key order flexibility to allow the advice to be condensed into a single index. Figure 2
displays the condensed index advice. Besides providing the
user with the condensed column key order { JOB, SEX, BIRTHDATE }, the condensed advice includes contextual
information to aide in determining the importance of the advice. Columns, such as "Times Advised for Query Use" and "Average of Query Estimates", provide a sense of how beneficial the permanent index could be to this
environment. While the "Estimated Index Creation Time" column, helps to determine whether the creation of the index needs to be a scheduled activity.
Figure 2. Condensed index advice
iSeries Navigator condenser interface
From iSeries Navigator, the condense index advice action can be found anywhere that the Index Advisor is found.
Figure 3 displays the graphical interface for the condenser that was accessed by right-clicking on a schema object. The condenser can also be accessed from a table object.
Figure 3. iSeries Navigator condenser interface
The ability to use the iSeries Navigator interface for the condenser requires that the latest V5R4M0
iSeries Access for Windows Service Pack
be installed on your client.
Programmable condenser interface
In addition to the iSeries Navigator interface, the index advice condenser can
be accessed programmatically with an SQL statement. DB2 for i5/OS provides a new
view, CondensedIndexAdvice, in the QSYS2 schema. Listing 2 shows the data
returned by the new CondensedIndexAdvice view. Since the CondensedIndexAdvice view is implemented with a user-defined
table function (UDTF), the view itself is read-only. Any attempt to modify the view will fail with an error of SQL0150.
Listing 2. CondensedIndexAdvice view definition
QSYS2.CONDENSEDINDEXADVICE (
TABLE_NAME FOR COLUMN TABNAME VARCHAR(258) CCSID 37 NOT NULL ,
TABLE_SCHEMA FOR COLUMN TABSCHEMA CHAR(10) CCSID 37 NOT NULL ,
SYSTEM_TABLE_NAME FOR COLUMN SYS_TNAME CHAR(10) CCSID 37 NOT NULL ,
PARTITION_NAME FOR COLUMN TABPART VARCHAR(128) CCSID 37 DEFAULT NULL ,
KEY_COLUMNS_ADVISED FOR COLUMN KEYSADV VARCHAR(16000) CCSID 37 DEFAULT NULL ,
INDEX_TYPE CHAR(14) CCSID 37 DEFAULT NULL ,
LAST_ADVISED FOR COLUMN LASTADV TIMESTAMP DEFAULT NULL ,
TIMES_ADVISED FOR COLUMN TIMESADV BIGINT DEFAULT NULL ,
ESTIMATED_CREATION_TIME FOR COLUMN ESTTIME INTEGER DEFAULT NULL ,
LOGICAL_PAGE_SIZE FOR COLUMN "PAGESIZE" INTEGER DEFAULT NULL ,
MOST_EXPENSIVE_QUERY FOR COLUMN QUERYCOST INTEGER DEFAULT NULL ,
AVERAGE_QUERY_ESTIMATE FOR COLUMN QUERYEST INTEGER DEFAULT NULL ,
TABLE_SIZE BIGINT DEFAULT NULL ,
NLSS_TABLE_NAME FOR COLUMN NLSSNAME CHAR(10) CCSID 37 DEFAULT NULL ,
NLSS_TABLE_SCHEMA FOR COLUMN NLSSSCHEMA CHAR(10) CCSID 37 DEFAULT NULL )
|
As you can see with the following SELECT statements, writing SQL statements
to access the condensed advice is straight forward. As mentioned earlier, the condensed index advice analysis can be scoped either to the table or schema level.
Listing 3. CondensedIndexAdvice query examples
-- Condensed index advice for an entire schema, named CONDENSE
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE WHERE table_schema = 'CONDENSE' ;
-- Condensed index advice for a specific table, named EMPLOYEE in CONDENSE schema
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE
WHERE table_name = 'EMPLOYEE' AND table_schema = 'CONDENSE';
-- Condensed index advice for a range of schemas, where the average
-- query estimate driving the index advice is greater than 10 seconds
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE
WHERE table_schema LIKE 'Q%' AND average_query_estimate > 10 ;
|
The Condense_Advice UDTF used by the new CondensedIndexAdvice view can also be accessed directly by user
queries. Here's the definition of the table function along with a simple example demonstrating how to use
the UDTF.
Listing 4. Condense_Advice UDTF
CREATE FUNCTION QSYS2.Condense_Advice(TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128) )
RETURNS TABLE(TABLE_PARTITION VARCHAR(128), KEY_COLUMNS_ADVISED VARCHAR(16000),
INDEX_TYPE CHAR(14), LAST_ADVISED TIMESTAMP, TIMES_ADVISED BIGINT,
ESTIMATED_CREATION_TIME INTEGER, LOGICAL_PAGE_SIZE INTEGER,
MOST_EXPENSIVE_QUERY INTEGER, AVERAGE_QUERY_ESTIMATE INTEGER,
TABLE_SIZE BIGINT, NLSS_TABLE_NAME CHAR(10),NLSS_TABLE_SCHEMA CHAR(10))
LANGUAGE C
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SCRATCHPAD 325064
DISALLOW PARALLEL
FINAL CALL
CARDINALITY 1
EXTERNAL NAME 'QSYS/QDBSSUDF2(CONDENSE_ADVICE)'
PARAMETER STYLE DB2SQL;
-- Query the condenser UDTF directly, providing selection criteria and ordering
-- the results of the 15 most important condensed entries
SELECT * FROM TABLE(QSYS2.CONDENSE_ADVICE('CRPDTA','B123456')) AS a
WHERE a.table_size >(1024*1024) AND MONTH(last_advised)= MONTH(CURRENT TIMESTAMP)
ORDER BY average_query_estimate DESC
FETCH FIRST 15 ROWS ONLY ;
|
Summary
The use of index advice, whether condensed or raw, is a powerful resource to improve any index strategy.
An important component to consider before acting upon this advice is to review the existing indexes and their
usage statistics. Since an implied maintenance cost exists for any index, its common for performance tuning
exercises to attempt to limit the number of permanent indexes. The morale of this story is to review the
condensed index advice to identify opportunities for improvement, but also review index usage statistics before
and after making any changes.
Resources Learn
Get products and technologies
Discuss
About the author | | | Scott Forstie is the SQL development leader on the DB2 for i5/OS development team in Rochester, Minn. Prior to working on DB2, he worked on UNIX* enablement for the AS/400* and S/390* systems. |
Rate this page
| |
