怎样看懂Oracle执行计划.docx

如何看懂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.一个简单的例子：SQL select /*+parallel (e 4)*/ * from emp e;Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=7134)1 0 TABLE ACCESS* (FULL) OF EMP (Cost=1 Card=82 Bytes=7134):Q5000--[:Q5000]表示是并行方式1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1.EMPNO,A1.ENAME,A1.JOB,A1.MGR,A1.HI优化模式是CHOOSE的情况下，看Cost参数是否有值来决定采用CBO还是RBO：SELECT STATEMENT [CHOOSE] Cost=1234--Cost有值，采用CBOSELECT STATEMENT [CHOOSE] --Cost为空，采用RBO(9I是如此显示的)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 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 its child objectsSo it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on its child objectsSo it executes its first child step: ID=2 PID=1 TA