1.性能管理部分视图列表
可以使用命令db2 list tables for schema sysibmadm获取所有的性能管理视图
视图名称 模式名 描述
APPLICATIONS SYSIBMADM 数据库中运行的应用
APPL_PERFORMANCE SYSIBMADM 每个应用中rows selected与rows read的比率
BP_HITRATIO SYSIBMADM 缓冲池的命中率
BP_READ_IO SYSIBMADM 缓冲池读的信息
BP_WRITE_IO SYSIBMADM 缓冲池写的信息
CONTAINER_UTLIZATION SYSIBMADM 表空间中容器的利用率信息
LOCK_HELD SYSIBMADM 当前获得的锁的信息
LOCKWAITS SYSIBMADM 锁等待的信息
LOG_UTILIZATION SYSIBMADM 日志利用率信息
LONG_RUNNING_SQL SYSIBMADM 执行时间最长的SQL
SNAPAGENT_MEMORY_POOL SYSIBMADM 代理级别的内存使用情况
SNAP_GET_AGENT_MEMORY_POOL(与上一句查看效果一样)
SNAPBP SYSIBMADM 缓冲池的基本信息
MON_BP_UTLLIZATION(与上一句查看效果一致)
SNAPDNY_SQL SYSIBMADM 数据库中动态SQL的执行情况
SNAPLOCKWAIT SYSIBMADM 锁等待信息
MON_LOCKWAITS(与上一句查看效果一致)
SNAPTMT SYSIBMADM 应用中SQL语句的执行情况
SNAP_GET_STMT (与上一句查看效果一致)
SNAPTAB SYSIBMADM 表的信息
SNAPTAB_REORG SYSIBMADM 重组信息
SNAP_GET_TAB_REORG (与上一句查看效果一致)
SNAPTBSP SYSIBMADM 表空间信息
MON_TBSP_UTILIZATION (与上一句查看效果一致)
TBSP_UTILIZATITION SYSIBMADM 表空间的利用情况
TOP_DYNAMIC_SQL SYSIBMADM 消耗资源最多的SQL语句信息
MON_CURRENT_SQL (与上一句查看效果一致)
MON_PKG_CACHE_SUMMARY SYSIBMADM PACKAGE命中率信息
MON_CURRENT_UOW SYSIBMADM 当前UOW事务信息
MON_WORKLOAD_SUMMARY SYSIBMADM WORKLOAD信息汇总
MON_CONNECTION_SUMMARY SYSIBMADM 数据库联系信息
MON_DB_SUMMARY SYSIBMADM 数据库信息
2.监控缓冲池命中率
缓冲池的命中率很低,那就代表很多数据都要从磁盘直接读取,那可想而知一个数据库的性能是多的差
专有名词说明:逻辑读与物理读
- 物理读:把数据从磁盘读取到Buffer Cache中
- 逻辑读:从Buffer Cache中读取数据块
2.1 我们使用以下命令来监控缓冲的命中率
SELECT
substr(bp_name,1,30) as bp_name,
data_hit_ratio_percent,
index_hit_ratio_percent,
total_hit_ratio_percent
FROM SYSIBMADM.BP_HITRATIO where bp_name not like 'IBMSYSTEM%'
2.2 查看当前数据库的同步和异步读写情况
select
substr(bp_name,1,20) as bp_name,
int((1-(decimal(pool_data_p_reads)/nullif(pool_data_l_reads,0)))*100) as data_hit_ratio,
int((1-(decimal(pool_index_p_reads)/nullif(pool_index_l_reads,0)))*100) as bp_hit_ratio,
int((1-(decimal(pool_async_data_reads+pool_async_index_reads)/nullif((pool_async_data_reads+pool_async_index_reads+direct_reads),0)))*100) as Asnyc_read_pct,
int((1-(decimal(direct_writes)/nullif(direct_reads,0)))*100) as Direct_RW
from table(snapshot_bp('qindb',-1)) as snapshot_bp where bp_name not like 'IBMSYSTEM%'
AS PK_CACHE_HIT,PKG_CACHE_NUM_OVERFLOWS,PKG_CACHE_SIZE_TOP
FROM SYSIBMADM.SNAPDB
SELECT
substr(appl_name,1,15) as Appl_name,
elapsed_time_min as Elapsed_Mim,
appl_status as status,
substr(authid,1,10) as auth_id,
substr(inbound_comm_address,1,15) as IpAddress,
substr(stmt_text,1,30) as SqlStatement
FROM SYSIBMADM.long_running_sql order by 2 desc
select
num_executions,
prep_time_ms,
prep_time_percent,
substr(stmt_text,1,40) as sql_text
from sysibmadm.query_prep_cost where average_execution_time_s > 0
order by prep_time_percent desc;
num_exections:SQL语句平均执行时间
prep_time_ms:最长的SQL编译时间
prep_time_percent:准备查询时耗用时间在查询执行时间所占的百分比。
sql_text:sql语句
7.监控执行次数最多的SQL语句
select
num_executions as NumExec,
average_execution_time_s as AvgTime,
stmt_sorts as NumSorts,
sorts_per_execution as SortPerStmt,
substr(stmt_text,1,35) as sql_text
from sysibmadm.top_dynamic_sql
where num_executions > 0 order by 1 desc fetch first 5 rows only
8.监控排序次数最多的SQL语句
select
stmt_sorts,
sorts_per_execution,
substr(stmt_text,1,60) as sql_text
from sysibmadm.top_dynamic_sql order by stmt_sorts desc fetch first 5 rows only