本文档风哥主要介绍DM数据库性能监控与分析，包括性能监控概述、性能指标、监控工具、资源监控、SQL监控、等待事件监控、AWR分析、ASH分析、性能调优、实际案例和最佳实践等内容，风哥教程参考DM官方文档DM8系统管理员手册、DM8性能优化指南，适合数据库技术人员在学习和生产环境中使用。
Part01-基础概念与理论知识
1.1 性能监控概述
性能监控是数据库管理的重要工作，通过监控数据库的性能指标，及时发现和解决性能问题，保障数据库的稳定运行。
# 性能监控的定义
性能监控是数据库管理的重要工作，通过监控数据库的性能指标，及时发现和解决性能问题，保障数据库的稳定运行。
# 性能监控的重要性
– 及时发现问题：及时发现性能问题，避免问题扩大
– 优化性能：通过监控数据，优化数据库性能
– 保障稳定：保障数据库的稳定运行，提高可用性
– 提高效率：提高数据库的运行效率，降低成本
– 满足需求：满足业务需求，提高用户体验
# 性能监控的目标
– 响应时间：监控数据库的响应时间，确保响应时间在合理范围内
– 吞吐量：监控数据库的吞吐量，确保吞吐量满足业务需求
– 资源利用率：监控数据库的资源利用率，确保资源利用率合理
– 可用性：监控数据库的可用性，确保数据库稳定运行
– 并发性：监控数据库的并发性，确保并发性能满足业务需求
# 性能监控的内容
– CPU监控：监控CPU的使用情况
– 内存监控：监控内存的使用情况
– IO监控：监控IO的使用情况
– 网络监控：监控网络的使用情况
– SQL监控：监控SQL的执行情况
– 等待事件监控：监控等待事件的情况
性能监控是数据库管理的重要工作，通过监控数据库的性能指标，及时发现和解决性能问题，保障数据库的稳定运行。
# 性能监控的重要性
– 及时发现问题：及时发现性能问题，避免问题扩大
– 优化性能：通过监控数据，优化数据库性能
– 保障稳定：保障数据库的稳定运行，提高可用性
– 提高效率：提高数据库的运行效率，降低成本
– 满足需求：满足业务需求，提高用户体验
# 性能监控的目标
– 响应时间：监控数据库的响应时间，确保响应时间在合理范围内
– 吞吐量：监控数据库的吞吐量，确保吞吐量满足业务需求
– 资源利用率：监控数据库的资源利用率，确保资源利用率合理
– 可用性：监控数据库的可用性，确保数据库稳定运行
– 并发性：监控数据库的并发性，确保并发性能满足业务需求
# 性能监控的内容
– CPU监控：监控CPU的使用情况
– 内存监控：监控内存的使用情况
– IO监控：监控IO的使用情况
– 网络监控：监控网络的使用情况
– SQL监控：监控SQL的执行情况
– 等待事件监控：监控等待事件的情况
1.2 性能指标
性能指标是评估数据库性能的重要依据，了解性能指标有助于更好地监控和优化数据库性能。
# 1. CPU性能指标
– CPU使用率：CPU的使用百分比
– CPU等待时间：CPU等待IO的时间
– CPU上下文切换：CPU上下文切换的次数
– CPU负载：CPU的负载情况
# 2. 内存性能指标
– 内存使用率：内存的使用百分比
– 缓冲区命中率：缓冲区的命中率
– 内存交换：内存交换的情况
– 内存碎片：内存碎片的情况
# 3. IO性能指标
– IO等待时间：IO等待的时间
– IO吞吐量：IO的吞吐量
– IO响应时间：IO的响应时间
– IO队列长度：IO队列的长度
# 4. 网络性能指标
– 网络带宽：网络的使用带宽
– 网络延迟：网络的延迟时间
– 网络丢包率：网络的丢包率
– 网络连接数：网络的连接数
# 5. SQL性能指标
– SQL执行时间：SQL的执行时间
– SQL逻辑读：SQL的逻辑读次数
– SQL物理读：SQL的物理读次数
– SQL执行次数：SQL的执行次数
# 6. 等待事件性能指标 风哥提示：
– 等待事件类型：等待事件的类型
– 等待事件次数：等待事件的次数
– 等待事件时间：等待事件的时间
– 等待事件占比：等待事件占总时间的比例
– CPU使用率：CPU的使用百分比
– CPU等待时间：CPU等待IO的时间
– CPU上下文切换：CPU上下文切换的次数
– CPU负载：CPU的负载情况
# 2. 内存性能指标
– 内存使用率：内存的使用百分比
– 缓冲区命中率：缓冲区的命中率
– 内存交换：内存交换的情况
– 内存碎片：内存碎片的情况
# 3. IO性能指标
– IO等待时间：IO等待的时间
– IO吞吐量：IO的吞吐量
– IO响应时间：IO的响应时间
– IO队列长度：IO队列的长度
# 4. 网络性能指标
– 网络带宽：网络的使用带宽
– 网络延迟：网络的延迟时间
– 网络丢包率：网络的丢包率
– 网络连接数：网络的连接数
# 5. SQL性能指标
– SQL执行时间：SQL的执行时间
– SQL逻辑读：SQL的逻辑读次数
– SQL物理读：SQL的物理读次数
– SQL执行次数：SQL的执行次数
# 6. 等待事件性能指标 风哥提示：
– 等待事件类型：等待事件的类型
– 等待事件次数：等待事件的次数
– 等待事件时间：等待事件的时间
– 等待事件占比：等待事件占总时间的比例
1.3 监控工具
DM数据库提供了多种监控工具，帮助数据库管理员监控和分析数据库性能。
# 1. 系统视图
– v$session：查看当前会话信息
– v$sql：查看SQL执行信息
– v$process：查看进程信息
– v$lock：查看锁信息
– v$lockwait：查看锁等待信息
– v$transaction：查看事务信息
– v$wait_class：查看等待类别信息
– v$system_event：查看系统等待事件信息
– v$session_event：查看会话等待事件信息
– v$session_wait：查看会话等待信息
# 2. 性能视图
– v$sysstat：查看系统统计信息
– v$sesstat：查看会话统计信息
– v$resource_limit：查看资源限制信息
– v$pgastat：查看PGA统计信息
– v$sgastat：查看SGA统计信息
– v$buffer_pool_statistics：查看缓冲池统计信息
– v$filestat：查看文件统计信息
– v$tempstat：查看临时文件统计信息
– v$undostat：查看撤销统计信息
– v$rollstat：查看回滚段统计信息 学习交流加群风哥微信: itpux-com
# 3. 性能报告
– AWR报告：自动工作负载仓库报告
– ASH报告：活动会话历史报告
– SQL报告：SQL性能报告
– 等待事件报告：等待事件性能报告
– 资源使用报告：资源使用情况报告
# 4. 监控工具
– DM管理工具：DM提供的管理工具
– DM监控工具：DM提供的监控工具
– DM性能分析工具：DM提供的性能分析工具
– 第三方监控工具：第三方提供的监控工具
– v$session：查看当前会话信息
– v$sql：查看SQL执行信息
– v$process：查看进程信息
– v$lock：查看锁信息
– v$lockwait：查看锁等待信息
– v$transaction：查看事务信息
– v$wait_class：查看等待类别信息
– v$system_event：查看系统等待事件信息
– v$session_event：查看会话等待事件信息
– v$session_wait：查看会话等待信息
# 2. 性能视图
– v$sysstat：查看系统统计信息
– v$sesstat：查看会话统计信息
– v$resource_limit：查看资源限制信息
– v$pgastat：查看PGA统计信息
– v$sgastat：查看SGA统计信息
– v$buffer_pool_statistics：查看缓冲池统计信息
– v$filestat：查看文件统计信息
– v$tempstat：查看临时文件统计信息
– v$undostat：查看撤销统计信息
– v$rollstat：查看回滚段统计信息 学习交流加群风哥微信: itpux-com
# 3. 性能报告
– AWR报告：自动工作负载仓库报告
– ASH报告：活动会话历史报告
– SQL报告：SQL性能报告
– 等待事件报告：等待事件性能报告
– 资源使用报告：资源使用情况报告
# 4. 监控工具
– DM管理工具：DM提供的管理工具
– DM监控工具：DM提供的监控工具
– DM性能分析工具：DM提供的性能分析工具
– 第三方监控工具：第三方提供的监控工具
风哥提示：性能监控是数据库管理的重要工作，掌握性能监控工具的使用，是及时发现和解决性能问题的关键。建立完善的监控体系，是保障数据库稳定运行的基础。
Part02-生产环境规划与建议
2.1 资源监控
2.1.1 CPU监控
# 1. 查看CPU使用情况
– 查看系统CPU使用情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%CPU%’;
– 查看会话CPU使用情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, ss.value AS cpu_usage
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic
#
AND sn.name = ‘CPU used by this session’
ORDER BY ss.value DESC;
# 2. 查看CPU等待情况
– 查看CPU等待时间
SQL> SELECT * FROM v$system_event WHERE wait_class = ‘CPU’;
– 查看会话CPU等待情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.wait_class = ‘CPU’
ORDER BY se.time_waited DESC;
# 3. 查看CPU负载情况
– 查看系统CPU负载 学习交流加群风哥QQ113257174
SQL> SELECT * FROM v$resource_limit WHERE resource_name = ‘cpu_count’;
– 查看会话CPU负载
SQL> SELECT s.sid, s.serial#, s.username, s.program, s.status, s.wait_class
FROM v$session s
WHERE s.wait_class = ‘CPU’
ORDER BY s.sid;
# 4. 实际示例
– 查看CPU使用率最高的会话
SQL> SELECT s.sid, s.serial#, s.username, s.program, ss.value AS cpu_usage
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic
#
AND sn.name = ‘CPU used by this session’
AND ss.value > 0
ORDER BY ss.value DESC
LIMIT 10;
– 查看CPU等待时间最长的会话
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.wait_class = ‘CPU’
AND se.time_waited > 0
ORDER BY se.time_waited DESC
LIMIT 10;
– 查看系统CPU使用情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%CPU%’;
– 查看会话CPU使用情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, ss.value AS cpu_usage
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic
#
AND sn.name = ‘CPU used by this session’
ORDER BY ss.value DESC;
# 2. 查看CPU等待情况
– 查看CPU等待时间
SQL> SELECT * FROM v$system_event WHERE wait_class = ‘CPU’;
– 查看会话CPU等待情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.wait_class = ‘CPU’
ORDER BY se.time_waited DESC;
# 3. 查看CPU负载情况
– 查看系统CPU负载 学习交流加群风哥QQ113257174
SQL> SELECT * FROM v$resource_limit WHERE resource_name = ‘cpu_count’;
– 查看会话CPU负载
SQL> SELECT s.sid, s.serial#, s.username, s.program, s.status, s.wait_class
FROM v$session s
WHERE s.wait_class = ‘CPU’
ORDER BY s.sid;
# 4. 实际示例
– 查看CPU使用率最高的会话
SQL> SELECT s.sid, s.serial#, s.username, s.program, ss.value AS cpu_usage
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic
#
AND sn.name = ‘CPU used by this session’
AND ss.value > 0
ORDER BY ss.value DESC
LIMIT 10;
– 查看CPU等待时间最长的会话
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.wait_class = ‘CPU’
AND se.time_waited > 0
ORDER BY se.time_waited DESC
LIMIT 10;
2.1.2 内存监控
# 1. 查看内存使用情况
– 查看SGA使用情况
SQL> SELECT * FROM v$sgastat;
– 查看PGA使用情况
SQL> SELECT * FROM v$pgastat;
– 查看缓冲池使用情况
SQL> SELECT * FROM v$buffer_pool_statistics;
# 2. 查看缓冲区命中率
– 查看缓冲区命中率
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN (‘consistent gets’, ‘db block gets’, ‘physical reads’);
– 计算缓冲区命中率 更多视频教程www.fgedu.net.cn
SQL> SELECT
(SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’) AS logical_reads,
(SELECT value FROM v$sysstat WHERE name = ‘physical reads’) AS physical_reads,
ROUND((1 – (SELECT value FROM v$sysstat WHERE name = ‘physical reads’) /
((SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’))) * 100, 2) AS buffer_hit_ratio
FROM dual;
# 3. 查看内存交换情况
– 查看内存交换情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%swap%’;
– 查看会话内存使用情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem
FROM v$session s
ORDER BY pga_used_mem DESC
LIMIT 10;
# 4. 实际示例
– 查看SGA使用情况
SQL> SELECT pool, name, bytes, ROUND(bytes / 1024 / 1024, 2) AS size_mb
FROM v$sgastat
ORDER BY bytes DESC;
– 查看PGA使用情况
SQL> SELECT name, value, ROUND(value / 1024 / 1024, 2) AS size_mb
FROM v$pgastat
ORDER BY value DESC;
– 查看SGA使用情况
SQL> SELECT * FROM v$sgastat;
– 查看PGA使用情况
SQL> SELECT * FROM v$pgastat;
– 查看缓冲池使用情况
SQL> SELECT * FROM v$buffer_pool_statistics;
# 2. 查看缓冲区命中率
– 查看缓冲区命中率
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN (‘consistent gets’, ‘db block gets’, ‘physical reads’);
– 计算缓冲区命中率 更多视频教程www.fgedu.net.cn
SQL> SELECT
(SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’) AS logical_reads,
(SELECT value FROM v$sysstat WHERE name = ‘physical reads’) AS physical_reads,
ROUND((1 – (SELECT value FROM v$sysstat WHERE name = ‘physical reads’) /
((SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’))) * 100, 2) AS buffer_hit_ratio
FROM dual;
# 3. 查看内存交换情况
– 查看内存交换情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%swap%’;
– 查看会话内存使用情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem
FROM v$session s
ORDER BY pga_used_mem DESC
LIMIT 10;
# 4. 实际示例
– 查看SGA使用情况
SQL> SELECT pool, name, bytes, ROUND(bytes / 1024 / 1024, 2) AS size_mb
FROM v$sgastat
ORDER BY bytes DESC;
– 查看PGA使用情况
SQL> SELECT name, value, ROUND(value / 1024 / 1024, 2) AS size_mb
FROM v$pgastat
ORDER BY value DESC;
2.1.3 IO监控
# 1. 查看IO使用情况
– 查看文件IO统计信息
SQL> SELECT * FROM v$filestat;
– 查看临时文件IO统计信息
SQL> SELECT * FROM v$tempstat;
– 查看IO等待情况
SQL> SELECT * FROM v$system_event WHERE wait_class = ‘User I/O’;
# 2. 查看IO性能指标
– 查看IO响应时间
SQL> SELECT file_name, phyrds, phywrts, phyblkrd, phyblkwrt,
ROUND(readtim / phyrds, 2) AS avg_read_time,
ROUND(writetim / phywrts, 2) AS avg_write_time 更多学习教程公众号风哥教程itpux_com
FROM v$filestat f, dba_data_files d
WHERE f.file# = d.file_id
ORDER BY readtim DESC
LIMIT 10;
– 查看IO吞吐量
SQL> SELECT file_name,
ROUND(phyblkrd * 8192 / 1024 / 1024, 2) AS read_mb,
ROUND(phyblkwrt * 8192 / 1024 / 1024, 2) AS write_mb
FROM v$filestat f, dba_data_files d
WHERE f.file# = d.file_id
ORDER BY phyblkrd DESC
LIMIT 10;
# 3. 查看IO等待情况
– 查看IO等待时间
SQL> SELECT event, total_waits, time_waited, ROUND(time_waited / total_waits, 2) AS avg_wait_time
FROM v$system_event
WHERE wait_class = ‘User I/O’
ORDER BY time_waited DESC;
– 查看会话IO等待情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.wait_class = ‘User I/O’
ORDER BY se.time_waited DESC
LIMIT 10;
# 4. 实际示例
– 查看IO响应时间最长的文件
SQL> SELECT file_name,
phyrds, phywrts,
ROUND(readtim / phyrds, 2) AS avg_read_time, from DB视频:www.itpux.com
ROUND(writetim / phywrts, 2) AS avg_write_time
FROM v$filestat f, dba_data_files d
WHERE f.file# = d.file_id
AND phyrds > 0
ORDER BY readtim DESC
LIMIT 10;
– 查看IO等待时间最长的会话
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.wait_class = ‘User I/O’
AND se.time_waited > 0
ORDER BY se.time_waited DESC
LIMIT 10;
– 查看文件IO统计信息
SQL> SELECT * FROM v$filestat;
– 查看临时文件IO统计信息
SQL> SELECT * FROM v$tempstat;
– 查看IO等待情况
SQL> SELECT * FROM v$system_event WHERE wait_class = ‘User I/O’;
# 2. 查看IO性能指标
– 查看IO响应时间
SQL> SELECT file_name, phyrds, phywrts, phyblkrd, phyblkwrt,
ROUND(readtim / phyrds, 2) AS avg_read_time,
ROUND(writetim / phywrts, 2) AS avg_write_time 更多学习教程公众号风哥教程itpux_com
FROM v$filestat f, dba_data_files d
WHERE f.file# = d.file_id
ORDER BY readtim DESC
LIMIT 10;
– 查看IO吞吐量
SQL> SELECT file_name,
ROUND(phyblkrd * 8192 / 1024 / 1024, 2) AS read_mb,
ROUND(phyblkwrt * 8192 / 1024 / 1024, 2) AS write_mb
FROM v$filestat f, dba_data_files d
WHERE f.file# = d.file_id
ORDER BY phyblkrd DESC
LIMIT 10;
# 3. 查看IO等待情况
– 查看IO等待时间
SQL> SELECT event, total_waits, time_waited, ROUND(time_waited / total_waits, 2) AS avg_wait_time
FROM v$system_event
WHERE wait_class = ‘User I/O’
ORDER BY time_waited DESC;
– 查看会话IO等待情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.wait_class = ‘User I/O’
ORDER BY se.time_waited DESC
LIMIT 10;
# 4. 实际示例
– 查看IO响应时间最长的文件
SQL> SELECT file_name,
phyrds, phywrts,
ROUND(readtim / phyrds, 2) AS avg_read_time, from DB视频:www.itpux.com
ROUND(writetim / phywrts, 2) AS avg_write_time
FROM v$filestat f, dba_data_files d
WHERE f.file# = d.file_id
AND phyrds > 0
ORDER BY readtim DESC
LIMIT 10;
– 查看IO等待时间最长的会话
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.wait_class = ‘User I/O’
AND se.time_waited > 0
ORDER BY se.time_waited DESC
LIMIT 10;
2.2 SQL监控
2.2.1 SQL执行监控
# 1. 查看SQL执行信息
– 查看当前执行的SQL
SQL> SELECT s.sid, s.serial#, s.username, s.program, q.sql_text, q.elapsed_time, q.cpu_time
FROM v$session s, v$sql q
WHERE s.sql_id = q.sql_id
AND s.status = ‘ACTIVE’
ORDER BY q.elapsed_time DESC
LIMIT 10;
– 查看SQL执行统计信息
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
ORDER BY elapsed_time DESC
LIMIT 10;
# 2. 查看SQL执行计划
– 查看SQL执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看SQL详细执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
# 3. 查看SQL执行历史
– 查看SQL执行历史
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE sql_id = ‘xxx’
ORDER BY last_active_time DESC;
– 查看SQL执行统计历史
SQL> SELECT * FROM v$sqlstats WHERE sql_id = ‘xxx’;
# 4. 实际示例
– 查看执行时间最长的SQL
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE elapsed_time > 0
ORDER BY elapsed_time DESC
LIMIT 10;
– 查看逻辑读最多的SQL
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE buffer_gets > 0
ORDER BY buffer_gets DESC
LIMIT 10;
– 查看当前执行的SQL
SQL> SELECT s.sid, s.serial#, s.username, s.program, q.sql_text, q.elapsed_time, q.cpu_time
FROM v$session s, v$sql q
WHERE s.sql_id = q.sql_id
AND s.status = ‘ACTIVE’
ORDER BY q.elapsed_time DESC
LIMIT 10;
– 查看SQL执行统计信息
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
ORDER BY elapsed_time DESC
LIMIT 10;
# 2. 查看SQL执行计划
– 查看SQL执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看SQL详细执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
# 3. 查看SQL执行历史
– 查看SQL执行历史
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE sql_id = ‘xxx’
ORDER BY last_active_time DESC;
– 查看SQL执行统计历史
SQL> SELECT * FROM v$sqlstats WHERE sql_id = ‘xxx’;
# 4. 实际示例
– 查看执行时间最长的SQL
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE elapsed_time > 0
ORDER BY elapsed_time DESC
LIMIT 10;
– 查看逻辑读最多的SQL
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE buffer_gets > 0
ORDER BY buffer_gets DESC
LIMIT 10;
2.2.2 慢查询监控
# 1. 开启慢查询日志
– 查看慢查询配置
SQL> SELECT * FROM v$parameter WHERE name = ‘SLOW_QUERY_TIME’;
– 设置慢查询时间阈值（单位：秒）
SQL> ALTER SYSTEM SET ‘SLOW_QUERY_TIME’ = 2 BOTH;
– 开启慢查询日志
SQL> ALTER SYSTEM SET ‘SLOW_QUERY_LOG’ = 1 BOTH;
# 2. 查看慢查询日志
– 查看慢查询日志文件
$ tail -f /dm/fgdata/fgedudb/log/slow_query.log
– 查看慢查询统计信息
SQL> SELECT * FROM v$sql WHERE elapsed_time > 2000000;
# 3. 分析慢查询
– 查看慢查询的执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看慢查询的资源使用情况
SQL> SELECT sql_text, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE sql_id = ‘xxx’;
# 4. 实际示例
– 查看执行时间超过2秒的SQL
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE elapsed_time > 2000000
ORDER BY elapsed_time DESC
LIMIT 10;
– 查看逻辑读超过10000的SQL
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC
LIMIT 10;
– 查看慢查询配置
SQL> SELECT * FROM v$parameter WHERE name = ‘SLOW_QUERY_TIME’;
– 设置慢查询时间阈值（单位：秒）
SQL> ALTER SYSTEM SET ‘SLOW_QUERY_TIME’ = 2 BOTH;
– 开启慢查询日志
SQL> ALTER SYSTEM SET ‘SLOW_QUERY_LOG’ = 1 BOTH;
# 2. 查看慢查询日志
– 查看慢查询日志文件
$ tail -f /dm/fgdata/fgedudb/log/slow_query.log
– 查看慢查询统计信息
SQL> SELECT * FROM v$sql WHERE elapsed_time > 2000000;
# 3. 分析慢查询
– 查看慢查询的执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看慢查询的资源使用情况
SQL> SELECT sql_text, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE sql_id = ‘xxx’;
# 4. 实际示例
– 查看执行时间超过2秒的SQL
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE elapsed_time > 2000000
ORDER BY elapsed_time DESC
LIMIT 10;
– 查看逻辑读超过10000的SQL
SQL> SELECT sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC
LIMIT 10;
2.3 等待事件监控
2.3.1 等待事件概述
# 等待事件的定义
等待事件是数据库在执行过程中等待资源或操作完成的事件，是性能分析的重要依据。
# 等待事件的分类
1. 用户IO（User I/O）
– 数据文件读取等待
– 数据文件写入等待
– 临时文件读取等待
– 临时文件写入等待
2. 系统IO（System I/O）
– 控制文件读取等待
– 控制文件写入等待
– 日志文件写入等待
– 归档日志等待
3. 并发（Concurrency）
– 锁等待
– 闩锁等待
– 缓冲区等待
– 撤销段等待
4. 网络（Network）
– 网络发送等待
– 网络接收等待
– 网络连接等待
5. CPU（CPU）
– CPU等待
– CPU调度等待
# 等待事件的分析
– 查看等待事件类型
– 查看等待事件次数
– 查看等待事件时间
– 查看等待事件占比
– 查看等待事件的会话
– 查看等待事件的SQL
等待事件是数据库在执行过程中等待资源或操作完成的事件，是性能分析的重要依据。
# 等待事件的分类
1. 用户IO（User I/O）
– 数据文件读取等待
– 数据文件写入等待
– 临时文件读取等待
– 临时文件写入等待
2. 系统IO（System I/O）
– 控制文件读取等待
– 控制文件写入等待
– 日志文件写入等待
– 归档日志等待
3. 并发（Concurrency）
– 锁等待
– 闩锁等待
– 缓冲区等待
– 撤销段等待
4. 网络（Network）
– 网络发送等待
– 网络接收等待
– 网络连接等待
5. CPU（CPU）
– CPU等待
– CPU调度等待
# 等待事件的分析
– 查看等待事件类型
– 查看等待事件次数
– 查看等待事件时间
– 查看等待事件占比
– 查看等待事件的会话
– 查看等待事件的SQL
2.3.2 等待事件监控
# 1. 查看系统等待事件
– 查看系统等待事件统计
SQL> SELECT wait_class, event, total_waits, time_waited, ROUND(time_waited / total_waits, 2) AS avg_wait_time
FROM v$system_event
ORDER BY time_waited DESC
LIMIT 10;
– 查看等待类别统计
SQL> SELECT wait_class, SUM(total_waits) AS total_waits, SUM(time_waited) AS time_waited
FROM v$system_event
GROUP BY wait_class
ORDER BY time_waited DESC;
# 2. 查看会话等待事件
– 查看当前会话等待事件
SQL> SELECT s.sid, s.serial#, s.username, s.program, s.status, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND s.status = ‘WAITING’
ORDER BY se.time_waited DESC
LIMIT 10;
– 查看会话等待详细信息
SQL> SELECT s.sid, s.serial#, s.username, s.program, s.status, sw.event, sw.wait_class, sw.seconds_in_wait, sw.state
FROM v$session s, v$session_wait sw
WHERE s.sid = sw.sid
AND s.status = ‘WAITING’
ORDER BY sw.seconds_in_wait DESC
LIMIT 10;
# 3. 实际示例
– 查看等待时间最长的会话
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.time_waited > 0
ORDER BY se.time_waited DESC
LIMIT 10;
– 查看等待次数最多的会话
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.total_waits > 0
ORDER BY se.total_waits DESC
LIMIT 10;
– 查看系统等待事件统计
SQL> SELECT wait_class, event, total_waits, time_waited, ROUND(time_waited / total_waits, 2) AS avg_wait_time
FROM v$system_event
ORDER BY time_waited DESC
LIMIT 10;
– 查看等待类别统计
SQL> SELECT wait_class, SUM(total_waits) AS total_waits, SUM(time_waited) AS time_waited
FROM v$system_event
GROUP BY wait_class
ORDER BY time_waited DESC;
# 2. 查看会话等待事件
– 查看当前会话等待事件
SQL> SELECT s.sid, s.serial#, s.username, s.program, s.status, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND s.status = ‘WAITING’
ORDER BY se.time_waited DESC
LIMIT 10;
– 查看会话等待详细信息
SQL> SELECT s.sid, s.serial#, s.username, s.program, s.status, sw.event, sw.wait_class, sw.seconds_in_wait, sw.state
FROM v$session s, v$session_wait sw
WHERE s.sid = sw.sid
AND s.status = ‘WAITING’
ORDER BY sw.seconds_in_wait DESC
LIMIT 10;
# 3. 实际示例
– 查看等待时间最长的会话
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.time_waited > 0
ORDER BY se.time_waited DESC
LIMIT 10;
– 查看等待次数最多的会话
SQL> SELECT s.sid, s.serial#, s.username, s.program, se.event, se.total_waits, se.time_waited
FROM v$session s, v$session_event se
WHERE s.sid = se.sid
AND se.total_waits > 0
ORDER BY se.total_waits DESC
LIMIT 10;
生产环境建议：根据业务需求和性能要求，建立完善的监控体系，定期监控数据库的性能指标，及时发现和解决性能问题。使用监控工具，提高监控效率。
Part03-生产环境项目实施方案
3.1 AWR分析
3.1.1 AWR快照管理
# 1. 创建AWR快照
– 手动创建AWR快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
– 查看AWR快照
SQL> SELECT snap_id, snap_time, startup_time FROM dba_hist_snapshot ORDER BY snap_id DESC;
# 2. 删除AWR快照
– 删除指定快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 100, high_snap_id => 200);
– 删除过期快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 1, high_snap_id => 90);
# 3. 修改AWR快照保留时间
– 查看AWR快照保留时间
SQL> SELECT * FROM dba_hist_wr_control;
– 修改AWR快照保留时间（单位：分钟）
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 43200, interval => 60);
# 4. 实际示例
– 创建AWR快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
– 查看最新的AWR快照
SQL> SELECT snap_id, snap_time, startup_time FROM dba_hist_snapshot ORDER BY snap_id DESC LIMIT 10;
– 手动创建AWR快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
– 查看AWR快照
SQL> SELECT snap_id, snap_time, startup_time FROM dba_hist_snapshot ORDER BY snap_id DESC;
# 2. 删除AWR快照
– 删除指定快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 100, high_snap_id => 200);
– 删除过期快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 1, high_snap_id => 90);
# 3. 修改AWR快照保留时间
– 查看AWR快照保留时间
SQL> SELECT * FROM dba_hist_wr_control;
– 修改AWR快照保留时间（单位：分钟）
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 43200, interval => 60);
# 4. 实际示例
– 创建AWR快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
– 查看最新的AWR快照
SQL> SELECT snap_id, snap_time, startup_time FROM dba_hist_snapshot ORDER BY snap_id DESC LIMIT 10;
3.1.2 AWR报告生成
# 1. 生成AWR报告
– 生成AWR报告（HTML格式）
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_snap => 100,
end_snap => 101
));
– 生成AWR报告（文本格式）
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
dbid => 1234567890,
instance_number => 1,
begin_snap => 100,
end_snap => 101
));
# 2. 生成AWR报告到文件
– 生成AWR报告到文件
SQL> SPOOL /tmp/awr_report.html
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_snap => 100,
end_snap => 101
));
SQL> SPOOL OFF;
# 3. 实际示例
– 生成AWR报告
SQL> SELECT dbid, instance_number, snap_id, snap_time FROM dba_hist_snapshot ORDER BY snap_id DESC LIMIT 2;
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_snap => 100,
end_snap => 101
));
– 生成AWR报告（HTML格式）
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_snap => 100,
end_snap => 101
));
– 生成AWR报告（文本格式）
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
dbid => 1234567890,
instance_number => 1,
begin_snap => 100,
end_snap => 101
));
# 2. 生成AWR报告到文件
– 生成AWR报告到文件
SQL> SPOOL /tmp/awr_report.html
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_snap => 100,
end_snap => 101
));
SQL> SPOOL OFF;
# 3. 实际示例
– 生成AWR报告
SQL> SELECT dbid, instance_number, snap_id, snap_time FROM dba_hist_snapshot ORDER BY snap_id DESC LIMIT 2;
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_snap => 100,
end_snap => 101
));
3.1.3 AWR报告分析
# 1. 分析AWR报告的关键指标
– 分析数据库时间
– 查看数据库时间分布
– 查看等待事件占比
– 查看CPU使用情况
– 分析SQL统计信息
– 查看执行次数最多的SQL
– 查看执行时间最长的SQL
– 查看逻辑读最多的SQL
– 查看物理读最多的SQL
– 分析等待事件
– 查看等待事件类型
– 查看等待事件次数
– 查看等待事件时间
– 查看等待事件占比
# 2. 分析AWR报告的资源使用情况
– 分析CPU使用情况
– 查看CPU使用率
– 查看CPU等待时间
– 查看CPU负载情况
– 分析内存使用情况
– 查看SGA使用情况
– 查看PGA使用情况
– 查看缓冲区命中率
– 分析IO使用情况
– 查看IO响应时间
– 查看IO吞吐量
– 查看IO等待时间
# 3. 实际示例
– 查看AWR报告中的Top SQL
SQL> SELECT sql_id, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN 100 AND 101
ORDER BY elapsed_time DESC
LIMIT 10;
– 查看AWR报告中的等待事件
SQL> SELECT event, total_waits, time_waited
FROM dba_hist_system_event
WHERE snap_id BETWEEN 100 AND 101
ORDER BY time_waited DESC
LIMIT 10;
– 分析数据库时间
– 查看数据库时间分布
– 查看等待事件占比
– 查看CPU使用情况
– 分析SQL统计信息
– 查看执行次数最多的SQL
– 查看执行时间最长的SQL
– 查看逻辑读最多的SQL
– 查看物理读最多的SQL
– 分析等待事件
– 查看等待事件类型
– 查看等待事件次数
– 查看等待事件时间
– 查看等待事件占比
# 2. 分析AWR报告的资源使用情况
– 分析CPU使用情况
– 查看CPU使用率
– 查看CPU等待时间
– 查看CPU负载情况
– 分析内存使用情况
– 查看SGA使用情况
– 查看PGA使用情况
– 查看缓冲区命中率
– 分析IO使用情况
– 查看IO响应时间
– 查看IO吞吐量
– 查看IO等待时间
# 3. 实际示例
– 查看AWR报告中的Top SQL
SQL> SELECT sql_id, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN 100 AND 101
ORDER BY elapsed_time DESC
LIMIT 10;
– 查看AWR报告中的等待事件
SQL> SELECT event, total_waits, time_waited
FROM dba_hist_system_event
WHERE snap_id BETWEEN 100 AND 101
ORDER BY time_waited DESC
LIMIT 10;
3.2 ASH分析
3.2.1 ASH数据查询
# 1. 查看ASH数据
– 查看当前ASH数据
SQL> SELECT * FROM v$active_session_history ORDER BY sample_time DESC LIMIT 100;
– 查看历史ASH数据
SQL> SELECT * FROM dba_hist_active_sess_history ORDER BY sample_time DESC LIMIT 100;
# 2. 查看ASH等待事件
– 查看当前ASH等待事件
SQL> SELECT event, wait_class, COUNT(*) AS count
FROM v$active_session_history
WHERE sample_time > SYSDATE – 1/24
GROUP BY event, wait_class
ORDER BY count DESC
LIMIT 10;
– 查看历史ASH等待事件
SQL> SELECT event, wait_class, COUNT(*) AS count
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE – 1
GROUP BY event, wait_class
ORDER BY count DESC
LIMIT 10;
# 3. 查看ASH会话信息
– 查看当前ASH会话信息
SQL> SELECT session_id, session_serial#, user_id, sql_id, event, wait_class
FROM v$active_session_history
WHERE sample_time > SYSDATE – 1/24
ORDER BY sample_time DESC
LIMIT 10;
– 查看历史ASH会话信息
SQL> SELECT session_id, session_serial#, user_id, sql_id, event, wait_class
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE – 1
ORDER BY sample_time DESC
LIMIT 10;
# 4. 实际示例
– 查看最近1小时的ASH等待事件
SQL> SELECT event, wait_class, COUNT(*) AS count
FROM v$active_session_history
WHERE sample_time > SYSDATE – 1/24
GROUP BY event, wait_class
ORDER BY count DESC
LIMIT 10;
– 查看最近24小时的ASH等待事件
SQL> SELECT event, wait_class, COUNT(*) AS count
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE – 1
GROUP BY event, wait_class
ORDER BY count DESC
LIMIT 10;
– 查看当前ASH数据
SQL> SELECT * FROM v$active_session_history ORDER BY sample_time DESC LIMIT 100;
– 查看历史ASH数据
SQL> SELECT * FROM dba_hist_active_sess_history ORDER BY sample_time DESC LIMIT 100;
# 2. 查看ASH等待事件
– 查看当前ASH等待事件
SQL> SELECT event, wait_class, COUNT(*) AS count
FROM v$active_session_history
WHERE sample_time > SYSDATE – 1/24
GROUP BY event, wait_class
ORDER BY count DESC
LIMIT 10;
– 查看历史ASH等待事件
SQL> SELECT event, wait_class, COUNT(*) AS count
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE – 1
GROUP BY event, wait_class
ORDER BY count DESC
LIMIT 10;
# 3. 查看ASH会话信息
– 查看当前ASH会话信息
SQL> SELECT session_id, session_serial#, user_id, sql_id, event, wait_class
FROM v$active_session_history
WHERE sample_time > SYSDATE – 1/24
ORDER BY sample_time DESC
LIMIT 10;
– 查看历史ASH会话信息
SQL> SELECT session_id, session_serial#, user_id, sql_id, event, wait_class
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE – 1
ORDER BY sample_time DESC
LIMIT 10;
# 4. 实际示例
– 查看最近1小时的ASH等待事件
SQL> SELECT event, wait_class, COUNT(*) AS count
FROM v$active_session_history
WHERE sample_time > SYSDATE – 1/24
GROUP BY event, wait_class
ORDER BY count DESC
LIMIT 10;
– 查看最近24小时的ASH等待事件
SQL> SELECT event, wait_class, COUNT(*) AS count
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE – 1
GROUP BY event, wait_class
ORDER BY count DESC
LIMIT 10;
3.2.2 ASH报告生成
# 1. 生成ASH报告
– 生成ASH报告（HTML格式）
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_time => SYSDATE – 1/24,
end_time => SYSDATE
));
– 生成ASH报告（文本格式）
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(
dbid => 1234567890,
instance_number => 1,
begin_time => SYSDATE – 1/24,
end_time => SYSDATE
));
# 2. 生成ASH报告到文件
– 生成ASH报告到文件
SQL> SPOOL /tmp/ash_report.html
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_time => SYSDATE – 1/24,
end_time => SYSDATE
));
SQL> SPOOL OFF;
# 3. 实际示例
– 生成ASH报告
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_time => SYSDATE – 1/24,
end_time => SYSDATE
));
– 生成ASH报告（HTML格式）
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_time => SYSDATE – 1/24,
end_time => SYSDATE
));
– 生成ASH报告（文本格式）
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(
dbid => 1234567890,
instance_number => 1,
begin_time => SYSDATE – 1/24,
end_time => SYSDATE
));
# 2. 生成ASH报告到文件
– 生成ASH报告到文件
SQL> SPOOL /tmp/ash_report.html
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_time => SYSDATE – 1/24,
end_time => SYSDATE
));
SQL> SPOOL OFF;
# 3. 实际示例
– 生成ASH报告
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_time => SYSDATE – 1/24,
end_time => SYSDATE
));
3.3 性能调优
3.3.1 性能调优步骤
# 1. 性能问题识别
– 收集性能数据
– 收集AWR数据
– 收集ASH数据
– 收集SQL性能数据
– 收集资源使用数据
– 分析性能数据
– 分析AWR报告
– 分析ASH报告
– 分析SQL性能数据
– 分析资源使用数据
– 识别性能问题
– 识别慢查询
– 识别资源瓶颈
– 识别等待事件
– 识别性能瓶颈
# 2. 性能问题分析
– 分析慢查询
– 查看执行计划
– 查看统计信息
– 查看索引使用情况
– 查看资源使用情况
– 分析资源瓶颈
– 分析CPU瓶颈
– 分析内存瓶颈
– 分析IO瓶颈
– 分析网络瓶颈
– 分析等待事件
– 分析等待事件类型
– 分析等待事件次数
– 分析等待事件时间
– 分析等待事件占比
# 3. 性能问题解决
– 优化SQL语句
– 创建索引
– 重写SQL语句
– 优化执行计划
– 使用批量操作
– 优化数据库参数
– 优化内存参数
– 优化IO参数
– 优化网络参数
– 优化并发参数
– 优化系统资源
– 增加CPU资源
– 增加内存资源
– 优化IO性能
– 优化网络性能
– 收集性能数据
– 收集AWR数据
– 收集ASH数据
– 收集SQL性能数据
– 收集资源使用数据
– 分析性能数据
– 分析AWR报告
– 分析ASH报告
– 分析SQL性能数据
– 分析资源使用数据
– 识别性能问题
– 识别慢查询
– 识别资源瓶颈
– 识别等待事件
– 识别性能瓶颈
# 2. 性能问题分析
– 分析慢查询
– 查看执行计划
– 查看统计信息
– 查看索引使用情况
– 查看资源使用情况
– 分析资源瓶颈
– 分析CPU瓶颈
– 分析内存瓶颈
– 分析IO瓶颈
– 分析网络瓶颈
– 分析等待事件
– 分析等待事件类型
– 分析等待事件次数
– 分析等待事件时间
– 分析等待事件占比
# 3. 性能问题解决
– 优化SQL语句
– 创建索引
– 重写SQL语句
– 优化执行计划
– 使用批量操作
– 优化数据库参数
– 优化内存参数
– 优化IO参数
– 优化网络参数
– 优化并发参数
– 优化系统资源
– 增加CPU资源
– 增加内存资源
– 优化IO性能
– 优化网络性能
3.3.2 性能调优方法
# 1. SQL优化
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 重写SQL语句
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 优化执行计划
SQL> SELECT /*+ INDEX(fgedu_user idx_fgedu_user_status) */ user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 使用批量操作
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
(3, ‘user3’, ‘user3@fgedu.net.cn’);
# 2. 参数优化
– 优化内存参数
SQL> ALTER SYSTEM SET ‘MEMORY_POOL’ = 8192 BOTH;
SQL> ALTER SYSTEM SET ‘BUFFER’ = 16384 BOTH;
– 优化IO参数
SQL> ALTER SYSTEM SET ‘MAX_BUFFER_SIZE’ = 1024 BOTH;
SQL> ALTER SYSTEM SET ‘IO_THROTTLE_THRESHOLD’ = 1000 BOTH;
– 优化网络参数
SQL> ALTER SYSTEM SET ‘MAX_PACKET_SIZE’ = 32768 BOTH;
SQL> ALTER SYSTEM SET ‘MAX_SESSIONS’ = 1000 BOTH;
# 3. 系统优化
– 增加CPU资源
– 升级CPU
– 增加CPU核心数
– 优化CPU调度
– 增加内存资源
– 升级内存
– 增加内存容量
– 优化内存使用
– 优化IO性能
– 升级存储设备
– 使用SSD
– 优化IO调度
– 优化网络性能
– 升级网络设备
– 增加网络带宽
– 优化网络配置
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 重写SQL语句
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 优化执行计划
SQL> SELECT /*+ INDEX(fgedu_user idx_fgedu_user_status) */ user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 使用批量操作
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
(3, ‘user3’, ‘user3@fgedu.net.cn’);
# 2. 参数优化
– 优化内存参数
SQL> ALTER SYSTEM SET ‘MEMORY_POOL’ = 8192 BOTH;
SQL> ALTER SYSTEM SET ‘BUFFER’ = 16384 BOTH;
– 优化IO参数
SQL> ALTER SYSTEM SET ‘MAX_BUFFER_SIZE’ = 1024 BOTH;
SQL> ALTER SYSTEM SET ‘IO_THROTTLE_THRESHOLD’ = 1000 BOTH;
– 优化网络参数
SQL> ALTER SYSTEM SET ‘MAX_PACKET_SIZE’ = 32768 BOTH;
SQL> ALTER SYSTEM SET ‘MAX_SESSIONS’ = 1000 BOTH;
# 3. 系统优化
– 增加CPU资源
– 升级CPU
– 增加CPU核心数
– 优化CPU调度
– 增加内存资源
– 升级内存
– 增加内存容量
– 优化内存使用
– 优化IO性能
– 升级存储设备
– 使用SSD
– 优化IO调度
– 优化网络性能
– 升级网络设备
– 增加网络带宽
– 优化网络配置
风哥提示：性能调优是一个系统性的工作，需要从多个方面考虑，包括SQL优化、参数优化、系统优化等。掌握性能调优的方法，是提高数据库性能的关键。
Part04-生产案例与实战讲解
4.1 CPU性能问题分析案例
4.1.1 案例描述
某企业DM数据库出现CPU使用率过高的问题，经分析发现CPU使用率达到100%，导致数据库响应缓慢，需要解决CPU性能问题。
4.1.2 分析步骤
# 1. 问题分析
– 查看CPU使用情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%CPU%’;
# 输出结果
# NAME VALUE
# ————————————- ———-
# CPU used by this session 1000000
# CPU used when call started 1000000
# parse time cpu 100000
– 查看会话CPU使用情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, ss.value AS cpu_usage
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic
#
AND sn.name = ‘CPU used by this session’
ORDER BY ss.value DESC
LIMIT 10;
# 输出结果
# SID SERIAL
# USERNAME PROGRAM CPU_USAGE
# —- ——– ——— ———— ———-
# 100 12345 fgedu fgedu_app 500000
# 101 12346 fgedu fgedu_app 300000
# 102 12347 fgedu fgedu_app 200000
– 查看会话执行的SQL
SQL> SELECT s.sid, s.serial#, s.username, s.program, q.sql_text, q.elapsed_time, q.cpu_time
FROM v$session s, v$sql q
WHERE s.sql_id = q.sql_id
AND s.sid IN (100, 101, 102)
ORDER BY q.cpu_time DESC;
# 输出结果
# SID SERIAL
# USERNAME PROGRAM SQL_TEXT ELAPSED_TIME CPU_TIME
# —- ——– ——— ———— —————————————– ————- ———-
# 100 12345 fgedu fgedu_app SELECT * FROM fgedu_user 1000000 500000
# 101 12346 fgedu fgedu_app SELECT * FROM fgedu_user 600000 300000
# 102 12347 fgedu fgedu_app SELECT * FROM fgedu_user 400000 200000
# 2. 优化方案
– 查看表结构
SQL> DESC fgedu_user;
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 优化SQL语句，只查询需要的字段
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化SQL语句，只查询需要的字段
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 4. 效果验证
– 查看CPU使用情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%CPU%’;
# 输出结果
# NAME VALUE
# ————————————- ———-
# CPU used by this session 100000
# CPU used when call started 100000
# parse time cpu 10000
– 查看会话CPU使用情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, ss.value AS cpu_usage
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic
#
AND sn.name = ‘CPU used by this session’
ORDER BY ss.value DESC
LIMIT 10;
# 输出结果
# SID SERIAL
# USERNAME PROGRAM CPU_USAGE
# —- ——– ——— ———— ———-
# 100 12345 fgedu fgedu_app 50000
# 101 12346 fgedu fgedu_app 30000
# 102 12347 fgedu fgedu_app 20000
# 5. 实施结果
– CPU使用率从100%降低到10%
– SQL执行时间从1000ms降低到100ms
– CPU使用量从1000000降低到100000
– 性能提升90%
– 查看CPU使用情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%CPU%’;
# 输出结果
# NAME VALUE
# ————————————- ———-
# CPU used by this session 1000000
# CPU used when call started 1000000
# parse time cpu 100000
– 查看会话CPU使用情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, ss.value AS cpu_usage
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic
#
AND sn.name = ‘CPU used by this session’
ORDER BY ss.value DESC
LIMIT 10;
# 输出结果
# SID SERIAL
# USERNAME PROGRAM CPU_USAGE
# —- ——– ——— ———— ———-
# 100 12345 fgedu fgedu_app 500000
# 101 12346 fgedu fgedu_app 300000
# 102 12347 fgedu fgedu_app 200000
– 查看会话执行的SQL
SQL> SELECT s.sid, s.serial#, s.username, s.program, q.sql_text, q.elapsed_time, q.cpu_time
FROM v$session s, v$sql q
WHERE s.sql_id = q.sql_id
AND s.sid IN (100, 101, 102)
ORDER BY q.cpu_time DESC;
# 输出结果
# SID SERIAL
# USERNAME PROGRAM SQL_TEXT ELAPSED_TIME CPU_TIME
# —- ——– ——— ———— —————————————– ————- ———-
# 100 12345 fgedu fgedu_app SELECT * FROM fgedu_user 1000000 500000
# 101 12346 fgedu fgedu_app SELECT * FROM fgedu_user 600000 300000
# 102 12347 fgedu fgedu_app SELECT * FROM fgedu_user 400000 200000
# 2. 优化方案
– 查看表结构
SQL> DESC fgedu_user;
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 优化SQL语句，只查询需要的字段
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化SQL语句，只查询需要的字段
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 4. 效果验证
– 查看CPU使用情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%CPU%’;
# 输出结果
# NAME VALUE
# ————————————- ———-
# CPU used by this session 100000
# CPU used when call started 100000
# parse time cpu 10000
– 查看会话CPU使用情况
SQL> SELECT s.sid, s.serial#, s.username, s.program, ss.value AS cpu_usage
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic
#
AND sn.name = ‘CPU used by this session’
ORDER BY ss.value DESC
LIMIT 10;
# 输出结果
# SID SERIAL
# USERNAME PROGRAM CPU_USAGE
# —- ——– ——— ———— ———-
# 100 12345 fgedu fgedu_app 50000
# 101 12346 fgedu fgedu_app 30000
# 102 12347 fgedu fgedu_app 20000
# 5. 实施结果
– CPU使用率从100%降低到10%
– SQL执行时间从1000ms降低到100ms
– CPU使用量从1000000降低到100000
– 性能提升90%
4.2 内存性能问题分析案例
4.2.1 案例描述
某企业DM数据库出现内存使用率过高的问题，经分析发现内存使用率达到95%，导致数据库响应缓慢，需要解决内存性能问题。
4.2.2 分析步骤
# 1. 问题分析
– 查看内存使用情况
SQL> SELECT * FROM v$sgastat;
# 输出结果
# POOL NAME BYTES
# ———— ————————————- ———-
# shared pool free memory 104857600
# shared pool SQL area 1048576000
# shared pool library cache 524288000
# buffer pool free memory 104857600
# buffer pool buffer cache 10737418240
– 查看缓冲区命中率
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN (‘consistent gets’, ‘db block gets’, ‘physical reads’);
# 输出结果
# NAME VALUE
# —————– ———-
# consistent gets 10000000
# db block gets 1000000
# physical reads 5000000
– 计算缓冲区命中率
SQL> SELECT
(SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’) AS logical_reads,
(SELECT value FROM v$sysstat WHERE name = ‘physical reads’) AS physical_reads,
ROUND((1 – (SELECT value FROM v$sysstat WHERE name = ‘physical reads’) /
((SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’))) * 100, 2) AS buffer_hit_ratio
FROM dual;
# 输出结果
# LOGICAL_READS PHYSICAL_READS BUFFER_HIT_RATIO
# ————- ————— —————-
# 11000000 5000000 54.55
# 2. 优化方案
– 增加缓冲区大小
SQL> ALTER SYSTEM SET ‘BUFFER’ = 32768 BOTH;
– 优化SQL语句，减少物理读
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
# 3. 实施步骤
– 增加缓冲区大小
SQL> ALTER SYSTEM SET ‘BUFFER’ = 32768 BOTH;
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化SQL语句，只查询需要的字段
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 4. 效果验证
– 查看内存使用情况
SQL> SELECT * FROM v$sgastat;
# 输出结果
# POOL NAME BYTES
# ———— ————————————- ———-
# shared pool free memory 104857600
# shared pool SQL area 1048576000
# shared pool library cache 524288000
# buffer pool free memory 209715200
# buffer pool buffer cache 21474836480
– 查看缓冲区命中率
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN (‘consistent gets’, ‘db block gets’, ‘physical reads’);
# 输出结果
# NAME VALUE
# —————– ———-
# consistent gets 10000000
# db block gets 1000000
# physical reads 1000000
– 计算缓冲区命中率
SQL> SELECT
(SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’) AS logical_reads,
(SELECT value FROM v$sysstat WHERE name = ‘physical reads’) AS physical_reads,
ROUND((1 – (SELECT value FROM v$sysstat WHERE name = ‘physical reads’) /
((SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’))) * 100, 2) AS buffer_hit_ratio
FROM dual;
# 输出结果
# LOGICAL_READS PHYSICAL_READS BUFFER_HIT_RATIO
# ————- ————— —————-
# 11000000 1000000 90.91
# 5. 实施结果
– 缓冲区命中率从54.55%提升到90.91%
– 物理读从5000000降低到1000000
– 内存使用率从95%降低到80%
– 性能提升67%
– 查看内存使用情况
SQL> SELECT * FROM v$sgastat;
# 输出结果
# POOL NAME BYTES
# ———— ————————————- ———-
# shared pool free memory 104857600
# shared pool SQL area 1048576000
# shared pool library cache 524288000
# buffer pool free memory 104857600
# buffer pool buffer cache 10737418240
– 查看缓冲区命中率
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN (‘consistent gets’, ‘db block gets’, ‘physical reads’);
# 输出结果
# NAME VALUE
# —————– ———-
# consistent gets 10000000
# db block gets 1000000
# physical reads 5000000
– 计算缓冲区命中率
SQL> SELECT
(SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’) AS logical_reads,
(SELECT value FROM v$sysstat WHERE name = ‘physical reads’) AS physical_reads,
ROUND((1 – (SELECT value FROM v$sysstat WHERE name = ‘physical reads’) /
((SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’))) * 100, 2) AS buffer_hit_ratio
FROM dual;
# 输出结果
# LOGICAL_READS PHYSICAL_READS BUFFER_HIT_RATIO
# ————- ————— —————-
# 11000000 5000000 54.55
# 2. 优化方案
– 增加缓冲区大小
SQL> ALTER SYSTEM SET ‘BUFFER’ = 32768 BOTH;
– 优化SQL语句，减少物理读
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
# 3. 实施步骤
– 增加缓冲区大小
SQL> ALTER SYSTEM SET ‘BUFFER’ = 32768 BOTH;
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化SQL语句，只查询需要的字段
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 4. 效果验证
– 查看内存使用情况
SQL> SELECT * FROM v$sgastat;
# 输出结果
# POOL NAME BYTES
# ———— ————————————- ———-
# shared pool free memory 104857600
# shared pool SQL area 1048576000
# shared pool library cache 524288000
# buffer pool free memory 209715200
# buffer pool buffer cache 21474836480
– 查看缓冲区命中率
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN (‘consistent gets’, ‘db block gets’, ‘physical reads’);
# 输出结果
# NAME VALUE
# —————– ———-
# consistent gets 10000000
# db block gets 1000000
# physical reads 1000000
– 计算缓冲区命中率
SQL> SELECT
(SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’) AS logical_reads,
(SELECT value FROM v$sysstat WHERE name = ‘physical reads’) AS physical_reads,
ROUND((1 – (SELECT value FROM v$sysstat WHERE name = ‘physical reads’) /
((SELECT value FROM v$sysstat WHERE name = ‘consistent gets’) +
(SELECT value FROM v$sysstat WHERE name = ‘db block gets’))) * 100, 2) AS buffer_hit_ratio
FROM dual;
# 输出结果
# LOGICAL_READS PHYSICAL_READS BUFFER_HIT_RATIO
# ————- ————— —————-
# 11000000 1000000 90.91
# 5. 实施结果
– 缓冲区命中率从54.55%提升到90.91%
– 物理读从5000000降低到1000000
– 内存使用率从95%降低到80%
– 性能提升67%
4.3 IO性能问题分析案例
4.3.1 案例描述
某企业DM数据库出现IO性能问题，经分析发现IO响应时间过长，导致数据库响应缓慢，需要解决IO性能问题。
4.3.2 分析步骤
# 1. 问题分析
– 查看IO使用情况
SQL> SELECT * FROM v$filestat;
# 输出结果
# FILE
# PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT READTIM WRITETIM
# —— ——- ——– ——— ———- ——– ———
# 1 100000 10000 100000 10000 1000000 100000
# 2 100000 10000 100000 10000 1000000 100000
# 3 100000 10000 100000 10000 1000000 100000
– 查看IO响应时间
SQL> SELECT file_name, phyrds, phywrts, phyblkrd, phyblkwrt,
ROUND(readtim / phyrds, 2) AS avg_read_time,
ROUND(writetim / phywrts, 2) AS avg_write_time
FROM v$filestat f, dba_data_files d
WHERE f.file# = d.file_id
ORDER BY readtim DESC;
# 输出结果
# FILE_NAME PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT AVG_READ_TIME AVG_WRITE_TIME
# ————————– ——- ——– ——— ———- ————– —————
# /dm/data/fgedudb01.dbf 100000 10000 100000 10000 10.00 10.00
# /dm/data/fgedudb02.dbf 100000 10000 100000 10000 10.00 10.00
# /dm/data/fgedudb03.dbf 100000 10000 100000 10000 10.00 10.00
– 查看IO等待情况
SQL> SELECT event, total_waits, time_waited, ROUND(time_waited / total_waits, 2) AS avg_wait_time
FROM v$system_event
WHERE wait_class = ‘User I/O’
ORDER BY time_waited DESC;
# 输出结果
# EVENT TOTAL_WAITS TIME_WAITED AVG_WAIT_TIME
# —————————– ———— ————- ————–
# db file sequential read 100000 1000000 10.00
# db file scattered read 100000 1000000 10.00
# db file parallel write 10000 100000 10.00
# 2. 优化方案
– 优化SQL语句，减少物理读
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 增加缓冲区大小
SQL> ALTER SYSTEM SET ‘BUFFER’ = 32768 BOTH;
– 优化IO参数
SQL> ALTER SYSTEM SET ‘MAX_BUFFER_SIZE’ = 1024 BOTH;
SQL> ALTER SYSTEM SET ‘IO_THROTTLE_THRESHOLD’ = 1000 BOTH;
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 增加缓冲区大小
SQL> ALTER SYSTEM SET ‘BUFFER’ = 32768 BOTH;
– 优化IO参数
SQL> ALTER SYSTEM SET ‘MAX_BUFFER_SIZE’ = 1024 BOTH;
SQL> ALTER SYSTEM SET ‘IO_THROTTLE_THRESHOLD’ = 1000 BOTH;
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化SQL语句，只查询需要的字段
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 4. 效果验证
– 查看IO使用情况
SQL> SELECT * FROM v$filestat;
# 输出结果
# FILE
# PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT READTIM WRITETIM
# —— ——- ——– ——— ———- ——– ———
# 1 10000 1000 10000 1000 10000 10000
# 2 10000 1000 10000 1000 10000 10000
# 3 10000 1000 10000 1000 10000 10000
– 查看IO响应时间
SQL> SELECT file_name, phyrds, phywrts, phyblkrd, phyblkwrt,
ROUND(readtim / phyrds, 2) AS avg_read_time,
ROUND(writetim / phywrts, 2) AS avg_write_time
FROM v$filestat f, dba_data_files d
WHERE f.file# = d.file_id
ORDER BY readtim DESC;
# 输出结果
# FILE_NAME PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT AVG_READ_TIME AVG_WRITE_TIME
# ————————– ——- ——– ——— ———- ————– —————
# /dm/data/fgedudb01.dbf 10000 1000 10000 1000 1.00 10.00
# /dm/data/fgedudb02.dbf 10000 1000 10000 1000 1.00 10.00
# /dm/data/fgedudb03.dbf 10000 1000 10000 1000 1.00 10.00
– 查看IO等待情况
SQL> SELECT event, total_waits, time_waited, ROUND(time_waited / total_waits, 2) AS avg_wait_time
FROM v$system_event
WHERE wait_class = ‘User I/O’
ORDER BY time_waited DESC;
# 输出结果
# EVENT TOTAL_WAITS TIME_WAITED AVG_WAIT_TIME
# —————————– ———— ————- ————–
# db file sequential read 10000 10000 1.00
# db file scattered read 10000 10000 1.00
# db file parallel write 1000 10000 10.00
# 5. 实施结果
– IO响应时间从10ms降低到1ms
– 物理读从100000降低到10000
– IO等待时间从1000000降低到10000
– 性能提升90%
– 查看IO使用情况
SQL> SELECT * FROM v$filestat;
# 输出结果
# FILE
# PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT READTIM WRITETIM
# —— ——- ——– ——— ———- ——– ———
# 1 100000 10000 100000 10000 1000000 100000
# 2 100000 10000 100000 10000 1000000 100000
# 3 100000 10000 100000 10000 1000000 100000
– 查看IO响应时间
SQL> SELECT file_name, phyrds, phywrts, phyblkrd, phyblkwrt,
ROUND(readtim / phyrds, 2) AS avg_read_time,
ROUND(writetim / phywrts, 2) AS avg_write_time
FROM v$filestat f, dba_data_files d
WHERE f.file# = d.file_id
ORDER BY readtim DESC;
# 输出结果
# FILE_NAME PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT AVG_READ_TIME AVG_WRITE_TIME
# ————————– ——- ——– ——— ———- ————– —————
# /dm/data/fgedudb01.dbf 100000 10000 100000 10000 10.00 10.00
# /dm/data/fgedudb02.dbf 100000 10000 100000 10000 10.00 10.00
# /dm/data/fgedudb03.dbf 100000 10000 100000 10000 10.00 10.00
– 查看IO等待情况
SQL> SELECT event, total_waits, time_waited, ROUND(time_waited / total_waits, 2) AS avg_wait_time
FROM v$system_event
WHERE wait_class = ‘User I/O’
ORDER BY time_waited DESC;
# 输出结果
# EVENT TOTAL_WAITS TIME_WAITED AVG_WAIT_TIME
# —————————– ———— ————- ————–
# db file sequential read 100000 1000000 10.00
# db file scattered read 100000 1000000 10.00
# db file parallel write 10000 100000 10.00
# 2. 优化方案
– 优化SQL语句，减少物理读
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 增加缓冲区大小
SQL> ALTER SYSTEM SET ‘BUFFER’ = 32768 BOTH;
– 优化IO参数
SQL> ALTER SYSTEM SET ‘MAX_BUFFER_SIZE’ = 1024 BOTH;
SQL> ALTER SYSTEM SET ‘IO_THROTTLE_THRESHOLD’ = 1000 BOTH;
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 增加缓冲区大小
SQL> ALTER SYSTEM SET ‘BUFFER’ = 32768 BOTH;
– 优化IO参数
SQL> ALTER SYSTEM SET ‘MAX_BUFFER_SIZE’ = 1024 BOTH;
SQL> ALTER SYSTEM SET ‘IO_THROTTLE_THRESHOLD’ = 1000 BOTH;
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化SQL语句，只查询需要的字段
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 4. 效果验证
– 查看IO使用情况
SQL> SELECT * FROM v$filestat;
# 输出结果
# FILE
# PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT READTIM WRITETIM
# —— ——- ——– ——— ———- ——– ———
# 1 10000 1000 10000 1000 10000 10000
# 2 10000 1000 10000 1000 10000 10000
# 3 10000 1000 10000 1000 10000 10000
– 查看IO响应时间
SQL> SELECT file_name, phyrds, phywrts, phyblkrd, phyblkwrt,
ROUND(readtim / phyrds, 2) AS avg_read_time,
ROUND(writetim / phywrts, 2) AS avg_write_time
FROM v$filestat f, dba_data_files d
WHERE f.file# = d.file_id
ORDER BY readtim DESC;
# 输出结果
# FILE_NAME PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT AVG_READ_TIME AVG_WRITE_TIME
# ————————– ——- ——– ——— ———- ————– —————
# /dm/data/fgedudb01.dbf 10000 1000 10000 1000 1.00 10.00
# /dm/data/fgedudb02.dbf 10000 1000 10000 1000 1.00 10.00
# /dm/data/fgedudb03.dbf 10000 1000 10000 1000 1.00 10.00
– 查看IO等待情况
SQL> SELECT event, total_waits, time_waited, ROUND(time_waited / total_waits, 2) AS avg_wait_time
FROM v$system_event
WHERE wait_class = ‘User I/O’
ORDER BY time_waited DESC;
# 输出结果
# EVENT TOTAL_WAITS TIME_WAITED AVG_WAIT_TIME
# —————————– ———— ————- ————–
# db file sequential read 10000 10000 1.00
# db file scattered read 10000 10000 1.00
# db file parallel write 1000 10000 10.00
# 5. 实施结果
– IO响应时间从10ms降低到1ms
– 物理读从100000降低到10000
– IO等待时间从1000000降低到10000
– 性能提升90%
生产环境建议：根据业务需求和性能要求，建立完善的监控体系，定期监控数据库的性能指标，及时发现和解决性能问题。使用监控工具，提高监控效率。建立性能调优流程，确保性能问题得到及时解决。
Part05-风哥经验总结与分享
5.1 性能监控最佳实践
DM数据库性能监控最佳实践：
- 建立监控体系：建立完善的监控体系，覆盖所有关键性能指标
- 定期监控：定期监控数据库的性能指标，及时发现性能问题
- 设置告警阈值：为关键性能指标设置告警阈值，及时发现异常
- 使用监控工具：使用监控工具，提高监控效率
- 分析性能数据：分析性能数据，找出性能瓶颈
- 优化性能：根据性能分析结果，优化数据库性能
- 持续改进：持续改进监控体系，提高监控效果
- 文档记录：记录性能问题和解决方案，积累经验
- 团队协作：与团队协作，共同解决性能问题
- 学习提升：学习新的监控技术和方法，提升监控能力
5.2 常见问题与解决方案
# 1. CPU性能问题
– 症状：CPU使用率过高
– 原因：SQL执行效率低、并发连接过多、系统资源不足
– 解决方案：优化SQL语句、优化并发连接、增加CPU资源
# 2. 内存性能问题
– 症状：内存使用率过高
– 原因：缓冲区命中率低、SQL执行效率低、内存配置不合理
– 解决方案：优化SQL语句、增加缓冲区大小、优化内存配置
# 3. IO性能问题
– 症状：IO响应时间过长
– 原因：物理读过多、IO配置不合理、存储性能不足
– 解决方案：优化SQL语句、优化IO配置、升级存储设备
# 4. 网络性能问题
– 症状：网络延迟过高
– 原因：网络带宽不足、网络配置不合理、网络设备性能不足
– 解决方案：优化网络配置、增加网络带宽、升级网络设备
# 5. SQL性能问题
– 症状：SQL执行时间过长
– 原因：缺少索引、SQL语句不合理、统计信息不准确
– 解决方案：创建索引、优化SQL语句、更新统计信息
– 症状：CPU使用率过高
– 原因：SQL执行效率低、并发连接过多、系统资源不足
– 解决方案：优化SQL语句、优化并发连接、增加CPU资源
# 2. 内存性能问题
– 症状：内存使用率过高
– 原因：缓冲区命中率低、SQL执行效率低、内存配置不合理
– 解决方案：优化SQL语句、增加缓冲区大小、优化内存配置
# 3. IO性能问题
– 症状：IO响应时间过长
– 原因：物理读过多、IO配置不合理、存储性能不足
– 解决方案：优化SQL语句、优化IO配置、升级存储设备
# 4. 网络性能问题
– 症状：网络延迟过高
– 原因：网络带宽不足、网络配置不合理、网络设备性能不足
– 解决方案：优化网络配置、增加网络带宽、升级网络设备
# 5. SQL性能问题
– 症状：SQL执行时间过长
– 原因：缺少索引、SQL语句不合理、统计信息不准确
– 解决方案：创建索引、优化SQL语句、更新统计信息
5.3 性能监控检查清单
DM数据库性能监控检查清单：
- CPU监控检查：CPU使用率是否在合理范围内
- 内存监控检查：内存使用率是否在合理范围内，缓冲区命中率是否合理
- IO监控检查：IO响应时间是否在合理范围内，IO吞吐量是否满足需求
- 网络监控检查：网络延迟是否在合理范围内，网络带宽是否满足需求
- SQL监控检查：SQL执行时间是否在合理范围内，是否存在慢查询
- 等待事件监控检查：等待事件时间是否在合理范围内，是否存在异常等待
- AWR报告检查：AWR报告是否定期生成，关键指标是否正常
- ASH报告检查：ASH报告是否定期生成，关键指标是否正常
- 告警检查：告警是否及时触发，告警处理是否及时
- 性能指标检查：性能指标是否在合理范围内，是否存在异常
持续改进：性能监控是一个持续的过程，需要根据业务需求和系统运行情况，不断调整和优化监控体系，确保系统的高性能和稳定性。建立完善的监控体系，是保障数据库稳定运行的关键。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
