小伙伴们，有没有经常和开发一起定位sql查询和分析性能问题，经常拿到一串sql进行先做个EXPLAIN，EXPLAIN PLAN作为Oracle数据库提供的一种用于分析SQL语句执行计划的工具，运行后，其实不会真的执行SQL语句而是通过模拟优化器生成执行计划，同时生成的执行计划被写入一个特定的表（默认为PLAN_TABLE，如果没有，实需使用utlxpan的sql新建，实操中有脚本)
第三方DATAGRIP,PL/SQL，Navicat等各种工具，一键EXPLAIN PLAN，同样查询PLAN_TABLE表来了解SQL语句的执行路径，Oracle执行了哪些，信息保存在哪里，API调用是否可行。
一、工作原理与技术要点
核心原理
- 静态分析：仅生成执行计划而不实际执行SQL（规避DDL/DML影响，模拟操作不真实运行，考量真实效果和EXPLAIN可能会有出入）
- 数据存储：使用全局临时表PLAN_TABLE$（ON COMMIT PRESERVE ROWS设计）
- 会话隔离：每个会话只能访问自己生成的执行计划
- 依赖统计信息：准确性取决于表/索引统计信息（行数、直方图等）
关键技术点
- 存储表结构：全局临时表（含Operation/Cost/Rows/Predicate等核心字段）
- 权限要求：SELECT ANY TABLE + CREATE SESSION
- 输出解读：
- Id：执行步骤编号（树形结构）
- Operation：关键操作类型（TABLE ACCESS FULL/INDEX RANGE SCAN等）
- Rows：优化器估算行数（统计信息决定）
- Cost：相对执行成本（CPU+IO权重）
二、 生成执行计划的过程
- USER执行EXPLAIN PLAN FOR 。
- Oracle解析SQL语句，优化器基于当前的统计信息生成执行计划。
- 将执行计划的每一步骤插入到PLAN_TABLE$（或用户指定的表）中。
- 用户通过查询TABLE(DBMS_XPLAN.DISPLAY)或直接查询PLAN_TABLE来查看执行计划。
2.1 底层流程
命令解析
：
- 用户执行EXPLAIN PLAN FOR [SQL语句]
- Oracle解析器验证SQL语法和语义正确性
优化器工作
：
- 优化器基于统计信息、系统参数和SQL结构
- 生成多个候选执行计划并计算成本估值
- 选择成本最低的执行计划方案
计划存储
：
- 将执行计划分解为操作步骤（Operation）
- 逐行写入全局临时表PLAN_TABLE$
- 每行包含：操作ID、父ID、操作类型、对象名、行数估算、成本值等58个字段
格式化输出
：
- 用户查询DBMS_XPLAN.DISPLAY函数
- 函数读取当前会话在PLAN_TABLE$中的计划数据
- 按树形结构格式化输出执行计划
执行计划树解析
：
- 树形结构：根节点（ID=0）到叶子节点
- 执行顺序：从最右叶子节点向根节点执行
- 缩进表示：层级关系（子操作比父操作缩进2空格）
会话清理
：
- 执行计划数据在会话期间持续保留
- 会话结束时自动清除对应数据
2.2 PLAN_TABLE$核心字段：
| 字段名 | 说明 | 调优意义 |
| OPERATION | 操作类型（TABLE ACCESS, INDEX SCAN等） | 识别全表扫描等性能瓶颈 |
| OPTIONS | 操作选项（FULL, RANGE SCAN等） | 判断索引使用情况 |
| CARDINALITY | 优化器估算行数 | 与实际行数偏差>10%需警惕 |
| COST | 相对执行成本 | 成本突增预示性能问题 |
| ACCESS_PREDICATES | 访问谓词 | 验证索引使用条件 |
| FILTER_PREDICATES | 过滤谓词 | 识别数据过滤效率 |
三、技术准备
3.1 环境准备
- 确认PLAN_TABLE存在：通过运行脚本@?/rdbms/admin/utlxplan.sql创建（需要由DBA执行）。
- 用户需要具有以下权限：
- CREATE SESSION（登录数据库）
- SELECT ANY TABLE（访问目标表）
- 如果使用自定义表存储执行计划，还需要有该表的INSERT权限。
SYS@CDB$ROOT> @?/rdbms/admin/utlxplan.sql
SYS@CDB$ROOT> DESC PLAN_TABLE;
Name Null? Type
____________________ ________ _________________
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
OBJECT_ALIAS VARCHAR2(261)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
……………………3.2 基本语法
EXPLAIN PLAN
[ SET STATEMENT_ID = 'statement_id' ]
[ INTO [schema.]table_name ]
FOR sql_statement;- STATEMENT_ID：可选，用于标识不同的执行计划。
- INTO：指定存储执行计划的表，默认是PLAN_TABLE。
- sql_statement：要分析的SQL语句。
3.3 查看执行计划
使用DBMS_XPLAN.DISPLAY函数：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
可以指定参数：
- table_name：存储执行计划的表（默认为PLAN_TABLE）。
- statement_id：标识执行计划的ID（默认为NULL）。
- format：显示格式（如'BASIC', 'TYPICAL', 'ALL'）。
3.4 执行计划的关键字段
- Id：步骤编号，根节点为0。
- Operation：操作类型（如TABLE ACCESS FULL）。
- Name：涉及的表或索引名。
- Rows：优化器估算的返回行数。
- Cost：优化器估算的成本。
- 其他：如Bytes（字节数）、Time（时间）等。
3.5 执行计划的准确性
- 高度依赖统计信息的准确性（包括表、索引的统计信息以及直方图等）。
- 如果统计信息过旧，执行计划可能不准确。
四、 验证脚本
4.1 创建测试表
-- 创建测试表HR.T4EXPLAIN，直接拷贝了DBA_OBJECTS的数据
CREATE TABLE HR.T4EXPLAIN AS SELECT * FROM dba_objects;
--Table HR.T4EXPLAIN created.4.2 生成执行计划
-- 生成执行计划
EXPLAIN PLAN FOR
SELECT object_id, object_name, object_type
FROM HR.T4EXPLAIN
WHERE object_type = 'TABLE';
--Explained.4.3 查看执行计划
-- 基本格式
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
--SYS@CDB$ROOT> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2206274169
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1537 | 81461 | 446 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4EXPLAIN | 1537 | 81461 | 446 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
13 rows selected.
-- 更加详细的格式
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));
SYS@CDB$ROOT> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));
Plan hash value: 2206274169
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1537 | 81461 | 446 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4EXPLAIN | 1537 | 81461 | 446 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "T4EXPLAIN"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22],
"OBJECT_TYPE"[VARCHAR2,23]
24 rows selected.
SYS@CDB$ROOT>4.4 检查PLAN_TABLE$（DBA权限）
-- 查看当前会话的执行计划在PLAN_TABLE$中的记录
SELECT operation, options, object_name, id, cardinality, cost
FROM sys.plan_table;五、 Oracle 23ai中的验证（向量搜索的EXPLAIN PLAN）
在Oracle 23ai中，EXPLAIN PLAN
-- 创建测试表（支持AI特性）
CREATE TABLE ai_sales_data (
sale_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_id NUMBER,
sale_date DATE,
quantity NUMBER,
amount NUMBER(10,2),
ai_features VECTOR
);
--Table AI_SALES_DATA created.
--插入语句
INSERT INTO ai_sales_data (product_id, sale_date, quantity, amount, ai_features)
SELECT
ROWNUM,
SYSDATE - DBMS_RANDOM.VALUE(1,365),
ROUND(DBMS_RANDOM.VALUE(1,100)),
DBMS_RANDOM.VALUE(10,1000),
VECTOR('['
|| DBMS_RANDOM.VALUE() || ','
|| DBMS_RANDOM.VALUE() || ','
|| DBMS_RANDOM.VALUE() || ']'
)
FROM dual
CONNECT BY LEVEL <= 1000;
COMMIT;
-- 确保统计信息最新
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'AI_SALES_DATA');
-- 生成向量搜索执行计划
EXPLAIN PLAN FOR
SELECT /*+ VECTOR_INDEX(sales_vec_idx) */
sale_id,
amount,
VECTOR_DISTANCE(ai_features, VECTOR('[0.5, 0.3, 0.8]'), EUCLIDEAN) AS dist
FROM ai_sales_data
WHERE VECTOR_DISTANCE(ai_features, VECTOR('[0.5, 0.3, 0.8]'), EUCLIDEAN) < 0.2
ORDER BY dist;
--Explained.
--查看完整计划详细信息
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(
format => 'ALL'
));
--
SYS@CDB$ROOT> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 1060895572
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 585 | 8 (13)| 00:00:01 |
| 1 | SORT ORDER BY | | 39 | 585 | 8 (13)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| AI_SALES_DATA | 39 | 585 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(VECTOR_DISTANCE("AI_FEATURES" /*+ LOB_BY_VALUE */ ,
VECTOR('[0.5, 0.3, 0.8]', *, *, * /*+ USEBLOBPCW_QVCGMD */ ),
EUCLIDEAN)<2.0000000000000001E-001D)
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))
20 rows selected.
SYS@CDB$ROOT>
--Oracle 23ai不要直接在 DBMS_XPLAN.DISPLAY 中组合 ADAPTIVE +VECTOR +AI，推荐的格式组合或分多次查询获取不同方面的信息。JSON显示EXPLAIN PLAN
-- 1. 直接查询PLAN_TABLE（基本计划信息）
SELECT
id,
LPAD(' ', depth*2) || operation || ' ' || options AS operation,
object_name,
cardinality AS "ROWS",
cost
FROM plan_table
ORDER BY id;
-- 方法2: 使用 DISPLAY 函数获取 JSON
-- 获取最新执行计划
SELECT DBMS_XPLAN.DISPLAY(
format => 'JSON'
) AS json_plan
FROM plan_table
WHERE id = 0;
--JSON格式
SELECT DBMS_XPLAN.DISPLAY(statement_id => NULL, format => 'JSON') AS json_plan
FROM DUAL;
oracle.sql.ARRAY@2b7774d5
oracle.sql.ARRAY@44bbb7c6
oracle.sql.ARRAY@3303e89e
--六、 使用体验
EXPLAIN PLAN是Oracle中用于分析SQL执行计划的重要工具，它通过静态分析生成执行计划，避免对生产环境产生影响。使用它需要确保统计信息准确，并理解其输出结果。在Oracle 23ai中Vector查询支持EXPLAIN PLAN 显示按照层次分布。
最佳实践总结
- 使用场景 敏感操作预分析 ，索引优化验证 ， 复杂SQL逻辑检查；做不到实际性能测试（需配合AWR/SQL Trace）
- 优化建议
- 定期收集统计信息：DBMS_STATS.GATHER_TABLE_STATS
- 比较不同格式输出：BASIC/TYPICAL/ALL/ADVANCED
- 结合动态性能视图：V$SQL_PLAN
- 风险规避
- 避免在统计信息过期时依赖执行计划
- 生产环境配合SQL Plan Baseline使用
扫一扫
1232
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
