Administration Guide

SQL Advise Facility

The Index Advisor is a management tool that reduces the need for you to design and define suitable indexes for your data.

The Index Advisor is good for:

• Finding the best indexes for a problem query.
• Finding the best indexes for a set of queries (a workload), subject to resource limits which are optionally applied.
• Testing an index on a workload without having to create the index.

There are concepts associated with the SQL Advise Facility. First, there is a workload. A workload is a set of SQL statements which the database manager has to process over a given period of time. The SQL statements can include: SELECT, INSERT, UPDATE, and DELETE statements. For example, over a one month period of time your database manager may have to process 1 000 INSERTs, 10 000 UPDATEs, 10 000 SELECTs, and 1 000 DELETEs. The information in the workload is concerned with the type and frequency of the SQL statements over a given period of time. The advising engine uses this workload information in conjunction with the database information to recommend indexes. The goal of the advising engine is to minimize the total workload cost.

Second, there is a concept of a virtual index. Virtual indexes are indexes which do not exist in the current database schema. These indexes could be either recommendations that the Advise Facility has made to you, or indexes that you are looking to the Advise Facility to evaluate for you. These indexes could also be those the Advise Facility considers as part of the process and then discards because they are not going to be recommended. Virtual indexes are passed back and forth from you to the Advise Facility using the ADVISE_INDEX table.

The Advise Facility uses a workload and statistics from the database to generate recommended indexes.

The Advise Facility uses two EXPLAIN tables:

• ADVISE_WORKLOAD

  This table is where you describe the workload to be considered. Each row in the table represents an SQL statement and is described by an associated frequency. There is an identifier for each workload that is a field of the table called "WORKLOAD_NAME". All SQL statements which are part of the same workload should have the same WORKLOAD_NAME.

  The Index wizard and the db2advis tool use the table to pick up and store workload information.

• ADVISE_INDEX

  This table stores information about recommended indexes. Information is placed into this table from the SQL compiler, the Index wizard, the db2advis tool, or you.

  The table is used in two ways:

  • To get recommended indexes from the Advise Facility
  • To evaluate indexes.

The process for using the Index Advisor involves inputs, invocation of the advisor, outputs, and some special cases that should be considered.

There are three ways to create the input for the Index Advisor:

• Capturing a workload.

  That is, using one of the following ways to create the SQL to be evaluated:

  • Using the monitor to get dynamic SQL.
  • Using the SYSSTMT catalog view to get static SQL.
  • Adding statements and frequencies by cutting and pasting the values into the ADVISE_INDEX table.
• Modifying the workload frequencies to increase or decrease the importance of queries.
• Determining the constraints, if any, on the data.

There are four ways to invoke the Index Advisor:

• Using the Control Center.

  This is the recommended way to use the Index Advisor. From the Control Center, expand the object tree until you find the indexes folder. Click with mouse button two on the indexes folder and select Create->Index using wizard from the pop-up menu. The Index wizard opens. There is extensive help with the Index wizard and it is easy to use. The wizard also contains features to construct a workload by looking for recently executed SQL, or looking through the recently used packages, or by manually adding SQL statements.

• Using the command line processor.

  On the command line enter db2advis. The db2advis starts by reading in a workload from one of three locations:

  • From the command line
  • From the statements in a text file
  • From the ADVISE_WORKLOAD table after you have inserted rows with the proposed workload (SQL and frequency).

  The tool then uses the CURRENT EXPLAIN MODE register to obtain recommended indexes, combined with an internal optimization algorithm for picking out the best indexes. The output goes to your terminal screen, the ADVISE_INDEX table, and an output file, if desired.

  For example, you may wish the tool to recommend indexes for a simple query "select count(*) from sales where region = 'Quebec'"

  $ db2advis -d sample \
   -s "select count(*) from sales where region = 'Quebec'" \
   -t 1
  performing auto-bind
   
  Bind is successful. Used bindfile: /home3/valentin/sqllib/bnd/db2advis.bnd
   
  Calculating initial cost (without recommended indexes) [31.198040] timerons 
  Initial set of proposed indexes is ready. 
  Found maximum set of [1] recommended indexes 
  Cost of workload with all indexes included [2.177133] timerons 
  cost without index [0] is [31.198040] timerons.  Derived benefit is
  [29.020907] 
  total disk space needed for initial set [1] MB 
  total disk space constrained to         [-1] MB 
    1  indexes in current solution 
   [31.198040] timerons  (without indexes) 
   [2.177133] timerons  (with current solution) 
   [%93.02] improvement 
   
   Trying variations of the solution set. 
  Time elapsed. 
  LIST OF RECOMMENDED INDEXES 
  =========================== 
  index[1], 1MB CREATE INDEX WIZ689 ON VALENTIN.SALES (REGION DESC) 
  =========================== 
  Index Advisor tool is finished. 
  

  The db2advis tool can be used to recommend indexes for a workload as well. You can create an input file called "sample.sql":

  --#SET FREQUENCY 100 
  select count(*) from sales where region = ?; 
  --#SET FREQUENCY 3 
  select projno, sum(comm) tot_comm  from employee, emp_act 
  where employee.empno = emp_act.empno and 
       employee.job='DESIGNER' 
  group by projno 
  order by tot_comm desc; 
  --#SET FREQUENCY 50 
  select * from sales where sales_date = ?;
  

  Then execute the following command:

  $ db2advis -d sample -i sample.sql -t 0 
    found [3] SQL statements from the input file 
   
  Calculating initial cost (without recommmended indexes) [62.331280] timerons 
  Initial set of proposed indexes is ready. 
  Found maximum set of [2] recommended indexes 
  Cost of workload with all indexes included [29.795755] timerons 
  cost without index [0] is [58.816662] timerons.  Derived benefit is
  [29.020907] 
  cost without index [1] is [33.310373] timerons.  Derived benefit is
  [3.514618] 
  total disk space needed for initial set [2] MB 
  total disk space constrained to         [-1] MB 
    2  indexes in current solution 
   [62.331280] timerons  (without indexes) 
   [29.795755] timerons  (with current solution) 
   [%52.20] improvement 
   
  Trying variations of the solution set. 
  Time elapsed. 
  LIST OF RECOMMENDED INDEXES 
  =========================== 
  index[1], 1MB CREATE INDEX WIZ119 ON VALENTIN.SALES (SALES_DATE DESC,
  SALES_PERSON DESC) 
  index[2], 1MB CREATE INDEX WIZ63 ON VALENTIN.SALES (REGION DESC) 
  =========================== 
  Index Advisor tool is finished. 
             
  

• Using self-directed methods involving the EXPLAIN modes and PREP options.

  For example, the CURRENT EXPLAIN MODE special register is set to RECOMMEND INDEXES. This setting will cause the SQL compiler to capture explain data and the recommended indexes to be placed in the ADVISE_INDEX table; however, the SQL statement is not executed.

  Or, the CURRENT EXPLAIN MODE special register is set to EVALUATE INDEXES. This setting will cause the SQL compiler to use indexes placed by the user in the ADVISE_INDEX table. The user inserts a new row for each index that should be evaluated. The required information for each index is: index name, table name, and the columns names that make up the index being evaluated. Once entered, the special register CURRENT EXPLAIN MODE should be set to EVALUATE INDEXES. Then the SQL compiler scans the ADVISE_INDEX table for indexes where the field USE_INDEX="Y" (these are called virtual indexes). All dynamic statements executed in EVALUATE INDEXES mode are explained as if these virtual indexes were available. The SQL compiler then chooses to use the virtual indexes if they improve the performance of the statements. Otherwise, the indexes are ignored. By reviewing the EXPLAIN results, you can see if the indexes proposed by the user were used by the SQL compiler. Those that were used should be considered to be implemented to improve access.

• Using the Call Level Interface (CLI).

  If you are using this interface to write applications, you can also use the advisor.

There are different ways to use the results from the advisor:

• Interpreting the output from the Index Advisor.

  To see what indexes were recommended by the Advise Facility, you can use the following query:

     SELECT CAST(CREATION_TEXT as CHAR(200))
        FROM ADVISE_INDEX
  

• Applying the recommendations of the Index Advisor.
• Knowing when to drop an index.

To get better recommendations for a specific query, it is suggested that you advise that query by itself. You can use the Index wizard to recommend indexes for a single query by building a workload which contains only that query.

A sample workload can be collected from Event Monitor output. The Event Monitor can be used to collect dynamic SQL executions. Then these statements can be fed back to the Advise Facility.

The Index wizard is a simple, straight-forward, easy to use, visual interface providing an excellent way to access the Advise Facility.