DB2 9.7 Statement Concentrator Performance Benefits
RickRhea 060000AVGB Visits (5050)
Each SQL query presented to the DBMS must be processed before data rows can be retrieved, updated, or inserted.
Each SQL statement will be parsed and checked whether it is syntactically correct. In addition to parsing the query, the database optimizer will make decisions about what is the best access plan to process the SQL statement, using the statistics collected by the DBMS. Both parsing and optimizing the statements would introduce a certain amount of overhead to the overall processing time of the query. If a query needs to be executed many times, the overhead could be very significant.
DB2 9.7 introduces a feature called statement concentrator that IBM Tivoli service management products users can benefit from. The statement concentrator modifies the dynamic SQL statements that DB2 receives and converts the literals in the statement into parameter markers. As a result, SQL statements with different literals will be presented to the optimizer as the same SQL statement.
After being processed by the statement concentrator, an IBM Tivoli service management products SQL statement will be like the following:
Statement text = select * from asset where (assetnum = :L0 and siteid = :L1 ) fetch first 1000 rows only optimize for 1000 rows for read only
In this query, the literals originally in the query were replaced by a more generic representation such as ":L0" and ":L1". In the snapshot, you can also see that the statement was executed 17592 times, but it only compiled once.
Number of exec
Number of comp
Worst preparation time (ms) = 3
Best preparation time (ms) = 3
You can use the following DB2 commands to enable and disable statement concentrator:
· Enable: UPDATE DB CFG USING STMT_CONC LITERALS IMMEDIATE;
· Disable: UPDATE DB CFG USING STMT_CONC OFF IMMEDIATE;
The advantages of the statement concentrator are the following: First, it will reduce the overall processing time of the query. The optimizer will store the access plan of the first query in the SQL statement cache (or package cache). For subsequent queries, the access plan will be retrieved from the statement cache, and the optimizer does not have to re-calculate the access plan. Second, the statement concentrator can help to reduce the CPU processing overhead since the SQL statement optimization is a CPU intensive task, especially for complex queries. Our lab results show that IBM Tivoli service management products can benefit greatly from DB2 statement concentrator.
One thing to keep in mind is that the statement concentrator might change the access plan for the existing queries. We discovered that if there are queries which are not fully optimized, the performance of these queries might be worse with statement concentrator. So the users would need to re-optimize these statements, possibly with additional indexes.