印刻数据库

外观

Oracle 慢查询风暴故障处理最佳实践 ​

慢查询风暴是指数据库中突然出现大量执行时间过长的SQL语句，导致数据库性能急剧下降，甚至无法响应正常请求的情况。这种故障在高并发生产环境中尤为常见，往往会导致业务中断、用户投诉，是DBA面临的最紧急、最具挑战性的性能问题之一。

生产场景案例 ​

案例1：电商大促期间的慢查询风暴 ​

背景：某电商平台在双11大促开始后5分钟，数据库CPU使用率突然飙升至100%，大量订单无法处理，页面响应时间超过30秒。

诊断过程：

  1. 通过v$session视图发现活跃会话数从平时的200+猛增至1500+
  2. 查看v$sql发现一条查询商品库存的SQL语句执行时间超过20秒，且被执行了数万次
  3. 分析执行计划发现该SQL使用了错误的索引，导致全表扫描

解决方案：

  1. 立即使用DBMS_SHARED_POOL.PURGE清理共享池，强制SQL重新解析
  2. 为该SQL创建SQL Plan Baseline，固定正确的执行计划
  3. 调整数据库资源管理器，优先保障订单处理相关SQL的资源

结果：10分钟内数据库CPU使用率降至60%以下，订单处理恢复正常

案例2：统计信息过期引发的连锁反应 ​

背景：某金融系统在批量处理完千万级数据后，核心交易SQL执行时间从毫秒级突增至秒级，导致交易系统超时。

诊断过程：

  1. 检查执行计划发现优化器选择了笛卡尔积连接
  2. 查看表统计信息发现最近更新时间是3个月前，数据量已增长10倍
  3. 确认是统计信息过期导致优化器生成了错误的执行计划

解决方案：

  1. 紧急更新相关表的统计信息，包含直方图
  2. 为关键SQL创建SQL Profile
  3. 调整自动统计信息收集策略，增加对大表的监控频率

结果：核心交易SQL执行时间恢复至毫秒级，系统稳定运行

慢查询风暴概述 ​

慢查询风暴的常见症状 ​

慢查询风暴的症状通常会在短时间内突然出现，DBA需要通过多维度监控来快速识别：

  • 响应时间：数据库平均响应时间从正常的毫秒级突增至秒级，甚至几十秒
  • 活跃会话：活跃会话数超过平时峰值的3-5倍，或接近数据库配置的processes参数上限
  • CPU使用率：数据库服务器CPU使用率持续超过90%，且主要由Oracle进程占用
  • I/O指标：磁盘I/O利用率超过95%，或I/O等待时间占总执行时间的80%以上
  • 应用表现：前端应用出现大量500错误、超时或连接失败
  • SQL执行情况：慢查询日志中同一SQL模板的执行次数突增，或出现大量新的慢查询
  • 等待事件：v$session_wait中出现大量db file sequential read、db file scattered read或CPU time等待
  • 共享池情况：共享池命中率下降至80%以下，或出现大量硬解析

慢查询风暴的危害 ​

  • 业务中断：严重时导致数据库无法响应，业务系统完全瘫痪
  • 数据丢失风险：长时间高负载可能导致数据库实例崩溃
  • 性能退化：即使恢复后，数据库性能可能需要较长时间才能恢复到正常水平
  • 资源浪费：大量无效SQL消耗服务器资源，影响其他正常业务
  • 客户体验下降：应用响应慢或无法访问，直接影响用户体验和业务收入

慢查询风暴的常见原因 ​

SQL语句问题 ​

  • 索引问题：缺少必要索引、索引失效（如分区表全局索引失效）、索引选择性差

    • 检测方法：查看执行计划中的TABLE ACCESS FULL操作，分析v$object_usage中的索引使用率
    • 示例：SELECT * FROM orders WHERE status = 'COMPLETED' 未在status列创建索引

  • SQL编写不当：全表扫描、笛卡尔积连接、不必要的ORDER BY/GROUP BY

    • 检测方法：分析执行计划，查看NESTED LOOPS的连接顺序
    • 示例：SELECT * FROM a, b WHERE a.id > b.id 未指定连接条件导致笛卡尔积

  • 绑定变量使用不当：SQL中直接拼接变量，导致大量硬解析

    • 检测方法：v$sql中同一逻辑的SQL有多个不同的SQL_ID
    • 示例：SELECT * FROM users WHERE id = 123 而非使用:id绑定变量

  • 执行计划问题：优化器选择了错误的执行计划

    • 检测方法：对比历史执行计划，查看v$sql_plan中的plan_hash_value变化
    • 示例：优化器选择了嵌套循环连接而非哈希连接处理大表关联

  • 统计信息问题：统计信息过期、不准确或缺少直方图

    • 检测方法：查看dba_tab_statistics中的last_analyzed和num_rows
    • 示例：表数据量增长10倍但统计信息未更新，导致优化器低估数据量

数据库配置问题 ​

  • 内存配置不足：SGA或PGA设置过小，导致大量磁盘I/O

    • 检测方法：v$sga_dynamic_components查看内存使用情况，v$pgastat查看PGA命中率
    • 示例：PGA设置为1G但处理大量并行查询，导致频繁的临时表空间I/O

  • 并行度设置不当：并行度过高导致资源争用，或过低导致性能无法提升

    • 检测方法：v$px_process查看并行进程使用情况，v$session中的degree列
    • 示例：设置parallel_degree_policy=auto但系统CPU不足，导致并行查询抢占资源

  • 资源管理器配置不合理：未为关键业务设置足够资源，或资源分配策略不当

    • 检测方法：查看v$rsrc_plan和v$rsrc_consumer_group
    • 示例：所有用户共享同一资源组，慢查询抢占了核心业务资源

  • 连接池配置问题：应用连接池设置过大，导致数据库连接数过多

    • 检测方法：v$session中program列查看应用连接数量
    • 示例：连接池最大连接数设置为500，远超数据库processes参数

系统资源问题 ​

  • CPU瓶颈：CPU核心数不足或被其他进程占用

    • 检测方法：top或vmstat查看CPU使用率，ps查看进程CPU占用
    • 示例：数据库服务器同时运行其他CPU密集型应用，导致Oracle可用CPU不足

  • 磁盘I/O瓶颈：存储性能不足、RAID配置不合理或磁盘故障

    • 检测方法：iostat -x查看%util和await，v$filestat查看文件I/O
    • 示例：使用SATA盘存储数据文件，无法满足高并发I/O需求

  • 内存不足：物理内存不足导致大量swap使用

    • 检测方法：free查看内存使用情况，vmstat查看swap in/out
    • 示例：系统内存8G但运行多个大型应用，导致Oracle进程频繁swap

  • 网络问题：网络延迟高或带宽不足

    • 检测方法：ping、traceroute查看网络延迟，netstat查看连接状态
    • 示例：应用服务器与数据库服务器跨地域部署，网络延迟超过100ms

业务变化 ​

  • 数据量增长：表数据量突增，导致原有执行计划失效

    • 检测方法：查看dba_segments中的bytes变化
    • 示例：某表从100万行突增至1亿行，全表扫描变为性能瓶颈

  • 业务逻辑变化：应用发布新版本，SQL语句或执行频率发生变化

    • 检测方法：对比慢查询日志的历史记录
    • 示例：新功能上线后，某个报表SQL从每天执行1次变为每秒执行10次

  • 并发用户增加：促销活动或业务高峰导致并发用户突增

    • 检测方法：查看v$session中的活跃用户数变化
    • 示例：电商大促期间，并发用户从平时的1000人增至10000人

  • 批量操作：数据迁移、ETL或批量更新操作

    • 检测方法：查看v$session中的module和action列
    • 示例：夜间批量更新操作在白天执行，与业务高峰期冲突

外部因素 ​

  • 数据库维护操作：备份、索引重建或统计信息收集

    • 检测方法：查看dba_scheduler_jobs或crontab中的维护任务
    • 示例：全库备份在业务高峰期执行，占用大量I/O资源

  • 其他系统干扰：杀毒软件扫描、系统补丁更新

    • 检测方法：查看系统日志和进程列表
    • 示例：杀毒软件实时扫描数据库文件，导致I/O性能下降

  • 硬件故障：磁盘坏道、CPU故障或内存损坏

    • 检测方法：查看系统日志、dmesg或硬件监控工具
    • 示例：磁盘出现坏道，导致I/O等待时间急剧增加

慢查询风暴的诊断方法 ​

实时诊断工作流 ​

当慢查询风暴发生时，DBA需要按照以下步骤进行快速诊断：

  1. 第一步：确认故障范围 - 快速判断是单实例问题还是全系统问题
  2. 第二步：定位瓶颈资源 - 确定是CPU、I/O、内存还是网络问题
  3. 第三步：识别罪魁祸首SQL - 找出消耗资源最多的SQL语句
  4. 第四步：分析执行计划 - 确定SQL执行效率低下的原因
  5. 第五步：验证根本原因 - 确认导致慢查询的具体因素

生产环境实时诊断脚本包 ​

1. 快速故障确认脚本 ​

sql
-- 慢查询风暴快速诊断脚本（执行时间 < 1秒）
SET LINESIZE 200
SET PAGESIZE 100

-- 1. 系统基本状态
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS current_time,
       (SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE') AS active_sessions,
       (SELECT value FROM v$parameter WHERE name = 'processes') AS max_processes,
       (SELECT ROUND(used_pct, 1) FROM v$pgastat WHERE name = 'aggregate PGA target parameter') AS pga_used_pct,
       (SELECT ROUND(100 - (sum(decode(name, 'free memory', bytes, 0)) / sum(bytes)) * 100, 1) FROM v$sga) AS sga_used_pct
FROM dual;

-- 2. 主要等待事件（前5个）
SELECT * FROM (
  SELECT event, COUNT(*) AS wait_count,
         ROUND(COUNT(*) / (SELECT COUNT(*) FROM v$session WHERE event IS NOT NULL) * 100, 1) AS wait_pct
  FROM v$session
  WHERE event NOT LIKE '%Idle%'
  GROUP BY event
  ORDER BY wait_count DESC
) WHERE ROWNUM <= 5;

-- 3. 消耗资源最多的前10个SQL
SELECT * FROM (
  SELECT sql_id, 
         SUBSTR(sql_text, 1, 100) AS sql_text,
         executions,
         ROUND(elapsed_time / 1000000, 2) AS total_elapsed_seconds,
         ROUND(elapsed_time / 1000000 / GREATEST(executions, 1), 2) AS avg_elapsed_seconds,
         buffer_gets,
         disk_reads
  FROM v$sql
  WHERE elapsed_time > 1000000 -- 超过1秒的SQL
  ORDER BY elapsed_time DESC
) WHERE ROWNUM <= 10;

2. 深度SQL分析脚本 ​

sql
-- 深度SQL分析脚本
SET LONG 2000000
SET LONGCHUNKSIZE 1000000

-- 1. 查看SQL的详细执行计划
EXECUTE DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', 'ALL');

-- 2. 查看SQL的历史执行计划变化
SELECT plan_hash_value, executions, 
       ROUND(elapsed_time / 1000000 / executions, 2) AS avg_elapsed_time,
       timestamp
FROM v$sql_plan_history
WHERE sql_id = '&sql_id'
ORDER BY timestamp;

-- 3. 查看SQL的详细统计信息
SELECT * FROM v$sqlstats WHERE sql_id = '&sql_id';

-- 4. 查看SQL的绑定变量值（如果有）
SELECT name, position, datatype_string, value_string
FROM v$sql_bind_capture
WHERE sql_id = '&sql_id';

3. 系统资源监控脚本 ​

bash
#!/bin/bash
# 慢查询风暴系统资源监控脚本
# 使用方法：./slow_query_monitor.sh <interval_seconds> <iterations>

INTERVAL=${1:-5}
ITERATIONS=${2:-12}

for ((i=1; i<=ITERATIONS; i++)); do
  echo "============================================================="
  echo "Iteration $i at $(date '+%Y-%m-%d %H:%M:%S')"
  echo "============================================================="
  
  # 1. CPU使用情况
  echo "--- CPU Usage ---
"
  top -bn1 | head -20 | grep -E "(top -|load average|Cpu|KiB Mem|PID USER)"
  
  # 2. 内存使用情况
  echo "
--- Memory Usage ---
"
  free -h
  
  # 3. 磁盘I/O情况
  echo "
--- Disk I/O (top 3 disks) ---
"
  iostat -x 1 2 | grep -A 5 avg-cpu | grep -v avg-cpu
  
  # 4. 网络连接情况
  echo "
--- Network Connections ---
"
  netstat -tuln | grep -E "(LISTEN|ESTABLISHED)" | wc -l
  netstat -tuln | grep 1521
  
  # 5. Oracle进程CPU占用
  echo "
--- Top 5 Oracle Processes by CPU ---
"
  ps -eo pid,ppid,pcpu,pmem,args | grep oracle | grep -v grep | sort -k3 -r | head -5
  
  sleep $INTERVAL
done

AWR/ASH报告快速分析要点 ​

在慢查询风暴期间，AWR和ASH报告是定位根本原因的关键工具，需要重点关注以下内容：

AWR报告分析重点 ​

  1. 负载概况：DB Time与Elapsed Time的比值，比值大于2表示系统负载较高
  2. Top Timed Events：主要等待事件，尤其是CPU、I/O、锁相关事件
  3. SQL Statistics：Top SQL by Elapsed Time、Buffer Gets、Disk Reads
  4. Instance Efficiency Percentages：命中率指标，尤其是Library Cache Hit Ratio
  5. SGA Statistics：SGA各组件的使用情况
  6. PGA Aggr Summary：PGA的使用和命中率
  7. Session Statistics：每秒执行的SQL数、解析数等

ASH报告分析重点 ​

  1. Top User Events：最近活跃会话的主要等待事件
  2. Top SQL：消耗资源最多的SQL语句
  3. Top Sessions：消耗资源最多的会话
  4. Top Blocks：频繁访问的数据块
  5. Wait Class Breakdown：等待事件分类统计
  6. SQL Plan Details：慢查询的执行计划详情

慢查询日志智能分析 ​

慢查询日志包含了大量的SQL执行信息，DBA可以使用以下方法进行高效分析：

1. 启用和配置慢查询日志 ​

sql
-- 1. 启用SQL跟踪（会话级别）
ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = 'slow_query_diagnosis';

-- 2. 启用自动SQL监控（适合长时间运行的SQL）
ALTER SYSTEM SET control_management_pack_access = 'DIAGNOSTIC+TUNING' SCOPE=BOTH;
ALTER SYSTEM SET sql_monitor_threshold = '10'; -- 10秒以上的SQL自动监控

-- 3. 配置SQL历史保留
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = true SCOPE=BOTH;
ALTER SYSTEM SET optimizer_use_sql_plan_baselines = true SCOPE=BOTH;

2. 慢查询日志分析工具 ​

  • Oracle Enterprise Manager：提供可视化的慢查询分析界面
  • AWR Report：包含Top SQL统计信息
  • ASH Report：提供实时的SQL执行情况
  • SQL Developer：内置SQL调优顾问
  • 第三方工具：如Quest Spotlight、SolarWinds Database Performance Analyzer

3. 自动化分析脚本 ​

sql
-- 慢查询日志自动分析脚本
CREATE OR REPLACE PROCEDURE analyze_slow_queries(p_min_elapsed_time IN NUMBER DEFAULT 10) IS
BEGIN
  -- 找出指定时间内的慢查询
  FOR sql_rec IN (
    SELECT sql_id, sql_text, 
           elapsed_time / 1000000 AS elapsed_seconds,
           executions
    FROM v$sql
    WHERE elapsed_time / 1000000 > p_min_elapsed_time
    ORDER BY elapsed_time DESC
  ) LOOP
    -- 生成调优建议
    DBMS_OUTPUT.PUT_LINE('SQL_ID: ' || sql_rec.sql_id);
    DBMS_OUTPUT.PUT_LINE('SQL Text: ' || SUBSTR(sql_rec.sql_text, 1, 200));
    DBMS_OUTPUT.PUT_LINE('Elapsed Time: ' || sql_rec.elapsed_seconds || 's');
    DBMS_OUTPUT.PUT_LINE('Executions: ' || sql_rec.executions);
    DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

慢查询风暴的应急处理 ​

应急响应工作流 ​

当慢查询风暴发生时，DBA需要按照以下优先级进行应急处理：

  1. 第一优先级：止损 - 立即终止或限制慢查询，恢复数据库基本可用性
  2. 第二优先级：恢复 - 快速恢复关键业务SQL的正常执行
  3. 第三优先级：优化 - 调整执行计划和资源配置，防止问题复发
  4. 第四优先级：监控 - 密切监控数据库状态，确保问题彻底解决

1. 快速止损：终止或限制慢查询 ​

智能会话终止策略 ​

在终止会话时，DBA需要谨慎选择目标，避免影响关键业务：

  • 优先终止：长时间运行的非关键业务会话、同一SQL模板的大量重复会话
  • 谨慎处理：核心业务会话、短时间运行的会话、系统会话
  • 避免操作：盲目终止所有活跃会话，可能导致数据库不稳定

批量终止会话脚本 ​

sql
-- 1. 智能生成终止会话命令（仅终止非关键业务的慢会话）
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_command,
       s.username, s.program, s.machine, SUBSTR(t.sql_text, 1, 50) AS sql_text,
       s.last_call_et/60 AS running_minutes
FROM v$session s
JOIN v$sqltext t ON s.sql_id = t.sql_id AND t.piece = 0
WHERE s.status = 'ACTIVE'
AND s.username IS NOT NULL
AND s.username NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN') -- 排除系统用户
AND s.last_call_et > 300 -- 运行超过5分钟
AND s.program NOT LIKE '%sqlplus%' -- 排除DBA管理会话
AND t.sql_text NOT LIKE '%SELECT /*+ MONITOR */%' -- 排除监控SQL
ORDER BY s.last_call_et DESC;

-- 2. 终止特定SQL的所有会话
DECLARE
  v_sql_id VARCHAR2(13) := '&sql_id';
BEGIN
  FOR r IN (
    SELECT sid, serial#
    FROM v$session
    WHERE sql_id = v_sql_id
    AND status = 'ACTIVE'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.sid || ',' || r.serial# || ''' IMMEDIATE';
    DBMS_OUTPUT.PUT_LINE('Killed session ' || r.sid || ',' || r.serial#);
  END LOOP;
END;
/

-- 3. 使用OS级终止（当ALTER SYSTEM KILL SESSION无效时）
SELECT 'kill -9 ' || p.spid AS os_kill_command,
       s.sid, s.serial#, s.username, s.program, s.machine
FROM v$process p
JOIN v$session s ON p.addr = s.paddr
WHERE s.status = 'ACTIVE'
AND s.last_call_et > 600;

2. 执行计划紧急修复 ​

执行计划修复工具包 ​

sql
-- 1. 快速刷新执行计划
EXECUTE dbms_shared_pool.purge((SELECT address || ',' || hash_value FROM v$sql WHERE sql_id = '&sql_id'), 'C');

-- 2. 固定执行计划（推荐）
DECLARE
  l_plans_accepted PLS_INTEGER;
BEGIN
  -- 加载当前执行计划到基线
  l_plans_accepted := dbms_spm.load_plans_from_cursor_cache(
    sql_id => '&sql_id',
    plan_hash_value => &good_plan_hash_value,  -- 使用已知良好的执行计划哈希值
    fixed => 'YES'  -- 固定计划，防止被替换
  );
  DBMS_OUTPUT.PUT_LINE('Accepted plans: ' || l_plans_accepted);
  
  -- 如果需要，禁用其他不良计划
  FOR r IN (
    SELECT plan_name
    FROM dba_sql_plan_baselines
    WHERE sql_handle = (SELECT sql_handle FROM dba_sql_plan_baselines WHERE sql_id = '&sql_id' AND rownum = 1)
    AND plan_hash_value != &good_plan_hash_value
  ) LOOP
    dbms_spm.alter_sql_plan_baseline(
      plan_name => r.plan_name,
      attribute_name => 'ENABLED',
      attribute_value => 'NO'
    );
    DBMS_OUTPUT.PUT_LINE('Disabled bad plan: ' || r.plan_name);
  END LOOP;
END;
/

-- 3. 使用SQL Profile快速优化
DECLARE
  l_sqlprofile_name VARCHAR2(100);
BEGIN
  -- 快速创建SQL Profile（跳过完整调优，仅使用基本建议）
  l_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    sql_id => '&sql_id',
    task_name => NULL,
    profile_type => DBMS_SQLTUNE.PROFILE_TYPE_SQLPROFILE,
    replace => TRUE,
    force_match => TRUE  -- 匹配所有相似SQL
  );
  DBMS_OUTPUT.PUT_LINE('Created SQL Profile: ' || l_sqlprofile_name);
END;
/

-- 4. 使用Hint临时修复（适用于应用无法快速修改的情况）
-- 在应用连接字符串中添加：?hint=/*+ INDEX(orders idx_orders_status) */
-- 或使用SQL Patch
BEGIN
  DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(
    sql_id => '&sql_id',
    hint_text => 'INDEX(orders idx_orders_status)',
    name => 'patch_for_slow_query_' || '&sql_id',
    description => 'Temporary patch for slow query'
  );
END;
/

3. 资源配置应急调整 ​

内存和并行度调整 ​

sql
-- 1. 内存应急调整
-- 增加PGA大小（适合排序、哈希连接密集型SQL）
ALTER SYSTEM SET pga_aggregate_target = GREATEST(pga_aggregate_target * 2, 8G) SCOPE=BOTH;

-- 增加共享池大小（适合硬解析密集型SQL）
ALTER SYSTEM SET shared_pool_size = GREATEST(shared_pool_size * 1.5, 4G) SCOPE=BOTH;

-- 2. 并行度调整
-- 限制最大并行度
ALTER SYSTEM SET parallel_max_servers = LEAST(parallel_max_servers / 2, 32) SCOPE=BOTH;

-- 禁用自动并行度（防止小表也使用并行）
ALTER SYSTEM SET parallel_degree_policy = 'MANUAL' SCOPE=BOTH;

-- 3. 游标共享调整（谨慎使用）
-- 对于大量相似SQL，强制共享游标（可能影响执行计划）
ALTER SYSTEM SET cursor_sharing = 'FORCE' SCOPE=BOTH;

资源管理器应急配置 ​

sql
-- 1. 创建应急资源计划
BEGIN
  -- 清理旧计划（如果存在）
  BEGIN
    dbms_resource_manager.delete_plan('EMERGENCY_PLAN', TRUE);
  EXCEPTION WHEN OTHERS THEN NULL;
  END;
  
  -- 创建计划
  dbms_resource_manager.create_plan('EMERGENCY_PLAN', 'Emergency plan for slow query storm');
  
  -- 为核心业务分配更多资源
  dbms_resource_manager.create_plan_directive(
    plan => 'EMERGENCY_PLAN',
    group_or_subplan => 'OLTP_GROUP',
    comment => 'OLTP transactions',
    cpu_p1 => 70,  -- 70% CPU资源
    active_sessions_limit => 100,
    parallel_degree_limit_p1 => 8
  );
  
  -- 限制报表和批处理资源
  dbms_resource_manager.create_plan_directive(
    plan => 'EMERGENCY_PLAN',
    group_or_subplan => 'REPORT_GROUP',
    comment => 'Reporting and batch jobs',
    cpu_p1 => 20,  -- 20% CPU资源
    active_sessions_limit => 20,
    parallel_degree_limit_p1 => 4
  );
  
  -- 为系统进程保留资源
  dbms_resource_manager.create_plan_directive(
    plan => 'EMERGENCY_PLAN',
    group_or_subplan => 'SYS_GROUP',
    comment => 'System processes',
    cpu_p1 => 100,  -- 优先使用CPU
    active_sessions_limit => unlimited
  );
END;
/

-- 2. 激活资源计划
ALTER SYSTEM SET resource_manager_plan = 'EMERGENCY_PLAN' SCOPE=BOTH;

-- 3. 将用户分配到资源组
-- 例如：将报表用户分配到低优先级组
ALTER USER report_user SWITCH CURRENT_CONSUMER_GROUP TO REPORT_GROUP;

4. 索引应急创建 ​

对于缺少索引导致的慢查询，可以紧急创建索引：

sql
-- 1. 分析SQL，找出缺失的索引
-- 使用SQL Tuning Advisor快速获取索引建议
DECLARE
  l_task_name VARCHAR2(30) := 'tune_sql_' || '&sql_id';
BEGIN
  DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id', task_name => l_task_name);
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_name);
  DBMS_OUTPUT.PUT_LINE(DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name => l_task_name));
END;
/

-- 2. 在线创建索引（最小化锁影响）
CREATE INDEX idx_orders_status ON orders(status)
  ONLINE PARALLEL 4 NOLOGGING;

-- 3. 创建后恢复正常设置
ALTER INDEX idx_orders_status NOPARALLEL LOGGING;

5. 慢查询风暴后的验证 ​

应急处理后，DBA需要验证处理效果：

sql
-- 1. 检查数据库基本状态
SELECT (SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE') AS active_sessions,
       (SELECT value FROM v$sysmetric WHERE metric_name = 'CPU Usage Per Sec' AND rownum = 1) AS cpu_usage_per_sec,
       (SELECT ROUND(avg(elapsed_time/1000000), 2) FROM v$sql WHERE sql_id = '&sql_id' AND executions > 0) AS avg_elapsed_seconds
FROM dual;

-- 2. 检查关键SQL的执行情况
SELECT sql_id, executions, 
       ROUND(elapsed_time/1000000/executions, 2) AS avg_elapsed_seconds,
       buffer_gets/executions AS avg_buffer_gets,
       disk_reads/executions AS avg_disk_reads
FROM v$sql
WHERE sql_id IN ('&sql_id1', '&sql_id2', '&sql_id3') -- 关键SQL的SQL_ID
AND executions > 0;

-- 3. 监控等待事件
SELECT event, COUNT(*) AS wait_count
FROM v$session
WHERE event NOT LIKE '%Idle%'
GROUP BY event
ORDER BY wait_count DESC;

慢查询风暴的根本解决方法 ​

根因分析方法论 ​

要彻底解决慢查询风暴问题，DBA需要进行系统的根因分析，遵循以下步骤：

  1. 收集证据：获取AWR/ASH报告、慢查询日志、系统监控数据
  2. 定位问题：确定是SQL语句、执行计划、资源配置还是硬件问题
  3. 分析原因：深入分析导致性能下降的具体因素
  4. 制定方案：根据根因制定针对性的解决方案
  5. 实施验证：实施解决方案并验证效果
  6. 预防措施：制定预防措施，防止问题再次发生

1. SQL语句深度优化 ​

优化流程 ​

收集慢SQL → 分析执行计划 → 识别瓶颈 → 制定优化方案 → 实施优化 → 验证效果

执行计划分析技巧 ​

  • 查看完整执行计划：使用DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', 'ALLSTATS LAST')查看实际执行统计
  • 关注高成本操作：重点优化TABLE ACCESS FULL、HASH JOIN、SORT AGGREGATE等高成本操作
  • 检查连接顺序：确保小表驱动大表，减少中间结果集
  • 分析基数估算：对比ROWS列的估算值与实际值，差异大可能需要更新统计信息
  • 查看谓词信息：检查PREDICATE_INFO中的过滤条件，确认索引是否被有效使用

索引优化最佳实践 ​

sql
-- 1. 分析索引使用情况
SELECT index_name, table_name, used, start_monitoring
FROM v$object_usage
WHERE table_name = '&table_name';

-- 2. 查找未使用的索引（浪费资源）
SELECT i.index_name, i.table_name
FROM user_indexes i
LEFT JOIN v$object_usage u ON i.index_name = u.index_name AND i.table_name = u.table_name
WHERE u.used IS NULL OR u.used = 'NO';

-- 3. 创建高效索引
-- 复合索引：将过滤性高的列放在前面
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- 函数索引：对于频繁使用函数的列
CREATE INDEX idx_orders_created_date ON orders(TRUNC(created_date));

-- 位图索引：适用于低基数列（如性别、状态）
CREATE BITMAP INDEX idx_orders_status ON orders(status);

-- 4. 优化索引维护
-- 定期重建碎片化索引
SELECT index_name, leaf_blocks, blocks, 
       ROUND((1 - (leaf_blocks / blocks)) * 100, 2) AS fragmentation_pct
FROM user_indexes
WHERE (1 - (leaf_blocks / blocks)) * 100 > 30;  -- 碎片化超过30%的索引

-- 在线重建索引，减少锁影响
ALTER INDEX idx_orders_customer_status REBUILD ONLINE;

2. 统计信息精细化管理 ​

统计信息收集策略 ​

  • 自动收集：启用Oracle自动统计信息收集，设置合适的收集窗口
  • 手动收集：对于频繁变化的大表，在业务低峰期手动收集
  • 增量收集：对于分区表，使用增量统计信息收集
  • 直方图收集：对于数据倾斜的列，收集直方图统计信息

智能统计信息收集脚本 ​

sql
-- 1. 检查统计信息过期的表
SELECT owner, table_name, last_analyzed, 
       ROUND((num_rows * avg_row_len) / 1024 / 1024, 2) AS table_size_mb
FROM dba_tab_statistics
WHERE owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
AND last_analyzed < SYSDATE - 7  -- 超过7天未更新
AND num_rows > 1000000  -- 大于100万行的表
ORDER BY table_size_mb DESC;

-- 2. 智能收集统计信息（仅收集需要的表）
BEGIN
  FOR r IN (
    SELECT owner, table_name
    FROM dba_tab_statistics
    WHERE owner = '&schema_name'
    AND (last_analyzed < SYSDATE - 7 OR last_analyzed IS NULL)
    AND num_rows > 0
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('收集表 ' || r.owner || '.' || r.table_name || ' 的统计信息');
    DBMS_STATS.GATHER_TABLE_STATS(
      ownname => r.owner,
      tabname => r.table_name,
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt => 'FOR ALL COLUMNS SIZE AUTO',
      cascade => TRUE,
      degree => 8,
      no_invalidate => FALSE
    );
  END LOOP;
END;
/

-- 3. 收集直方图（针对数据倾斜列）
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(
  ownname => '&schema_name',
  tabname => '&table_name',
  columns => 'status',  -- 仅收集特定列的直方图
  method_opt => 'FOR COLUMNS status SIZE 254',  -- 收集详细直方图
  cascade => FALSE,
  degree => 4
);

3. 数据库配置优化 ​

内存配置最佳实践 ​

  • SGA：建议占物理内存的50-70%，根据数据库类型调整
    • OLTP系统：SGA可占内存的60-70%
    • 数据仓库：SGA可占内存的40-50%
  • PGA：建议占物理内存的20-30%，使用pga_aggregate_target自动管理
  • 共享池：对于大量硬解析的系统，增加共享池大小
  • 缓冲区缓存：对于I/O密集型系统，增加db_cache_size

并行度优化 ​

sql
-- 1. 查看当前并行度设置
SELECT name, value FROM v$parameter WHERE name LIKE '%parallel%';

-- 2. 优化并行度配置
-- 设置CPU_COUNT为实际CPU核心数
ALTER SYSTEM SET cpu_count = 32 SCOPE=SPFILE;

-- 设置并行度限制
ALTER SYSTEM SET parallel_degree_limit = 8 SCOPE=BOTH;

-- 启用自动并行度调整
ALTER SYSTEM SET parallel_adaptive_multi_user = TRUE SCOPE=BOTH;

-- 3. 为特定表或索引设置并行度
ALTER TABLE orders PARALLEL 4;
ALTER INDEX idx_orders_customer_id NOPARALLEL;

4. 系统资源优化 ​

存储优化 ​

  • 使用RAID 10：兼顾性能和可靠性，适合数据库存储
  • 分离I/O：将数据文件、日志文件和临时表空间放在不同的存储设备上
  • 使用ASM：Oracle自动存储管理，提供更好的性能和可靠性
  • 优化文件系统：使用XFS或EXT4文件系统，调整I/O调度器为deadline或noop

网络优化 ​

  • 调整网络缓冲区：增加sqlnet.recv_buffer_size和sqlnet.send_buffer_size
  • 使用专用网络：数据库服务器与应用服务器使用专用网络
  • 启用TCP_NODELAY：减少网络延迟
  • 优化连接池：调整应用连接池大小，设置合理的超时时间

5. 实施全面监控 ​

监控体系建设 ​

  1. 实时监控：使用Oracle Enterprise Manager或Prometheus + Grafana
  2. 性能基线：建立正常负载下的性能指标基线
  3. 告警机制：设置多级告警，及时发现性能异常
  4. 日志分析：集中管理和分析慢查询日志
  5. 定期报告：生成每日/每周性能报告

监控指标建议 ​

指标类型关键指标告警阈值
CPUCPU使用率持续5分钟>90%
内存可用内存<10%
I/O磁盘I/O利用率持续5分钟>90%
I/O等待时间>50ms
数据库活跃会话数>processes参数的80%
响应时间>1秒
共享池命中率<95%
硬解析率>10%
SQL慢查询数每分钟>10条
SQL执行时间>10秒

自动化监控脚本 ​

sql
-- 自动监控慢查询的PL/SQL作业
CREATE OR REPLACE PROCEDURE monitor_slow_queries IS
  v_slow_query_count NUMBER;
BEGIN
  -- 统计10秒以上的慢查询
  SELECT COUNT(*)
  INTO v_slow_query_count
  FROM v$sql
  WHERE elapsed_time > 10000000 -- 10秒
  AND last_active_time > SYSDATE - 1/1440; -- 最近1分钟
  
  -- 如果慢查询数超过阈值，发送告警
  IF v_slow_query_count > 10 THEN
    -- 发送邮件告警
    UTL_MAIL.SEND(
      sender => 'dba@example.com',
      recipients => 'oncall-dba@example.com',
      subject => 'Oracle慢查询告警',
      message => '数据库中检测到 ' || v_slow_query_count || ' 条慢查询，请立即处理！'
    );
    
    -- 生成慢查询报告
    INSERT INTO dba_slow_query_alerts (alert_time, slow_query_count, report)
    VALUES (SYSDATE, v_slow_query_count, 
            DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST(
              type => 'TEXT',
              report_level => 'ALL',
              filter_condition => 'elapsed_time > 10000000'));
  END IF;
END;
/

-- 每5分钟执行一次
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_SLOW_QUERIES_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN monitor_slow_queries; END;',
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
    enabled => TRUE
  );
END;
/

Oracle 19c vs 21c 慢查询处理差异 ​

核心差异对比 ​

特性Oracle 19cOracle 21c
慢查询诊断支持AWR、ASH、SQL Tuning Advisor增强版诊断工具，新增SQL Performance Analyzer和SQL Plan Management Advisor，支持实时SQL监控
执行计划管理支持SQL Plan Baseline和SQL Profile自动计划演变、自适应执行计划、SQL Plan Management Dashboard，支持计划空间探索
统计信息管理自动统计信息收集，基本直方图增量统计、自适应统计、混合柱状图、统计信息自动调整
资源管理基本资源管理器，按组分配资源细粒度资源控制、自动资源分配、多租户资源隔离、实时资源消耗监控
自适应执行自适应执行计划（初始版本）优化的自适应执行，支持更多执行阶段的自适应调整
并行执行自动并行度，基本并行控制智能并行度调整、并行执行优化、并行服务器池管理
SQL监控SQL Monitor基础版增强版SQL Monitor，实时执行统计、可视化界面、历史执行回放
SQL调优SQL Tuning Advisor，手动建议实施自动SQL调优、建议自动实施、SQL Profile智能创建
索引优化基本索引建议，手动创建智能索引建议、自动索引创建和维护、索引使用情况分析
等待事件分析基本等待事件分类增强的等待事件分析，支持等待链跟踪、等待事件归因

Oracle 21c 慢查询处理新特性 ​

1. 自适应统计信息增强 ​

Oracle 21c显著增强了统计信息管理，能够自动适应数据变化，减少因统计信息过期导致的慢查询：

sql
-- 启用自适应统计信息
ALTER SYSTEM SET optimizer_adaptive_statistics = TRUE SCOPE=BOTH;

-- 收集自适应直方图（自动决定是否需要）
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(
  ownname => '&schema_name',
  tabname => '&table_name',
  method_opt => 'FOR ALL COLUMNS SIZE AUTO(ADAPTIVE)',
  degree => 8
);

-- 查看自适应统计信息配置
SELECT name, value FROM v$parameter WHERE name LIKE '%adaptive_statistics%';

2. 自动计划演变 ​

Oracle 21c的自动计划演变功能能够自动检测执行计划退化，并在确保性能提升的前提下平滑切换到更好的执行计划：

sql
-- 启用自动计划演变
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE SCOPE=BOTH;
ALTER SYSTEM SET optimizer_use_sql_plan_baselines = TRUE SCOPE=BOTH;
ALTER SYSTEM SET optimizer_plan_evolution = TRUE SCOPE=BOTH;

-- 查看自动计划演变状态
SELECT sql_handle, plan_name, enabled, accepted, fixed, autopurge
FROM dba_sql_plan_baselines
WHERE sql_id = '&sql_id';

-- 手动触发计划演变
DECLARE
  l_result PLS_INTEGER;
BEGIN
  l_result := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => '&sql_handle',
    time_limit => 60,
    verify => 'YES',
    commit => 'YES'
  );
  DBMS_OUTPUT.PUT_LINE('Evolved plans: ' || l_result);
END;
/

3. 增强的SQL Monitor ​

Oracle 21c的SQL Monitor提供了更详细的实时执行信息和可视化界面，便于DBA快速定位慢查询问题：

sql
-- 启用实时SQL监控（默认已启用）
ALTER SYSTEM SET control_management_pack_access = 'DIAGNOSTIC+TUNING' SCOPE=BOTH;

-- 查看当前正在监控的SQL
SELECT sql_id, sql_text, status, elapsed_time/1000000 AS elapsed_seconds
FROM v$sql_monitor
WHERE status = 'EXECUTING';

-- 生成SQL监控报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id', type => 'HTML') AS report
FROM dual;

4. 自动索引管理 ​

Oracle 21c引入了自动索引功能，能够自动创建、使用和删除索引，减少因缺少索引导致的慢查询：

sql
-- 启用自动索引功能
ALTER SYSTEM SET optimizer_auto_index_mode = 'IMPLEMENT' SCOPE=BOTH;

-- 查看自动索引配置
SELECT parameter_name, parameter_value
FROM dba_auto_index_config;

-- 查看自动创建的索引
SELECT index_name, table_name, status, created
FROM dba_auto_indexes;

-- 手动接受或拒绝自动索引建议
BEGIN
  DBMS_AUTO_INDEX.CONFIGURE(
    parameter_name => 'AUTO_INDEX_MODE',
    parameter_value => 'REPORT ONLY'  -- 仅生成报告，不自动实施
  );
END;
/

5. 增强的资源管理 ​

Oracle 21c的资源管理器提供了更细粒度的资源控制，能够更好地处理慢查询风暴：

sql
-- 创建细粒度资源计划
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    plan => 'DETAILED_RESOURCE_PLAN',
    comment => 'Detailed resource plan for slow query management'
  );
  
  -- 为不同类型的SQL分配资源
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'DETAILED_RESOURCE_PLAN',
    group_or_subplan => 'OLTP_GROUP',
    comment => 'OLTP transactions',
    cpu_p1 => 60,  -- 60% CPU资源
    parallel_degree_limit_p1 => 8,
    active_sessions_limit => 100,
    switch_time => 300,  -- 5分钟后切换到低优先级组
    switch_group => 'LOW_PRIORITY_GROUP'
  );
  
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'DETAILED_RESOURCE_PLAN',
    group_or_subplan => 'LOW_PRIORITY_GROUP',
    comment => 'Low priority queries',
    cpu_p1 => 10,  -- 10% CPU资源
    parallel_degree_limit_p1 => 2,
    active_sessions_limit => 20
  );
END;
/

-- 激活资源计划
ALTER SYSTEM SET resource_manager_plan = 'DETAILED_RESOURCE_PLAN' SCOPE=BOTH;

迁移建议：从19c到21c ​

对于从Oracle 19c迁移到21c的DBA，建议：

  1. 逐步启用新特性：先在测试环境验证新特性，再逐步在生产环境启用
  2. 重点关注自动计划演变：这是减少慢查询风暴的重要特性
  3. 利用自动索引功能：但要监控自动创建的索引，避免资源浪费
  4. 升级资源计划：利用21c的细粒度资源控制优化资源分配
  5. 增强监控能力：使用21c的增强版SQL Monitor提升慢查询检测能力
  6. 培训开发团队：让开发人员了解21c的新特性，优化SQL编写

Oracle 19c 慢查询优化最佳实践 ​

虽然Oracle 21c提供了更多自动化功能，但Oracle 19c仍然广泛使用。以下是19c的慢查询优化最佳实践：

  1. 定期更新统计信息：至少每周更新一次，大表每天更新
  2. 使用SQL Plan Baseline：固定关键SQL的执行计划
  3. 优化索引设计：根据查询模式创建合适的索引
  4. 监控慢查询日志：定期分析慢查询日志，优化频繁出现的SQL
  5. 调整内存配置：根据系统负载调整SGA和PGA大小
  6. 优化并行度：避免过高的并行度导致资源争用
  7. 使用资源管理器：为不同业务类型分配合理的资源
  8. 定期生成AWR报告：分析性能趋势，提前发现问题

慢查询风暴的预防措施 ​

1. 建立完善的SQL审核机制 ​

SQL审核全流程 ​

开发编写 → 本地测试 → 提交审核 → DBA分析 → 优化建议 → 修复验证 → 上线发布

开发阶段最佳实践 ​

  • 使用绑定变量：强制开发人员使用绑定变量，避免SQL注入和硬解析

    • 示例：SELECT * FROM users WHERE id = :id 而非 SELECT * FROM users WHERE id = 123
    • 工具：使用SQL Developer的"Bind Variable Advisor"检查绑定变量使用情况

  • IDE集成审核：在开发IDE中集成SQL审核工具，实时给出优化建议

    • 配置：Oracle SQL Developer → Preferences → Database → SQL Tuning Advisor
    • 规则：禁止全表扫描、禁止笛卡尔积连接、限制单条SQL的表连接数

  • 测试环境验证：开发人员必须在测试环境验证SQL性能

    • 要求：执行计划中无全表扫描（除非表数据量小于1万行）
    • 指标：单条SQL执行时间 < 500ms，逻辑读 < 1000

DBA审核重点 ​

  • 执行计划分析：使用DBMS_XPLAN.DISPLAY_CURSOR查看完整执行计划
  • 索引使用情况：确认SQL使用了合适的索引，避免索引失效
  • 统计信息验证：检查相关表的统计信息是否最新
  • 资源消耗评估：评估SQL在生产环境的资源消耗
  • 并发影响：考虑高并发情况下的性能表现

自动化SQL审核工具 ​

  • Oracle Enterprise Manager：内置SQL审核功能，支持批量审核
  • SQL Sentry：实时SQL性能监控和审核
  • Quest SQL Optimizer：自动生成优化建议
  • 开源工具：Percona Toolkit、pt-query-digest等
  • CI/CD集成：将SQL审核集成到Jenkins、GitLab CI等持续集成工具中

2. 定期性能分析与优化 ​

性能分析时间表 ​

频率分析内容责任人输出文档
每日慢查询日志分析（前20条）、数据库基本状态检查初级DBA每日性能日报
每周AWR报告分析、Top SQL优化、等待事件分析中级DBA每周性能周报
每月全面性能评估、索引优化、统计信息优化高级DBA月度性能报告
每季度数据库健康检查、架构评估、容量规划首席DBA季度性能评估报告

每日性能检查清单 ​

sql
-- 1. 检查慢查询数量
SELECT COUNT(*) FROM v$sql WHERE elapsed_time > 10000000;  -- 超过10秒的SQL

-- 2. 检查TOP 10慢查询
SELECT * FROM (
  SELECT sql_id, SUBSTR(sql_text, 1, 100) AS sql_text, 
         ROUND(elapsed_time/1000000, 2) AS elapsed_seconds
  FROM v$sql
  WHERE elapsed_time > 1000000
  ORDER BY elapsed_time DESC
) WHERE ROWNUM <= 10;

-- 3. 检查数据库基本状态
SELECT (SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE') AS active_sessions,
       (SELECT value FROM v$sysmetric WHERE metric_name = 'CPU Usage Per Sec' AND rownum = 1) AS cpu_usage,
       (SELECT ROUND(used_pct, 1) FROM v$pgastat WHERE name = 'aggregate PGA target parameter') AS pga_used_pct
FROM dual;

-- 4. 检查等待事件
SELECT event, COUNT(*) FROM v$session WHERE event NOT LIKE '%Idle%' GROUP BY event ORDER BY COUNT(*) DESC;

3. 索引和统计信息精细化管理 ​

索引管理最佳实践 ​

  • 索引设计原则

    • 为经常用于WHERE、JOIN、ORDER BY的列创建索引
    • 复合索引：将过滤性高的列放在前面
    • 避免创建过多索引（每个表建议不超过5个）
    • 删除未使用的索引（浪费资源和影响DML性能）

  • 索引监控与维护

    sql
    -- 启用索引监控
ALTER INDEX idx_orders_status MONITORING USAGE;

-- 查看索引使用情况
SELECT index_name, table_name, used, start_monitoring, end_monitoring
FROM v$object_usage;

-- 查找碎片化索引
SELECT index_name, table_name, 
       ROUND((1 - (leaf_blocks / blocks)) * 100, 2) AS fragmentation_pct
FROM user_indexes
WHERE (1 - (leaf_blocks / blocks)) * 100 > 30;

-- 重建碎片化索引
ALTER INDEX idx_orders_customer_id REBUILD ONLINE PARALLEL 4;
ALTER INDEX idx_orders_customer_id NOPARALLEL;

统计信息管理最佳实践 ​

  • 自动统计信息收集配置

    sql
    -- 检查自动统计信息收集任务
SELECT client_name, status FROM dba_autotask_client;

-- 调整收集窗口
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.MAINTENANCE_WINDOW_GROUP', 'repeat_interval', 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; DURATION=02:00');

-- 为特定表禁用自动收集
EXECUTE DBMS_STATS.LOCK_TABLE_STATS('&schema_name', '&table_name');

  • 手动收集策略

    • 大表：使用增量统计信息收集
    • 频繁变化的表：每日收集
    • 数据倾斜列：收集直方图
    • 分区表：使用granularity => 'AUTO'自动选择粒度

4. 资源限制与隔离 ​

资源配置文件管理 ​

sql
-- 创建不同级别的资源配置文件
-- 1. 核心业务用户配置文件
CREATE PROFILE core_user_profile LIMIT
  CPU_PER_SESSION 7200  -- 2小时CPU时间
  CPU_PER_CALL 600      -- 10分钟单次调用
  CONNECT_TIME 1440     -- 24小时连接时间
  IDLE_TIME 60          -- 1小时空闲时间
  LOGICAL_READS_PER_SESSION 10000000  -- 1000万逻辑读
  LOGICAL_READS_PER_CALL 1000000;     -- 100万单次调用

-- 2. 报表用户配置文件（限制更严格）
CREATE PROFILE report_user_profile LIMIT
  CPU_PER_SESSION 3600  -- 1小时CPU时间
  CPU_PER_CALL 300      -- 5分钟单次调用
  CONNECT_TIME 720      -- 12小时连接时间
  IDLE_TIME 30          -- 30分钟空闲时间
  LOGICAL_READS_PER_SESSION 5000000   -- 500万逻辑读
  LOGICAL_READS_PER_CALL 500000;      -- 50万单次调用

-- 分配资源配置文件
ALTER USER core_user PROFILE core_user_profile;
ALTER USER report_user PROFILE report_user_profile;

-- 启用资源限制
ALTER SYSTEM SET resource_limit = TRUE SCOPE=BOTH;

资源管理器配置 ​

sql
-- 创建资源使用者组
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('CORE_GROUP', 'Core business transactions');
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('REPORT_GROUP', 'Reporting and batch jobs');
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('LOW_PRIORITY_GROUP', 'Low priority ad-hoc queries');
END;
/

-- 创建资源计划
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN('PRODUCTION_PLAN', 'Production resource plan');
  
  -- 为核心业务分配70% CPU资源
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'PRODUCTION_PLAN',
    group_or_subplan => 'CORE_GROUP',
    comment => 'Core business transactions',
    cpu_p1 => 70,
    parallel_degree_limit_p1 => 12,
    active_sessions_limit => 200
  );
  
  -- 为报表分配20% CPU资源
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'PRODUCTION_PLAN',
    group_or_subplan => 'REPORT_GROUP',
    comment => 'Reporting and batch jobs',
    cpu_p1 => 20,
    parallel_degree_limit_p1 => 8,
    active_sessions_limit => 50
  );
  
  -- 为低优先级查询分配10% CPU资源
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'PRODUCTION_PLAN',
    group_or_subplan => 'LOW_PRIORITY_GROUP',
    comment => 'Low priority ad-hoc queries',
    cpu_p1 => 10,
    parallel_degree_limit_p1 => 4,
    active_sessions_limit => 20
  );
  
  -- 为系统进程保留资源
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'PRODUCTION_PLAN',
    group_or_subplan => 'SYS_GROUP',
    comment => 'System processes',
    cpu_p1 => 100,
    active_sessions_limit => unlimited
  );
END;
/

-- 激活资源计划
ALTER SYSTEM SET resource_manager_plan = 'PRODUCTION_PLAN' SCOPE=BOTH;

5. 完善的监控与告警机制 ​

监控体系架构 ​

  1. 实时监控层：监控数据库核心指标，如CPU、I/O、内存、会话数等
  2. 性能分析层：分析SQL执行计划、等待事件、资源消耗等
  3. 告警通知层：通过多种渠道发送告警，如邮件、短信、钉钉、微信等
  4. 历史存储层：存储性能数据，用于趋势分析和容量规划

关键监控指标与阈值 ​

指标类型指标名称正常范围警告阈值严重阈值告警方式
系统资源CPU使用率< 70%70-85%> 85%邮件
内存使用率< 80%80-90%> 90%邮件
磁盘I/O利用率< 70%70-90%> 90%短信
数据库活跃会话数< 50% of processes50-80% of processes> 80% of processes短信+钉钉
共享池命中率> 95%90-95%< 90%邮件
硬解析率< 5%5-10%> 10%邮件
SQL性能慢查询数/分钟< 55-10> 10短信+钉钉
单SQL执行时间< 1秒1-10秒> 10秒邮件
全表扫描次数/小时< 100100-500> 500邮件

监控工具推荐 ​

  • Oracle Enterprise Manager (OEM)：全面的Oracle数据库监控和管理
  • Prometheus + Grafana：开源监控解决方案，配合Oracle exporter
  • Zabbix：功能强大的开源监控系统，支持Oracle监控
  • Datadog：云原生监控平台，支持Oracle数据库
  • New Relic：应用性能监控，支持数据库监控

6. 建立应急响应机制 ​

慢查询风暴应急响应计划 ​

  1. 预警阶段：监控系统发出告警，DBA开始初步诊断
  2. 响应阶段：根据诊断结果采取相应措施（终止会话、修复执行计划等）
  3. 恢复阶段：验证系统恢复正常，监控关键指标
  4. 复盘阶段：分析故障原因，制定预防措施，更新应急计划

应急响应工具包 ​

  • 诊断脚本：预定义的SQL脚本，用于快速诊断问题
  • 修复工具：SQL Plan Baseline、SQL Profile创建脚本
  • 监控仪表盘：实时监控数据库状态的仪表盘
  • 沟通渠道：与业务、开发团队的沟通机制
  • 文档模板：故障报告模板，用于记录和分析故障

定期演练 ​

  • 每季度：进行一次慢查询风暴应急演练
  • 演练内容：模拟慢查询风暴场景，测试应急响应流程
  • 演练评估：评估响应时间、措施有效性、团队协作情况
  • 持续改进：根据演练结果优化应急响应计划

常见问题（FAQ） ​

1. 如何在高并发场景下快速识别慢查询风暴？ ​

答：在高并发场景下，慢查询风暴的识别需要结合多维度指标：

  • 实时监控：设置CPU使用率>85%、活跃会话数>80%的processes参数、单SQL执行时间>10秒等告警阈值
  • 等待事件分析：通过v$session_wait查看是否存在大量db file sequential read、CPU time等等待事件
  • SQL层面：使用v$sql_monitor实时查看正在执行的慢SQL，重点关注执行时间长、执行次数多的SQL
  • 应用层面：监控应用响应时间、错误率、超时率等指标，这些往往是慢查询风暴的第一信号

快速诊断脚本：

sql
-- 高并发下的慢查询风暴快速诊断
SELECT '当前时间' AS metric, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS value FROM dual
UNION ALL
SELECT '活跃会话数', TO_CHAR(COUNT(*)) FROM v$session WHERE status = 'ACTIVE'
UNION ALL
SELECT 'CPU使用率(%)', TO_CHAR(ROUND((SELECT value FROM v$sysmetric WHERE metric_name = 'CPU Usage Per Sec' AND rownum = 1), 1))
FROM dual
UNION ALL
SELECT '慢查询数(>10秒)', TO_CHAR(COUNT(*)) FROM v$sql WHERE elapsed_time > 10000000
UNION ALL
SELECT 'TOP等待事件', event FROM (
  SELECT event, COUNT(*) AS cnt FROM v$session WHERE event NOT LIKE '%Idle%' GROUP BY event ORDER BY cnt DESC
) WHERE ROWNUM = 1;

2. 如何在不影响业务的情况下处理慢查询风暴？ ​

答：处理慢查询风暴时，需要平衡故障处理和业务连续性：

  • 分级处理：优先终止长时间运行的非核心业务会话，对核心业务会话采取更谨慎的措施
  • 资源隔离：使用资源管理器为核心业务分配足够资源，限制慢查询的资源使用
  • 执行计划修复：使用SQL Plan Baseline或SQL Profile修复执行计划，避免终止会话
  • 在线索引创建：使用CREATE INDEX ... ONLINE创建缺失的索引，减少锁影响
  • 分批次处理：分批次终止会话，避免瞬间释放大量资源导致系统抖动

最佳实践：先使用资源管理器限制慢查询的资源，再逐步优化SQL，最后恢复正常资源分配

3. 如何处理因大量硬解析导致的慢查询风暴？ ​

答：大量硬解析通常是由于SQL未使用绑定变量导致的，处理步骤：

  1. 临时缓解：设置cursor_sharing=FORCE（谨慎使用，可能影响执行计划）

    sql
    ALTER SYSTEM SET cursor_sharing = FORCE SCOPE=BOTH;

  2. 根本解决：修改应用代码，使用绑定变量

  3. 监控硬解析：

    sql
    SELECT parse_calls, hard_parse_count, 
       ROUND(hard_parse_count / parse_calls * 100, 2) AS hard_parse_pct
FROM v$sysstat WHERE name = 'parse count (total)';

  4. 优化共享池：增加共享池大小，优化共享池内存分配

    sql
    ALTER SYSTEM SET shared_pool_size = GREATEST(shared_pool_size * 1.5, 8G) SCOPE=BOTH;

4. 如何防止统计信息更新导致的慢查询？ ​

答：统计信息更新可能导致执行计划变化，引发慢查询，可采取以下措施：

  • 使用统计信息锁定：对关键表的统计信息进行锁定，避免自动更新

    sql
    EXECUTE DBMS_STATS.LOCK_TABLE_STATS('&schema_name', '&table_name');

  • 使用统计信息历史：启用统计信息历史记录，便于回滚

    sql
    EXECUTE DBMS_STATS.SET_GLOBAL_PREFS('STATISTICS_HISTORY_RETENTION', '30');

  • 测试环境验证：在测试环境先更新统计信息，验证执行计划是否变化

  • 维护窗口更新：在业务低峰期更新统计信息，便于及时处理可能出现的问题

  • 使用SQL Plan Baseline：固定关键SQL的执行计划，防止统计信息更新导致执行计划变化

5. 如何优化数据量突增导致的慢查询？ ​

答：数据量突增是慢查询风暴的常见原因，处理方法：

  • 分区表：将大表改为分区表，按时间或业务维度分区

    sql
    CREATE TABLE orders (
  order_id NUMBER,
  order_date DATE,
  customer_id NUMBER
) PARTITION BY RANGE (order_date) (
  PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
  PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

  • 索引优化：重新评估索引设计，可能需要添加新的索引或修改现有索引

  • 执行计划调整：使用SQL Plan Baseline固定适合大数据量的执行计划

  • 统计信息更新：更新统计信息，确保优化器了解最新的数据分布

  • 查询重写：优化SQL语句，减少数据扫描量，如添加合理的过滤条件

6. 如何使用AWR报告分析慢查询风暴的根本原因？ ​

答：AWR报告是分析慢查询风暴根本原因的重要工具，重点关注：

  1. Load Profile：查看DB Time与Elapsed Time的比值，比值>2表示系统负载高
  2. Top Timed Events：识别主要等待事件，如CPU、I/O、锁等
  3. SQL Statistics：分析Top SQL by Elapsed Time、Buffer Gets、Disk Reads
  4. Instance Efficiency Percentages：检查命中率指标，尤其是Library Cache Hit Ratio
  5. SGA/PGA Statistics：查看内存使用情况，确认是否存在内存瓶颈
  6. Session Statistics：分析每秒执行的SQL数、解析数等
  7. Wait Events Histogram：查看等待事件的分布情况

分析技巧：结合ASH报告使用，ASH报告提供更细粒度的实时信息，AWR报告提供整体趋势

7. 如何处理Oracle 19c中的自适应执行计划不稳定问题？ ​

答：Oracle 19c的自适应执行计划可能存在不稳定问题，可采取以下措施：

  • 调整自适应执行计划参数：

    sql
    ALTER SYSTEM SET optimizer_adaptive_plans = TRUE SCOPE=BOTH;
ALTER SYSTEM SET optimizer_adaptive_reporting_only = FALSE SCOPE=BOTH;

  • 使用SQL Plan Baseline：固定执行计划，防止自适应调整导致的不稳定

  • 监控自适应执行：使用v$sql中的is_adaptive_plan列查看自适应执行计划的使用情况

  • 测试验证：在测试环境充分验证自适应执行计划的稳定性

8. 如何建立慢查询风暴的自动化处理机制？ ​

答：建立自动化处理机制可减少DBA的响应时间：

  • 自动化监控：使用Prometheus + Grafana、Zabbix等工具实时监控数据库状态
  • 自动化告警：设置多级告警，通过邮件、短信、钉钉等渠道通知
  • 自动化诊断：编写PL/SQL或Shell脚本，自动收集慢查询信息
  • 自动化修复：对于常见问题，如统计信息过期，可编写脚本自动更新
  • 自动化恢复：对于执行计划问题，可自动创建SQL Plan Baseline

示例：自动化统计信息更新脚本

sql
-- 自动更新统计信息的存储过程
CREATE OR REPLACE PROCEDURE auto_update_stats IS
BEGIN
  FOR r IN (
    SELECT owner, table_name
    FROM dba_tab_statistics
    WHERE last_analyzed < SYSDATE - 7  -- 超过7天未更新
    AND num_rows > 1000000  -- 大于100万行的表
    AND owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
  ) LOOP
    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(
        ownname => r.owner,
        tabname => r.table_name,
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt => 'FOR ALL COLUMNS SIZE AUTO',
        cascade => TRUE,
        degree => 8
      );
      DBMS_OUTPUT.PUT_LINE('Updated stats for ' || r.owner || '.' || r.table_name);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error updating stats for ' || r.owner || '.' || r.table_name || ': ' || SQLERRM);
    END;
  END LOOP;
END;
/

-- 调度作业，每天凌晨2点执行
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'AUTO_UPDATE_STATS_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN auto_update_stats; END;',
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0;',
    enabled => TRUE
  );
END;
/

9. 如何在读写分离架构中处理慢查询风暴？ ​

答：读写分离架构中的慢查询风暴处理需要考虑主从同步和负载均衡：

  • 监控从库：从库往往是慢查询的重灾区，需要重点监控
  • 读写分离策略调整：临时将部分读请求切换到主库，缓解从库压力
  • 从库资源隔离：为从库配置独立的资源管理器，限制慢查询资源
  • 主从同步优化：确保主从同步延迟在合理范围内，避免因同步问题导致的慢查询
  • 从库只读优化：针对从库的只读特性，优化索引和执行计划

10. 如何评估慢查询优化的效果？ ​

答：评估慢查询优化效果需要从多个维度进行：

  • 性能指标：SQL执行时间、逻辑读、物理读、CPU使用率等
  • 业务指标：应用响应时间、吞吐量、错误率等
  • 资源利用率：CPU、I/O、内存等资源的使用率变化
  • 执行计划：优化前后执行计划的变化，尤其是成本和行数估算
  • 长期趋势：观察优化后一段时间内的性能趋势，确认是否稳定

评估脚本：

sql
-- 优化前后SQL性能对比
SELECT 
  sql_id,
  executions,
  ROUND(elapsed_time/1000000/executions, 2) AS avg_elapsed_seconds,
  ROUND(buffer_gets/executions, 0) AS avg_buffer_gets,
  ROUND(disk_reads/executions, 0) AS avg_disk_reads
FROM v$sql
WHERE sql_id = '&sql_id' AND executions > 0;

最佳实践 ​

故障处理流程 ​

  1. 快速响应：收到慢查询告警后，立即开始诊断
  2. 分级处理：根据故障严重程度采取不同的处理措施
  3. 先治标后治本：先终止消耗资源过多的会话，再分析根本原因
  4. 记录过程：详细记录诊断过程和解决方案
  5. 根因分析：对慢查询风暴进行根因分析，防止类似故障再次发生
  6. 持续优化：根据故障分析结果，持续优化数据库和SQL语句

预防措施 ​

  1. 实施SQL审核机制：确保上线前的SQL语句经过性能测试
  2. 定期性能分析：定期生成和分析AWR报告，及时发现和处理慢查询
  3. 优化索引和统计信息：定期检查和优化索引，更新统计信息
  4. 配置合理的资源限制：使用资源配置文件和资源管理器限制资源使用
  5. 实施监控和告警：配置性能监控工具，设置性能告警
  6. 建立性能基线：记录正常负载下的性能指标，便于比较和分析性能变化
  7. 培训开发人员：提高开发人员的SQL编写能力，减少慢查询的产生
  8. 准备应急方案：制定慢查询风暴的应急响应计划，并定期演练

持续优化 ​

  1. 监控慢查询：定期监控和分析慢查询日志
  2. 优化执行计划：使用SQL Plan Baseline固定执行计划
  3. 优化索引：定期检查和优化索引
  4. 更新统计信息：定期更新统计信息
  5. 调整数据库配置：根据业务需求调整数据库配置
  6. 优化系统资源：根据负载情况增加系统资源

总结 ​

慢查询风暴是Oracle数据库常见的严重性能故障之一，对业务连续性造成极大威胁。快速定位和解决慢查询风暴问题对于DBA来说至关重要。通过掌握慢查询风暴的常见原因、诊断方法和解决方案，DBA可以有效地处理慢查询风暴，减少故障对业务的影响。

Oracle 19c和21c在慢查询处理方面有许多改进，特别是在执行计划管理、统计信息管理和资源管理方面。DBA应该充分利用这些新特性，提高慢查询处理的效率和准确性。

通过实施预防措施，如SQL审核机制、定期性能分析、优化索引和统计信息、配置合理的资源限制以及实施监控和告警，可以降低慢查询风暴的发生率，提高数据库的性能和可用性。

最后，DBA应该建立完善的慢查询处理流程和应急响应计划，并定期演练，以便在慢查询风暴发生时能够快速、有效地处理，减少故障对业务的影响。