推荐教程：《oracle视频教程》
今天谈一谈oracle查看执行计划的方式，以及怎样看执行计划。
一、查看执行计划的方式1.1、设置autotraceautotrace命令如下
序号
命令
解释
1
set autotrace off
此为默认值，即关闭autotrace
2
set autotrace on explain
只显示执行计划
3
set autotrace on statistics
只显示执行的统计信息
4
set autotrace on
包含2,3两项内容
5
set autotrace traceonly
与on相似，但不显示语句的执行结果
1.2、使用第三方工具比如pl/sql develop的explain窗口
1.3、explain plan for据说在执行的sql前加上 explain plan for 可以查看执行计划，还没搞明白，后续补上
举例：
sql> explain plan for select * from emp;
已解释。
sql> select plan_table_output from table(dbms_xplan.display('plan_table'));
或者：
sql> select * from table(dbms_xplan.display);
二、清除sga缓存因为在sql执行时，sql的执行计划、从磁盘读取的数据库等信息会在sga的某些缓存中保存一段时间，为了查看语句第一次执行的效果，就需要清空这些缓存。
alter system flush shared_pool;alter system flush buffer_cache;alter system flush global context;
三、分析执行计划3.1、创建测试表新建两张表cust_info、cst_tran（单纯用来测试，没有实际意义）
create table cust_info(cst_no number,cst_name varchar2(50),age smallint);create table cst_tran(cst_no number,tran_date varchar2(8),tran_amt number(19,3));
插入一些数据，cust_info表1万，cst_tran表100万。
insert into cust_infoselect 100000+level, 'test'||level, round(dbms_random.value(1,100))from dualconnect by level<=10000;insert into cst_tranwith aa as(select level from dual connect by level<=100)select t.cst_no, to_char(sysdate - dbms_random.value(1,1000),'yyyymmdd'), round(dbms_random.value(1,999999999),3)from cust_info tinner join aaon 1=1;
3.2、查看执行计划查看这两个表关联的执行计划
sql> select t.cst_no, t.cst_name, g.tran_date, g.tran_amt from cust_info t inner join cst_tran g on g.cst_no = t.cst_no;1000000 rows selected.execution plan----------------------------------------------------------plan hash value: 2290587575--------------------------------------------------------------------------------| id | operation | name | rows | bytes | cost (%cpu)| time |--------------------------------------------------------------------------------| 0 | select statement | | 996k| 68m| 1079 (2)| 00:00:13 ||* 1 | hash join | | 996k| 68m| 1079 (2)| 00:00:13 || 2 | table access full | cust_info | 10000 | 390k| 11 (0)| 00:00:01 || 3 | table access full | cst_tran | 1065k| 32m| 1064 (1)| 00:00:13 |--------------------------------------------------------------------------------predicate information (identified by operation id):--------------------------------------------------- 1 - access("g"."cst_no"="t"."cst_no")note----- - dynamic sampling used for this statement (level=2)statistics---------------------------------------------------------- 561 recursive calls 0 db block gets 70483 consistent gets 4389 physical reads 0 redo size 45078003 bytes sent via sql*net to client 733845 bytes received via sql*net from client 66668 sql*net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 1000000 rows processed
3.2.1、执行计划首先我们看一下第一部分
--------------------------------------------------------------------------------| id | operation | name | rows | bytes | cost (%cpu)| time |--------------------------------------------------------------------------------| 0 | select statement | | 996k| 68m| 1079 (2)| 00:00:13 ||* 1 | hash join | | 996k| 68m| 1079 (2)| 00:00:13 || 2 | table access full | cust_info | 10000 | 390k| 11 (0)| 00:00:01 || 3 | table access full | cst_tran | 1065k| 32m| 1064 (1)| 00:00:13 |--------------------------------------------------------------------------------
执行计划中字段解释：
id: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。 operation： 当前操作的内容。 rows： 当前操作的cardinality，oracle估计当前操作的返回结果集。 cost（cpu）：oracle 计算出来的一个数值（代价），用于说明sql执行的代价。 time：oracle 估计当前操作的时间。说明：
1、operation
记录每一步的操作，按照缩进的程度判断执行的先后顺序。
在olap数据库中，hash join连接较多，特别是返回数据集大的时候，基本都是hash join。
2、rows
rows值表示cbo预期从一个行源（row source）返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。 在oracle 9i中的执行计划中，cardinality缩写成card。 在10g中，card值被rows替换。
rows值对于cbo做出正确的执行计划来说至关重要。 如果cbo获得的rows值不够准确（通常是没有做分析或者分析数据过旧造成），在执行计划成本计算上就会出现偏差，从而导致cbo错误的制定出执行计划。
在多表关联查询或者sql中有子查询时，每个关联表或子查询的rows的值对主查询的影响都非常大，甚至可以说，cbo就是依赖于各个关联表或者子查询rows值计算出最后的执行计划。
对于多表查询，cbo使用每个关联表返回的行数（rows）决定用什么样的访问方式来做表关联（如nested loops join 或 hash join）
3、cost（cpu）和time是执行计划的重要参考值
3.2.2、谓词说明：predicate information (identified by operation id):
---------------------------------------------------
1 - access("g"."cst_no"="t"."cst_no")
note
-----
- dynamic sampling used for this statement (level=2)
access: 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。
filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。（此例中没有）
注意：在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。
3.2.3、统计信息statistics---------------------------------------------------------- 561 recursive calls 0 db block gets 70483 consistent gets 4389 physical reads 0 redo size 45078003 bytes sent via sql*net to client 733845 bytes received via sql*net from client 66668 sql*net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 1000000 rows processed
参数说明：
recursive calls :递归调用。一般原因：dictionary cache未命中；动态存储扩展；pl/sql语句db block gets :bufer中读取的block数量，用于insert,update,delete,selectfor updateconsistent gets :这里是一致读次数（一个block可能会被读多次），bufer中读取的用于查询（除掉select forupdate）的block数量。 physical reads :从磁盘上读取的block数量,敬请关注每周五晚免费网络公开课。redo size :bytes，写到redo logs的数据量bytes sent via sql*net to client ：发送给客户端的字节数bytes received via sql*net from client ：从客户端接收的字节数sql*net roundtrips to/from client ：与客户端的交互次数（个人理解接收一条sql语句，执行结果分多次发送给客户端，如有问题请指正）sorts (memory) :内存排序次数sorts (disk) :磁盘排序次数；与sort_area_size有关 rows processed ：执行完sql后返回结果集的行数四、部分信息解释4.1、sql*net roundtrips to/from client的计算方式这个指标的计算方式和一个参数息息相关，arraysize。
arraysize是什么呢？
请查阅大牛博文：oracle arraysize 和 fetch size 参数 与 性能优化 说明
arraysize定义了一次返回到客户端的行数，取值范围【1-5000】，默认15。
使用命令在数据库中查看arraysize的值。
show arraysize
还可以修改这个值
set arraysize 5000;
明白了arraysize这个参数就可以计算sql*net roundtrips to/from client的值了。上例中，返回客户端结果集的行数是1000000，默认arraysize值是15，1000000/15向上取整等于66667。
为啥要向上取整？
举个栗子，如果有10个苹果，一个只能拿3个，几次可以拿完，3次可以拿9个，还剩1个，所以还需要再拿一次，共4次。
统计分析中的值是66668，为什么我们计算的值是66667？
就要看这个指标本身了，再粘贴一次：sql*net roundtrips to/from client 重点看from，意思是我们还要接受一次客户端发来的sql语句，因此是：66667+1，本问题纯属个人臆断，无真凭实据，受限于本人的知识水平，如有误，请指出。
将arraysize的值修改为5000后，再观察sql*net roundtrips to/from client的变化，结果为201。
前面提到 arraysize的取值范围是【1-5000】，我们可以试一下改为不在这个区间的值，比如改为0，结果报错了
sql> set arraysize 0;sp2-0267: arraysize option 0 out of range (1 through 5000)
4.2、consistent gets译为中文就是：一致性读， 好抽象的一个指标，啥叫一致性读，心中无数羊驼驼在大海中狂奔。
官网对consistent gets 的解释：
consistent gets：number of times a consistent read wasrequested for a block.
通常我们执行sql查询时涉及的每一block都是consistent read, 只是有些cr(consistent read)需要使用undo 来进行构造, 大部分cr(consistent read)并不涉及到undo block的读.
还有就是每次读这个block都是一次cr(可能每个block上有多个数据row), 也就是如果某个block被读了10次, 系统会记录10个consistent read.
如果想深入学习，请参考大佬博文：oracle 有关 consistent gets 的测试 -- cndba.cn_中国dba社区
接来下测试下， consistent gets是从哪来的，需要使用有sysdba权限的用户，因为oradebug工具需要sysdba权限。
oradebug工具介绍：oracle实用工具：oradebug
使用10046对同一条数据跟踪两次，注意观察 consistent gets的不同
为了不影响测试结果，首先清空缓存
sql> alter system flush shared_pool;system altered.sql> alter system flush buffer_cache;system altered.sql> alter system flush global context;system altered.
第一次执行
sql> set tim on timing on00:42:30 sql> set autot trace stat00:42:36 sql> oradebug setmypidstatement processed.00:42:42 sql> alter session set tracefile_identifier='chf1';session altered.elapsed: 00:00:00.0100:42:50 sql> oradebug event 10046 trace name context forever,level 12;statement processed.00:42:57 sql> select t.cst_no, t.cst_name, g.tran_date, g.tran_amt from chf.cust_info t inner join chf.cst_tran g on g.cst_no = t.cst_no;1000000 rows selected.elapsed: 00:00:22.71statistics---------------------------------------------------------- 547 recursive calls 0 db block gets 70368 consistent gets 3898 physical reads 0 redo size 45078003 bytes sent via sql*net to client 733845 bytes received via sql*net from client 66668 sql*net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 1000000 rows processed00:44:24 sql> oradebug event 10046 trace name context off;statement processed.00:45:54 sql> oradebug tracefile_name/u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf1.trc
第二次执行
00:46:04 sql> alter session set tracefile_identifier='chf2';session altered.elapsed: 00:00:00.0000:46:35 sql> oradebug event 10046 trace name context forever,level 12;statement processed.00:46:43 sql> select t.cst_no, t.cst_name, g.tran_date, g.tran_amt from chf.cust_info t inner join chf.cst_tran g on g.cst_no = t.cst_no;1000000 rows selected.elapsed: 00:00:21.62statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 70301 consistent gets 3850 physical reads 0 redo size 45078003 bytes sent via sql*net to client 733845 bytes received via sql*net from client 66668 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1000000 rows processed00:47:11 sql> oradebug event 10046 trace name context off;statement processed.00:49:03 sql> oradebug tracefile_name/u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf2.trc
通过对比两次执行，发现consistent gets、physical reads、sorts (memory)都有变化，这是因为sga中已经缓存了部分数据块。
再对比下我们刚才生产的两个跟踪日志，为方便查看，先将其格式转换以下
[oracle@localhost ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf1.trc /u01/chf1.trctkprof: release 11.2.0.1.0 - development on wed dec 8 00:53:37 2021copyright (c) 1982, 2009, oracle and/or its affiliates. all rights reserved.[oracle@localhost ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf2.trc /u01/chf2.trctkprof: release 11.2.0.1.0 - development on wed dec 8 00:53:48 2021copyright (c) 1982, 2009, oracle and/or its affiliates. all rights reserved.
打开 /u01/chf1.trc，下面贴出部分重要信息
overall totals for all non-recursive statementscall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------parse 1 0.03 0.03 8 67 0 0execute 1 0.00 0.00 0 0 0 0fetch 66668 0.76 3.24 3890 70301 0 1000000------- ------ -------- ---------- ---------- ---------- ---------- ----------total 66670 0.79 3.28 3898 70368 0 1000000misses in library cache during parse: 1elapsed times include waiting on following events: event waited on times max. wait total waited ---------------------------------------- waited ---------- ------------ sql*net message to client 66670 0.01 0.14 sql*net message from client 66670 64.54 79.11 db file sequential read 5 0.00 0.00 disk file operations i/o 1 0.00 0.00 db file scattered read 5 0.00 0.00 asynch descriptor resize 4 0.00 0.00 direct path read 69 0.00 0.02overall totals for all recursive statementscall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------parse 12 0.00 0.00 0 0 0 0execute 24 0.01 0.01 0 0 0 0fetch 30 0.00 0.00 8 67 0 18------- ------ -------- ---------- ---------- ---------- ---------- ----------total 66 0.02 0.02 8 67 0 18
打开 /u01/chf2.trc，下面贴出部分重要信息
overall totals for all non-recursive statementscall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------parse 1 0.00 0.00 0 0 0 0execute 1 0.00 0.00 0 0 0 0fetch 66668 1.57 3.73 3850 70301 0 1000000------- ------ -------- ---------- ---------- ---------- ---------- ----------total 66670 1.57 3.73 3850 70301 0 1000000misses in library cache during parse: 0elapsed times include waiting on following events: event waited on times max. wait total waited ---------------------------------------- waited ---------- ------------ sql*net message to client 66670 0.00 0.10 sql*net message from client 66670 6.83 19.93 asynch descriptor resize 4 0.00 0.00 direct path read 69 0.00 0.01overall totals for all recursive statementscall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------parse 0 0.00 0.00 0 0 0 0execute 0 0.00 0.00 0 0 0 0fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 0 0.00 0.00 0 0 0 0
比较发现，第一次执行解析sql语句，生产执行计划时，consistent gets发生67次，执行sql语句时发生70301。第一次执行解析sql语句，生产执行计划时，因已经有缓存，所以consistent gets发生0次，执行sql语句时发生70301。
推荐教程：《oracle视频教程》
以上就是完全掌握oracle进阶学习之查看执行计划的详细内容。