OPTHINT - A step by step approach

The word OPTHINT can be new to many, I will try to keep the post simple and easy to understand - So that you can along with a pop corn be able to grasp good amount of information posted here. To begin with - OPTHINT also called as an OPTimzer HINT is a method to teach DB2, yes teach DB2 to use a particular access path for your SQL query. When I say - particular access path, it means I have an access path in mind and I want DB2 to use it - Right away !!

If you look at the PLAN_TABLE of your application program, you will find two columns OPTHINT and HINT_USED. You may find some data populated or left blank in most of the cases when you query it with SELECT statement. These are the mastermind columns of the PLAN_TABLE which help us establish a user defined access path for the query.

Grab a test module in your test subsystem and try the following steps. Needless to mention, after seeking appropriate approvals etc from your application programers, DM's etc - Just to let them know you want to ' learn ' and ' help ' - It's a good practice and I follow it in my organization

Why do you need OPTHINT ?

I have a query that is performing bad/good/worse by accessing one of the secondary indexes present on the table. Lets name the index as IX_0002. I am keen to check the behavior of the query with IX_0001, which is the primary index for the table.

How do I establish a OPTHINT rule ?

Step 1 :

All the queries in the PLAN_TABLE are designated with a QUERYNO, So now you know your query for which you wish to apply the method. First query the PLAN_TABLE values for the QUERYNO of your choice using

SELECT  QUERYNO, ACCESSNAME, TNAME, ACCESSTPYE FROM PLAN_TABLE WHERE QUERYNO = 1234 ;

Notice the ACCESSNAME - IX_0002, it is the secondary index and we want DB2 optimizer to choose IX_0001 which is the primary Index for our table TAB0001

Step 2 : Optional

If your shop happened to use CA or BMC tools you can very well try to use the edit function and edit the PLAN_TABLE data and repeat the row on which you wish to apply OPTHINT

Step 3 :

Set your SPUFI session on ROLLBACK mode to avoid any unnecessary updates or wrong query executions etc.

Use the UPDATE statement to update the access path, i.e. ACCESSNAME column ( your Index name ) for the QUERYNO of your choice. You should also update the OPTHINT column with a value - Preferred value would be the Index name itself

Here is how the UPDATE statement would look for the above scenario

UPDATE APPLN.PLAN_TABLE
SET ACCESSNAME='IX_0001' WHERE QUERYNO = 1234 ;

Commit your SPUFI session if ' 1 ' row is affected else ROLLBACK and check your UPDATE query !! There are times where ' 2 ' or more rows can get affected due to the JOIN methods in your query - You can proceed with such results.

UPDATE APPLN.PLAN_TABLE
SET OPTHINT='IX_0001' WHERE QUERYNO = 1234 ;

Commit your SPUFI session if ' 1 ' row is affected else ROLLBACK and check your UPDATE query !! There are times where ' 2 ' or more rows can get affected due to the JOIN methods in your query - You can proceed with such results.

Step 4 :

In the BIND card of your application program use the OPTHINT parameter and pass the OPTHINT value as shown below and BIND the program. Once the BIND is successful you will be notified as to how many rows were involved in the OPTHINT operations.

Step 5 : Optional

Revisit the PLAN_TABLE and look for the value of the column HINT_USED against the same query no. Use the below query :

SELECT * FROM APPLN.PLAN_TABLE WHEREHINT_USED = 'IX_0001' ;

The SPUFI result should display one row for our test case. The ACCESSNAME would have changed from IX_0002 to IX_0001, the MATCHCOLS value also change depending upon the index arrangement and WHERE predicates.

Step 6 :

Run your application program and validate the savings if any. Most of the times OPTHINT does not give expected results - So you may want to roll it back ?

How to remove OPTHINT rule ?

Assuming we performed the above steps -

Step1  :

Populate the BIND card for the program and remove the OPTHINT parameter and issue a BIND to rollback. The populated BIND card for your program will by default contain the OPTHINT parameter and you will have to manually delete the line containing the OPTHINT from the BIND card

Step 2 :

Verify the access path roll back by using

SELECT * FROM APPLN.PLAN_TABLE WHERE QUERYNO = 1234 ORDER BY BIND_TIME

Two or more rows would be populated, the value of the OPTHINT in the first row should be '            ' ( spaces ) and ACCESSNAME should be IX_0002

Conclusion and Summary :

Its always good to test and check for a better access path. OPTHINT does not always provide or guarantee optimum results due to various factors such as Index definition, usage, cluster ratio etc. But you can always roll it back and bring the systems back to how they were prior to the OPTHINT operation.

The UNION Clause - Saving DB2 CPU

I am about to reveal a thumb rule for performance tuning and I want you to try it out as well on your test tables to see if it works. Have you ever encountered an SQL query while performing the famous ' SQL HUNT ' with lots of open and close brackets, AND clause, OR clause and several other predicates ? If I were to post an example of such a query it would be -

SELECT * FROM TEST.TABLE WHERE
(( COL1 = :HV-01 AND COL2 = :HV-02 AND COL3 = :HV-03 )
OR
( COL1 = :Hv-01 AND Col2 <> :HV-02 AND COL3 = :HV-03 )) ;

This is a complex query to the DB2 optimizer. When I ran an explain on this query, the ACCESSTYPE returned a value of I and the MATHCOLS had a value of 0. The Index was present on COL1, COL2, COL3. The query was taking a long time to run and I was figuring out what can be done to improve the performance.

Eureka !! Something strikes my mind. If you have read my earlier post, you will find exactly a similar query that was tuned using the UNION clause. Well, why not apply the same rule to the above query and improve performance ?

The query was changed to -

SELECT * FROM TEST.TABLE WHERE
COL1= :HV-01 AND COL2= :HV-02 AND COL3= :HV-03

UNION

SELECT * FROM TEST.TABLE WHERE
COL1 = :HV-01 AND COL2 <> :HV-02 AND COL3 = :HV-03  ;

The total number of rows selected were exactly same and now comes performance - ACCESSTYPE I and MATCHCOLS value of 3.

Thumb Rule if you agree to the above fact -

Replace the open brackets, OR clause and close brackets with the UNION clause wherever possible and split the query. It works all the time for such scenarios and we can perform the SELECT operation and validate the results !. Making the query simpler for the DB2 optimizer to decide the best possible access path by using the UNION clause is a boon for the application.

Revisiting the SQL Queries

One way to improve your application performance is to scan through the queries used in them. Most of the time, the design of the query would have been pretty complex and the DB2 optimizer was unable to determine a ' good ' access path. A good practice would be to visit the application plan table and look for query numbers ( QUERYNO ) having ACCESSTYPE & MATCHCOL value of I and 0 respectively. You can also look as to why it is R & 0. Using your shops administration tools ( CA - Platinum or IBM Administration tool or any other ) list out the queries which you plan to re-visit.

Here is my experience on one of the query that I modified  -

SELECT * FROM PRDNSCH.TABLE01 WHERE
( ( COL1 = :HV-01 AND
COL2 > :HV-02 ) OR
(   COL1 > :HV-03 ) ) ;

This query had an ACCESSTYPE value of I and a MATCHCOLS value of 0, its obvious isn't it - open and close brackets, OR clause : DB2 optimizer went dizzy and could not help much with respect to access path even if an index was present on COL1 & COL2.

I managed to split the query into two queries using the UNION clause. Here is how the query looked after the split -

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 = :HV-01 AND
COL2 > :HV-02

UNION

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 > :HV-03     ;

The first query above the UNION clause now had an ACCESSTYPE & MATCHCOLS value of I & 2 while the query below the UNION clause had an ACCESSTYPE & MATCHCOLS value of I & 1 respectively. The access path improved and the application program run time efficiency improved proportionally.

How was I sure that the these queries would result the same amount of rows ? Here is how you ensure it. You will have to first get some unique values from the table or approach your application development team for the same. Feed those values and perform a SELECT operation for the two queries as shown below. To double check, apply two or three test cases and verify the results.
First perform a SELECT operation on the table without modifying the original query.

SELECT * FROM PRDNSCH.TABLE01 WHERE
( ( COL1 = 1 AND
COL2 > 2 ) OR
(   COL1 > 3 ) ) ;

Number of rows retrieved - 100 Rows

Perform a SELECT operation on the table by applying the new query that you just designed.

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 = 1 AND
COL2 > 2

UNION

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 > 3     ;

Number of rows retrieved for 1st half of the query - 23 Rows
Number of rows retrieved for 2nd half of the query - 77 Rows

Number of rows retrieved - 100 Rows

Amazing !

Importance of PCTFREE & FREEPAGE

May be I should have done it earlier. There is a tablespace in production which is meant for " INSERT " operation, yes ! Only INSERT DML's run on this table. The application performs SELECT on this table, Ok - So what's the big deal ? , The tablespace was designed long back with DSSIZE as 2Gigs - fair ? I felt so, Average LRECL of 1,098 and with 3 million rows. Now, with such a configuration why should this tablespace have 2 extents ?? The size of the tablespace was beyond 3.5 GB ( 3.86 GB to be precise ). What causing this massive space utilization and of course high number of active pages

FREEPAGE and PCTFREE is defined for the table -

FREEPAGE 9
PCTFREE 30

The LDS of this tablspace with the above values -

The application run time on this table -

Is it really required to have PCTFREE as 30 ? Like i mentioned above, this table is a history table where records get inserted on a daily basis.

So here is what I did - I altered the tablespace and set the FREEPAGE value to 0 and PCTFREE to 5 ( I could have made it 0 as well ), I felt what if in case without my notice there is a update query that triggers on the table, what about REORG's ? 5 is a good value for PCTFREE if you know your table very well and administer what is happening on it. I then ran a REORG with share level CHANGE, performed a REBIND on the program

The LDS of this tablespace after alteration & REORG -

The REORG worked like a charm and there was a dip in the space utilization on the underlined dataset for this tablespace. The LDS now accounted for just 1.8 GB as compared to its earlier size of 3.8 GB, we have successfully optimized space - very fruitful indeed !

The application run time on this table post tuning activities -

There was a reduction in GETPAGES value, around 30,000 pages were saved from reading in. Efficiency gain achieved on the application was around 86 %.  After reading this you may want to go back to your tablespace and check with your application developers as to what these tables are and follow up on the PCTFREE and FREEPAGE values and save !

Note : You can try such options out with indexes as well. The above savings were influenced due to various other factors such as system availability, number of records, tablespace definitions, job start time, CPU utilization etc. Due to the drop in the application run time there was no wait for I/O on the systems that led to such drastic downsized figures. You may not get the exact numbers, but digging into what the table does and tweaking the parameters - does not stop you, at anytime !

Exploring the Multi-row FETCH Cursor

I have a program that has been in my performance tuning radar ever since we met. High Elapsed time and High wait I/O for 192K fetches. It had a very low DB2 CPU time of 97 seconds. I used to monitor this program and couldn't make out what is wrong. Where is this DB2 elapsed time is coming from ? Table sizes are not that huge and get pages are considerable for this application and buffer hits.



I felt, for those many fetches why do you need to OPEN and CLOSE cursor 79K times ?. Is there a way to reduce the repeated OPEN & CLOSE mechanism with cursors. I did some search of my own and came across the Multi-Row Fetch cursor. When I read about it - I wanted to try it out.



This is how the FETCH statement of normal cursor would look -

EXEC SQL
FETCH P16-CUR
INTO :HV-EMPNO
END-EXEC.

Designing a Multi-Row Fetch cursor for improving application performance -
First, Lets Design the working sotrage section handling for multi-row fetch cursors :

01 EMP-REC.
05 HV-EMPNO         PIC X(10) OCCURS 50 TIMES.
05 HV-EMPNAME   PIC X(20) OCCURS 50 TIMES.

We will have to change the declarations to accommodate the rowset size ( discussed below ). Usually the number in the OCCURS parameter should greater than or equal to the rowset size.

1) Declaring the Multi-row fetch cursor with ROWSET parameter :

EXEC SQL

DECLARE P16-CUR WITH ROWSET POSITIONING FOR

SELECT EMPNO, EMPNAME

FROM PRDNSCH.TABLE00

END-EXEC.

By using the ROWSET parameter the cursor is positioned on rowset of the result table.

2) Opening the row set cursor :

EXEC SQL

  OPEN P16-CUR

END-EXEC.

3) Fetching the data :

EXEC SQL

     FETCH NEXT ROWSET FROM P16-CUR

     FOR 50 ROWS

     INTO :HV-EMPNO, :HV-EMPNAME

END-EXEC.

4) Handling SQL +100 ( end of rowset ) :

If the P16-CUR is fetching 220 rows and you design the rowset size as 50 then

1st first will result in SQLCODE 0 and SQLERRD(3) will be 50

2nd fetch will result in SQLCODE 0 and SQLERRD(3) will be 50

3rd fetch will result in SQLCODE 0 and SQLERRD(3) will be 50

4th fetch will result in SQLCODE 0 and SQLERRD(3) will be 50

5th fetch will result in SQLCODE +100 and SQLERRD(3) will be 20

The 5th fetch has calculated that there are no more rows satisfying the rowset size designed and hence you need to ensure a mechanism to handle those remaining rows ( 20 ).

5) Closing the rowset cursor :

EXEC SQL

   CLOSE P16-CUR

END-EXEC.

I have recommended this option to my application developers who will be setting this up for the program. I will keep you posted on the savings if any with the help of this method.