【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