DB / Oracle / 性能诊断

Oracle Adaptive Features（自适应特性）

2026.09.06

Oracle Adaptive Features（自适应特性）

适用版本：Oracle Database 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

Oracle 12c 引入自适应优化特性[1]：

特性：

  • Adaptive Plans（自适应计划）
  • Adaptive Statistics（自适应统计）
  • Adaptive Cursor Sharing（自适应游标）

2. Adaptive Plans

2.1 概述

  • 执行时根据实际数据调整计划
  • 默认启用

2.2 配置

-- 12c
ALTER SYSTEM SET optimizer_adaptive_plans = TRUE;  -- 默认

-- 19c
ALTER SYSTEM SET optimizer_adaptive_plans = TRUE;

2.3 查看

SELECT sql_id, child_number, plan_hash_value
FROM v$sql
WHERE sql_text LIKE '%...%';

-- Note 行
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
-- - this is an adaptive plan

2.4 机制

  • 默认计划
  • 监控统计
  • 切换优化

2.5 Adaptive JOIN

  • Nested Loop vs Hash Join
  • 根据行数自动切换

3. Adaptive Statistics

3.1 概述

  • 12c：默认启用
  • 12.2：默认禁用（性能问题）
  • 19c：默认禁用

3.2 配置

ALTER SYSTEM SET optimizer_adaptive_statistics = FALSE;  -- 推荐

3.3 动态统计

-- 自动动态采样
SHOW PARAMETER optimizer_dynamic_sampling

-- 12c+
ALTER SYSTEM SET optimizer_dynamic_sampling = 11;
-- 11: 自适应

3.4 SQL Plan Directives

-- 自动创建指令
SELECT * FROM dba_sql_plan_directives;

4. Adaptive Cursor Sharing

4.1 概述

  • 11g 引入
  • 绑定变量窥视
  • 多子游标

4.2 配置

ALTER SYSTEM SET optimizer_adaptive_cursor_sharing = TRUE;  -- 默认

4.3 查看

SELECT 
  sql_id,
  child_number,
  bind_sensitive,
  bind_aware
FROM v$sql
WHERE sql_id = '&sql_id';

4.4 机制

  • 绑定敏感：数据倾斜
  • 绑定感知：生成多计划

5. Adaptive Query Optimization

5.1 整体

-- 12c+
ALTER SYSTEM SET optimizer_adaptive_plans = TRUE;
ALTER SYSTEM SET optimizer_adaptive_statistics = FALSE;

5.2 19c 推荐

-- 默认配置
optimizer_adaptive_plans = TRUE
optimizer_adaptive_statistics = FALSE

6. 12c 性能问题

6.1 12.1 问题

  • Adaptive Statistics 性能差
  • 大量动态采样
  • SQL Plan Directives 失控

6.2 解决

-- 12.1
ALTER SYSTEM SET optimizer_adaptive_features = FALSE;

-- 12.2+
ALTER SYSTEM SET optimizer_adaptive_statistics = FALSE;

7. Adaptive Join

7.1 示例

SELECT * FROM employees e, departments d
WHERE e.dept_id = d.id;

-- 默认计划：Nested Loop
-- 若数据多：切换 Hash Join

7.2 执行计划

| Id | Operation             | Name     |
|  0 | SELECT STATEMENT      |          |
|* 1 |  HASH JOIN            |          |  -- 切换
|  2 |   TABLE ACCESS FULL   | DEPT     |
|  3 |   TABLE ACCESS FULL   | EMP      |

Note: adaptive plan

8. Adaptive Parallel

8.1 自动 DOP

ALTER SYSTEM SET parallel_degree_policy = AUTO;
-- 自动决定 DOP

8.2 并行队列

ALTER SYSTEM SET parallel_servers_target = 24;
-- 超过排队

9. 监控

9.1 Adaptive Plans

SELECT 
  sql_id,
  plan_hash_value,
  child_number
FROM v$sql_plan
WHERE id = 1
  AND operation LIKE '%ADAPTIVE%';

9.2 SQL Plan Directives

SELECT 
  directive_id,
  type,
  state,
  enabled
FROM dba_sql_plan_directives;

9.3 动态采样

SELECT name, value FROM v$sysstat 
WHERE name LIKE '%dynamic%';

10. 常见坑与排错

10.1 12.1 性能问题

-- 禁用 Adaptive Features
ALTER SYSTEM SET optimizer_adaptive_features = FALSE;

10.2 计划不稳定

-- 1. Adaptive Plans
ALTER SYSTEM SET optimizer_adaptive_plans = FALSE;

-- 2. Adaptive Statistics
ALTER SYSTEM SET optimizer_adaptive_statistics = FALSE;

-- 3. SQL Plan Baseline 固定

10.3 SQL Plan Directives 过多

-- 清理
EXEC DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE(:directive_id);

11. 最佳实践

  1. 19c 默认配置：合理
  2. 12.1 禁用：避免问题
  3. Adaptive Plans 启用：性能
  4. Adaptive Statistics 禁用：稳定
  5. Adaptive Cursor Sharing：默认
  6. 监控子游标：避免过多
  7. SQL Plan Baseline：稳定
  8. 测试验证：业务
  9. 定期收集统计：基础
  10. 结合 HINT：精准

12. 参考资料

[1] Oracle Database SQL Tuning Guide 19c, “Adaptive Query Optimization” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/adaptive-query-optimization.html