Technical Blog Post
75 ways to demystify DB2: #34: Techtip :A quick way to identify the top N heavy scanned tables in the database
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.
db2pd -db mydb -tcbs | grep -p "TCB Table Stats" | sort -k9n | tail -3
Sample output from the above command:
TCB Table Stats:
The results are sorted by column 9 which is 'Reads' whose meaning is how many rows on the table are scanned.
Note that the results are not sorted by column 4 'Scans' because it only counts the access times of a table not for rows, more scan times do not mean more row access times ,e.g if table1 is scanned 1000 times but one scan only for accessing one row ,in contrast, if table2 is only scanned 10 times, but for each scan it needs to access 1 million rows, then table2 should be a hotter table than table1.