怎样看懂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 CHILD followed by the SECOND CHILD and finally the PARENT collates the output. 四、例子解说 Execution Plan ---------------------------------------------------------- 0 **SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=8 Bytes=248) 1 0 **HASH JOIN (Cost=3 Card=8 Bytes=248) 2 1 ****TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36) 3 1 ****TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304) 左侧的两排数据，前面的是序列号 ID，后面的是对应的 PID（父 ID）。 A ...