Topic
1 reply Latest Post - ‏2011-08-04T10:19:29Z by UteBaumbach
Kpfitzgerald
Kpfitzgerald
1 Post
ACCEPTED ANSWER

Pinned topic SELECT DEADLOCK_ID OPM Query...

‏2011-08-03T18:07:28Z |
this fella here :

SELECT DEADLOCK_ID FROM OPM.STMTHIST_OPMD@CXC9A7 WHERE DEADLOCK_ID = 0 FOR UPDATE;


is crushing my production DB... so i tuned it with an index...


CREATE INDEX DB2INST1.WhyIsOptimNotTunedIDX1 ON OPM.STMTHIST_OPMD@CXC9A7(DEADLOCK_ID ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS; COMMIT WORK ;


`42338.5468` timerons before indexing....
`787.5745` timerons with `DB2INST1.WhyIsOptimNotTunedIDX1`
`98.14` percent improvement
besides the increased time for inserts... anyone see any issues with this? I am really new to using OPM... like 4 days... but i have used DBI, Embarcadero, and Foglight...
Updated on 2011-08-04T10:19:29Z at 2011-08-04T10:19:29Z by UteBaumbach
  • UteBaumbach
    UteBaumbach
    55 Posts
    ACCEPTED ANSWER

    Re: SELECT DEADLOCK_ID OPM Query...

    ‏2011-08-04T10:19:29Z  in response to Kpfitzgerald
    Hello,
    thank you very much for your feedback.
    The current procedure within OPM is that it creates the DEADLOCK event monitor. The tables are then created by DB2. But I agree that it makes sense to create an INDEX afterwards on the event monitor tables. A follow up will be made with the development department.
    The SELECT statement is done on the STMTHIST table, which means that you have turned on the statement history collection for deadlocks within the OPM configuration. Statement history collection creates some overhead on the monitored database therefore it is recommended to turn that on only if really needed ( e.g. if you have many deadlocks and you need to find out the exact statements of all transactions involved in the deadlock ). If it is not really needed then turn off the statement history collection for the event monitor. In that case the deadlock events are still collected, but without the statements.

    If you want to learn more about best practices for OPM setup especially when monitoring a large number of databases then refer to the following whitepaper:
    http://www.ibm.com/developerworks/data/bestpractices/opmlargescale/index.html

    Additionally the OPM redbook gives you a lot of useful information:
    http://www.redbooks.ibm.com/redpieces/abstracts/sg247925.html?Open

    Regards
    Ute Baumbach