The Design Advisor
The Db2® Design Advisor is a tool that can help you significantly improve your workload performance. The task of selecting which indexes, materialized query tables (MQTs), clustering dimensions, or database partitions to create for a complex workload can be daunting. The Design Advisor identifies all of the objects that are needed to improve the performance of your workload.
- New indexes
- New clustering indexes
- New MQTs
- Conversion to multidimensional clustering (MDC) tables
- The redistribution of tables
Use the db2advis command to launch the Design Advisor utility.
- Planning for and setting up a new database
- While designing your database, use the Design Advisor to generate design alternatives in a test
environment for indexing, MQTs, MDC tables, or database partitioning.In partitioned database environments, you can use the Design Advisor to:
- Determine an appropriate database partitioning strategy before loading data into a database
- Assist in upgrading from a single-partition database to a multi-partition database
- Assist in migrating from another database product to a multi-partition Db2 database
- Workload performance tuning
- After your database is set up, you can use the Design Advisor to:
- Improve the performance of a particular statement or workload
- Improve general database performance, using the performance of a sample workload as a gauge
- Improve the performance of the most frequently executed queries, as identified, for example, by the IBM® InfoSphere® Optim Performance Manager
- Determine how to optimize the performance of a new query
- Respond to IBM Data Studio Health Monitor recommendations regarding shared memory utility or sort heap problems with a sort-intensive workload
- Find objects that are not used in a workload
IBM Data Server Manager provides tools for improving the performance of single SQL statements and the performance of groups of SQL statements, which are called query workloads. For more information about this product, see Tuning.
Design Advisor output
- The ADVISE_INSTANCE table is updated with one new row each time that the Design Advisor runs:
- The START_TIME and END_TIME fields show the start and stop times for the utility.
- The STATUS field contains a value of COMPLETED if the utility ended successfully.
- The MODE field indicates whether the -m parameter was used on the db2advis command.
- The COMPRESSION field indicates the type of compression that was used.
- The USE_TABLE column in the ADVISE_TABLE table contains a value of Y if MQT, MDC table, or
database partitioning strategy recommendations were made.
MQT recommendations can be found in the ADVISE_MQT table; MDC recommendations can be found in the ADVISE_TABLE table; and database partitioning strategy recommendations can be found in the ADVISE_PARTITION table. The RUN_ID column in these tables contains a value that corresponds to the START_TIME value of a row in the ADVISE_INSTANCE table, linking it to the same Design Advisor run.
When MQT, MDC, or database partitioning recommendations are provided, the relevant ALTER TABLE stored procedure call is placed in the ALTER_COMMAND column of the ADVISE_TABLE table. The ALTER TABLE stored procedure call might not succeed due to restrictions on the table for the ALTOBJ stored procedure.
- The USE_INDEX column in the ADVISE_INDEX table contains a value of Y (index recommended or evaluated ) or R (an existing clustering RID index was recommended to be unclustered) if index recommendations were made.
- The COLSTATS column in the ADVISE_MQT table contains column statistics for an MQT. These
statistics are contained within an XML structure as
follows:
<?xml version=\"1.0\" encoding=\"USASCII\"?> <colstats> <column> <name>COLNAME1<⁄name> <colcard>1000<⁄colcard> <high2key>999<⁄high2key> <low2key>2<⁄low2key> <⁄column> .... <column> <name>COLNAME100<⁄name> <colcard>55000<⁄colcard> <high2key>49999<⁄high2key> <low2key>100<⁄low2key> <⁄column> <⁄colstats>
- CREATE statements associated with any new indexes, MQTs, MDC tables, or database partitioning strategies
- REFRESH statements for MQTs
- RUNSTATS commands for new objects
--<?xml version="1.0"?>
--<design-advisor>
--<mqt>
--<identifier>
--<name>MQT612152202220000</name>
--<schema>ZILIO2 </schema>
--</identifier>
--<statementlist>3</statementlist>
--<benefit>1013562.481682</benefit>
--<overhead>1468328.200000</overhead>
--<diskspace>0.004906</diskspace>
--</mqt>
.....
--<index>
--<identifier>
--<name>IDX612152221400000</name>
--<schema>ZILIO2 </schema>
--</identifier>
--<table><identifier>
--<name>PART</name>
--<schema>SAMP </schema>
--</identifier></table>
--<statementlist>22</statementlist>
--<benefit>820160.000000</benefit>
--<overhead>0.000000</overhead>
--<diskspace>9.063500</diskspace>
--</index>
.....
--<statement>
--<statementnum>11</statementnum>
--<statementtext>
--
-- select
-- c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice,
-- sum(l_quantity) from samp.customer, samp.orders,
-- samp.lineitem where o_orderkey in( select
-- l_orderkey from samp.lineitem group by l_orderkey
-- having sum(l_quantity) > 300 ) and c_custkey
-- = o_custkey and o_orderkey = l_orderkey group by
-- c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
-- order by o_totalprice desc, o_orderdate fetch first
-- 100 rows only
--</statementtext>
--<objects>
--<identifier>
--<name>MQT612152202490000</name>
--<schema>ZILIO2 </schema>
--</identifier>
--<identifier>
--<name>ORDERS</name>
--<schema>SAMP </schema>
--</identifier>
--<identifier>
--<name>CUSTOMER</name>
--<schema>SAMP </schema>
--</identifier>
--<identifier>
--<name>IDX612152235020000</name>
--<schema>ZILIO2 </schema>
--</identifier>
--<identifier>
--<name>IDX612152235030000</name>
--<schema>ZILIO2 </schema>
--</identifier>
--<identifier>
--<name>IDX612152211360000</name>
--<schema>ZILIO2 </schema>
--</identifier>
--</objects>
--<benefit>2091459.000000</benefit>
--<frequency>1</frequency>
--</statement>
This XML structure can contain more than one column. The column cardinality (that is, the number of values in each column) is included and, optionally, the HIGH2KEY and LOW2KEY values.
The base table on which an index is defined is also included. Ranking of indexes and MQTs can be done using the benefit value. You can also rank indexes using (benefit - overhead) and MQTs using (benefit - 0.5 * overhead).
Following the list of indexes and MQTs is the list of statements in the workload, including the SQL text, the statement number for the statement, the estimated performance improvement (benefit) from the recommendations, and the list of tables, indexes, and MQTs that were used by the statement. The original spacing in the SQL text is preserved in this output example, but the SQL text is normally split into 80 character commented lines for increased readability.
Existing indexes or MQTs are included in the output if they are being used to execute a workload.
MDC and database partitioning recommendations are not explicitly shown in this XML output example.
- Combining all of the RUNSTATS commands into a single RUNSTATS invocation against the new or modified objects
- Providing more usable object names in place of system-generated IDs
- Removing or commenting out any data definition language (DDL) for objects that you do not want to implement immediately