sql监控

所有的snapshot都是从share memory里取出来的，如果没开monitor switch，也就是计数器的值不会被记录，你会看到SQL的text，但是看不到计数器的值。

    Dynamic SQL Snapshot Result

Database name                      = HADR97

Database path                      = /home/zxianing/zxianing/NODE0000/SQL00010/

Number of executions               = 1
Number of compilations             = 1
Worst preparation time (ms)        = 49
Best preparation time (ms)         = 49
Internal rows deleted              = Not Collected
Internal rows inserted             = Not Collected
Rows read                          = Not Collected
Internal rows updated              = Not Collected
Rows written                       = Not Collected
Statement sorts                    = Not Collected
Statement sort overflows           = Not Collected
Total sort time                    = Not Collected
Buffer pool data logical reads     = Not Collected
Buffer pool data physical reads    = Not Collected
Buffer pool temporary data logical reads   = Not Collected
Buffer pool temporary data physical reads  = Not Collected
Buffer pool index logical reads    = Not Collected
Buffer pool index physical reads   = Not Collected
Buffer pool temporary index logical reads  = Not Collected
Buffer pool temporary index physical reads = Not Collected
Buffer pool xda logical reads      = Not Collected
Buffer pool xda physical reads     = Not Collected
Buffer pool temporary xda logical reads    = Not Collected
Buffer pool temporary xda physical reads   = Not Collected
Total execution time (sec.microsec)= Not Collected
Total user cpu time (sec.microsec) = Not Collected
Total system cpu time (sec.microsec)= Not Collected
Total statistic fabrication time (milliseconds) = Not Collected
Total synchronous runstats time  (milliseconds) = Not Collected
Statement text                     = select * from t5收起

This the default setting.  To make sure the monitor switch off run the following to check:

db2 get dbm monitor switches

$ db2 get dbm monitor switches

      DBM System Monitor Information Collected

Switch list for db partition number 0
Buffer Pool Activity Information  (BUFFERPOOL) = OFF
Lock Information                        (LOCK) = ON  02/07/2012 21:37:58.374279
Sorting Information                     (SORT) = OFF
SQL Statement Information          (STATEMENT) = OFF
Table Activity Information             (TABLE) = OFF
Take Timestamp Information         (TIMESTAMP) = ON  02/07/2012 04:37:33.194722
Unit of Work Information                 (UOW) = OFF收起

楼上的，你snapshot app 看不到sql吧收起

本来就能找到这个语句啊 ，你把所有的监控都关了，还是能找到的~~~但你会发现里面只记录了SQL语句。什么执行了多少次啊，执行时间啊什么的都是0.收起

Statement – 4% - 8% (much less for static SQL)
• How long an application has been idle
• Type of statement (static/dynamic) and operation being performed
(Prepare/Open/Fetch/etc)
• Cursor Name
• Statement start, stop and elapsed time
• Statement Text (shown by default in get snapshot for dynamic sql)
• Number of sorts, rows fetched
• Estimates of the statement cost, number of rows returned
• Number of agents working on a statement and the degree of parallelism收起

回复 3# tiancheng_2011

这涉及两个不同的

"get  snapshot for  dynamic  sql " 是得到 SQL 在 cache里了， 跟statement switch无关。

如果statement switch打开， 你才可能用
“get  snapshot for  applications 。。。“ or “get  snapshot for  application agentid。。。“
看到running SQL收起

Are u shure the monior has been reset?

reset monitor all收起

Statement                              (DFT_MON_STMT) = OFF收起

在数据库管理器的配置打开了，db2 get dbm cfg |grep DFT_MON_STMT 。收起