怎样看懂 Oracle 的执行计划 收藏
一、什么是执行计划
An explain plan is a representation of the access path that is taken when a query is executed within
Oracle.
二、如何访问数据
At the physical level Oracle reads blocks of data. The smallest amount of data read is a single
Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically
Oracle finds the data to read by using the following methods:
Full Table Scan (FTS) --全表扫描
Index Lookup (unique & non-unique) --索引扫描(唯一和非唯一)
Rowid --物理行 id
三、执行计划层次关系
When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing
that is executed. --采用最右最上最先执行的原则看层次关系,在同一级如果某个动作没有子
ID 就最先执行
1.看一个简单的例子:
Query Plan
-----------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1234
**TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED] --[:Q65001] 表示是并行方式,
[ANALYZED]表示该对象已经分析过了
优化模式是 CHOOSE 的情况下,看 Cost 参数是否有值来决定采用 CBO 还是 RBO:
SELECT STATEMENT [CHOOSE] Cost=1234 --Cost 有值,采用 CBO
SELECT STATEMENT [CHOOSE] Cost= --Cost 为空,采用 RBO
2.层次的父子关系,看比较复杂的例子:
PARENT1
**FIRST CHILD
****FIRST GRANDCHILD
**SECOND CHILD
Here the same principles apply, the FIRST GRANDCHILD is the initial operation then the FIRST