75 ways to demystify DB2: #34: Techtip :A quick way to identify the top N heavy scanned tables in the database
MikeZhang 270007JCJ2 Visits (8608)
There would be a requirement for locating the top N heavy accessing tables in the database for the performance diagnostic purpose. A common tool called db2top is mostly used by DBAs for tracking such performance metrics. However, db2top monitoring has a big performance impact because it uses db2 snapshot mechanism to maintain the counters in the monitor heap which incurs a lot of performance overhead.
This article introduces an easy and quick method to detect your top N heavy scanned tables in the database by using db2pd. db2pd directly accesses the table control block structure with almost zero performance overhead.
Here is a simple script command which uses db2pd -tcbs option to quickly identify the top 3 heavy scanned tables of mydb (database name) which almost has no extra performance impact to the system.