微课sql优化(9)、如何获取执行计划

来源：这里教程网时间：2026-03-03 15:13:14 作者：

一、获取执行计划方法总结

1. explain plan for获取；

2. set autotrace on ；

3. statistics_level=all;

4. 通过(dbms_xplan.display_cursor/dbms_xplan.display_awr)输入sql_id参数直接获取

5. awrsqrpt.sql

6. 10046/10053 trace跟踪

7. v$sql_plan_statistics_all

二、获取执行计划示例

1、explain plan for

SQL > set line 200
SQL > explain plan for
2 select c . org_name , sum ( a . amt ) from ht . c_cons c , ht . a_amt a
where c . cons_no = a . cons_no
and a . amt_ym = '201701' and c . cons_name = 'wang1706'
group by c . org_name ; 3 4 5
Explained .
SQL > select * from table ( dbms_xplan . display ) ;
SQL > /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value : 1779151266
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ( % CPU ) | Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 72 ( 2 ) | 00 : 00 : 01 |
| 1 | HASH GROUP BY | | 1 | 39 | 72 ( 2 ) | 00 : 00 : 01 |
| * 2 | HASH JOIN | | 3 | 117 | 71 ( 0 ) | 00 : 00 : 01 |
| 3 | TABLE ACCESS BY INDEX ROWID | C_CONS | 2 | 44 | 3 ( 0 ) | 00 : 00 : 01 |
| * 4 | INDEX RANGE SCAN | IDX_CONS_NAME | 2 | | 1 ( 0 ) | 00 : 00 : 01 |
| * 5 | TABLE ACCESS FULL | A_AMT | 10364 | 172K | 68 ( 0 ) | 00 : 00 : 01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Predicate Information ( identified by operation id ) :
---------------------------------------------------
2 - access ( "C" . "CONS_NO" = "A" . "CONS_NO" )
4 - access ( "C" . "CONS_NAME" = 'wang1706' )
5 - filter ( "A" . "AMT_YM" = '201701' )
19 rows selected .

仅使用数据字典评估执行计划，SQL语句未执行。

结果解释

字段描述

ID 执行计划中每一个操作(行)的标识符。如果数字前面带有*号，意味着将在随后提供这行包含的谓词信息

Operation 执行的操作，也叫做行源操作。

Name 操作的对象

查询优化器评估

Rows(E-Rows) 评估中操作返回的记录条数

Bytes(E-Bytes) 评估中操作返回的记录字节数

TempSpc 评估中操作使用的临时空间大小

Cost(%CPU) 评估中操作的开销。在括号中列出了CPU开销的百分比。注意这些值是通过执行计划计算出来的。即，你操作的开销包含子操作的开销

Time 评估中执行操作需要的时间(HH:MM:SS)

分区

Pstart 访问的第一个分区

Pstop 访问的最后一个分区

并行和分布式处理

Inst 在分布式操作中，指操作使用的数据库链名称

TQ 在并行操作中，用于属线程间通信的表队列

IN-OUT 并行或分布式操作间的关系

PQ Distrib 在并行操作中，生产者为发送数据给消费者进行的分配

运行进统计

Starts 指定操作执行的次数

A-Rows 操作返回的真实记录数

A-Time 操作执行的真实时间

I/O统计

Buffers 执行期间进行的逻辑读操作数量

Reads 执行期间进行的物理读操作数量

Writes 执行期间进行的物理写操作数量

内存使用统计

0Mem 最优执行所需内存的评估值

1Mem 一次通过(one-pass：使用最少的磁盘操作，大部分操作在内存中执行)执行所需内存的评估值

0/1/M 最优/一次通过/多次通过(multipass: 工作区域设置太小，存在大量磁盘操作)模式操作执行次数

Used-Mem 最后一次执行时操作使用的内存量

Used-Tmp 最后一次执行时操作使用的临时空间大小。

Max-Tmp 操作使用的最大临时空间大小

2. set autotrace on

select c . org_name , sum ( a . amt ) from ht . c_cons c , ht . a_amt a
where c . cons_no = a . cons_no
and a . amt_ym = '201701' and c . cons_name = 'wang1706'
group by c . org_name ;
SQL > set autotrace on
SQL > select c . org_name , sum ( a . amt ) from ht . c_cons c , ht . a_amt a
where c . cons_no = a . cons_no
and a . amt_ym = '201701' and c . cons_name = 'wang1706'
group by c . org_name ; 2 3 4
ORG_NAME
------------------------------------------------------
guangdong
9986
Execution Plan
----------------------------------------------------------
Plan hash value : 1779151266
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ( % CPU ) | Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 72 ( 2 ) | 00 : 00 : 01 |
| 1 | HASH GROUP BY | | 1 | 39 | 72 ( 2 ) | 00 : 00 : 01 |
| * 2 | HASH JOIN | | 3 | 117 | 71 ( 0 ) | 00 : 00 : 01 |
| 3 | TABLE ACCESS BY INDEX ROWID | C_CONS | 2 | 44 | 3 ( 0 ) | 00 : 00 : 01 |
| * 4 | INDEX RANGE SCAN | IDX_CONS_NAME | 2 | | 1 ( 0 ) | 00 : 00 : 01 |
| * 5 | TABLE ACCESS FULL | A_AMT | 10364 | 172K | 68 ( 0 ) | 00 : 00 : 01 |
-----------------------------------------------------------------------------------------------
Predicate Information ( identified by operation id ) :
---------------------------------------------------
2 - access ( "C" . "CONS_NO" = "A" . "CONS_NO" )
4 - access ( "C" . "CONS_NAME" = 'wang1706' )
5 - filter ( "A" . "AMT_YM" = '201701' )
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
250 consistent gets
0 physical reads
0 redo size
609 bytes sent via SQL * Net to client
523 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
0 sorts ( memory )
0 sorts ( disk )
1 rows processed

语法解释：

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 ----如果SELECT 就不执行SQL(dml 执行)，只显示执行计划

set autot trace stat ----执行SQL，只显示统计信息

结果解释

1、DB Block Gets（当前请求的块数目）

2、Consistent Gets（数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块）

3、physical reads 物理读——执行SQL的过程中，从硬盘上读取的数据块个数

4、redo size 重做数——执行SQL的过程中，产生的重做日志的大小

5、bytes set via sql*net to client 通过sql*net发送给客户端的字节数

6、bytes received via sql*net from client 通过sql*net接受客户端的字节数

7、sorts(memory) 在内存中发生的排序

8、sorts(disk) 不能在内存中发生的排序，需要硬盘来协助

9、rows processed 结果的记录数

3. statistics_level=all

set line 200
set heading off
alter session set statistics_level = all ;
select c . org_name , sum ( a . amt ) from ht . c_cons c , ht . a_amt a
where c . cons_no = a . cons_no
and a . amt_ym = '201701' and c . cons_name = 'wang1706'
group by c . org_name ;
select * from table ( DBMS_XPLAN . DISPLAY_CURSOR ( null , null , 'allstats last' ) ) ;
Plan hash value : 1779151266
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation ? ? ? ? ? ? ? ? ? ? ? | Name | Starts | E - Rows | A - Rows | A - Time | Buffers | OMem | 1Mem | Used - Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT ? ? ? ? ? ? | | 1 | | 1 | 00 : 00 : 00 . 01 | 250 | | | |
| 1 | HASH GROUP BY ? ? ? ? ? ? ? | | 1 | 1 | 1 | 00 : 00 : 00 . 01 | 250 | 1126K | 1126K | 499K ( 0 ) |
| * 2 | HASH JOIN ? ? ? ? ? ? ? ? ? | | 1 | 3 | 1 | 00 : 00 : 00 . 01 | 250 | 1483K | 1483K | 679K ( 0 ) |
| 3 | TABLE ACCESS BY INDEX ROWID ? | C_CONS | 1 | 2 | 1 | 00 : 00 : 00 . 01 | 3 | | | |
| * 4 | INDEX RANGE SCAN ? ? ? ? | IDX_CONS_NAME | 1 | 2 | 1 | 00 : 00 : 00 . 01 | 2 | | | |
| * 5 | TABLE ACCESS FULL ? ? ? ? | A_AMT | 1 | 10364 | 9998 | 00 : 00 : 00 . 01 | 247 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information ( identified by operation id ) :
---------------------------------------------------
2 - access ( "C" . "CONS_NO" = "A" . "CONS_NO" )
4 - access ( "C" . "CONS_NAME" = 'wang1706' )
5 - filter ( "A" . "AMT_YM" = '201701' )

结果解释：

Starts表示这个操作执行了几次

E-Rows表示优化器估算的行数

A-Rows 表示实际的行数

A-Time 表示这个操作执行的时间(累加的)

Buffers 表示逻辑度(累加的)

4. 通过(dbms_xplan.display_cursor/dbms_xplan.display_awr)输入sql_id参数直接获取

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

select * from table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

5、awrsqrpt.sql

SQL> @?/rdbms/admin/awrsqrpt.sql

Enter value for begin_snap: 110

Begin Snapshot Id specified: 110

Enter value for end_snap: 112

End Snapshot Id specified: 112

Specify the SQL Id

~~~~~~~~~~~~~~~~~~

Enter value for sql_id:

6. 10046/10053 trace跟踪

oradebug setmypid
oradebug unlimit
select c . org_name , sum ( a . amt ) from ht . c_cons c , ht . a_amt a
where c . cons_no = a . cons_no
and a . amt_ym = '201701' and c . cons_name = 'wang1706'
group by c . org_name ;
oradebug session_event 10046 trace name context forever , level 12
oradebug event 10046 trace name context off
oradebug tracefile_name
[ oracle @ sndb ~ ] $ tkprof / u01 / app / oracle / diag / rdbms / sndb1 / sndb1 / trace / sndb1_ora_29017 . trc / home / oracle / sndb1_ora_29017 . trc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0 . 00 0 . 00 0 0 0 0
Execute 1 0 . 00 0 . 00 0 0 0 0
Fetch 2 0 . 02 0 . 02 0 250 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0 . 02 0 . 02 0 250 0 1
Misses in library cache during parse : 0
Optimizer mode : ALL_ROWS
Parsing user id : SYS
Number of plan statistics captured : 1
Rows ( 1st ) Rows ( avg ) Rows ( max ) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 HASH GROUP BY ( cr = 250 pr = 0 pw = 0 time = 24310 us cost = 72 size = 39 card = 1 )
1 1 1 HASH JOIN ( cr = 250 pr = 0 pw = 0 time = 23827 us cost = 71 size = 117 card = 3 )
1 1 1 TABLE ACCESS BY INDEX ROWID C_CONS ( cr = 3 pr = 0 pw = 0 time = 113 us cost = 3 size = 44 card = 2 )
1 1 1 INDEX RANGE SCAN IDX_CONS_NAME ( cr = 2 pr = 0 pw = 0 time = 91 us cost = 1 size = 0 card = 2 ) ( object id 87459 )
9998 9998 9998 TABLE ACCESS FULL A_AMT ( cr = 247 pr = 0 pw = 0 time = 11812 us cost = 68 size = 176188 card = 10364 )
Elapsed times include waiting on following events :
Event waited on Times Max . Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL * Net message to client 2 0 . 00 0 . 00
SQL * Net message from client 2 0 . 00 0 . 00

7. v$sql_plan_statistics_all

select '| Operation |Object Name | Rows | Bytes| Cost |'
as "Explain Plan in library cache:" from dual
union all
select rpad ( '| ' | | substr ( lpad ( ' ' , 1 * ( depth - 1 ) ) | | operation | |
decode ( options , null , '' , ' ' | | options ) , 1 , 35 ) , 36 , ' ' ) | | '|' | |
rpad ( decode ( id , 0 , '----------------------------' ,
substr ( decode ( substr ( object_name , 1 , 7 ) , 'SYS_LE_' , null , object_name )
| | ' ' , 1 , 30 ) ) , 31 , ' ' ) | | '|' | | lpad ( decode ( cardinality , null , ' ' ,
decode ( sign ( cardinality - 1000 ) , - 1 , cardinality | | ' ' ,
decode ( sign ( cardinality - 1000000 ) , - 1 , trunc ( cardinality / 1000 ) | | 'K' ,
decode ( sign ( cardinality - 1000000000 ) , - 1 , trunc ( cardinality / 1000000 ) | | 'M' ,
trunc ( cardinality / 1000000000 ) | | 'G' ) ) ) ) , 7 , ' ' ) | | '|' | |
lpad ( decode ( bytes , null , ' ' ,
decode ( sign ( bytes - 1024 ) , - 1 , bytes | | ' ' ,
decode ( sign ( bytes - 1048576 ) , - 1 , trunc ( bytes / 1024 ) | | 'K' ,
decode ( sign ( bytes - 1073741824 ) , - 1 , trunc ( bytes / 1048576 ) | | 'M' ,
trunc ( bytes / 1073741824 ) | | 'G' ) ) ) ) , 6 , ' ' ) | | '|' | |
lpad ( decode ( cost , null , ' ' , decode ( sign ( cost - 10000000 ) , - 1 , cost | | ' ' ,
decode ( sign ( cost - 1000000000 ) , - 1 , trunc ( cost / 1000000 ) | | 'M' ,
trunc ( cost / 1000000000 ) | | 'G' ) ) ) , 8 , ' ' ) | | '|' as "Explain plan"
from v$sql_plan_statistics_all sp
where sp . hash_value = & hash_value ;