数据库性能诊断
一个项目的运行,总伴随着性能问题,数据库的性能问题不容忽视,本章节主要介绍数据库性能问题定位方法以及解决方案。
数据库性能优化的目标
根据角色的不同,数据库优化分为以下几个目标:
- 业务角度(用户):减少用户页面响应时间。
- 数据库角度(开发):减少数据库 SQL 响应时间。
- 数据库服务器角度(运维):充分使用数据库服务器物理资源减少数据库服务器 CPU 使用率,减少数据库服务器 IO 使用率,减少数据库服务器内存使用率。
数据库优化指标如下:
- SQL 平均响应时间变短。
- 数据库服务器 CPU 占用率变少。
- 数据库服务器 IO 使用率变低。
收集信息
首先我们需要观察确认现场环境的信息,环境是承载数据库稳定运行的基础保障。
- 数据库硬件信息收集
硬件 | 硬件配置 | 运行情况 |
CPU | cat/proc/cpuinfo | top\vmstat\sar |
内存 | cat/proc/meminfo | top\vmstat\free |
磁盘 | RAID | iostat |
网络 | ifconfig | ping\route\netstat |
通过对硬件环境的收集分析我们可以判断是否是因为硬件问题造成的数据库性能瓶颈。在排除硬件问题的情况下,我们可以通过收集软件信息进一步进行问题定位。
- 数据库软件信息收集
分类 | 说明 |
数据库版本 | V$VERSION |
数据库架构 | 单机\主备\读写分离\MPP\DSC |
交易类型 | OLTP\OLAP\OLTP与 OLAP 混合 |
配置参数 | 资源类参数\查询优化参数 |
数据库规模 | 库大小\大表\分区表\索引 |
关注的对象 | 触发器\大字段\物化视图\外部链接 |
会话数 | V$SESSIONS |
事务数 | TRX |
等待事件 | VTRXWAIT/VLOCK |
内存相关使用 | V$SESSIONS |
热点 | 热点表\热点 SQL |
在进行收集软硬件的同时,我们也要沟通数据库的使用者来确认近期的环境变化。
- 用户信息收集
分类 | 说明 |
硬件 | 是否有硬件更换\重启\升级 |
软件 | 是否有新业务上线\新模块适配 |
用户 | 是否有业务用户增加\业务访问时间调整\业务访问模块集中 |
问题定位
我们可以通过动态视图+SQL 日志+JDBC 驱动日志来进行性能问题定位。
- 动态性能视图
查询活动会话数,语句如下所示:
SELECT COUNT(*) FROM V$SESSIONS WHERE STATE='ACTIVE';
已执行超过 2 秒的活动 SQL,语句如下所示:
SELECT* FROM (
SELECT SESS_ID,SQL_TEXT,DATEDIFF(SS,LAST_RECV_TIME,SYSDATE) Y_EXETIME,
SF_GET_SESSION_SQL(SESS_ID) FULLSQL,CLNT_IP
FROM V$SESSIONS WHERE STATE='ACTIVE')
WHERE Y_EXETIME>=2;
锁查询,语句如下所示:
SELECT O.NAME,L.* FROM V$LOCK L,SYSOBJECTS O WHERE L.TABLE_ID=O.ID AND BLOCKED=1;
阻塞查询,语句如下所示:
WITH LOCKS
AS (SELECT O.NAME,L.*,S.SESS_ID,S.SQL_TEXT,S.CLNT_IP,S.LAST_SEND_TIME
FROM V$LOCK L, SYSOBJECTS O, V$SESSIONS S
WHERE L.TABLE_ID = O.ID AND L.TRX_ID = S.TRX_ID),
LOCK_TR
AS (SELECT TRX_ID WT_TRXID, TID BLK_TRXID
FROM LOCKS
WHERE BLOCKED = 1),
RES
AS (SELECT SYSDATE STATTIME,T1.NAME,T1.SESS_ID WT_SESSID,S.WT_TRXID,
T2.SESS_ID BLK_SESSID,S.BLK_TRXID,T2.CLNT_IP,
SF_GET_SESSION_SQL (T1.SESS_ID) FULSQL,
DATEDIFF (SS, T1.LAST_SEND_TIME, SYSDATE) SS,
T1.SQL_TEXT WT_SQL
FROM LOCK_TR S, LOCKS T1, LOCKS T2
WHERE T1.LTYPE = 'OBJECT'
AND T1.TABLE_ID <> 0
AND T2.LTYPE = 'OBJECT'
AND T2.TABLE_ID <> 0
AND S.WT_TRXID = T1.TRX_ID
AND S.BLK_TRXID = T2.TRX_ID)
SELECT DISTINCT WT_SQL,CLNT_IP,SS,WT_TRXID,BLK_TRXID
FROM RES;
- SQL 日志
--设置 SQL 过滤规则,只记录必要的 SQL,生产环境不要设成 1
-- 2 只记录 DML 语句 3 只记录 DDL 语句 22 记录绑定参数的语句
-- 25 记录 SQL 语句和它的执行时间 28 记录 SQL 语句绑定的参数信息
SELECT SF_SET_SYSTEM_PARA_VALUE('SQL_TRACE_MASK','2:3:22:25:28',0,1);
--同步日志会严重影响系统效率,生产环境必须设置为异步日志
SELECT SF_SET_SYSTEM_PARA_VALUE('SVR_LOG_ASYNC_FLUSH',1,0,1);
--下面这个语句设置只记录执行时间超过 200 ms 的语句
SELECT SF_SET_SYSTEM_PARA_VALUE('SVR_LOG_MIN_EXEC_TIME',200,0,1);
--下面的语句查看设置是否生效
SELECT * FROM V$DM_INI where para_name='SVR_LOG_ASYNC_FLUSH';
SELECT * FROM V$DM_INI where para_name='SQL_TRACE_MASK';
SELECT * FROM V$DM_INI where para_name='SVR_LOG_MIN_EXEC_TIME';
--开启 SQL 日志:
SP_SET_PARA_VALUE(1, 'SVR_LOG', 1);
--关闭 SQL 日志:
SP_SET_PARA_VALUE(1, 'SVR_LOG', 0);
- AWR 日志
启用系统包和 AWR 包:
CALL SP_INIT_AWR_SYS(1);
CALL SP_CREATE_SYSTEM_PACKAGES(1);
查询 AWR 快照:
SELECT *FROM SYS.WRM$_SNAPSHOT;
设置快照间隔,如果不设置快照间隔,手动执行快照后 SYS.WRM$_SNAPSHOT 视图中没有记录:
CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(50);
在两个时间点分别手动创建快照,或者等待系统自动生成:
10:00时创建第一快照:
CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
30分钟后再创建一个,10:30,
CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
查询 AWR 快照:
SELECT* FROM SYS.WRM$_SNAPSHOT;
创建AWR报告,SYS.AWR_REPORT_HTML(快照ID1,快照ID2,'AWR报告存放路径','AWR报告名称.HTLM');:
SYS.AWR_REPORT_HTML(1,2,'C:\','AWR1.HTML');
- JDBC 驱动日志
解决方案
通过对硬件环境的收集分析我们可以判断是否是因为硬件问题造成的数据库性能瓶颈,具体排查方法请参考操作系统内存性能诊断章节。
大多数情况数据库的性能问题涉及到 SQL 语句的优化,解决优化问题可以从以下几个方面入手。
- 分析执行计划
具体请参考 SQL 优化——DM 执行计划解读章节。
- 表设计优化
表设计优化—表类型选择,如下图所示:
表设计优化—HUGE 表并发 DML 优化,如下图所示:
表设计优化—HUGE 表并发 DML 优化,如下图所示:
表设计优化—分区表,如下图所示:
表设计优化—临时表优化,如下图所示:
表设计优化—字段类型,如下图所示:
表设计优化—外键索引,如下图所示:
表设计优化—反范式,如下图所示:
- 索引设计优化
具体请参考统计信息与索引详解章节。
- 配置参数优化
具体请参考数据库参数优化章节。
- 收集统计信息
具体请参考统计信息与索引详解章节。