Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    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?

    Cheers,

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    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.
    mota

  3. #3
    Join Date
    Jul 2004
    Posts
    306
    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.

    Thanks for the suggestions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •