Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Unanswered: Why after RUNSTATS the performance degrated

    Hi ,
    We have db2v7.2 Aix4.3

    recently I used db2look and db2move and reloaded data from production to test box, performace was great after I ran runstate the performace became very bad . I have two questions
    1- when I use reload instead of restore to database, what happend to syscat , all statistics are -1?

    2- could you please tell me why after runstats the performance became so bad.
    Thanks in advance for your help

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Without getting in a discussion of every possible thing that might be causing the problem, it would be best to reorg all your tables and indexes, and then do a runstats again (with full stats collected). If you have any static bound packages, you will need to rebind them. If the performance is still slower, let us know and we can get into a more detailed analysis of what the problem may be.

  3. #3
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Originally posted by Marcus_A
    Without getting in a discussion of every possible thing that might be causing the problem, it would be best to reorg all your tables and indexes, and then do a runstats again (with full stats collected). If you have any static bound packages, you will need to rebind them. If the performance is still slower, let us know and we can get into a more detailed analysis of what the problem may be.
    Hi Marcus.
    could you please answer my questions
    1- after reload data from production (db2look, db2move) to a new database what is the situation of syscat catalog statistic.
    2- as I said performace before runstats was very good ,what happend to syscat catalog ?
    3- if the table has several indexes, with one should be selected for reorging? I do not think it be correct to reorg all indexes .
    Thank you so much in advance.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In DB2 version 7, all the indexes will be reorged automatically with the table. It works slightly different in DB2 version 8.

    If you have defined one of the indexes as the clustering index, then you don't need to specify the index name in the reorg (but if you do specify the index, then it must be the clustering index).

    Explicitly defining a clustering index not only affects the order of the table rows after a reorg, but also affects the sequence that DB2 tries to maintain while inserting rows into the table. If a clustering index has been defined, and it would cause rows to be inserted in the middle of the table (i.e., not always at the end of the table), then sufficient percent free space needs to be defined on the table, and also percent free space on the indexes (assuming there is some insert activity in-between reorgs).

    If a clustering index has not been defined, then you must specify the index to be used for ordering the Table rows when you issue the reorg command. The choice of how to cluster the table depends on how the table is used by all applications and which SQL statements should be given priority.

    But here are some rules of thumb for picking the index to use in the reorg (which is used to order the rows in the table) or when explicitly defining a clustering index:

    Choosing how to cluster a table:
    Generally speaking a primary key which consists of a single column unique value should not be the clustering index. For example, on the sample employee table, employee_id should not be the clustering index. Either last_name, or dept_no would be better choices.

    However, on tables where the primary key consists of multiple columns, then the primary key is often a good candidate for clustering. For example, on a order_item table (primary key is order_no, item_no), then the primary key would be a good candidate for the clustering index to put the rows of the table in that order during after a REORG.

    Keep in mind that these are broad rules of thumb, and it is impossible to know for sure without understanding you table and how your applications access it.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In answer to your other questions:

    1- after reload data from production (db2look, db2move) to a new database what is the situation of syscat catalog statistic.

    I am not sure exactly what the complete sequence of events that was used to move the data, but I suspect that it did not affect the system catalog, and DB2 was using the default stats (-1) on the new database.

    2- as I said performance before runstats was very good ,what happend to syscat catalog ?

    When statistics are updated, and the tables and indexes are disorganized, then sometimes DB2 will not use an index even if it exists. To determine whether this is your problem, a complete reorg, runstats, and rebind (of static packages) are required before proceeding to other possibilities.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Here is command for updating all statistics:
    reorgchk update statistics on table all

    Is there any similar command for "reorg table all"?

    Thanks,
    Grofaty

  7. #7
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186

    Re: Why after RUNSTATS the performance degrated

    If your using V7, make sure the runstats finish with no warnings or errors, if need run with shrlevel = reference. We have found allot of bugs in the optimizer. IBM has created fixes (special builds) for us multiple times,( on fp6,7,8,10)

    Its very important not to get any warnings. I would also look doing an explain plan and make sure its using the same access plan. Make sure all your indexes have stats on them.
    We have demostrated to IBM that the optimizer will choose the wrong access plan on lots of occasions if runstats received any warnings. Treat a warning just like and error and re-run until correct. We have copied over the stats from the prod systems to our outher systems using the db2look command.

  8. #8
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Re: Why after RUNSTATS the performance degrated

    Originally posted by quigleyd
    If your using V7, make sure the runstats finish with no warnings or errors, if need run with shrlevel = reference. We have found allot of bugs in the optimizer. IBM has created fixes (special builds) for us multiple times,( on fp6,7,8,10)

    Its very important not to get any warnings. I would also look doing an explain plan and make sure its using the same access plan. Make sure all your indexes have stats on them.
    We have demostrated to IBM that the optimizer will choose the wrong access plan on lots of occasions if runstats received any warnings. Treat a warning just like and error and re-run until correct. We have copied over the stats from the prod systems to our outher systems using the db2look command.
    Hi all,
    I had no choice but do reload again, As you said some thing happend to system catalog and changed the access plan, anyway,

    I would say:
    Thank you all ,Marcus,grofaty,quigleyd for all your time that you spend for this issue.

Posting Permissions

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