Oracle 执行计划 Explain Plan 小田
一．
1.1 设置autotrace
|
序号 |
命令 |
解释 |
|
1 |
SET |
此为默认值，即关闭Autotrace |
|
2 |
SET |
只显示执行计划 |
|
3 |
SET |
|
|
4 |
SET |
|
|
5 |
SET |
|
SQL> set autotrace on
SQL> select * from dave;
---------- ----------
已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 3458767806
--------------------------------------------------------------------------
| Id
--------------------------------------------------------------------------
|
|
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
SQL>
1.2 使用SQL
SQL>EXPLAIN PLAN FOR sql语句;
SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
示例：
SQL> EXPLAIN PLAN FOR SELECT * FROM DAVE;
已解释。
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
或者：
SQL>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3458767806
--------------------------------------------------------------------------
| Id
--------------------------------------------------------------------------
|
|
--------------------------------------------------------------------------
已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 2137789089
--------------------------------------------------------------------------------
| Id
---------------------------------------------------------------------------------------------
|
|
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
SQL>
1.3 使用Toad,PL/SQL Developer工具
二．
Cardinality值表示CBO预期从一个行源（row
source）返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。
这是9i的一个执行计划，我们可以看到关键字Card：
----------------------------------------------------------
Oracle 10g的执行计划，关键字换成了rows：
执行计划
----------------------------------------------------------
Plan hash value: 2137789089
--------------------------------------------------------------------------------
| Id
---------------------------------------------------------------------------------------------
|
|
---------------------------------------------------------------------------------------------
Cardinality的值对于CBO做出正确的执行计划来说至关重要。如果CBO获得的Cardinality值不够准确（通常是没有做分析或者分析数据过旧造成），在执行计划成本计算上就会出现偏差，从而导致CBO错误的制定出执行计划。
对于子查询，它的Cardinality将决定子查询是使用索引还是使用全表扫描的方式访问数据。
三． SQL 的执行计划
示例：
SQL> SET AUTOTRACE TRACEONLY;
SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr;
已选择13行。
执行计划
----------------------------------------------------------
Plan hash value: 992080948
---------------------------------------------------------------------------------------
| Id
---------------------------------------------------------------------------------------
|
|
|
|
|*
|*
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
统计信息
----------------------------------------------------------
SQL>
图片是Toad工具查看的执行计划。 在Toad 里面，很清楚的显示了执行的顺序。但是如果在SQLPLUS里面就不是那么直接。但我们也可以判断：一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。
3.1 执行计划中字段解释：
3.2 谓词说明：
Predicate Information (identified by operation id):
---------------------------------------------------
在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。
3.3 统计信息说明：
db
consistent
physical
redo
sorts
sorts
关于physical reads ，db block gets 和consistent gets这三个参数之间有一个换算公式：
用以下语句可以查看数据缓冲区的命中率：
Recursive Calls：
Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls。
DB Block Gets： Number of times a CURRENT block was requested.
Current mode blocks are retrieved as they exist right now, not
in a consistent read fashion. Normally, blocks retrieved for a
query are retrieved as they existed when the query began. Current
mode blocks are retrieved as they exist right now, not from a
previous point in time. During a SELECT, you might see current mode
retrievals due to reading the data dictionary to find the extent
information for a table to do a full scan (because you need the
"right now" information, not the consistent read). During a
modification, you will access the blocks in current mode in order
to write to them. (DB Block Gets:请求的数据块在buffer能满足的个数)
Consistent Gets： Number of times a consistent read was requested for a block.
This is how many blocks you processed in "consistent read" mode.
This will include counts of blocks read from the rollback segment
in order to roll back a block. This is the mode you read blocks in
with a SELECT, for example. Also, when you do a searched
UPDATE/DELETE, you read the blocks in consistent read mode and then
get the block in current mode to actually do the modification.
(Consistent Gets: 数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块)
Physical Reads： Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache. (Physical Reads:实例启动后，从磁盘读到Buffer Cache数据块数量)
就是从磁盘上读取数据块的数量，其产生的主要原因是：
它们三者之间的关系大致可概括为：
Sorts(disk):
bytes sent via SQL*Net to client:
bytes received via SQL*Net from client:
SQL*Net roundtrips to/from client:
更多内容参考Oracle联机文档：
3.4 动态分析
这里会出现两种情况：
（1）
（2）
总结：
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23322954/viewspace-1115637/ ，如需转载，请注明出处，否则将追究法律责任。
