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:
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 tables which are extensions to the EXPLAIN tables:
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.
To create this table, run the EXPLAIN.DDL script found in the misc subdirectory of the sqllib subdirectory. If not already created, the Index SmartGuide can also create the table.
The Index SmartGuide and the db2advis tool use the table to pick up and store workload information.
This table stores information about recommended indexes. Information is placed into this table from the SQL compiler, the Index SmartGuide, the db2advis tool, or you.
The table is used in two ways:
To create this table, run the EXPLAIN.DDL script found in the misc subdirectory of the sqllib subdirectory. If not already created, the Index SmartGuide can also create the table.
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:
That is, using one of the following ways to create the SQL to be evaluated:
There are four ways to invoke the Index Advisor:
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 SmartGuide from the pop-up menu. The Index SmartGuide opens. There is extensive help with the Index SmartGuide and it is easy to use. The SmartGuide 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.
On the command line enter db2advis. The db2advis starts by reading in a workload from one of three locations:
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 executes 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.
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 in the ADVISE_INDEX table by the you. The user inserts a new row for each index they want 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, you set the special register. Then the SQL compiler scans the ADVISE_INDEX table where the field USE_INDEX="Y" and assume those indexes exist. All dynamic statements executed in that 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 you were used by the SQL compiler. If they were used, then this is the indication that you have proposed an index that should be considered to be implemented to improve access to those tables.
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:
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
To get better recommendations for a specific query, it is suggested that you advise that query by itself. You can use the Index SmartGuide 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 SmartGuide is a simple, straight-forward, easy to use, visual interface providing an excellent way to access the Advise Facility.