一、执行计划的本质

执行计划是数据库优化器基于成本模型计算得出的SQL执行方案。优化器的核心逻辑：

输入：SQL语句、表结构（字段类型、索引）、统计信息（数据量、值分布）、系统参数
计算：枚举所有可能的执行路径，计算每条路径的成本（CPU、I/O、内存）
输出：选择成本最低的路径，生成执行计划

查看执行计划的方法

– 基础查看（文本树形式）

EXPLAIN SELECT * FROM dmhr.employee WHERE department_id = 103;

– 开启监控后查看历史计划

ALTER SESSION SET 'ENABLE_MONITOR' = 1;
SELECT * FROM V$SQL_PLAN WHERE SQLSTR LIKE '%department_id%';

执行计划解读规则：

1、执行计划呈现为树形结构：
2、缩进越深的节点越先执行
3、同样缩进的上面的先执行，下面的后执行
4、控制流从上向下传递，数据流从下向上传递

二、核心操作符详解

2.1 表扫描操作符

CSCN2：聚集索引全扫描。小表或需全表数据时可接受；大表需添加过滤条件或索引
SSCN：二级索引全扫描，需回表，避免大结果集
SSEK2：二级索引范围扫描，高效，优先使用
CSEK2：聚集索引范围扫描，最优，直接获取完整数据
BLKUP2：回表操作，频繁出现时考虑覆盖索引

2.2 连接操作符

NEST LOOP JOIN2：嵌套循环连接，建议驱动表有强过滤条件时使用
NEST LOOP INDEX JOIN2：索引嵌套循环连接
HASH2 INNER JOIN：哈希内连接，可处理大数据量等值连接
MERGE INNER JOIN3：归并内连接，两表连接列均为索引列，尤其是非等值连接

2.3 聚合操作符

AAGR2：简单聚集，无GROUP BY时直接计算集函数
HAGR2：哈希分组，分组列无索引时的分组聚合
SAGR2：排序分组，利用索引有序性进行分组，不一定比HAGR更优，需根据统计信息决定

2.4 其他操作符

PRJT2：投影，选择返回列
SLCT2：选择，条件过滤
NSET2：结果集收集，计划顶层节点
PARALLEL：分区表子分区裁剪/扫描控制
GI：DMDPC环境下控制数据访问粒度和分区裁剪

### 三、SQL调优场景

场景一：索引调优——解决全表扫描瓶颈

问题SQL： 查询部门103、2010年后入职的员工信息，执行计划显示CSCN2全表扫描。

-- 低效SQL（全表扫描）
SELECT employee_id, employee_name, salary, hire_date
FROM dmhr.employee
WHERE department_id = 103 
  AND hire_date >= TO_DATE('2010-01-01', 'YYYY-MM-DD');

-- 优化：创建复合索引（等值条件列在前，范围条件列在后）
CREATE INDEX IDX_EMP_DEPT_HIRE ON dmhr.employee(department_id, hire_date);

– 优化后执行计划变为SSEK2索引范围扫描

索引设计原则：
1、等值查询列放在索引前面，范围查询列放在后面
2、高频查询考虑覆盖索引（包含所有SELECT列）避免回表
3、过滤性好的列优先作为索引前导列

场景二：子查询改写——避免重复计算开销

问题SQL： 查询薪资高于本部门平均值的员工，使用了相关子查询（外层每行触发一次子查询）。

-- 低效SQL（相关子查询）
SELECT e.employee_id, e.employee_name, e.department_id, e.salary
FROM dmhr.employee e
WHERE e.salary > (
    SELECT AVG(salary) 
    FROM dmhr.employee 
    WHERE department_id = e.department_id  -- 关联条件导致每行执行一次
);

-- 优化：改写为非相关子查询 + JOIN（只扫描1次表）
SELECT e.employee_id, e.employee_name, e.department_id, e.salary
FROM dmhr.employee e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM dmhr.employee
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

场景三：WITH子句复用——减少表扫描次数

问题SQL： 多个标量子查询重复扫描同一张表，导致LOG_ADDSALARY表被扫描4次。

-- 优化：用WITH提取公共数据，仅扫描1次表
WITH salary_log_2025 AS (
    SELECT 
        department_id,
        newsalary - oldsalary AS salary_increase
    FROM dmhr.log_addsalary
    WHERE LOGTIME BETWEEN '2025-01-01' AND '2025-12-31'
)
SELECT 
    d.department_id,
    d.department_name,
    COUNT(log.salary_increase) AS 调薪人数,
    AVG(log.salary_increase) AS 平均调薪幅度
FROM dmhr.department d
LEFT JOIN salary_log_2025 log ON d.department_id = log.department_id
GROUP BY d.department_id, d.department_name;

四、使用优化器提示（HINT）

DBA可通过HINT强制优化器选择指定的执行计划。

方式一：表名后直接跟INDEX

SELECT * FROM dmhr.employee INDEX IDX_EMP_DEPT_HIRE 
WHERE department_id = 103;

方式二：HINT写法

SELECT /*+ INDEX(EMPLOYEE, IDX_EMP_DEPT_HIRE) */ 
    employee_id, employee_name
FROM dmhr.employee
WHERE department_id = 103;

强制使用哈希连接

SELECT /*+ USE_HASH(EMPLOYEE, DEPARTMENT) */ *
FROM dmhr.employee e, dmhr.department d
WHERE e.department_id = d.department_id;

常用HINT：

INDEX(表名, 索引名)：强制使用指定索引
FULL(表名)：强制全表扫描
USE_HASH(表1, 表2)：强制使用哈希连接
USE_NL(表1, 表2)：强制使用嵌套循环连接
USE_MERGE(表1, 表2)：强制使用归并连接
PARALLEL(并行度)：启用并行查询

五、统计信息管理

统计信息是优化器代价估算的依据，定期收集统计信息可提升执行计划准确性。

收集统计信息：

– 收集表统计信息（使用DBMS_STATS包）

CALL SP_CREATE_SYSTEM_PACKAGES(1, 'DBMS_STATS');
DBMS_STATS.GATHER_TABLE_STATS('DMHR', 'EMPLOYEE');

– 收集索引统计信息

DBMS_STATS.GATHER_INDEX_STATS('DMHR', 'IDX_EMP_DEPT_HIRE');

– 或使用STAT语句

STAT 30 ON SYS.SYSOBJECTS(ID);
STAT ON DMHR.EMPLOYEE;

– 查看统计信息

SELECT * FROM SYSSTATS WHERE ID = (SELECT ID FROM SYSOBJECTS WHERE NAME = 'EMPLOYEE');

六、高效SQL编写

1、避免使用SELECT * ，尽量只选择需要的列。

2、优先使用COUNT(*)统计行数，使用count（列名）时，需要读取实际数据，会增加不必要的开销。

3、使用IN替代OR。

4、LIKE通配符优化，遵循联合索引的最左匹配原则，避免索引失效。

5、UNION和UNION ALL的选择，UNION ALL不会去重，性能更优。而UINION会缓存数据并去重，有一定的开销。

6、GROUP BY + HAVING优化

-- 低效：HAVING中包含普通过滤条件
SELECT department_id, AVG(salary) 
FROM dmhr.employee 
GROUP BY department_id 
HAVING department_id = 103;

-- 高效：放在WHERE中提前过滤
SELECT department_id, AVG(salary) 
FROM dmhr.employee 
WHERE department_id = 103 
GROUP BY department_id;