【DB2】性能管理视图

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%'

3.监控Package Cache大小

我们可以使用SQL语句来监视应用程序包的大小，其中PKG_CACHE_LOOKUPS表示执行在程序包中直接从Package Cache中查找的次数，PKG_CACHE_INSERTS表示由于应用程序包缓存中没有存在SQL语句的执行计划而需要重新插入的次数。

SELECT DECIMAL(1-(PKG_CACHE_INSERTS*1.0/PKG_CACHE_LOOKUPS),3,2)
AS PK_CACHE_HIT,PKG_CACHE_NUM_OVERFLOWS,PKG_CACHE_SIZE_TOP
FROM SYSIBMADM.SNAPDB

4.监控执行成本最高的SQL

SELECT agent_id,rows_selected,rows_read FROM SYSIBMADM.SNAPAPPL ORDER BY rows_read desc fetch first 10 rows only

5.监控运行时间最长的SQL语句

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

6.监控SQL准备和预编译时间最长的SQL

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