15.10. Case StudyYou 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 runningC:\>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 usesC:\>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 allocatedC:\>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 usesC:\>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. |