查看oracle执行计划的方法

婉9843

一、查看oracle执行计划的方法有三种



1、设置autotrace


详见http://yijiangyanyu.iyunv.com/admin/blogs/1628343




2、使用EXPLAIN PLAN






SQL>EXPLAIN PLAN FOR sql;
SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
 

3、使用TOAD、PL/SQL Developer


PL/SQL Developer中输入SQL语句后，F5即可查看执行计划。



二、执行计划中的字段解释





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

| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Tim

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

      
Operation
： 当前操作的内容。


       Name：对象名



      
Rows
： 当前操作的Cardinality
，Oracle
估计当前操作的返回结果集。Cardinality
值表示
CBO
预期从一个行源（
row source
）返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。





      
Cost
（CPU
）：Oracle
计算出来的一个数值（代价），用于说明SQL
执行的代价。


      
Time
：Oracle
估计当前操作的时间。





    Cardinality
的值对于
CBO
做出正确的执行计划来说至关重要。
如果
CBO
获得的
Cardinality
值不够准确（通常是没有做分析或者分析数据过旧造成），在执行计划成本计算上就会出现偏差，从而导致
CBO
错误的制定出执行计划。






    在多表关联查询或者
SQL
中有子查询时，每个关联表或子查询的
Cardinality
的值对主查询的影响都非常大，甚至可以说，
CBO
就是依赖于各个关联表或者子查询
Cardinality
值计算出最后的执行计划。




　　    对于多表查询，
CBO
使用每个关联表返回的行数（
Cardinality
）决定用什么样的访问方式来做表关联（如
Nested loops Join
或
hash Join
）。



对于子查询，它的
Cardinality
将决定子查询是使用索引还是使用全表扫描的方式访问数据。





三、谓词说明


Predicate Information (identified by operation id):



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




  
4 - access
("A"."EMPNO"="B"."MGR")




      
filter("A"."EMPNO"="B"."MGR")




  
5 - filter
("B"."MGR" IS NOT NULL)




 



      
Access:
表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。



      
Filter
：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。





四、表访问方式

　　1.Full Table Scan (FTS) 

全表扫描




　　


　　In
a FTS operation, the whole table is read up 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（即曾经扩展的最后一个数据块）。如果使用delete删除了所有行，全表扫描仍然读到HWM。Truncate会使HWM重置到表开始的位置。



　　


　　2.Index Lookup 

索引扫描




　　有5种索引扫描的方式：

　　


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.


查询条件中使用了唯一索引，总是返回一条数据。至少使用索引的前导列访问数据。


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. >
< <> >= <= between) .


索引局部扫描用于访问一个特定列一定范围内的数据。查询条件至少包含索引的前导列。涉及范围的SQL操作：>、
<、 <>、 >=、 <=、 between。




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.


索引全局扫描只在CBO（基于成本的优化器）使用。当统计信息表明索引全局扫描比全表扫描和排序更有效时，选择这种扫描方式。例如：当进行一次无范围的索引扫描并且想使数据按照索引顺序排序的时候。



index fast full scan





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.


 

索引快速全局扫描，不带
order by
情况下常发生





index skip scan  





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.



索引跳跃扫描，
where
条件列是非索引的前导列情况下常发生。




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.NESTED LOOP




对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择
。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于
1

万
不适合），要把返回子集较小表的作为外表（
CBO
默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用
ORDERED
提示来改变
CBO
默认的驱动表，使用
USE_NL(table_name1 table_name2)可是强制
CBO 执行嵌套循环连接
。



        






Nested loop一般用在连接的表中有索引，并且索引选择性较好的时候
。



 





步骤：
确定一个驱动表
(outer table)
，另一个表为
inner table
，驱动表中的每一行与
inner
表中的相应记录
JOIN
。类似一个嵌套的循环。适用于驱动表的记录集比较小（
<10000）而且
inner表需要有有效的访问方法（
Index）
。需要注意的是：
JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。




cost = outer access cost + (inner access cost * outer cardinality)








2.HASH JOIN




散列连接是
CBO 做大数据集连接时的常用方式
，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。



这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高
I/O
的性能。



也可以用
USE_HASH(table_name1 table_name2)提示来强制使用散列连接
。如果使用散列连接
HASH_AREA_SIZE
初始化参数必须足够的大，如果是
9i
，
Oracle
建议使用
SQL
工作区自动管理，设置
WORKAREA_SIZE_POLICY
为
AUTO
，然后调整
PGA_AGGREGATE_TARGET
即可。



        






Hash join在两个表的数据量差别很大的时候
。




 





步骤：
将两个表中较小的一个在内存中构造一个
HASH
表（对
JOIN KEY
），扫描另一个表，同样对
JOIN KEY
进行
HASH
后探测是否可以
JOIN
。适用于记录集比较大的情况。需要注意的是：如果
HASH
表太大，无法一次构造在内存中，则分成若干个
partition
，写入磁盘的
temporary segment
，则会多一个写的代价，会降低效率。



cost = (outer access cost * # of hash partitions) + inner access cost








3.SORT MERGE JOIN







通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用
USE_MERGE(table_name1 table_name2)来强制使用排序合并连接
。


        






Sort Merge join 用在没有索引，并且数据已经排序的情况
。




 





cost = (outer access cost * # of hash partitions) + inner access cost



 





步骤：
将两个表排序，然后将两个表合并。通常情况下，只有在以下情况发生时，才会使用此种
JOIN
方式：



1.RBO
模式



2.
不等价关联
(>,<,>=,<=,<>)



3.HASH_JOIN_ENABLED=false



4.
数据源已排序





4.比较








Hash join
的工作方式
是将一个表（通常是小一点的那个表）做
hash
运算，将列数据存储到
hash
列表中，从另一个表中抽取记录，做
hash
运算，到
hash
列表中找到相应的值，做匹配。



    Nested loops
工作方式
是从一张表中读取数据，访问另一张表（通常是索引）来做匹配，
nested loops
适用的场合是当一个关联表比较小的时候，效率会更高。



   
Merge Join

是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为
merge join
需要做更多的排序，所以消耗的资源更多。
通常来讲，能够使用
merge join
的地方，
hash join
都可以发挥更好的性能。





六、运算符


1.sort




There are a number of different operations that promote sorts:


(1)
order by clauses
 (2)
group by
 (3)
sort merge join 



排序，很消耗资源



。以上
三个操作会产生排序运算。




2.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, functions such as min may introduce filter steps
into query plans.



3.view 

   --
视图，大都由内联视图产生(
可能深入到视图基表)



When
a view cannot be merged into the main query you will often see a
projection view operation. This indicates that the 'view' will be
selected from directly as opposed to being broken down into joins on the
base tables. A number of constructs make a view non mergeable. Inline
views are also non mergeable.


eg: SQL> explain plan for


select ename,tot
 
from emp,(select empno,sum(empno) tot from big_emp group by empno) tmp



where emp.empno = tmp.empno;


Query Plan


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


SELECT STATEMENT [CHOOSE]


**HASH JOIN


**TABLE ACCESS FULL EMP [ANALYZED]


**VIEW


****SORT GROUP BY


******INDEX FULL SCAN BE_IX


4.partition view




Partition
views are a legacy technology that were superceded by the partitioning
option. This section of the article is provided as reference for such
legacy systems.



　　


　　网上资源:http://blog.csdn.net/tianlesoftware/article/details/5827245