怎样看懂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、看一个简单的例子：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为空，采用RBO2、层次的父子关系，看比较复杂的例子：PARENT1*FIRST CHILD*FIRST GRANDCHILD*SECOND CHILDHere 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 shortened summary of this is:Execution starts with ID=0: SELECT STATEMENT；but this is dependand on its child objects；So it executes its first child step: ID=1 PID=0 HASH JOIN；but this is dependand on its 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=2 TABLE ACCESS (FULL) OF EMP；Rows are returned to the parent step(s) until finished五、表访问方式1、Full Table Scan (FTS)全表扫描In a FTS operation, the whole table is readup to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. 全表扫描模式下会读数据到表的高水位线（HWM即表示表曾经扩展的最后一个数据块），读取速度依赖于Oracle初始化参数db_block_multiblock_read_countQuery Plan-SELECT STATEMENT CHOOSE Cost=1*INDEX UNIQUE SCAN EMP_I1 如果索引里就找到了所要的数据，就不会再去访问表了。2、Index Lookup索引扫描There are 5 methods of index lookup:l index unique scan 索引唯一扫描Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.eg:SQLexplain plan for select empno,ename from emp where empno=10;l index range scan 索引局部扫描Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. = explain plan for select mgr from emp where mgr = 5;l index full scan 索引全局扫描Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.eg:SQL explain plan for select empno,ename from big_emp order by empno,ename;l index fast full scan 索引快速全局扫描，不带order by情况下常发生Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.eg:SQL explain plan for select empno,ename from big_emp;index skip scan -索引跳跃扫描，where条件列是非索引的前导列情况下常发生Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.eg:SQL create index i_emp on emp(empno, ename);SQL select /*+ index_ss(emp i_emp)*/ job from emp where ename=SMITH;3、Rowid物理ID扫描This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. Rowid扫描是最快的访问数据方式六、表连接方式有三种连接方式：1、Sort Merge Join (SMJ)由于sort是非常耗资源的，所以这种连接方式要避免；Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently.SQL explain plan forselect /*+ ordered */ e.deptno,d.deptnofrom emp e,dept dwhere e.deptno = d.deptnoorder by e.deptno,d.deptno;Query Plan-SELECT STATEMENT CHOOSE Cost=17*MERGE JOIN*SORT JOIN*TABLE ACCESS FULL EMP ANALYZED*SORT JOIN*TABLE ACCESS FULL DEPT ANALYZEDSorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.2、Nested Loops (NL) 比较高效的一种连接方式Fetches the first batch of rows from row source 1, Then we probe row source 2 once for each row returned from row source 1.For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.SQL explain plan forselect a.dname,b.sqlfrom dept a,emp bwhere a.deptno = b.deptno;Query Plan-SELECT STATEMENT CHOOSE Cost=5*NESTED LOOPS*TABLE ACCESS FULL DEPT ANALYZED*TABLE ACCESS FULL EMP ANALYZED3、Hash Join 最为高效的一种连接方式New join type introduced in 7.3, More efficient in theory than NL & SMJ,Only accessible via the CBO. Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory.SQL explain plan forselect /*+ use_hash(emp) */ empnofrom emp,deptwhere emp.deptno = dept.deptno;Query Plan-SELECT STATEMENT CHOOSE Cost=3*HASH JOIN*TABLE ACCESS FULL DEPT*TABLE ACCESS FULL EMPHash joins are enabled by the parameter HASH_JOIN_ENABLED=TRUE in the init.ora or session. TRUE is the default in 7.3.4、Cartesian Product 卡迪尔积，不算真正的连接方式，sql肯定写的有问题A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data. Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out.It can be useful in some circumstances - Star joins uses cartesian products.Notice that there is no join between the 2 tables:SQL explain plan forselect emp.deptno,dept,deptnofrom emp,deptQuery Plan-SLECT STATEMENT CHOOSE Cost=5*MERGE JOIN CARTESIAN*TABLE ACCESS FULL DEPT*SORT JOIN*TABLE ACCESS FULL EMPThe CARTESIAN keyword indicate that we are doing a cartesian product.七、运算符1、sort 排序，很消耗资源There are a number of different operations that promote sorts:order by clausesgroup bysort merge join2、filter 过滤，如not in、min函数等容易产生Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another,