当前位置： 首页 > java >正文

55-Oracle-EXPLAIN PLAN（含23ai ）实操

java 2025/6/22 6:04:36

小伙伴们，有没有经常和开发一起定位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权重）

二、 生成执行计划的过程

  1. USER执行EXPLAIN PLAN FOR 。
  2. Oracle解析SQL语句，优化器基于当前的统计信息生成执行计划。
  3. 将执行计划的每一步骤插入到PLAN_TABLE$（或用户指定的表）中。
  4. 用户通过查询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）
  • ​优化建议​
  1. 定期收集统计信息：DBMS_STATS.GATHER_TABLE_STATS
  2. 比较不同格式输出：BASIC/TYPICAL/ALL/ADVANCED
  3. 结合动态性能视图：V$SQL_PLAN
  • ​风险规避​
  1. 避免在统计信息过期时依赖执行计划
  2. 生产环境配合SQL Plan Baseline使用

 

 

http://www.xdnf.cn/news/14559.html

相关文章：

  • 终端里的AI黑魔法：OpenCode深度体验与架构揭秘
  • 启动hardhat 项目，下载依赖的npm问题
  • Taro 跨端应用性能优化全攻略：从原理到实践
  • 【设计模式】6.原型模式
  • FTTR+软路由网络拓扑方案
  • NY339NY341美光固态闪存NW841NW843
  • Flutter ListTile 深度解析
  • 西门子S7通信协议抓包分析应用
  • OSI网络通信模型详解
  • react扩展
  • 智能群跃小助手发布说明
  • 局域网文件共享及检索系统
  • 初学python的我开始Leetcode题10-2
  • 基于大模型的三叉神经痛预测及治疗方案研究报告
  • window显示驱动开发—使用状态刷新回调函数
  • WebGL图形学总结（二）
  • Spring Boot + MyBatis + Vue：从零到一构建全栈应用
  • linux线程同步
  • P7 QT项目----会学天气预报（完结）
  • 【内存】Linux 内核优化实战 - vm.max_map_count
  • HarmonyOS 6 + 盘古大模型5.5
  • 解决uni-app发布微信小程序主包大小限制为＜2M的问题
  • 从服务器收到预料之外的响应。此文件可能已被成功上传。请检查媒体库或刷新本页
  • DAY 37 早停策略和模型权重的保存
  • @annotation：Spring AOP 的“精准定位器“
  • uniapp开发小程序，导出文件打开并保存，实现过程downloadFile下载，openDocument打开
  • 4.文件管理（文本、日志、Excel表）
  • 基于PyQt5和PaddleSpeech的中文语音识别系统设计与实现(Python)
  • Spring Boot + MyBatis + Vue：全栈开发中的最佳实践
  • C++11 右值引用（Rvalue Reference）