Oracle 资源使用分析

资源使用分析概述

资源使用分析是Oracle数据库性能优化的基础，通过监控和分析CPU、内存、I/O、存储等关键资源的使用情况，可以及时发现性能瓶颈，采取相应的优化措施。有效的资源使用分析能够帮助DBA确保数据库系统高效、稳定地运行，避免因资源争用导致的性能下降或系统故障。

CPU 资源分析

CPU 使用率监控

查看实时 CPU 使用率

sql

-- 查看实例级 CPU 使用率
SELECT
    instance_name,
    ROUND(value / 100, 2) AS cpu_usage_pct
FROM
    gv$sysmetric
WHERE
    metric_name = 'CPU Usage Per Sec'
    AND group_id = 2;

-- 查看会话级 CPU 使用率
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.status,
    ROUND(s.cpu_time / 1000000, 2) AS cpu_time_seconds
FROM
    v$session s
WHERE
    s.cpu_time > 0
    AND s.username IS NOT NULL
ORDER BY
    s.cpu_time DESC;

使用 AWR 报告分析 CPU 使用率

AWR报告中的"CPU Statistics"部分提供了CPU使用的详细统计信息，包括：

CPU 使用率趋势
CPU 瓶颈分析
消耗 CPU 最多的 SQL 语句

使用 ASH 报告分析 CPU 密集型会话

sql

-- 查找最近 30 分钟内消耗 CPU 最多的会话
SELECT
    s.sql_id,
    s.session_id,
    s.user_id,
    COUNT(*) AS sample_count,
    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM v$active_session_history WHERE sample_time > SYSDATE - 30/1440), 2) AS cpu_pct
FROM
    v$active_session_history s
WHERE
    s.sample_time > SYSDATE - 30/1440
    AND s.wait_class = 'CPU'
GROUP BY
    s.sql_id,
    s.session_id,
    s.user_id
ORDER BY
    sample_count DESC;

CPU 瓶颈识别

常见 CPU 瓶颈症状

系统 CPU 使用率持续超过 90%
大量会话处于 "CPU + Wait" 状态
响应时间明显延长
数据库吞吐量下降

识别 CPU 密集型 SQL

sql

-- 查找消耗 CPU 最多的 SQL 语句
SELECT
    sql_id,
    plan_hash_value,
    executions,
    buffer_gets,
    disk_reads,
    ROUND(cpu_time / 1000000, 2) AS cpu_time_seconds,
    ROUND(elapsed_time / 1000000, 2) AS elapsed_time_seconds
FROM
    v$sql
WHERE
    executions > 0
ORDER BY
    cpu_time DESC;

CPU 优化策略

SQL 优化：
- 优化消耗 CPU 最多的 SQL 语句
- 减少硬解析，使用绑定变量
- 优化执行计划，减少逻辑读
实例参数调整：
- 调整 cpu_count 参数，匹配实际 CPU 核心数
- 调整 parallel_max_servers 参数，控制并行执行进程数
- 调整 cursor_sharing 参数，减少硬解析
操作系统优化：
- 确保操作系统有足够的空闲 CPU 资源
- 调整操作系统调度策略
- 避免在数据库服务器上运行其他 CPU 密集型应用

内存资源分析

内存结构概述

Oracle 数据库的内存结构主要包括：

SGA (System Global Area)：共享内存区域
- 数据库缓冲区高速缓存 (DB Buffer Cache)
- 共享池 (Shared Pool)
- 大型池 (Large Pool)
- Java 池 (Java Pool)
- 流池 (Streams Pool)
- 日志缓冲区 (Redo Log Buffer)
PGA (Program Global Area)：私有内存区域
- 排序区
- 哈希区
- 会话内存
- SQL 工作区

SGA 使用率监控

查看 SGA 组件大小和使用率

sql

-- 查看 SGA 总体大小
SELECT
    name,
    value / 1024 / 1024 AS size_mb
FROM
    v$sga;

-- 查看 SGA 组件详细信息
SELECT
    component,
    current_size / 1024 / 1024 AS current_size_mb,
    min_size / 1024 / 1024 AS min_size_mb,
    max_size / 1024 / 1024 AS max_size_mb
FROM
    v$sga_dynamic_components;

-- 查看共享池使用率
SELECT
    ROUND((1 - (free_memory / total_memory)) * 100, 2) AS shared_pool_usage_pct
FROM
    v$shared_pool_reserved;

-- 查看数据库缓冲区高速缓存命中率
SELECT
    ROUND((1 - (physical_reads / (db_block_gets + consistent_gets))) * 100, 2) AS buffer_cache_hit_ratio
FROM
    v$sysstat
WHERE
    name IN ('physical reads', 'db block gets', 'consistent gets');

PGA 使用率监控

查看 PGA 总体使用情况

sql

-- 查看 PGA 统计信息
SELECT
    name,
    value / 1024 / 1024 AS size_mb
FROM
    v$pgastat;

-- 查看 PGA 使用率
SELECT
    ROUND(pga_used_mem / pga_alloc_mem * 100, 2) AS pga_used_pct,
    ROUND(pga_used_mem / pga_max_mem * 100, 2) AS pga_max_pct
FROM
    v$process;

识别 PGA 密集型会话

sql

-- 查找 PGA 使用最多的会话
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    p.pga_used_mem / 1024 / 1024 AS pga_used_mb,
    p.pga_alloc_mem / 1024 / 1024 AS pga_alloc_mb
FROM
    v$session s
    JOIN v$process p ON s.paddr = p.addr
WHERE
    s.username IS NOT NULL
ORDER BY
    p.pga_used_mem DESC;

内存瓶颈识别与优化

SGA 瓶颈症状

共享池碎片严重
数据库缓冲区高速缓存命中率低于 90%
频繁的硬解析
大量的 "library cache pin" 或 "library cache lock" 等待事件

PGA 瓶颈症状

大量的 "direct path read" 或 "direct path write" 等待事件
频繁的排序溢出到磁盘
PGA 使用率持续超过 90%

内存优化策略

SGA 优化：
- 调整 sga_target 或 memory_target 参数，使用自动内存管理
- 根据数据库缓冲区高速缓存命中率调整 db_cache_size
- 根据共享池使用率调整 shared_pool_size
- 对于 RAC 环境，调整 global_cache_size
PGA 优化：
- 调整 pga_aggregate_target 参数
- 优化排序操作，减少排序数据量
- 调整 sort_area_size 和 hash_area_size 参数（如果不使用自动 PGA 管理）
- 避免在内存中处理大量数据

I/O 资源分析

I/O 性能监控

查看磁盘 I/O 统计信息

sql

-- 查看磁盘 I/O 使用率
SELECT
    df.tablespace_name,
    df.file_name,
    f.phyrds,
    f.phywrts,
    f.readtim,
    f.writetim,
    ROUND((f.readtim / f.phyrds), 2) AS avg_read_time_ms,
    ROUND((f.writetim / f.phywrts), 2) AS avg_write_time_ms
FROM
    v$filestat f
    JOIN dba_data_files df ON f.file# = df.file_id
WHERE
    f.phyrds > 0
    AND f.phywrts > 0
ORDER BY
    avg_read_time_ms DESC;

-- 查看 I/O 等待事件
SELECT
    event,
    total_waits,
    time_waited_micro / 1000000 AS time_waited_seconds,
    ROUND(average_wait / 1000, 2) AS avg_wait_ms
FROM
    v$system_event
WHERE
    wait_class = 'User I/O'
ORDER BY
    time_waited_micro DESC;

使用 AWR 报告分析 I/O 性能

AWR报告中的"Tablespace IO Statistics"和"File IO Statistics"部分提供了详细的I/O性能统计信息，包括：

每个表空间的 I/O 统计
每个数据文件的 I/O 统计
I/O 等待时间分布
I/O 密集型 SQL 语句

使用 ASM 磁盘组 I/O 统计（如果使用 ASM）

sql

-- 查看 ASM 磁盘组 I/O 统计
SELECT
    name AS diskgroup_name,
    ROUND(read_bytes / 1024 / 1024, 2) AS read_mb,
    ROUND(write_bytes / 1024 / 1024, 2) AS write_mb,
    ROUND(read_time / 100, 2) AS read_time_sec,
    ROUND(write_time / 100, 2) AS write_time_sec,
    ROUND(read_bytes / read_time / 1024 / 1024, 2) AS read_throughput_mb_s,
    ROUND(write_bytes / write_time / 1024 / 1024, 2) AS write_throughput_mb_s
FROM
    v$asm_diskgroup_stat;

I/O 瓶颈识别

常见 I/O 瓶颈症状

高 I/O 等待时间（平均 I/O 等待时间超过 20ms）
大量的 "db file sequential read" 或 "db file scattered read" 等待事件
存储设备 I/O 使用率持续超过 80%
数据库响应时间明显延长

I/O 密集型 SQL 识别

sql

-- 查找 I/O 密集型 SQL 语句
SELECT
    sql_id,
    plan_hash_value,
    executions,
    buffer_gets,
    disk_reads,
    ROUND(disk_reads / executions, 2) AS avg_disk_reads_per_exec
FROM
    v$sql
WHERE
    executions > 0
    AND disk_reads > 1000
ORDER BY
    disk_reads DESC;

I/O 优化策略

SQL 优化：
- 优化 I/O 密集型 SQL 语句
- 增加适当的索引，减少全表扫描
- 使用分区表，减少 I/O 范围
- 优化连接操作，减少中间结果集
存储优化：
- 确保数据文件分布在多个磁盘上，避免热点磁盘
- 使用 RAID 技术提高 I/O 性能
- 考虑使用 SSD 存储提高 I/O 速度
- 调整存储设备的 I/O 调度策略
数据库参数优化：
- 调整 db_file_multiblock_read_count 参数，优化全表扫描性能
- 调整 disk_asynch_io 和 filesystemio_options 参数，启用异步 I/O
- 调整 log_buffer 参数，减少 redo 日志 I/O

存储资源分析

表空间使用率监控

查看表空间使用情况

sql

-- 查看表空间使用率
SELECT
    tablespace_name,
    ROUND(total_size_mb, 2) AS total_size_mb,
    ROUND(used_size_mb, 2) AS used_size_mb,
    ROUND(free_size_mb, 2) AS free_size_mb,
    ROUND(used_pct, 2) AS used_pct
FROM
    (SELECT
        tablespace_name,
        SUM(bytes) / 1024 / 1024 AS total_size_mb,
        SUM(bytes - NVL(free_bytes, 0)) / 1024 / 1024 AS used_size_mb,
        SUM(NVL(free_bytes, 0)) / 1024 / 1024 AS free_size_mb,
        (SUM(bytes - NVL(free_bytes, 0)) / SUM(bytes)) * 100 AS used_pct
    FROM
        (SELECT
            df.tablespace_name,
            df.bytes,
            SUM(fs.bytes) AS free_bytes
        FROM
            dba_data_files df
            LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_id
        GROUP BY
            df.tablespace_name,
            df.file_id,
            df.bytes)
    GROUP BY
        tablespace_name);

监控临时表空间使用情况

sql

-- 查看临时表空间使用率
SELECT
    tablespace_name,
    ROUND(total_size_mb, 2) AS total_size_mb,
    ROUND(used_size_mb, 2) AS used_size_mb,
    ROUND(free_size_mb, 2) AS free_size_mb,
    ROUND(used_pct, 2) AS used_pct
FROM
    (SELECT
        tablespace_name,
        SUM(bytes) / 1024 / 1024 AS total_size_mb,
        SUM(bytes - NVL(free_bytes, 0)) / 1024 / 1024 AS used_size_mb,
        SUM(NVL(free_bytes, 0)) / 1024 / 1024 AS free_size_mb,
        (SUM(bytes - NVL(free_bytes, 0)) / SUM(bytes)) * 100 AS used_pct
    FROM
        (SELECT
            tf.tablespace_name,
            tf.bytes,
            SUM(fs.bytes) AS free_bytes
        FROM
            dba_temp_files tf
            LEFT JOIN dba_temp_free_space fs ON tf.tablespace_name = fs.tablespace_name
        GROUP BY
            tf.tablespace_name,
            tf.file_id,
            tf.bytes)
    GROUP BY
        tablespace_name);

存储增长趋势分析

使用 AWR 报告分析存储增长

AWR报告中的"Tablespace Usage History"部分提供了表空间使用的历史趋势，DBA可以据此预测未来的存储需求。

计算表空间增长速率

sql

-- 计算过去 7 天表空间增长速率
SELECT
    tablespace_name,
    ROUND((MAX(end_size) - MIN(start_size)) / 7, 2) AS daily_growth_mb
FROM
    (SELECT
        tablespace_name,
        snap_id,
        MIN(tablespace_size) AS start_size,
        MAX(tablespace_size) AS end_size
    FROM
        dba_hist_tbspc_space_usage
    WHERE
        snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE begin_interval_time > SYSDATE - 7)
    GROUP BY
        tablespace_name,
        snap_id)
GROUP BY
    tablespace_name
ORDER BY
    daily_growth_mb DESC;

存储优化策略

表空间管理：
- 定期监控表空间使用率，及时扩展表空间
- 对于大表，考虑使用分区表
- 启用自动扩展功能，但设置合理的最大值
- 定期清理无用数据，释放存储空间
数据压缩：
- 使用表压缩减少存储空间
- 对于归档数据，使用高级压缩选项
- 对于 RAC 环境，使用 Hybrid Columnar Compression (HCC)
存储分层：
- 将热数据存储在高性能存储上
- 将冷数据存储在低成本存储上
- 使用自动存储分层 (ASM Storage Tiering)

网络资源分析

网络性能监控

查看网络等待事件

sql

-- 查看网络相关等待事件
SELECT
    event,
    total_waits,
    time_waited_micro / 1000000 AS time_waited_seconds,
    ROUND(average_wait / 1000, 2) AS avg_wait_ms
FROM
    v$system_event
WHERE
    event LIKE 'SQL*Net%'
ORDER BY
    time_waited_micro DESC;

查看会话级网络等待

sql

-- 查看当前会话网络等待情况
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.event,
    s.wait_time_micro / 1000000 AS wait_time_seconds
FROM
    v$session s
WHERE
    s.event LIKE 'SQL*Net%'
    AND s.username IS NOT NULL
ORDER BY
    s.wait_time_micro DESC;

网络瓶颈识别与优化

常见网络瓶颈症状

大量的 "SQLNet message from client" 或 "SQLNet message to client" 等待事件
高网络延迟（平均网络等待时间超过 100ms）
网络带宽使用率持续超过 80%
远程客户端响应时间明显延长

网络优化策略

网络配置优化：
- 确保网络带宽足够
- 优化网络协议和参数
- 对于远程连接，使用连接池减少连接开销
数据库配置优化：
- 调整 sqlnet.recv_buffer_size 和 sqlnet.send_buffer_size 参数
- 调整 dispatchers 和 shared_servers 参数，优化共享服务器配置
- 启用 TCP/IP 缓存
应用程序优化：
- 减少网络往返次数
- 优化 SQL 语句，减少结果集大小
- 使用批量操作减少网络流量

19c 和 21c 版本差异

资源管理新特性

Oracle 19c 新特性

自动内存管理增强：改进了内存分配算法，提高了内存使用效率
PGA 自适应调整：根据工作负载自动调整 PGA 大小
大页内存支持增强：改进了对透明大页 (THP) 的支持
RAC 资源协调：增强了 RAC 环境下的资源协调机制

Oracle 21c 新特性

实时内存诊断：提供了更详细的内存使用诊断信息
自动内存优化：能够自动识别和修复内存配置问题
内存泄漏检测：内置内存泄漏检测功能
智能 PGA 管理：根据 SQL 语句特征自动调整 PGA 工作区大小
SGA 动态调整增强：支持更细粒度的 SGA 组件调整

资源监控增强

Oracle 19c 增强

AWR 报告增强：添加了更多资源使用统计信息
ASH 报告增强：提供了更详细的资源使用分析
实时监控视图增强：添加了新的视图 V$RESOURCE_LIMIT 和 V$RSRC_CONSUMER_GROUP

Oracle 21c 增强

实时资源监控：提供了近实时的资源使用监控
资源使用预测：能够预测未来资源需求
自动告警增强：能够自动检测和报告资源瓶颈
新视图 V$RESOURCE_USAGE_HISTORY：提供资源使用历史信息
资源使用趋势分析：增强了资源使用趋势分析功能

生产环境最佳实践

监控与告警配置

设置资源使用告警阈值：
- CPU 使用率超过 90% 持续 5 分钟告警
- 内存使用率超过 90% 持续 10 分钟告警
- 表空间使用率超过 85% 告警
- I/O 平均等待时间超过 20ms 告警
定期生成资源使用报告：
- 每天生成 AWR 报告，分析资源使用情况
- 每周生成资源使用趋势报告
- 每月进行一次全面的资源审计
使用自动化监控工具：
- 使用 Oracle Enterprise Manager (OEM) 进行集中监控
- 配置 Prometheus + Grafana 监控资源使用
- 集成告警系统，及时通知 DBA

资源优化最佳实践

定期进行 SQL 调优：
- 识别并优化消耗资源最多的 SQL 语句
- 使用 SQL Tuning Advisor 自动优化 SQL
- 建立 SQL 基线，确保优化效果持久
实施资源隔离：
- 使用资源管理器 (Resource Manager) 分配资源
- 为不同的应用程序或用户组设置资源限制
- 确保关键业务应用获得足够的资源
容量规划：
- 根据资源增长趋势，提前规划资源扩展
- 考虑业务增长对资源需求的影响
- 定期进行容量测试，验证资源配置
定期维护：
- 定期收集统计信息，确保执行计划准确
- 定期重建索引，减少索引碎片
- 定期清理无用数据，释放存储空间

常见问题 (FAQ)

如何快速识别消耗资源最多的 SQL 语句？

使用以下查询可以快速识别消耗资源最多的 SQL 语句：

sql

-- 消耗 CPU 最多的 SQL
SELECT * FROM v$sql ORDER BY cpu_time DESC;

-- 消耗 I/O 最多的 SQL
SELECT * FROM v$sql ORDER BY disk_reads DESC;

-- 消耗内存最多的 SQL
SELECT * FROM v$sql ORDER BY buffer_gets DESC;

如何判断数据库是否存在资源瓶颈？

可以通过以下方法判断数据库是否存在资源瓶颈：

检查系统级资源使用率（CPU、内存、I/O）
分析等待事件，查找主要等待类型
查看 AWR 报告中的 Top 5 等待事件
检查会话状态，查找大量处于等待状态的会话
分析资源使用率趋势，判断是否存在持续增长的资源需求

如何优化高 CPU 使用率？

优化高 CPU 使用率的方法包括：

优化消耗 CPU 最多的 SQL 语句
减少硬解析，使用绑定变量
调整数据库参数，如 cursor_sharing、session_cached_cursors
考虑增加 CPU 资源
优化应用程序逻辑，减少不必要的计算

如何优化 I/O 性能？

优化 I/O 性能的方法包括：

优化 I/O 密集型 SQL 语句
增加适当的索引，减少全表扫描
使用分区表，减少 I/O 范围
调整存储配置，如 RAID 级别、磁盘分布
启用异步 I/O
考虑使用 SSD 存储

19c 和 21c 在资源管理方面有什么主要区别？

Oracle 21c 在资源管理方面相比 19c 有以下主要增强：

更强大的内存管理功能，包括实时内存诊断和自动内存优化
智能 PGA 管理，根据 SQL 语句特征自动调整 PGA 工作区大小
增强的资源监控功能，提供近实时的资源使用监控和趋势分析
内置内存泄漏检测功能
更细粒度的 SGA 组件动态调整
资源使用预测功能，能够预测未来资源需求

总结

资源使用分析是Oracle数据库性能优化的重要组成部分，通过监控和分析CPU、内存、I/O、存储等关键资源的使用情况，可以及时发现性能瓶颈，采取相应的优化措施。有效的资源使用分析能够帮助DBA确保数据库系统高效、稳定地运行，避免因资源争用导致的性能下降或系统故障。

在实际生产环境中，DBA需要建立完善的资源监控和告警机制，定期分析资源使用情况，采取有效的优化策略，确保数据库系统能够满足业务需求。随着Oracle版本的升级，资源管理和监控功能不断增强，DBA应充分利用这些新特性，提高数据库的性能和可靠性。

Oracle 资源使用分析 ​

资源使用分析概述 ​

CPU 资源分析 ​

CPU 使用率监控 ​

查看实时 CPU 使用率 ​

使用 AWR 报告分析 CPU 使用率 ​

使用 ASH 报告分析 CPU 密集型会话 ​

CPU 瓶颈识别 ​

常见 CPU 瓶颈症状 ​

识别 CPU 密集型 SQL ​

CPU 优化策略 ​

内存资源分析 ​

内存结构概述 ​

SGA 使用率监控 ​

查看 SGA 组件大小和使用率 ​

PGA 使用率监控 ​

查看 PGA 总体使用情况 ​

识别 PGA 密集型会话 ​

内存瓶颈识别与优化 ​

SGA 瓶颈症状 ​

PGA 瓶颈症状 ​

内存优化策略 ​

I/O 资源分析 ​

I/O 性能监控 ​

查看磁盘 I/O 统计信息 ​

使用 AWR 报告分析 I/O 性能 ​

使用 ASM 磁盘组 I/O 统计（如果使用 ASM） ​

I/O 瓶颈识别 ​

常见 I/O 瓶颈症状 ​

I/O 密集型 SQL 识别 ​

I/O 优化策略 ​

存储资源分析 ​

表空间使用率监控 ​

查看表空间使用情况 ​

监控临时表空间使用情况 ​

存储增长趋势分析 ​

使用 AWR 报告分析存储增长 ​

计算表空间增长速率 ​

存储优化策略 ​

网络资源分析 ​

网络性能监控 ​

查看网络等待事件 ​

查看会话级网络等待 ​

网络瓶颈识别与优化 ​

常见网络瓶颈症状 ​

网络优化策略 ​

19c 和 21c 版本差异 ​

资源管理新特性 ​

Oracle 19c 新特性 ​

Oracle 21c 新特性 ​

资源监控增强 ​

Oracle 19c 增强 ​

Oracle 21c 增强 ​

生产环境最佳实践 ​

监控与告警配置 ​

资源优化最佳实践 ​

常见问题 (FAQ) ​

如何快速识别消耗资源最多的 SQL 语句？ ​

如何判断数据库是否存在资源瓶颈？ ​

如何优化高 CPU 使用率？ ​

如何优化 I/O 性能？ ​

19c 和 21c 在资源管理方面有什么主要区别？ ​

总结 ​