Oracle获取执行计划之DBMS_XPLAN 技术详解1-EW帮帮网

在 Oracle 数据库的管理与优化工作中，深入了解 SQL 语句的执行计划是至关重要的一环。DBMS_XPLAN 包作为 Oracle 提供的强大工具，能够帮助数据库管理员（DBAs）和开发人员清晰地查看和分析 SQL 语句的执行计划，从而实现对数据库性能的有效优化。本文将全面深入地介绍 DBMS_XPLAN 包的相关知识。

一、DBMS_XPLAN 包概述

DBMS_XPLAN 包是 Oracle 内置的实用工具，主要用于展示 SQL 语句的执行计划。当我们向 Oracle 数据库发出一条 SQL 查询时，数据库会确定一种最有效的方式来执行该查询，这个执行方式的具体描述就是执行计划。执行计划涵盖了 Oracle 为检索或修改所需数据而执行的精确操作，包括表的连接方式、数据的访问路径以及索引的使用情况等。

传统的explain plan语句常用于生成执行计划，但它仅仅是对执行计划的预测，并不一定能准确反映 SQL 语句在实际运行时的行为。而 DBMS_XPLAN 包则更具优势，它不仅可以展示预测的执行计划，还能根据查询类型呈现实际运行时的性能表现，为我们深入分析 SQL 语句的执行过程提供了更可靠的依据。

二、DBMS_XPLAN 包的使用前提

要使用 DBMS_XPLAN 包，需要满足以下条件：

权限要求：用户需要具备查询计划表（plan_table）或v$sql_plan视图的权限。其中，plan_table是用于存储explain plan命令输出结果的临时表；v$sql_plan视图则包含了已执行查询的实际执行计划。
SQL 语句的可用性：如果要获取实时的执行计划，那么所分析的查询必须仍然存在于共享 SQL 区域中；如果是进行历史分析，则需要确保该查询的执行计划之前已被捕获并存储。

当执行explain plan后，Oracle 会自动向plan_table填充相关内容，此时 DBMS_XPLAN 包就能对这些内容进行格式化处理，使其更易于阅读和理解。

三、DBMS_XPLAN 包的核心函数

DBMS_XPLAN 包包含多个关键函数，每个函数都针对特定的使用场景进行设计，以下是对这些函数的详细介绍：

3.1 display函数

（一）display函数参数说明

display函数主要用于展示存储在计划表（plan_table）中的执行计划。其调用格式如下：

select * from table(dbms_xplan.display(table_name, statement_id, format, filter_preds));

table_name：用于指定计划表的名称，默认值为plan_table，如果填入null，则表示采用默认的计划表。

statement_id：用于指定 SQL 语句的名称，默认值为null，在这种情况下，函数将显示最近插入到计划表中的执行计划。

format：该参数用于控制输出内容的格式，常见的取值包括：

basic：只显示最基本的信息，简洁明了。
typical：显示常规的执行计划信息，是较为常用的一种格式。
all：展示所有相关信息，内容最为全面。
advanced：提供最全的信息，适合对执行计划进行深入分析的场景。

filter_preds：用于在查询计划表时添加约束条件，例如statement_id = 'test3' 。默认值为null，表示显示最近插入计划表的执行计划，不添加额外约束。

（二）display 示例

首先，使用explain plan for语句生成执行计划并将其存入plan_table ：

explain plan for select * from test1;

然后，通过dbms_xplan.display函数来展示执行计划：

select * from table(dbms_xplan.display);

这种方式和explain plan展示一样，这里就不做具体示例展示。

下面补充一个非常实用的功能，就是当一个SQL带有绑定变量时，explain plan for仍然可以解析。

SQL> explain plan for select object_id,object_name,owner from test1 where rownum<:1;

Explained.

SQL> select * from table(dbms_xplan.display); 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1430905904

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 91074 |  8538K|   336   (1)| 00:00:05 |
|*  1 |  COUNT STOPKEY     |       |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST1 | 91074 |  8538K|   336   (1)| 00:00:05 |
----------------------------------------------------------------------------

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

   1 - filter(ROWNUM<TO_NUMBER(:1))

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

3.2 display_cursor函数

（一）display_cursor函数参数说明

display_cursor函数用于显示存储在库缓存（v$sql_plan视图）中的执行计划，由于它是直接从共享池的library cache内存中直接获取，所以获取的执行计划是真实的，其格式为：

select * from table(dbms_xplan.display_cursor(sql_id, cursor_child_no, format));

sql_id：指定要返回执行计划的SQL语句的父游标，默认值为null。当取值为null时，函数将返回当前会话最后被执行的SQL语句的执行计划。

cursor_child_no：用于指定父游标下子游标的序号，默认值为0。若将其设定为null，则表示返回所有子游标的执行计划。

format：与display函数中的format参数类似，用于控制输出信息的类型。除了前面提到的基本格式外，它还包含一些附加选项，用于定制化输出行为，这些选项可以通过逗号和空格进行分隔声明，同时还可以使用+和-符号来包含或排除相应的显示元素。常见的附加选项如下：

ROWS：显示被优化器估算的记录的行号。
BYTES：显示优化器估算的字节数。
COST：显示优化器计算的成本信息。
PARTITION：显示分区的分割信息。
PARALLEL：显示并行执行信息。
PREDICATE：显示谓语。
PROJECTION：显示列投影部分，即每一行的哪些列被传递给其父列以及这些列的大小。
ALIAS：显示查询块名称以及对象别名。
REMOTE：显示分布式查询信息。
NOTE：显示注释。
IOSTATS：显示游标执行的IO统计信息。
MEMSTATS：为内存密集运算（如散列联结、排序或一些类型的位图运算）显示内存管理统计信息。
PEEKED_BINDS:打印解析时使用的绑定变量
ALLSTATS：与IOSTATS MEMSTATS等价。
LAST：显示最后执行的执行计划统计信息，默认显示为ALL类型，并且可以累积。

（二）display_cursor示例

展示最近的执行计划(不带绑定变量)

dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')

SQL> set autot off
SQL> select object_id,object_name,owner from test1 where rownum<10;

 OBJECT_ID OBJECT_NAME          OWNER
---------- -------------------- ------------------------------
        20 ICOL$                SYS
        46 I_USER1              SYS
        28 CON$                 SYS
        15 UNDO$                SYS
        29 C_COBJ#              SYS
         3 I_OBJ#               SYS
        25 PROXY_ROLE_DATA$     SYS
        41 I_IND1               SYS
        54 I_CDEF2              SYS

9 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')); 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  d6tfa5t1a8m7w, child number 0
-------------------------------------
select object_id,object_name,owner from test1 where rownum<10

Plan hash value: 1430905904

---------------------------------------------
| Id  | Operation          | Name  | E-Rows |
---------------------------------------------
|   0 | SELECT STATEMENT   |       |        |
|*  1 |  COUNT STOPKEY     |       |        |
|   2 |   TABLE ACCESS FULL| TEST1 |  91074 |
---------------------------------------------

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

   1 - filter(ROWNUM<10)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


26 rows selected.

这里由于没有参考统计信息，报一个警告。另外还有其它常用的参数，例如ADVANCED，ALL请读者自行尝试。

展示最近的执行计划(带绑定变量)

-- 1. 创建测试表并插入示例数据
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    department_id NUMBER,
    salary NUMBER,
    hire_date DATE
);

INSERT INTO employees VALUES (1, 'John', 'Doe', 10, 6000, SYSDATE);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 20, 7500, SYSDATE-100);
INSERT INTO employees VALUES (3, 'Mike', 'Johnson', 20, 5500, SYSDATE-200);
INSERT INTO employees VALUES (4, 'Sarah', 'Williams', 30, 9000, SYSDATE-150);
COMMIT;

-- 2. 创建索引以支持查询
CREATE INDEX idx_dept_sal ON employees (department_id, salary);

-- 3. 执行带有绑定变量的SQL（使用绑定变量而非硬编码值）
VARIABLE dept_id NUMBER;
VARIABLE min_salary NUMBER;
EXEC :dept_id := 20;
EXEC :min_salary := 5000;

SELECT /*+ MONITOR */ * 
FROM employees 
WHERE department_id = :dept_id 
  AND salary > :min_salary;

-- 4. 立即查看执行计划（包含绑定变量值）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  gd7vts27rnvua, child number 0
-------------------------------------
SELECT /*+ MONITOR */ * FROM employees WHERE department_id = :dept_id
AND salary > :min_salary

Plan hash value: 1445457117

--------------------------------------------------------------------------------
| Id  | Operation         | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |        |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |      2 |   204 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMPLOYEES@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMPLOYEES"@"SEL$1")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 20
   2 - (NUMBER): 5000

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

   1 - filter(("DEPARTMENT_ID"=:DEPT_ID AND "SALARY">:MIN_SALARY))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMPLOYEES"."EMPLOYEE_ID"[NUMBER,22],
       "EMPLOYEES"."FIRST_NAME"[VARCHAR2,50],
       "EMPLOYEES"."LAST_NAME"[VARCHAR2,50], "DEPARTMENT_ID"[NUMBER,22],
       "SALARY"[NUMBER,22], "EMPLOYEES"."HIRE_DATE"[DATE,7]

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when
:

       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system leve
l



59 rows selected.

通过SQLID和游标ID获取执行计划

通过执行select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'))查看指定 SQL 的执行计划。

第一个参数为 SQL ID 或 SQL HASH VALUE
第二个是 Child Cursor Number
第三个常用advanced以呈现最详细执行计划。

--查看SQLID及游标号
select sql_id,child_number from v$sql where sql_text like 'select object_id,object_name,owner from test1 where%';

SQL_ID        CHILD_NUMBER
------------- ------------
d6tfa5t1a8m7w            0

--查看执行计划
SQL> 
set pages 1000
select * from table(dbms_xplan.display_cursor('d6tfa5t1a8m7w',0,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  d6tfa5t1a8m7w, child number 0
-------------------------------------
select object_id,object_name,owner from test1 where rownum<10

Plan hash value: 1430905904

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |     2 (100)|          |
|*  1 |  COUNT STOPKEY     |       |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST1 | 91074 |  8538K|     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / TEST1@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TEST1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - filter(ROWNUM<10)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OWNER"[VARCHAR2,30], "OBJECT_NAME"[VARCHAR2,128],
       "OBJECT_ID"[NUMBER,22]
   2 - "OWNER"[VARCHAR2,30], "OBJECT_NAME"[VARCHAR2,128],
       "OBJECT_ID"[NUMBER,22]

Note
-----
   - dynamic sampling used for this statement (level=2)


51 rows selected.

注意：当目标 SQL 执行计划所在的 Child Cursor 未因空间不足等原因被从共享池（Shared Pool）中置换出去（age out ）时才能查到执行计划。

3.3 display_awr函数

当 SQL 的执行计划被 age out 出 Shared Pool 后，若该执行计划此前被 Oracle 采集并存储到 AWR（自动工作量存储库，AWR Repository ）中，就可以使用该方法查看该 SQL 的所有历史执行计划。AWR 定期收集、处理和存储数据库性能统计信息以及执行计划等相关数据，即使执行计划不在共享池，只要已被采集到 AWR 中，就能通过特定方法来查看历史执行计划，这为分析 SQL 在不同时间、不同条件下的执行情况提供了途径。

（一）display_awr函数参数说明

display_awr函数能够从自动工作量存储库（AWR）中存储的历史数据里获取并显示执行计划。这在分析过去某个时段内 SQL 语句的执行情况时非常有用，有助于排查历史性能问题。其语法格式为：

select * from table(dbms_xplan.display_awr(sql_id, plan_hash_value, dbid, format));

其中，sql_id是 SQL 语句的标识；plan_hash_value是执行计划的哈希值；dbid是数据库的唯一标识；format同样用于控制输出格式。

（二）display_awr 示例

查看当前共享池中的执行计划

SQL> col SQL_TEXT for a40
select sql_text, sql_id, version_count,executions from v$sqlarea where sql_text like 'SELECT /*+ MONITOR */ * FROM employees WHERE department_id = :dept_id%'; 

SQL_TEXT                                 SQL_ID        VERSION_COUNT EXECUTIONS
---------------------------------------- ------------- ------------- ----------
SELECT /*+ MONITOR */ * FROM employees W gd7vts27rnvua             1          1
HERE department_id = :dept_id   AND sala
ry > :min_salary

采集AWR

exec dbms_workload_repository.create_snapshot();

清空共享池

alter system flush shared_pool;

确认执行计划被置换出共享池

SQL> select sql_text, sql_id, version_count,executions from v$sqlarea where sql_text like 'SELECT /*+ MONITOR */ * FROM employees WHERE department_id = :dept_id%'; 

no rows selected

select * from table(dbms_xplan.display_cursor('gd7vts27rnvua',0,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: gd7vts27rnvua, child number: 0 cannot be found

确认执行计划被置换出共享池

SQL> select * from table(dbms_xplan.display_awr('gd7vts27rnvua'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID gd7vts27rnvua
--------------------
SELECT /*+ MONITOR */ * FROM employees WHERE department_id = :dept_id
AND salary > :min_salary

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |     2 |   204 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


18 rows selected.

与DBMS_XPLAN.DISPLAY_CURSOR相比，DBMS_XPLAN.DISPLAY_AWR显示的执行计划存在不足，无法呈现执行步骤对应的谓词条件（包括驱动和过滤查询条件）。这是由于Oracle将执行计划采样数据从V$SQL_PLAN迁移到AWR Repository基表WRH$_SQL_PLAN时，未保留记录谓词条件的ACCESS_PREDICATES和FILTER_PREDICATES列的值，导致DBMS_XPLAN.DISPLAY_AWR无相关条件可展示，而谓词条件对理解执行计划尤其是复杂计划至关重要。

（四）display_sts函数

display_sts函数主要用于检索存储在 SQL 调试集（STS）中特定 SQL 语句的执行计划。其语法如下：

select * from table(dbms_xplan.display_sts(sqlset_name, sqlset_owner, sql_id, format));

这里，sqlset_name是 SQL 调试集的名称；sqlset_owner是 SQL 调试集的所有者；sql_id是需要查询执行计划的 SQL 语句标识；format用于控制输出信息。

这个使用场景不多，不做过多介绍。

五、DBMS_XPLAN 包的使用总结

DBMS_XPLAN 包在 Oracle 数据库中是查看 SQL 执行计划的得力工具，涵盖了从缓存、AWR、SQL 调试集等多来源的计划展示。通过掌握其各函数的功能、使用方法及适用场景，并留意权限与参数设置等要点，能助力数据库管理员和开发人员精准剖析 SQL 执行情况，高效开展性能优化工作。

Oracle获取执行计划之DBMS_XPLAN 技术详解1