分析oracle执行计划,Oracle 执行计划简单范例说明(一)

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有值，采用CBOSELECT STATEMENT [CHOOSE] Cost= --Cost为空，采用RBO

2.例子解说

Execution Plan

----------------------------------------------------------

0 **SELECT STATEMENT Optimizer=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

shortened summary of this is:

Execution starts with ID=0: SELECT STATEMENT but this is dependand

on it's child objects

So it executes its first child step: ID=1 PID=0 HASH JOIN but this

is dependand on it's child objects

So it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL)

OF 'DEPT'

Then the second child step: ID=3 PID=1 TABLE ACCESS (FULL) OF

'EMP'

Rows are returned to the parent step(s) until finished