目录
对于SQL优化来讲，除了大神级别的人物一眼洞穿原理；我等凡人除了对那些明显逻辑错误的SQL可以撸起袖子直接干；否则起手式就是先获取目标SQL的执行计划，再根据执行计划分析目标SQL慢的原因。获取执行计划的方法多种多样，本篇文章会介绍4种用过的方法。下面先甩张图，说明本篇文章将要介绍的获取执行计划方法。
一、AUTOTRACE方法
首先说明AUTOTRACE方法是SQL plus自带的工具。 朋友们不要拿该方法去PLSQL或者其他数据库客户端连接工具中去用，不好使。（我有一个朋友，曾经干过）
AUTOTRACE用法
SQL> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]下面对用法做进一步说明
| 命令 | 含义 |
| set autot on | 该命令会运行SQL且显示运行结果、执行计划、统计信息。 |
| set autot trace | 该命令会运行SQL，但不显示运行结果，会显示执行计划和统计信息。 |
| set autot trace exp | 该命令不执行查询语句，但会执行DML语句，只显示执行计划。 |
| set autot trace stat | 该命令会运行SQL，但只显示统计信息。 |
| set autot off | 该命令会关闭AUTOTRACE |
使用 set autot on查看执行计划例子
SQL> set autot on
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
执行计划
----------------------------------------------------------
Plan hash value: 2937609675
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
305 recursive calls
0 db block gets
58 consistent gets
1 physical reads
0 redo size
534 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed使用set autot trace查看执行计划例子
SQL> set autot trace
SQL> select count(*) from scott.emp;
执行计划
----------------------------------------------------------
Plan hash value: 2937609675
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
534 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
关闭AUTOTRACE
SQL> set autot off
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
SQL>其他几种AUTOTRACE用法不太常用，就不占用篇幅写例子了，朋友们如果有兴趣可以自己去试试；AUTOTRACE用法还是比较简单的。
二、EXPLAIN PLAN
Oracle优化器基于当前数据库对象统计信息生成执行计划和相关信息，但不会实际执行EXPLAIN PLAN后面的语句；最终将执行计划默认输出到PLAN_TABLE表中。
EXPLAIN PLAN语法
EXPLAIN PLAN
SET statement_id = 'xxx'
[INTO table]
FOR 需要获取执行计划的SQL语句示例
EXPLAIN PLAN
SET statement_id = 'zqd20240226'
FOR SELECT COUNT(*) FROM scott.js js WHERE js.object_id >100然后查看PLAN_TABLE，可以看到输出结果中已经有目标SQL的执行信息了（但此时的执行信息不可以直接使用）
SELECT * FROM plan_table
部分参数说明
| 参数 | 说明 |
| statement_id | 用于区分PLAN TABLE中不同SQL语句的标识符，自定义写；默认值为NULL。 |
| INTO table | 用于指定输出表的名称，还可以指定用户和数据库。当省略不写时，默认输出到当前用户的PLAN _TABLE。 |
| FOR 需要获取执行计划的SQL语句 | 要为哪个SQL生成执行计划，FOR后面就写什么SQL；支持DML语句和部分DDL语句（MERGE、CREATE TABLE、CREATE INDEX等） |
注意：
PLAN_TABLE是一个会话级的临时表，生成的数据只能用于当前会话，其他会话是看不到的
查看PLAN_TABLE中返回的执行计划和成本的2种方法
第1种：使用DBMS_XPLAN中的DISPLAY函数。
SELECT * FROM table(dbms_xplan.display(NULL,'zqd20240226','ADVANCED'))
第2种：使用SELECT语句，利用树形查询语法进行查询。这种方法的优点是可以根据个人需求与喜好自己定制执行计划要看哪些内容；但缺点也很明显，需要自己耗时去编写；且新手对于执行计划并不了解，或者对于树形查询语法不熟悉，无从下笔。下面例子仅仅是一个最简单的示例，可以根据自己需求改写。
SELECT
id,
LPAD(' ',2 * (LEVEL-1)) || operation operation,
options,
object_name,
cost,
object_alias,
position
FROM plan_table
START WITH id = 0
AND statement_id = 'zqd20240226'
CONNECT BY PRIOR ID = parent_id
AND statement_id = 'zqd20240226'
ORDER BY id
三、DBMS_XPLAN包
DBMS_XPLAN包定义了几个表函数，可以用来显示执行计划。
1、DISPLAY
这个函数在上文的EXPLAIN PLAN章节中已经提及过了，下面再进一步进行说明。DISPLAY表函数展示的是PLAN_TABLE表中的执行计划。官方描述中，只要一个表的结构与PLAN_TABLE一致，那么DISPLAY函数就可以展示这个表中的计划；但知道有这回事儿就行了，不需要重新搞个表去存执行计划，默认的PLAN_TABLE挺好用的。
下面是DISPLAY函数的入参描述，使用例子已经在EXPLAIN PLAN章节描述过，不再赘述。
function display(table_name varchar2 default 'PLAN_TABLE',
statement_id varchar2 default null,
format varchar2 default 'TYPICAL',
filter_preds varchar2 default null)
return dbms_xplan_type_table| 参数 | 说明 |
| table_name | 默认是PLAN_TABLE，但可以指定读取某张表中的执行计划，但前提是这张表的结构要与PLAN_TABLE一致。 |
| statement_id | 默认是null。这个就是我们在用EXPLAIN PLAN方式获取执行计划时指定的statement_id。当该参数为null时，会显示计划表中最近的一个执行计划（前提是filter_preds参数也为null）。 |
| format | 该参数表示输出格式。 BASIC：只显示执行计划 TYPICAL：显示执行计划、谓词信息（默认值） SERIAL：显示执行计划、谓词信息 ALL：显示执行计划、查询块名/对象别名、谓词信息、列投影信息 ADVANCED：显示执行计划、查询块名/对象别名、Outline Data、谓词信息、列投影信息 |
| filter_preds | 默认是null。过滤谓词。用于过滤从plan_table表中返回的记录，简单理解为where条件。当该值为NULL时，执行计划显示最近插入计划表中的执行计划。 |
下图是PLAN_TABLE中的执行计划，statement_id一致，但plan_id不一致。如果filter_preds为空，那会展示PLAN_ID为52的执行计划，因为它执行时间最近。
如果还想获取PLAN_ID为51的执行计划，filter_preds参数的写法如下代码所示，就是一个简单的where条件。（其实filter_preds不为空的这个应用场景不是很多的，朋友们了解就行了，无聊的时候可以玩一玩）
SELECT * FROM table(dbms_xplan.display(NULL,'zqd20240226','SERIAL','plan_id = 51'))2、DISPLAY_CURSOR
该函数用于获取内存中shard_pool游标缓存。其参数如下。
-- display from V$SQL_PLAN (or V$SQL_PLAN_STATISTICS_ALL)
function display_cursor(sql_id varchar2 default null,
cursor_child_no integer default 0,
format varchar2 default 'TYPICAL')
return dbms_xplan_type_table参数说明
| 参数 | 说明 |
| sql_id | 指定游标缓存中SQL语句的SQL_ID。可以通过查询V$SQL或V$SQLAREA中的SQL_ID列来获取相应的值。或者，您可以为V$ session中的特定会话选择列PREV_SQL_ID。该参数默认为NULL，在这种情况下，显示会话执行的最后一个游标的计划。 |
| cursor_child_no | 要显示的游标的子编号。如果不提供，则显示匹配sql_id参数的child_number=0游标的执行计划。只有指定了sql_id后，才可以指定child_number。 |
| format |
为了更好地控制显示输出，您可以将以下关键字添加到上述格式选项中，以自定义其默认行为。每个关键字要么表示计划表列的逻辑组。 格式关键字必须用逗号或空格分隔：
以上是常用关键字的说明，其他的关键字还有 以下格式在最新版本中已弃用，但支持向后兼容：
您可以使用'-'作为关键字的前缀，以排除指定的信息。例如，'-PROJECTION'排除投影信息。 |
DBMS_XPLAN包中给出了一些使用例子，现在将这些例子呈现给朋友们。但需要提前说明一个注意事项，在使用DISPLAY_CURSOR时需要将统计级别更改成ALL，该级别可以在会话级或者系统级更改，但如果在系统级更改会降低数据库性能；所以推荐大家更改会话级即可。代码如下：
SQL> show parameter statistics_level;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
statistics_level string TYPICAL
SQL> alter session set statistics_level =ALL;
会话已更改。
SQL> show parameter statistics_level;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
statistics_level string ALL除了更改统计级别，还可以在目标SQL语句中添加HINT：SELECT /*+gather_plan_statistics*/ ....
但这种方法略麻烦，还是推荐更改会话级的统计级别。
下面开始说例子：
例1、展示当前会话的当前或者最近一次的执行语句的执行计划
这个获取的是当前会话中的当前或者最近一次的SQL语句执行计划，不能获取其他会话的SQL语句执行计划。代码如下所示：
SELECT * FROM TABLE(dbms_xplan.display_cursor);
***因为涉及到执行计划输出结果展示，下面的例子统一在SQLPLUS中进行，方便文章编写***
例2、展示指定会话当前的或者最近一次的执行计划
识别目标SQL语句的sql_id 和 child_number，使用它们作为DISPLAY_CUSRSOR的参数。代码如下所示。如果是新手朋友可能对session不太了解，但没关系，这只是一个例子；且您可以根据v$session视图中的program和machine大概定位到您想要查看的会话。
SQL> SELECT prev_sql_id, prev_child_number FROM v$session WHERE sid=130;
PREV_SQL_ID PREV_CHILD_NUMBER
-------------------------- -----------------
fsv2yh2b2ja1t 0
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('fsv2yh2b2ja1t', 0));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fsv2yh2b2ja1t, child number 0
-------------------------------------
SELECT * FROM scott.js js WHERE js.object_id < 200
Plan hash value: 215799804
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| JS | 152 | 14592 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_OBJECT_ID | 152 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JS"."OBJECT_ID"<200)
已选择19行。
也可以将上述两个SQL合并在一起，代码如下。
SQL> SELECT t.*
2 FROM v$session s,
3 TABLE(dbms_xplan.display_cursor(s.prev_sql_id,s.prev_child_number)) t
4 WHERE s.sid=130;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fsv2yh2b2ja1t, child number 0
-------------------------------------
SELECT * FROM scott.js js WHERE js.object_id < 200
Plan hash value: 215799804
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| JS | 152 | 14592 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_OBJECT_ID | 152 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JS"."OBJECT_ID"<200)
已选择19行。
为了方便复制，下面把SQL单独拎出来：
SELECT t.*
FROM v$session s,
TABLE(dbms_xplan.display_cursor(s.prev_sql_id,s.prev_child_number)) t
WHERE s.sid=130;例3、显示包含区分大小写字符串’xxxx‘的所有游标，不包括SYS解析的SQL。
这个例子其实挺好用的，因为要优化一个SQL获取它的执行计划时，一般懒得去查它的sql_id，所以直接根据SQL中包含的一些唯一字符内容，获取其执行计划很方便。但是朋友们也不要认死理，获取执行计划的方式这么多就是为了应对不同的场景，一个方法的好不好用要和使用场景关联起来聊。该例子代码如下：
SQL> SELECT t.*
2 FROM v$sql s,
3 TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number)) t
4 WHERE s.sql_text LIKE '%700%' AND s.parsing_user_id <> 0;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 683agnzwmsrg8, child number 0
-------------------------------------
SELECT * FROM scott.js js WHERE js.object_id < 700
Plan hash value: 215799804
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| JS | 535 | 51360 | 12 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_OBJECT_ID | 535 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JS"."OBJECT_ID"<700)
已选择19行。
为了方便复制，下面把SQL单独拎出来：
SELECT t.*
FROM v$sql s,
TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number)) t
WHERE s.sql_text LIKE '%700%' AND s.parsing_user_id <> 0;例4、显示所有包含不区分大小写字符串'xxxx'的游标的所有信息，包括SYS解析的SQL
该例子的format参数是ALL。
代码如下：
SQL> SELECT t.*
2 FROM v$sql s,
3 TABLE(dbms_xplan.display_cursor(s.sql_id,s.child_number, 'ALL')) t
4 WHERE UPPER(s.sql_text) like '%JS.OBJECT_NAME%';
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bz071f64y60x4, child number 0
-------------------------------------
SELECT JS.object_name FROM scott.js js WHERE js.object_id < 700
Plan hash value: 215799804
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| JS | 535 | 16050 | 12 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_OBJECT_ID | 535 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / JS@SEL$1
2 - SEL$1 / JS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JS"."OBJECT_ID"<700)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "JS"."OBJECT_NAME"[VARCHAR2,128]
2 - "JS".ROWID[ROWID,10]
已选择31行。
SQL>为了方便复制，下面把SQL单独拎出来：
SELECT t.*
FROM v$sql s,
TABLE(dbms_xplan.display_cursor(s.sql_id,s.child_number, 'ALL')) t
WHERE UPPER(s.sql_text) like '%JS.OBJECT_NAME%';例5、显示所有包含字符串’xxxx‘ ，且不区分大小写的游标最近一次的执行计划，包括SYS用户解析的SQL。
该例子使用的format参数是ALLSTATS LAST
代码如下:
SQL> SELECT first_name,email FROM HR.employees WHERE salary >1000 AND job_id = 'AD_VP';
FIRST_NAME EMAIL
-------------------- -------------------------
Neena NKOCHHAR
Lex LDEHAAN
SQL> SELECT t.* FROM v$sql s,TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number,'ALLSTATS LAST')) t WHERE UPPER(s.sql_text) LIKE '%AD_VP%';
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d1gfgmfwbqs9k, child number 1
-------------------------------------
SELECT first_name,email FROM HR.employees WHERE salary >1000 AND job_id
= 'AD_VP'
Plan hash value: 2096651594
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 2 | 2 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SALARY">1000)
2 - access("JOB_ID"='AD_VP')
48 rows selected.
为了方便复制，下面把SQL单独拎出来：
SELECT t.*
FROM v$sql s,
TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number,'ALLSTATS LAST')) t
WHERE UPPER(s.sql_text) LIKE '%AD_VP%';
例6、显示由'XXX'用户解析的包含’xxx‘字符的，且区分大小写的所有游标的执行计划
该例子使用的format参数是RUNSTATS_TOT
SQL> SELECT t.* FROM v$sql s, dba_users u,TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number,'RUNSTATS_TOT')) t WHERE s.sql_text like '%MK_MAN%' AND u.user_id=s.parsing_user_id AND u.username='HR';
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0j21c70c63ntg, child number 0
-------------------------------------
SELECT first_name,email FROM HR.employees WHERE salary >1000 AND job_id
= 'MK_MAN'
Plan hash value: 2096651594
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 1 | 1 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SALARY">1000)
2 - access("JOB_ID"='MK_MAN')
21 rows selected.
为了方便复制，下面把SQL单独拎出来：
SELECT t.*
FROM v$sql s, dba_users u,
TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number,'RUNSTATS_TOT')) t
WHERE s.sql_text like '%MK_MAN%'
AND u.user_id=s.parsing_user_id
AND u.username='HR';3、DISPLAY_AWR
这个表函数显示存储在AWR中的执行计划的内容。
官方的最新文档中，表示已将DISPLAY_AWR已经被DISPLAY_WORKLOAD_REPOSITORY表函数 取代了。但是这个事儿吧，朋友们也不用多想，反正19C及之前的版本的DISPLAY_AWR都还在，20C及以上版本咱没用过。
DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);参数说明：
| 参数 | 说明 |
| sql_id | SQL语句的SQL_ID。可以通过查询DBA_HIST_SQLTEXT中的SQL_ID列来检目标SQL语句的适当值 |
| plan_hash_value | 指定SQL语句的PLAN_HASH_VALUE。可选参数。如果省略，表函数返回给定SQL_ID的所有存储的执行计划。 |
| db_id | 指定database_id，用于显示由SQL_ID标识的SQL语句的计划。如果没有提供，则使用本地数据库的database_id，如V$ database所示。 |
| format | 控制执行计划的详细级别。它接受四个值：
为了更好地控制显示输出，可以将以下关键字添加到上述四个标准格式选项中，以自定义其默认行为。每个关键字要么表示计划表列的逻辑组(如PARTITION)，要么表示对基本计划表输出的逻辑添加(如PREDICATE)。格式关键字必须用逗号或空格分隔：
格式关键字的前缀可以加上'-'，以排除指定的信息。例如，'-PROJECTION'排除投影信息。 |
用法注意事项：
要使用DISPLAY_AWR功能，用户必须对DBA_HIST_SQL_PLAN、AWR_ROOT_SQL_PLAN、AWR_PDB_SQL_PLAN、DBA_HIST_SQLTEXT、AWR_ROOT_SQLTEXT、AWR_PDB_SQLTEXT和V$DATABASE具有SELECT或READ权限，否则将显示相应的错误消息。默认情况下，这些视图的选择权限被授予select_catalog角色。
下面的例子会展示使用format参数的不同例子
- 当format为 ’BASIC ROWS‘ 时，会显示基本信息和优化器估计的行数
- 当format为 ’ALL -PROJECTION -NOTE‘ 时，会显示除了projection 和note以外的所有信息
- 当format为 ’TYPICAL PROJECTION‘ 时，会显示带有“Column Projection Information”的典型格式，由于typical是默认值，因此使用简单的'PROJECTION'是等效的。
- 当format为 ’BYTES -COST -PREDICATE‘ 时，以典型格式显示，但不包括优化器成本和字节大小估计以及谓词部分。
下面是DISPLAY_AWR 的使用例子：
（1）执行目标SQL
SQL> SELECT
2 e.empno,e.job,d.dname
3 FROM scott.emp e,scott.dept d
4 WHERE e.deptno = d.deptno
5 AND e.job = 'SALESMAN';
EMPNO JOB DNAME
---------- ------------------ ----------------------------
7499 SALESMAN SALES
7844 SALESMAN SALES
7654 SALESMAN SALES
7521 SALESMAN SALES（2）手动创建数据库快照
可以在DBA_HIST_SNAPSHOT表中查询到刚刚创建的数据库快照，下面的代码示例中SNAP_ID为12298的数据，就说明刚刚创建数据库快照成功了；快照周期开始时间是2024-02-29 15:00:06，快照周期结束时间是2024-02-29 15:34:07。手动创建快照的目的是确保我们刚刚执行的SQL包含于快照中。
SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL');
PL/SQL 过程已成功完成。
SQL> SELECT * FROM
2 (SELECT
3 DHS.snap_id,
4 DHS.dbid,
5 TO_CHAR(DHS.startup_time,'YYYY-MM-DD HH24:MI:SS') startup_time,
6 TO_CHAR(DHS.begin_interval_time,'YYYY-MM-DD HH24:MI:SS') begin_interval_time,
7 TO_CHAR(DHS.end_interval_time,'YYYY-MM-DD HH24:MI:SS') end_interval_time
8 FROM DBA_HIST_SNAPSHOT DHS
9 ORDER BY DHS.begin_interval_time DESC)
10 WHERE ROWNUM < 5
11 ;
SNAP_ID DBID STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ---------- -------------------------------------- -------------------------------------- --------------------------------------
12298 1608366025 2024-02-29 08:13:15 2024-02-29 15:00:06 2024-02-29 15:34:07
12297 1608366025 2024-02-29 08:13:15 2024-02-29 14:16:16 2024-02-29 15:00:06
12296 1608366025 2024-02-29 08:13:15 2024-02-29 13:58:08 2024-02-29 14:16:16
12295 1608366025 2024-02-29 08:13:15 2024-02-29 13:56:20 2024-02-29 13:58:08（3）使用DISPLAY_AWR表函数查看执行计划
SQL> SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table(DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null, 'ALL' )) tf WHERE ht.sql_text like '%SALESMAN%';
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k5uj73ms559k
--------------------
SELECT e.empno,e.job,d.dname FROM scott.emp e,scott.dept d WHERE
e.deptno = d.deptno AND e.job = 'SALESMAN'
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | MERGE JOIN | | 3 | 84 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
| 4 | SORT JOIN | | 3 | 45 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 3 | 45 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / D@SEL$1
3 - SEL$1 / D@SEL$1
5 - SEL$1 / E@SEL$1
已选择26行。为了方便复制，下面把SQL单独拎出来：
SELECT tf.*
FROM DBA_HIST_SQLTEXT ht,
table(DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null, 'ALL' )) tf
WHERE ht.sql_text like '%SALESMAN%';四、Sql Developer 工具
Sql Developer工具是Oracle官方提供的一个SQL客户端连接工具，免费好用。在SQL优化这方面，个人觉得 Sql Developer比PL/Sql Developer工具其实要好用很多。下面简单描述下如何使用Sql Developer获取执行计划。
（1）选中目标SQL语句
（2）点击左上角红框圈注的”SQL优化指导“，或者CTRL+F12；就会输出SQL优化指导信息。
（3）在详细信息模块就会有执行计划
除了执行计划以外， Sql Developer在概览模块还会提供优化建议、原理等信息；非常方便优化工作，对新手朋友也十分友好。当然因为我本地搭建的数据库没啥复杂的SQL，所以这个工具也没有啥优化建议了。
五、总结与分析
每种方法都有它适合的使用场景，不能抛开场景去评价一个方法的好坏。下面就总结上文提到的4种方法。
1、虚假和真实
眼见不一定为真，执行计划也有虚假的计划和真实的计划。
虚假的执行计划：
上文中提到的AUTOTRACE、EXPLAIN PLAN、Sql Developer工具的执行计划都是估算出来的，真实的执行计划不应该是被估算的，应该是真正执行过的。而AUTOTRACE虽然也可以真正执行SQL，但它的执行计划其实还是来自于PLAN_TABLE，而非共享池的v$sql_plan。这些执行计划是依赖于统计信息估算出来的，如果统计信息过旧，那怎么保证执行计划的真实性呢？
真实的执行计划：
上文提到的DBMS_XPLAN包中的DISPLAY_CURSOR、DISPLAY_AWR这两个表函数都可以获得真实的执行计划。这两个表函数一个是获取的共享池中的执行计划，一个获取的是存储在AWR中的执行计划。用这些表函数获取执行计划的前提是目标SQL都必须执行过。
2、使用场景建议
- 上线SQL审核
系统上线前的SQL审核，有很多SQL不方便直接执行；所以推荐使用EXPLAIN PLAN、Sql Developer、AUTOTRACE。这些都可以不执行SQL，直接根据统计信息获取执行计划。
- 目标SQL执行耗时巨长
有的SQL写的非常复杂，且查询数据又大，访问路径、连接方式也很烂。所以执行耗时很恐怖，不可能一直等它执行完，所以也建议使用EXPLAIN PLAN、Sql Developer、AUTOTRACE。
- 真实执行计划
当没有其他特殊情况时，肯定还是获取真实的执行计划最好，所以此时没有推荐，就是需要DBMS_XPLAN.DISPLAY_CURSOR、DBMS_XPLAN.DISPLAY_AWR方法获取执行计划。
- 获取目标SQL的多个执行计划
需要获取目标SQL的多个执行计划时，调整好format参数，DBMS_XPLAN.DISPLAY_CURSOR、DBMS_XPLAN.DISPLAY_AWR这两个表函数就可以满足要求，其实PLAN_TABLE中也能获取，但比较麻烦还有些谓词过滤条件，不是很推荐。
当然，场景还有很多很多，这里只是举了几个我遇到过的例子。朋友们如果真遇到了奇怪情况，那就仁者见仁智者见智了，做好记录总结，都是财富啊。
3、个人习惯
获取执行计划方法有很多，但说到底我们也只是为了获取到执行计划；真正的优化还得是看后面的分析与调整。所以朋友们一定要能提炼出来自己用着最舒服的执行计划，当遇到需要优化的SQL时，没那么多时间让我们去挑选该用哪一种方法，都是本能的用自己最熟悉的，所以到底该用哪种方法，要养成习惯哦。
同时也和各位朋友聊下我习惯使用的方法：
当遇到查询时间不是很长的的目标SQL，我会本能的使用DBMS_XPLAN.DISPLAY_CURSOR。这个方法既准确也不用想着手动收集快照，并且编写一个PLSQL方便获取计划，代码如下，觉得有需要的朋友可以看下。但是有个小问题
DECLARE
x VARCHAR2(36);
BEGIN
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
x := 'display_cursor';
FOR text IN (
/*业务SQL(sql后面不需要加";")*/
) LOOP
NULL;
END LOOP;
for i in (SELECT p.plan_table_output
FROM TABLE(dbms_xplan.display_cursor(NULL, NULL,'ADVANCED ALLSTATS LAST')) p) LOOP
dbms_output.put_line(i.plan_table_output);
END LOOP;
ROLLBACK;
END;在 Sql Developer的dbms输出框里可以看到上面代码的执行计划。再提醒一点，dbms输出是默认不显示的；需要在Sql Developer左上角点击【查看】，选择dbms输出，并对dbms输出进行连接才可以。（我有个朋友，曾经搞了半天）
当遇到又臭又长（耗时长）的SQL时
我还是会倾向使用 Sql Developer工具，但如果是远程其他项目的数据库服务器时，没有工具使用，那我本能反应还是AUTOTRACE。
4、最后、最后
除了上文提到的4种方法，获取方法还有不少：10046事件、PL/SQL工具、Oracle的EM和GridControl图形化查看工具等。我也是大家学习路上的同行者，有些方法我也没用过，有些奇怪情况我也没遇到过，所以也很期待解锁更多的知识。但还是认为有余力或者缘分到了的话可以多多研究几种新玩意儿，平时还是要搞清楚我们的主线任务，有些东西够用就行了。
最后的最后，谢谢您能阅读到文章结尾。如果能给您带来些许帮助，这是我的荣幸；如果因为我的技术水平有限，出现错误，请您指正；他日见张禄，绨袍怀旧恩！
5324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
