我要执行如下脚本
#!/usr/bin/ksh
dbname=$tdb
filename=locksql.$(date+'%m%d%H%M%S')
touch $filename
echo now,connect todatabase: $dbname
db2 "connect to $dbname"
db2 "update dbm cfg using DFT_MON_LOCK on DFT_MON_STMT on"
db2 "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON"
echo now,finding the SQLs which made lockwait
db2 "select AGENT_ID,substr(STMT_TEXT,1,100) as statement,STMT_ELAPSED_TIME_MS from table(SNAAPSHOT_STATMENT('$dbname',-1)) as B where AGENT_ID in (select AGENT_ID_HOLDING_LK from table(SNAPSHOT_LOCKWAIT('dbname',-1)) as A order by LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY ) order by STMT_ELAPSED_TIME_MS DESC"
echo The SQLs have saved to the file $filename
请问:
1、脚本会话结束后,这些参数会自动恢复为OFF状态吗?
2、所资料说明,DFT_MON_STMT参数,对OLTP的性能影响是4%~8%,这个值,准确吗?
3、打开 monitor switches 中上述五个开关,是不是就可以对执行成本最高、运行时间最长、执行次数最多、排序次数最多、准备和预编译耗时最长、全表扫描等SQL进行监控,并可以相关系统对象中查询到结果?
收起