位置：文档库 > 数据库 > Understand Oracle执行计划

Understand Oracle执行计划

鉴赏家上传于 2022-12-15 07:21

《Understand Oracle执行计划：从原理到实践的深度解析》

一、执行计划的核心价值

Oracle数据库的执行计划（Execution Plan）是SQL语句优化的核心依据，它揭示了数据库优化器（CBO）如何解析SQL并选择数据访问路径。理解执行计划能帮助开发者识别性能瓶颈，避免无效的索引使用、全表扫描等低效操作。据统计，70%以上的数据库性能问题源于执行计划选择不当，因此掌握执行计划分析是DBA和开发人员的必备技能。

二、执行计划的生成机制

1. 优化器工作模式

Oracle优化器分为基于规则（RBO）和基于成本（CBO）两种模式。现代Oracle版本默认使用CBO，通过统计信息计算不同执行路径的成本（Cost），选择最低成本的方案。统计信息包括表大小、索引选择性、数据分布等，可通过以下命令收集：

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

2. 执行计划组成要素

一个典型的执行计划包含以下关键列：

Operation：操作类型（TABLE ACCESS FULL/INDEX SCAN）
Name：关联的对象名（表名、索引名）
Rows：预估返回的行数
Bytes：预估返回的数据量
Cost：优化器计算的成本值
Time：预估执行时间
Access Predicates：访问谓词（过滤条件）
Filter Predicates：过滤谓词（执行后过滤）

三、获取执行计划的方法

1. EXPLAIN PLAN命令

通过SQL*Plus的EXPLAIN PLAN生成执行计划（结果存储在PLAN_TABLE中）：

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. SQL*Plus自动跟踪

启用AUTOTRACE查看实时执行计划：

SET AUTOTRACE ON EXPLAIN;
SELECT * FROM employees WHERE department_id = 10;

3. DBMS_XPLAN包

使用DBMS_XPLAN.DISPLAY_CURSOR获取实际执行计划（包含绑定变量信息）：

SELECT sql_id FROM v$sql WHERE sql_text LIKE '%employees%department_id%';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', null, 'ALLSTATS LAST'));

4. 图形化工具

Oracle SQL Developer、Toad等工具提供可视化执行计划分析，支持缩放查看操作层级关系。

四、执行计划关键操作解析

1. 表访问操作

TABLE ACCESS FULL：全表扫描，适用于小表或无合适索引的情况。若大表出现全表扫描，需检查是否缺少索引或统计信息过时。

TABLE ACCESS BY INDEX ROWID：通过索引定位行，需配合INDEX SCAN使用。

2. 索引操作

INDEX UNIQUE SCAN：唯一索引扫描，适用于等值查询（如主键查询）。

INDEX RANGE SCAN：范围索引扫描，适用于非唯一索引的范围查询。

INDEX SKIP SCAN：跳跃索引扫描，当复合索引首列选择性低时，优化器可能跳过首列直接扫描后续列。

INDEX FAST FULL SCAN：快速全索引扫描，适用于需要访问索引中大部分数据的情况。

3. 连接操作

HASH JOIN：适用于大表连接，通过哈希表实现。需关注内存分配（WORKAREA_SIZE_POLICY）。

NESTED LOOPS：嵌套循环连接，适用于小表驱动大表的情况。若内层表出现多次访问，可能需优化。

MERGE JOIN：排序合并连接，要求连接列已排序。常用于等值连接且数据有序的场景。

4. 排序操作

SORT ORDER BY：显式排序，可能引发磁盘排序（TEMP空间使用）。

SORT AGGREGATE：聚合操作排序，如GROUP BY。

SORT GROUP BY：分组排序，需检查是否可利用索引避免排序。

五、执行计划优化实战

1. 索引优化案例

问题SQL：

SELECT * FROM orders WHERE order_date > SYSDATE - 30 AND status = 'SHIPPED';

原执行计划显示全表扫描，原因是复合索引（status, order_date）的列顺序不合理。优化后创建新索引：

CREATE INDEX idx_orders_date_status ON orders(order_date, status);

新执行计划改用INDEX RANGE SCAN，响应时间从2.3秒降至0.15秒。

2. 连接条件优化

问题SQL：

SELECT e.name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.salary > 10000;

原执行计划使用NESTED LOOPS，外层为departments（小表），内层为employees（大表）。但内层表因salary过滤后数据量大幅减少，优化为HASH JOIN更高效：

/*+ HASH_JOIN(e d) */
SELECT e.name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.salary > 10000;

3. 统计信息过时问题

现象：执行计划突然变差，预估行数与实际严重不符。解决方案：

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

-- 收集系统统计信息（影响成本计算）
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');
-- 运行典型负载后
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

六、高级执行计划分析技巧

1. 比较不同SQL的执行计划

使用DBMS_XPLAN.DIFF_PLANS比较两个SQL的执行计划差异：

SELECT * FROM TABLE(
  DBMS_XPLAN.DIFF_PLANS(
    plan1 => 'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''sql_id1''))',
    plan2 => 'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''sql_id2''))'
  )
);

2. 识别执行计划突变

通过AWR报告检查执行计划历史：

SELECT sql_id, executions, elapsed_time/1000000 elapsed_sec
FROM dba_hist_sqlstat
WHERE sql_id = 'your_sql_id'
ORDER BY snap_id DESC;

3. 使用SQL Profile固定执行计划

当优化器选择不稳定时，可通过SQL Profile强制使用特定计划：

-- 创建SQL Profile
DECLARE
  l_plans_profile_name VARCHAR2(30);
BEGIN
  l_plans_profile_name := DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
    sql_text    => 'SELECT * FROM employees WHERE department_id = :dept_id',
    profile     => sqlprof_attr('FULL(e@SEL$1)'),
    name        => 'emp_dept_profile',
    description => 'profile to force full scan',
    force_match => TRUE
  );
END;
/

七、常见执行计划问题诊断

1. 全表扫描问题

检查点：

是否存在合适的索引？
统计信息是否准确？
查询条件的选择性是否过低？
是否可改写为索引覆盖查询？

2. 索引未使用问题

可能原因：

函数操作导致索引失效（如UPPER(column)）
隐式类型转换（如varchar列与number比较）
索引列选择性过低（如性别字段）
优化器认为全表扫描成本更低

3. 连接性能差问题

诊断步骤：

检查连接条件是否被正确推导
确认连接顺序是否合理（小表驱动大表）
检查是否缺少连接列索引
分析是否存在数据倾斜（如某部门员工数过多）

八、执行计划监控与维护

1. 持续监控工具

AWR报告：定期生成AWR报告（默认每小时一次），分析TOP SQL的执行计划变化
ASH报告：实时活动会话历史，查看当前执行的SQL及其执行计划
SQL Monitor：对长时间运行的SQL自动监控（EXECUTION_NAME列）

2. 执行计划缓存管理

查看共享池中的执行计划：

SELECT sql_id, executions, loads, invalidations
FROM v$sqlarea
WHERE sql_text LIKE '%employees%department_id%'
ORDER BY last_active_time DESC;

清除问题SQL的执行计划：

ALTER SYSTEM FLUSH SHARED_POOL;

3. 基线与补丁管理

使用SQL Plan Management（SPM）创建执行计划基线：

-- 创建基线
DECLARE
  l_plans_created NUMBER;
BEGIN
  l_plans_created := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name  => 'my_sqlset',
    basic_filter => 'sql_text like ''%employees%department_id%''',
    fixed       => 'NO'  -- 设置为YES可固定计划
  );
END;
/

关键词：Oracle执行计划、CBO优化器、EXPLAIN PLAN、DBMS_XPLAN、索引扫描、连接操作、统计信息、SQL Profile、执行计划突变、SPM基线

简介：本文系统阐述Oracle执行计划的生成机制、获取方法、关键操作解析及优化实践。通过代码示例和案例分析，讲解如何利用执行计划诊断SQL性能问题，涵盖索引优化、连接条件调整、统计信息管理等核心场景，并提供高级分析技巧与维护策略。

立即下载

数据库相关