印刻数据库

外观

Oracle 查询性能问题与解决方案 ​

慢查询分析 ​

识别慢查询 ​

问题现象 ​

  • 应用程序响应缓慢
  • 数据库CPU或I/O使用率过高
  • 用户投诉查询耗时过长

解决方案 ​

  • 启用慢查询日志：设置 SQL_TRACE 或 DBMS_PROFILER
  • 使用 V$SQL 和 V$SQLSTATS 视图分析长时间运行的SQL
  • 利用AWR/ASH报告识别Top SQL
  • 使用Oracle Enterprise Manager监控慢查询

示例 ​

sql
-- 启用SQL跟踪
ALTER SESSION SET sql_trace = true;

-- 使用DBMS_MONITOR启用会话跟踪
EXEC DBMS_MONITOR.session_trace_enable(session_id => <session_id>, waits => true, binds => true);

-- 查询Top 10慢查询
SELECT * FROM (
    SELECT sql_id, elapsed_time/1000000 as elapsed_seconds, sql_text
    FROM v$sql
    ORDER BY elapsed_time DESC
) WHERE ROWNUM <= 10;

执行计划分析 ​

问题现象 ​

  • 查询执行计划不理想
  • 未使用预期的索引
  • 出现全表扫描或笛卡尔积

解决方案 ​

  • 使用 EXPLAIN PLAN 生成执行计划
  • 分析执行计划中的成本、基数和访问路径
  • 检查统计信息是否过时
  • 考虑使用提示（Hint）优化执行计划

示例 ​

sql
-- 生成执行计划
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 使用AUTOTRACE查看执行计划和统计信息
SET AUTOTRACE ON
SELECT * FROM employees WHERE department_id = 10;

索引相关问题 ​

索引未被使用 ​

问题现象 ​

  • 查询应该使用索引但没有使用
  • 执行计划显示全表扫描

解决方案 ​

  • 检查索引是否存在且有效：SELECT status FROM dba_indexes WHERE index_name = '<index_name>';
  • 验证统计信息是否最新：EXEC DBMS_STATS.GATHER_TABLE_STATS('<owner>', '<table_name>');
  • 检查查询条件是否使用了函数或表达式：WHERE UPPER(column_name) = 'VALUE' 会导致索引失效
  • 考虑使用函数索引

示例 ​

sql
-- 检查索引状态
SELECT index_name, status FROM dba_indexes WHERE table_name = 'EMPLOYEES';

-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => true);

-- 创建函数索引
CREATE INDEX emp_last_name_upper_idx ON employees(UPPER(last_name));

索引碎片 ​

问题现象 ​

  • 索引扫描性能下降
  • 索引大小异常增长
  • 查询响应时间逐渐变长

解决方案 ​

  • 检查索引碎片：SELECT index_name, leaf_blocks, distinct_keys FROM dba_indexes WHERE table_name = '<table_name>';
  • 重建或重组索引：ALTER INDEX <index_name> REBUILD; 或 ALTER INDEX <index_name> COALESCE;
  • 定期维护索引统计信息

示例 ​

sql
-- 检查索引碎片
SELECT index_name, leaf_blocks, distinct_keys, 
       (leaf_blocks - num_rows) / leaf_blocks * 100 as fragmentation_pct
FROM dba_indexes WHERE table_name = 'EMPLOYEES';

-- 重建索引
ALTER INDEX emp_department_id_idx REBUILD;

-- 重组索引
ALTER INDEX emp_department_id_idx COALESCE;

SQL语句优化 ​

不合理的JOIN操作 ​

问题现象 ​

  • 查询包含多个表JOIN，执行时间过长
  • 执行计划显示笛卡尔积或嵌套循环效率低下

解决方案 ​

  • 优化JOIN顺序，将小表放在前面
  • 考虑使用更高效的JOIN类型（HASH JOIN、MERGE JOIN）
  • 确保JOIN条件上有合适的索引
  • 避免不必要的JOIN操作

示例 ​

sql
-- 优化前：不合理的JOIN顺序
SELECT * FROM large_table l JOIN small_table s ON l.id = s.id;

-- 优化后：调整JOIN顺序，使用提示
SELECT /*+ LEADING(s) USE_HASH(l) */ * 
FROM small_table s JOIN large_table l ON s.id = l.id;

不合理的WHERE条件 ​

问题现象 ​

  • 查询条件复杂，导致索引失效
  • 使用了NOT IN、!=、IS NULL等可能导致全表扫描的操作

解决方案 ​

  • 避免在WHERE条件中使用函数或表达式
  • 替换NOT IN为NOT EXISTS或LEFT JOIN
  • 替换IS NULL为合理的默认值
  • 使用BETWEEN替代多个OR条件

示例 ​

sql
-- 优化前：使用函数导致索引失效
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- 优化后：使用函数索引或调整查询
SELECT * FROM employees WHERE last_name = 'SMITH';

-- 优化前：使用NOT IN
SELECT * FROM employees WHERE department_id NOT IN (10, 20, 30);

-- 优化后：使用NOT EXISTS
SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.department_id IN (10, 20, 30) AND e.department_id = d.department_id);

统计信息问题 ​

统计信息过时 ​

问题现象 ​

  • 执行计划不准确
  • 优化器生成低效的查询计划
  • 查询性能突然下降

解决方案 ​

  • 定期收集统计信息：DBMS_STATS.GATHER_TABLE_STATS
  • 配置自动统计信息收集
  • 考虑使用动态采样
  • 收集系统统计信息以反映真实硬件性能

示例 ​

sql
-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => true, estimate_percent => 100);

-- 收集模式统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR', cascade => true);

-- 收集系统统计信息
EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

直方图缺失 ​

问题现象 ​

  • 数据分布不均匀的列上查询性能差
  • 优化器基数估计不准确

解决方案 ​

  • 为数据分布不均匀的列创建直方图
  • 使用 DBMS_STATS.GATHER_TABLE_STATS 时指定 method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  • 检查现有直方图：SELECT * FROM dba_histograms WHERE table_name = '<table_name>';

示例 ​

sql
-- 收集带有直方图的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', 
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', 
    cascade => true);

-- 检查直方图
SELECT column_name, histogram FROM dba_tab_col_statistics WHERE table_name = 'EMPLOYEES';

版本差异 ​

Oracle 11g ​

  • 支持基本的执行计划管理
  • 统计信息收集功能相对简单
  • 自动统计信息收集默认启用

Oracle 12c ​

  • 引入SQL计划管理（SPM）
  • 增强了统计信息收集功能
  • 支持实时统计信息
  • 引入自适应执行计划

Oracle 19c ​

  • 增强了SQL计划管理
  • 支持自动索引创建
  • 改进了统计信息管理
  • 引入SQL Performance Analyzer

Oracle 21c ​

  • 增强了自适应查询优化
  • 改进了自动索引功能
  • 支持机器学习辅助的执行计划优化
  • 引入实时统计信息增强

常见问题 ​

Q: 如何优化复杂查询？ ​

A: 分解复杂查询为多个简单查询，使用临时表存储中间结果，优化JOIN顺序，确保每个表都有合适的索引，收集准确的统计信息。

Q: 如何处理大量数据的查询？ ​

A: 使用分区表，优化索引设计，考虑使用并行查询，避免返回不必要的数据，使用分页查询，考虑使用物化视图。

Q: 如何判断索引是否需要创建？ ​

A: 分析查询的WHERE条件和JOIN条件，考虑数据分布和查询频率，使用Oracle SQL Access Advisor工具建议索引创建。

Q: 如何优化排序操作？ ​

A: 确保排序操作在索引中完成，增加PGA内存，调整SORT_AREA_SIZE参数，考虑使用并行排序，避免不必要的排序。

Q: 如何处理锁等待导致的查询延迟？ ​

A: 识别锁持有会话：SELECT * FROM v$lock;，分析锁类型和等待时间，考虑优化事务设计，减少锁定时间，使用适当的隔离级别。

Q: 如何优化分页查询？ ​

A: 使用ROWNUM或FETCH FIRST语法，确保分页查询使用索引，避免在分页查询中使用复杂JOIN或排序，考虑使用物化视图。

Q: 如何使用提示（Hint）优化查询？ ​

A: 了解各种提示的作用，如LEADING、USE_HASH、INDEX等，仅在必要时使用提示，定期审查提示的有效性，考虑使用SQL计划管理替代提示。

Q: 如何监控查询性能？ ​

A: 使用AWR/ASH报告，监控V$SQL和V$SQLSTATS视图，配置Oracle Enterprise Manager告警，启用SQL监控，定期分析慢查询日志。

Q: 如何处理执行计划不稳定的问题？ ​

A: 使用SQL计划管理（SPM）锁定执行计划，收集准确的统计信息，避免使用绑定变量窥探，考虑使用SQL Profile稳定执行计划。

Q: 如何优化聚合查询？ ​

A: 确保GROUP BY列有索引，考虑使用物化视图预计算聚合结果，使用并行查询，调整PGA内存，优化排序操作。