Oracle教程FG079-执行计划分析

Part02-生产环境规划与建议

2.1 查看执行计划的方法

查看执行计划的常用方法：

EXPLAIN PLAN：生成执行计划并存储在PLAN_TABLE表中
SQL*Plus AUTOTRACE：显示执行计划和统计信息
DBMS_XPLAN.DISPLAY：显示存储在PLAN_TABLE表中的执行计划
SQL Developer：图形化界面查看执行计划
Enterprise Manager：通过EM查看执行计划

风哥提示：在生产环境中，查看执行计划是SQL调优的重要步骤，应掌握多种查看方法。

2.2 执行计划的解读技巧

执行计划的解读技巧：

从下往上解读：执行计划是自下而上执行的
关注操作类型：全表扫描（TABLE ACCESS FULL）通常效率较低
关注成本：成本高的操作通常是性能瓶颈
关注估计行数：估计行数与实际行数的差异可能导致执行计划选择错误
关注谓词信息：了解SQL语句的过滤条件
关注连接方式：不同的连接方式适用于不同的场景

更多学习教程公众号风哥教程itpux_com

2.3 执行计划的优化建议

根据执行计划的优化建议：

全表扫描优化：为经常查询的列创建索引
索引扫描优化：确保索引被正确使用，避免索引失效
连接方式优化：选择合适的连接方式，如小表使用NESTED LOOPS，大表使用HASH JOIN
谓词优化：优化WHERE子句，避免在WHERE子句中使用函数
统计信息优化：确保表和索引的统计信息是最新的
SQL语句优化：修改SQL语句结构，如避免使用SELECT *

from oracle:www.itpux.com

Part03-生产环境项目实施方案

在生产环境中分析执行计划的实施方案：

识别问题SQL：通过AWR报告、ASH报告等工具识别执行时间长、资源消耗大的SQL语句。
查看执行计划：使用EXPLAIN PLAN、SQL*Plus AUTOTRACE等方法查看SQL语句的执行计划。
分析执行计划：分析执行计划中的操作类型、成本、估计行数等信息，找出性能瓶颈。
制定优化方案：根据执行计划分析结果，制定优化方案，如创建索引、修改SQL语句等。
实施优化方案：实施优化方案，如创建索引、修改SQL语句等。
验证优化效果：再次查看执行计划，验证优化效果。
监控维护：定期监控SQL语句的执行情况，及时调整执行计划。

Part04-生产案例与实战讲解

4.1 EXPLAIN PLAN实战

示例：使用EXPLAIN PLAN查看执行计划

— 创建示例表
CREATE TABLE fgedu_employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
emp_dept VARCHAR2(50),
emp_salary NUMBER,
hire_date DATE
);/– 插入示例数据
INSERT INTO fgedu_employees VALUES (1001, ‘风哥1号’, ‘技术部’, 5000, SYSDATE – 365);INSERT INTO fgedu_employees VALUES (1002, ‘风哥2号’, ‘市场部’, 6000, SYSDATE – 180);INSERT INTO fgedu_employees VALUES (1003, ‘风哥3号’, ‘技术部’, 5500, SYSDATE – 90);INSERT INTO fgedu_employees VALUES (1004, ‘赵六’, ‘市场部’, 6500, SYSDATE – 60);INSERT INTO fgedu_employees VALUES (1005, ‘孙七’, ‘财务部’, 5800, SYSDATE – 30);– 插入更多数据
BEGIN
FOR i IN 1006 .. 10000 LOOP
INSERT INTO fgedu_employees VALUES (
i,
‘员工’ || TO_CHAR(i),
CASE MOD(i, 3)
WHEN 0 THEN ‘技术部’
WHEN 1 THEN ‘市场部’
ELSE ‘财务部’
END,
5000 + MOD(i, 2000),
SYSDATE – MOD(i, 365)
);END LOOP;COMMIT;END;/– 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SYS’, ‘FGEDU_EMPLOYEES’);– 使用EXPLAIN PLAN生成执行计划
EXPLAIN PLAN FOR
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;– 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 带有谓词信息的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, ‘ALL’));– 带有别名和投影信息的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, ‘ALL ALLSTATS LAST’));

— 基本执行计划
Plan hash value: 3883868984

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 3333 | 232K| 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FGEDU_EMPLOYEES | 3333 | 232K| 15 (0)| 00:00:01 |
——————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – filter(“EMP_DEPT”=’技术部’ AND “EMP_SALARY”>5000)

— 带有谓词信息的执行计划
Plan hash value: 3883868984

Predicate Information (identified by operation id):
—————————————————

1 – filter(“EMP_DEPT”=’技术部’ AND “EMP_SALARY”>5000)

更多视频教程www.fgedu.net.cn

4.2 SQL*Plus AUTOTRACE实战

示例：使用SQL*Plus AUTOTRACE查看执行计划

— 启用AUTOTRACE
SET AUTOTRACE ON;– 执行SQL语句
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;– 启用AUTOTRACE但不显示查询结果
SET AUTOTRACE ON EXPLAIN;– 执行SQL语句
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;– 启用AUTOTRACE并显示统计信息
SET AUTOTRACE ON STATISTICS;– 执行SQL语句
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;– 关闭AUTOTRACE
SET AUTOTRACE OFF;

— 启用AUTOTRACE后的输出
EMP_ID EMP_NAME EMP_DEPT EMP_SALARY HIRE_DATE
—— ——— ——— ———- ———
1001 风哥1号技术部 5000 2025-03-31
1003 风哥3号技术部 5500 2025-12-31
1006 员工1006 技术部 5000 2025-03-31
1009 员工1009 技术部 5003 2025-03-28
1012 员工1012 技术部 5006 2025-03-25
…

Execution Plan
———————————————————-
Plan hash value: 3883868984

Predicate Information (identified by operation id):
—————————————————

1 – filter(“EMP_DEPT”=’技术部’ AND “EMP_SALARY”>5000)

Statistics
———————————————————-
0 recursive calls
0 db block gets
42 consistent gets
0 physical reads
0 redo size
17798 bytes sent via SQL*Net to client
532 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
334 rows processed

学习交流加群风哥微信: itpux-com

4.3 执行计划分析实战

示例：执行计划分析与优化

— 创建索引前的执行计划
EXPLAIN PLAN FOR
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 创建索引
CREATE INDEX idx_fgedu_emp_dept_salary ON fgedu_employees(emp_dept, emp_salary);– 创建索引后的执行计划
EXPLAIN PLAN FOR
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 测试不同条件的执行计划
EXPLAIN PLAN FOR
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);EXPLAIN PLAN FOR
SELECT * FROM fgedu_employees WHERE emp_salary > 5000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 测试JOIN查询的执行计划
CREATE TABLE fgedu_departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(50),
dept_location VARCHAR2(100)
);/INSERT INTO fgedu_departments VALUES (1, ‘技术部’, ‘北京’);INSERT INTO fgedu_departments VALUES (2, ‘市场部’, ‘上海’);INSERT INTO fgedu_departments VALUES (3, ‘财务部’, ‘广州’);COMMIT;/EXPLAIN PLAN FOR
SELECT e.emp_id, e.emp_name, e.emp_salary, d.dept_name, d.dept_location
FROM fgedu_employees e
JOIN fgedu_departments d ON e.emp_dept = d.dept_name
WHERE e.emp_salary > 6000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

— 创建索引前的执行计划
Plan hash value: 3883868984

— 创建索引后的执行计划
Plan hash value: 3711770936

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 3333 | 232K| 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGEDU_EMPLOYEES | 3333 | 232K| 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FGEDU_EMP_DEPT_SALARY | 3333 | | 2 (0)| 00:00:01 |
—————————————————————————————–

— 只使用emp_dept条件的执行计划
Plan hash value: 3711770936

— 只使用emp_salary条件的执行计划
Plan hash value: 3883868984

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 5000 | 351K| 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FGEDU_EMPLOYEES | 5000 | 351K| 15 (0)| 00:00:01 |
——————————————————————————-

— JOIN查询的执行计划
Plan hash value: 3393241698

——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 1667 | 76682 | 17 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1667 | 76682 | 17 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | FGEDU_EMPLOYEES | 1667 | 53344 | 15 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| FGEDU_DEPARTMENTS | 1 | 14 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C0014002 | 1 | | 0 (0)| 00:00:01 |
——————————————————————————————–

学习交流加群风哥QQ113257174

Part05-风哥经验总结与分享

风哥提示：在分析执行计划时，应注意以下几点：

理解执行计划的结构：执行计划是自下而上执行的，应从最底层的操作开始分析。
关注操作类型：全表扫描（TABLE ACCESS FULL）通常效率较低，应考虑为经常查询的列创建索引。
关注成本：成本高的操作通常是性能瓶颈，应重点优化。
关注估计行数：估计行数与实际行数的差异可能导致执行计划选择错误，应确保统计信息是最新的。
关注谓词信息：了解SQL语句的过滤条件，避免在WHERE子句中使用函数，以免索引失效。
关注连接方式：不同的连接方式适用于不同的场景，小表使用NESTED LOOPS，大表使用HASH JOIN。
使用多种工具：结合使用EXPLAIN PLAN、SQL*Plus AUTOTRACE等工具，全面了解执行计划。
定期分析：定期分析SQL语句的执行计划，及时发现和解决性能问题。
测试充分：在生产环境中实施优化方案前，应进行充分的测试，确保优化效果。
持续监控：持续监控SQL语句的执行情况，及时调整执行计划。

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

风哥提示：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。学习交流加群风哥QQ113257174

生产环境建议：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。

风哥提示：更多学习教程公众号风哥教程itpux_com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

Oracle教程FG079-执行计划分析

Part02-生产环境规划与建议

2.1 查看执行计划的方法

2.2 执行计划的解读技巧

2.3 执行计划的优化建议

Part03-生产环境项目实施方案

Part04-生产案例与实战讲解

4.1 EXPLAIN PLAN实战

4.2 SQL*Plus AUTOTRACE实战

4.3 执行计划分析实战

Part05-风哥经验总结与分享

相关推荐

联系我们