Unanswered: SQL performance degraded after instance restart
DB2 8.2 AIX 5.3
After an instance restart suddenly several application SQL's take over 100 times longer to execute, grinding the system to a halt.
I can't see anything particularly out of the ordinary and the only paramete that I believe was changed was the DB2_USE_ALTERNATE_PAGE_CLEANING registry parm.
I've removed that and restarted once more but the problem persists.
I've reorged and runstats on the tables/indexes involved.
A runstats with distribution and detailed indexes all returned the message that the index and table stats were not consistent....
So I dropped the index and recreated it and ran runstats once more....
The problem persists... any tips where I should be looking?
You can look at an Execution plan for a SQL; see if the appropriate indexes are being used. Using NMON or TOPAS, look also at the OS Level parameters to make sure NO swapping is taking place. The default AIX settings are not suitable for the db2 database.
We looked at the execution plans on this DB and a test copy. They were using different plans (the production was doign a tablescan instead of using an index)...after some additional investigation is seems that the DB2_REDUCED_OPTIMIZATION registry setting had been changed from 4,UNIQUEINDEX,JOIN to 4,INDEX,JOIN by the SAP WORKLOAD parameter.