DB2 performance monitoring

[Gregor Kovač]

Hi!

I'm at the beginning of my DBA career. I'm doing it for about 6 months.
The question I'm getting lately is something like this: "If I run my procedure X times and it is doing the same thing X times. Why does it sometimes take 60ms and sometimes 300ms?".

How do I go about solving this mistery?

Best regards,
Kovi

[Lennart Jonsson]

The performance can differ for a lot of reasons, and there's no general
mechanism that will tell you why. Example of things that can influence
performance:

Informations is read from disk first time, after that from bufferpool.
Check logical vs physical reads.

Procedure has to wait for locks held by other transactions. Check lock
waits/lock wait time

Perhaps there are utilities like backup, runstats running when
performance is degraded?

Other processes on the server using up resources?


There's probably more, but I think it will give you some ideas on where
to start.


/Lennart

[Gregor Kovač]

Dne nedelja, 30. december 2012 18:48:30 UTC+1 je oseba Lennart Jonsson napisala:

Hi!

Yes, these are some ideas. :) Thanks.
Is it possible to see results of for example logical, physical reads, only at some point in time or I can see them for some specific transaction?
Is it possible for a specific transaction see:
- tables involved in it
- what locks were held and for how long
- what were the SQL statements executed

Best regards,
Kovi

[Lennart Jonsson]

Roughly, sysibmadm.snap... provides cumulative numbers at different levels, since dB was started. Dumping to temp tables and compare with current numbers will tell you to some extent what happened during a period of time. But if you are on 9.7 or 10 have a look at mon... functions/ views. I have only had a quick look but they looks very useful.

If my spelling looks worse than normal, I'm using my IPad and it provides amazing corrections ... I probably typed half of the words twice

[Gregor Kovač]

Dne sreda, 02. januar 2013 00:30:41 UTC+1 je oseba Lennart Jonsson napisala:

> Roughly, sysibmadm.snap... provides cumulative numbers at different levels, since dB was started. Dumping to temp tables and compare with current numbers will tell you to some extent what happened during a period of time. But if you are on 9.7 or 10 have a look at mon... functions/ views. I have only had a quick look but they looks very useful.
>
>
>
> If my spelling looks worse than normal, I'm using my IPad and it provides amazing corrections ... I probably typed half of the words twice

Hi!

I've been playing with mon_* procedures and I'm quite impressed with what you can get out of them. I'm also reading that snapshots are getting deprecated. Do you know anything about that?
I'm especially impressed with:
select * from table(mon_get_pkg_cache_stmt(null, null, null, -2)) as t
where you can get info (execution time, warious wait times, ...) for SQL statements, static and dynamic. :))

Best regards,
Kovi

[Mark A]

Deprecated means it is still on the current release but will likely be removed in some future release/
fixpack. They are giving you advance notice that it may be removed in the future.

One issue with the MON_GET_PKG_CACHE_STMT table function is that it returns a point-in-time view
of both static and dynamic SQL statements. I don't believe that it retrieves information over a period of
time (1 hour for example). To get information over a period of time, you have to capture start and stop
info, and do a delta on the difference.

[Gregor Kovač]

Dne sreda, 02. januar 2013 09:02:22 UTC+1 je oseba Mark A napisala:

Hi!

Yes, you are right in all counts. :)
MON_* procedures are returning point in time information. If you want to look at various trends, then you have to create a table, fill it, wait for certain period of time, fill the table again and compare the results.

Best regards,
Kovi

[Luiz da Silva]

I had implemented something like this based on Jeffrey Benner's solution( http://www.ebenner.com/db2dba_blog/?p=9 ) and I found it very useful.

Regards,
Luiz