Improve Performance of RTS Collection Process RFE 126577

http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=126577

We are using Data Server Manager 2.1.5.1 and Autonomics Director 1.6 together to collect and save RTS stats for our tablespace objects on a 
weekly basis.  We have noticed progressively slower performance of this process over time to the point where it is now consuming a large amount of CPU.                                                                
                                                                       
We investigated this situation and found that the following SQL UPDATE statement was running very slowly.                                     
                                                                       
UPDATE SYSAUTO . SYSTABLESPACESTATS_HIST H SET H . SNAPSHOTTIME = CURRENT TIMESTAMP                                                      
WHERE H . UPDATESTATSTIME =                                            
( SELECT P . UPDATESTATSTIME FROM SYSIBM . SYSTABLESPACESTATS P        
WHERE P . DBNAME = H . DBNAME AND P . NAME = H . NAME AND P . PARTITION
= H . PARTITION AND P . INSTANCE = H . INSTANCE )                      
AND EXISTS ( SELECT 1 FROM SESSION . TEMP_OBJECTS_SNAPSHOT I           
WHERE I . DBNAME = H . DBNAME AND I . SPACENAME = H . NAME AND I .     
PARTITION = H . PARTITION )                                            
                                                                       
I subsequently ran this statement through the Query Workload Tuner tool to see how it could be improved.  The tuner results showed that this   
statement is doing a very expensive tablespace sequential scan on the 
SYSAUTO.SYSTABLESPACESTATS_HIST table.  The tuner indicated that adding
the following index would improve the statement performance by 99%!    
                                                                       
CREATE INDEX                                                           
"SYSAUTO"."SYSTABLESPACESTATS_HIST_VIRT_IDX_153971497095923067" ON     
"SYSAUTO"."SYSTABLESPACESTATS_HIST" ( "NAME" ASC, "DBNAME" ASC,        
"PARTITION" ASC, "INSTANCE" ASC )  NOT PADDED FREEPAGE 0 PCTFREE 10;   
                                                                       
Please investigate this issue and consider updating the Autonomics Director to include this index so that the RTS collection performance  
can be improved.

A similar index should probably also be      
defined on the SYSAUTO.SYSINDEXSPACESTATS_HIST  since a similar UPDATE  statement is probably being used to update this table as part of the RTS collection process.

 

Collecting RTS stats on a regular basis is necessary to help determine when to reorg DB2 objects.  However, the process can be very CPU intensive and time consuming due to a lack of appropriate indexes on SYSAUTO tables.