Topic
2 replies Latest Post - ‏2013-09-09T12:31:19Z by RobertTanenbaum
Bhoju
Bhoju
1 Post
ACCEPTED ANSWER

Pinned topic Bad Performance

‏2013-09-02T11:52:15Z |

Hi,

We have three environments, production and two acceptance testing. One of the DB2 INSERT query (with subselect) runs quickly in production and one of testing environment (say X). it takes around 1 min to finish, However on intermittant basis on another testing environment (Say Y), the same query runs extremely longer (around 9 to 13 hours).

Surprising thing is, the query run longer only on intermittant basis and that too only one environment Y.

One more important thing is, when the query runs longer on environment Y, We usually kill that job and restart it again. upon restart the query gets completed within 1 min or so.

This is really strange behaviour and we are now clueless, customer is really behind us considering this issue.

We tried to investigate with DBA's and below recommneded change was applied to environment Y.

The Default query optimization class  (DFT_QUERYOPT) = 7 was changed to Default query optimization class         (DFT_QUERYOPT) = 5. But it didnt make any difference and we are still stuck into the issue.

Its hard to debug as the problem applies to only one environment and on that too its intermittant.

There was also one more observation when the query was executing, the query seems to read large number of rows, that was visible in db2top output provided by DB2 DBA.

I am sure, there is not going to be one liner answer to this, however if there is any debugging approach someone can suggest on this forum, that would be really helpfull.

Hope to hear soon from experts on this group.

Thanks

 

  • Jotac
    Jotac
    82 Posts
    ACCEPTED ANSWER

    Re: Bad Performance

    ‏2013-09-09T10:38:52Z  in response to Bhoju

    Well...

    Assuming the query is *exactlly* the same and the data volume and distribution is different

    then I suggest you to firstly check the execution plan for the 3 system and check for difference.

    This got my attention :

    There was also one more observation when the query was executing, the query seems to read large number of rows, that was visible in db2top output provided by DB2 DBA.

    Try to force to optimizer to re-calculate the access plan at execution time.

    hope this help

    ciao

    GIovanni

     

  • RobertTanenbaum
    RobertTanenbaum
    4 Posts
    ACCEPTED ANSWER

    Re: Bad Performance

    ‏2013-09-09T12:31:19Z  in response to Bhoju

    Make sure the second query has the same indices defined in the same way on the relevant tables in both databases. Also check that the indices have been rebuilt recently. Also, since you say the problem is intermittent, see if any maintenance routines are running at that time which might affect the performance.