Section 15.10. Case Study | Understanding DB2: Learning Visually with Examples (2nd Edition)

15.10. Case Study

You can use the tools available in DB2 to examine how much memory is being used by different DB2 operations.

To get the most useful information, start with a stopped DB2 instance. Running the memory tracker tool, db2mtrk, indicates that the instance is not running (see Figure 15.7)

Figure 15.7. Verifying that an instance is not running

 C:\>db2stop 03/28/2005 13:39:29     0   0   SQL1064N  DB2STOP processing was successful. SQL1064N  DB2STOP processing was successful. C:\>db2mtrk -i -p -v Tracking Memory on: 2005/03/28 at 13:39:31 Instance not started

Start the instance and see how much memory it will use (see Figure 15.8).

Figure 15.8. Seeing how much memory an instance uses

 C:\>db2start 03/28/2005 13:47:08     0   0   SQL1063N  DB2START processing was successful. SQL1063N  DB2START processing was successful. C:\>db2mtrk -i -p -v Tracking Memory on: 2005/03/28 at 13:47:10 Memory for instance    Database Monitor Heap is of size 16384 bytes    Other Memory is of size 7667712 bytes    Total: 7684096 bytes No active agents

The output in Figure 15.8 shows this particular instance consumes 7,684,096 bytes (7.3MB) when it is started.

If you activate a database, all of its shared memory, such as the lock list and buffer pool, will be allocated (see Figure 15.9).

Figure 15.9. Verifying how much memory is allocated

 C:\>db2 activate db samp82 DB20000I  The ACTIVATE DATABASE command completed successfully. C:\>db2mtrk -i -p -v Tracking Memory on: 2005/03/28 at 13:47:43 Memory for instance    Backup/Restore/Util Heap is of size 16384 bytes    Package Cache is of size 81920 bytes    Catalog Cache Heap is of size 65536 bytes    Buffer Pool Heap is of size 1179648 bytes    Buffer Pool Heap is of size 655360 bytes    Buffer Pool Heap is of size 393216 bytes    Buffer Pool Heap is of size 262144 bytes    Buffer Pool Heap is of size 196608 bytes    Lock Manager Heap is of size 278528 bytes    Database Heap is of size 3637248 bytes    Database Monitor Heap is of size 180224 bytes    Other Memory is of size 7733248 bytes    Total: 14680064 bytes No active agents

The output in Figure 15.9 indicates that when this particular database is activated, 14,680,064 bytes (14MB) are consumed.

If you connect to the database, you will create a db2agent process, and this will also consume some memory (see Figure 15.10).

Figure 15.10. Seeing how much memory an agent uses

 C:\>db2 connect to sample    Database Connection Information  Database server        = DB2/NT 8.2.0  SQL authorization ID   = DSNOW  Local database alias   = SAMPLE C:\>db2mtrk -i -p -v Tracking Memory on: 2005/03/28 at 13:49:12 Memory for instance    Backup/Restore/Util Heap is of size 16384 bytes    Package Cache is of size 81920 bytes    Catalog Cache Heap is of size 65536 bytes    Buffer Pool Heap is of size 1179648 bytes    Buffer Pool Heap is of size 655360 bytes    Buffer Pool Heap is of size 393216 bytes    Buffer Pool Heap is of size 262144 bytes    Buffer Pool Heap is of size 196608 bytes    Lock Manager Heap is of size 278528 bytes    Database Heap is of size 3637248 bytes    Database Monitor Heap is of size 180224 bytes    Other Memory is of size 7766016 bytes    Total: 14712832 bytes Memory for agent 2224    Other Memory is of size 65536 bytes    Application Heap is of size 131072 bytes    Application Control Heap is of size 16384 bytes    Total: 212992 bytes

In this case, the output in Figure 15.10 shows the agent consumes 212,992 bytes (208KB) of memory.

Let's execute some SQL statements and see how that affects the agent private memory allocation (see Figure 15.11).

Figure 15.11. How executing SQL statements affects an agent's memory allocation

 C:\>db2mtrk -i -p -v Tracking Memory on: 2005/03/28 at 13:56:02 Memory for instance    Backup/Restore/Util Heap is of size 16384 bytes    Package Cache is of size 262144 bytes    Catalog Cache Heap is of size 65536 bytes    Buffer Pool Heap is of size 1179648 bytes    Buffer Pool Heap is of size 655360 bytes    Buffer Pool Heap is of size 393216 bytes    Buffer Pool Heap is of size 262144 bytes    Buffer Pool Heap is of size 196608 bytes    Lock Manager Heap is of size 278528 bytes    Database Heap is of size 3637248 bytes    Database Monitor Heap is of size 180224 bytes    Other Memory is of size 7766016 bytes    Total: 14893056 bytes Memory for agent 2224    Other Memory is of size 65536 bytes    Application Heap is of size 212992 bytes    Application Control Heap is of size 16384 bytes    Total: 294912 bytes

As shown in Figure 15.11, the memory tracker tool indicates now that the agent's memory has grown to 294,912 bytes (288KB), an increase of 80KB from the output shown in Figure 15.10, due to an increase in the application heap (from 131,072 to 212,992 bytes). Since the statements would have been optimized to be run, their access plans would have been copied into the application heap as they were run.

Let's back up the database and see how that affects the memory used. Figure 15.12 shows a snapshot of db2mtrk while the backup is running, and Figure 15.13 shows it when it has completed. During the backup operation you can see that the backup/restore buffer is allocated.

Figure 15.12. Memory in use while a backup is running

 C:\>db2mtrk -i -p -v Tracking Memory on: 2005/03/28 at 13:59:19 Memory for instance    Backup/Restore/Util Heap is of size 18432000 bytes    Package Cache is of size 81920 bytes    Catalog Cache Heap is of size 65536 bytes    Buffer Pool Heap is of size 1179648 bytes    Buffer Pool Heap is of size 655360 bytes    Buffer Pool Heap is of size 393216 bytes    Buffer Pool Heap is of size 262144 bytes    Buffer Pool Heap is of size 196608 bytes    Lock Manager Heap is of size 278528 bytes    Database Heap is of size 3620864 bytes    Database Monitor Heap is of size 16384 bytes    Other Memory is of size 7864320 bytes    Total: 33046528 bytes

Figure 15.13. Memory when a backup has finished

 C:\>db2mtrk -i -p -v Tracking Memory on: 2005/03/28 at 14:01:08 Memory for instance    Database Monitor Heap is of size 16384 bytes    Other Memory is of size 7782400 bytes    Total: 7798784 bytes No active agents

Once the backup completes, the backup/restore buffer gets released. Since this was an offline backup, it also releases the connection to the database, so the buffer pools, database heap, lock list, and other memory areas are also released.