执行计划

如果要分析某条SQL的性能问题，通常来讲，我们首先要去看SQL的执行 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 ，看看SQL的每一步执行计划是存在问题。如果一条SQL平时执行的很好，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本上可以断定是执行计划出了问题。看懂执行计划变成了SQL优化（其实在大多数情况下，SQL优化指的是SQL的性能问题定位）的先决条件。在讨论SQL执行计划计划之前，我们需要知道执行计划当中的一个非常重要的概念 —— cardinality（基数）。注释：下面做的所有试验都是在我本机上做过的SQL语句可以直接拷贝来执行！数据是我库上的数据。请注意！！！=====================================基数==========================================我们在看执行计划的每一步操作的时候，当前操作的cardinality值表示CBO预期从一个行源（row source）返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。在执行计划中，card就是cardinality的缩写（在10g以后，card值被rows替换显得更为直观），它表示CBO估算当前操作预期获得的记录数。cardinality的值对于CBO做出正确的执行计划来说至关重要。如果CBO获得的cardinality值不够准确（通常是没有做分析或者分析数据过旧导致的），在执行成本计算上就会出现偏差，从而导致CBO错误的执行处执行计划。create table t as select 1 id,object_name from dba_objects;update t set id = 99 where rownum=1;commit;create index t_ind on t(id);创建了一张表T，表里面的ID=99的记录有1条，ID=1的记录有49813条，这是一个数据分布非常不平均的表。select /*+ dynamic_sampling(t 0) +*/ * from t where id = 1;现在T表还没有被分析，hints /*+ dynamic_sampling(t 0) +*/ 的目的是让CBO无法通过动态采样获得表中实际数据的情况，此时，CBO只能根据数据字典表T的非常有限的信息（比如表的extents数量，数据块的数量）来猜测表中的数据。从结果中可以看到，CBO猜出的ID=1的数据量只有182条，这个数值对于表的总和来说，是一个比较小的值，所以CBO选择了索引而不是全表扫描。但是，实际情况呢！？select * from t where id = 1;通过动态采样（10g的数据库下面，如果表没有做分析，Oracle自动通过动态采样的方式来收集分析数据），CBO估算出表中的实际数据量54603条（cardinality），从执行计划中看到，这个数据值还是非常接近实际的数据量49813，CBO断定ID=1的数据基本上等同于表中的数据，所以选择了全表扫描。exec dbms_stats.gather_table_stats(user,'t',cascade=>true);set autotrace trace explain;select * from t where id = 99;通过对表的分析，CBO就可以获得T表和索引的充足的信息。上面的例子显示，CBO从分析数据中得到了id=99的数据记录是1，所以选择了索引，这种情况下，索引的效率是相当高的。set autotrace off;update t set id = 99;commit;set autotrace trace explain;select * from t where id = 99;然后我们把表中所有记录的id更新为99，因为没有对表进行分析，所以表中的分析数据还是之前的信息，CBO并不知道，可以看到，此时cardinality的值为1，就是说，此时CBO仍然认为表T中id=99的值只有1条，所以选择的仍然是索引。exec dbms_stats.gather_table_stats(user,'t',cascade=>true);select * from t where id = 99;重新对表进行分析后，CBO获得了正确的cardinality值 ———— 49809，表中id=1的记录有49809条，所以是全表扫描，这种情况下全表扫描是最佳的执行计划。在多表关联查询或者是SQL中有子查询时，每个关联表或者是子查询的cardinality的值对主查询的影响非常大，甚至可以说，CBO就是依赖于各个关联表或者子查询cardinality值来计算出最后的执行计划。对于多表查询，CBO使用每个关联表返回的行数（cardinality）决定使用什么样的访问方式来做表关联（比如nestd loops join 或者是 hash join）；对于子查询，它的cardinality将决定子查询是使用索引还是使用全表扫描的方式访问数据。下面是一个完整的子句查询的例子。我们来关注在子查询中，cardinality是如何影响主查询的执行计划的。create table t1(id int,name varchar2(1000));create table t2(id int,name varchar2(1000));create index ind_t1 on t1(id);create index ind_t2 on t2(id);create index ind_t2_name on t2(name);insert into t1 select object_id,object_name from dba_objects;exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all indexed columns');select * from t1 where id in(select /*+ dynamic_sampling(t2 0) cardinality(t2 30000) +*/ id from t2 where name='AA');其中：cardinality(t2 15000)的作用是告诉CBO从t2表将获得15000条数据。dynamic_sampling(t2 0)的作用是禁止动态采样。通过这种方式，我们模拟子查询中返回的结果数，同时为了让CBO完全依赖这个信息生成执行计划，禁止子查询使用动态采样(dynamic_sampling设置为0)。可以看到，当CBO得到来自于子查询中返回的结果集（row source）的记录数为15000条时候，采用了hash join的执行计划，hash join通常适用于两张关联的表都比较大的时候。我们再把子查询的结果集变的很小，看下面的例子：select * from t1 where id in(select /*+ dynamic_sampling(t2 0) cardinality(t2 1) +*/ id from t2 where name ='AA');将子查询的返回值设置为1，即：cardinality(t2 1)此时CBO选择了两表通过 NESTED LOOPS join进行关联的执行计划，因为子查询只有1条记录，这时候CBO会选择最合适这种情况的nested loop join关联方式。从这里我们得到这样的一个结论：子查询的cardinality值直接影响了主查询的执行计划，如果CBO对子查询的cardinality判断有误，那么主查询的执行计划很有可能是错误的。在看两张表关联查询的情况，还是刚才的例子：select /*+ dynamic_sampling(t2 0) cardinality(t2 25000) +*/ * from t1,t2 where t1.id = t2.id;这个例子中，CBO认为T2关联的数据足够多，而T1又足够大，所以在这样的情况下，hash join是合适的。select /*+ dynamic_sampling(t2 0) cardinality(t2 1) +*/ * from t1,t2 where t1.id = t2.id;因为这个时候T2给CBO提供信息是，只有一条记录做关联查询，所以CBO选择了nested loop join。以上的例子主要说明cardinality对CBO生成执行计划的影响，∴在看多表查询时候，一定要注意每个操作cardinality值，如果这个数值明显不对，那么很可能操作的表的分析数据出了问题或者表没有分析。比如上面的例子，你确切的知道T2表的数据很小，而在执行计划显示却是10000条，这显然不对，这个时候就要去检查问题的所在，看看T2表是不是曾经有很多数据，删除之后没有做重做分析等。===================================SQL执行计划=====================================如果一条SQL的性能出了问题，首先应该看一下他的执行计划，以便于确定（或者是猜测）问题的所在，至少我已经养成了这种习惯。首先要做的事情就是读懂SQL的执行计划，因为目前Oracle 10g 已经非常流行了。生成SQL的执行计划是ORACLE对SQL做硬解析是的一个非常重要的步骤，它制定出一个 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 告诉ORACLE在执行这条SQL时候以什么样的方式访问数据：索引还是全表扫描，是hash join还是nested loop join 等，比如某条SQL通过使用索引的方式访问数据是最节省资源的，结果CBO做出的执行计划却是全表扫描，那么这条SQL的性能必然是较差的。其实我们在上面的例子已经简单的介绍了执行计划的一些信息，执行计划可以使用以下方式得到：1.explain plan for2.SQLPLUS 命令 set autotrace on （★ 重点掌握）补充说明：autotrace 有几个常用选项，简单说明一下：set autotrace off：不生成autotrace 报告 软件系统测试报告下载sgs报告如何下载关于路面塌陷情况报告535n,sgs报告怎么下载竣工报告下载 ，这是缺省模式。set autotrace on explain：autotrace只显示优化器执行路径报告。set autotrace on statistics：只显示执行统计信息。set autotrace on：包括执行计划和统计信息。set autotrace traceonly：和set autotrace on一样，但是不显 示查询输出。3.第三方软件提供的GUI工具，常见的是Toad，PL/SQL Developer第二种方式除了生成执行计划之外，还可以输入SQL消耗的各种资源统计，比如LIO(逻辑读)，PIO(物理读)等信息。比如：explain plan for select * from t1,t2 where t1.id = t2.id;select * from table(dbms_xplan.display);set autotrace trace exp stat;select * from t1,t2 where t1.id = t2.id;可以看到，set autotrace 的语法不但可以生成执行计划，还可以跟踪SQL，并获取SQL执行中各种资源的使用情况。上面两种方法都可以用来生成SQL的执行计划。在例如：alter session set "_optimizer_mjc_enabled" = false;create table t1(id int,name varchar2(1000));create table t2(id int,name varchar2(1000));create index ind_t1 on t1(id);create index ind_t2 on t2(id);insert into t1 select object_id,object_name from dba_objects;select t1.* from t1,t2 where t1.id = t2.id and t1.id=5 and t2.name ='A';这是一个10g的执行计划，9i的执行计划和这个执行计划略有不同，其中在9i里面，row列使用的是Card（cardinality）看执行计划时候，首先从缩进最大的行数去读，他是最先执行的步骤。上面那句SQL的执行计划中：ID=3和ID=4最先被执行的，即：|* 3 | TABLE ACCESS FULL | T2 | 1 | 515 | 2 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IND_T1 | 1 | | 1 (0)| 00:00:01 |当这两行缩进一样的时候，最上面的最先执行，在这里就是：|* 3 | TABLE ACCESS FULL | T2 | 1 | 515 | 2 (0)| 00:00:01 |然后是第二个被执行的：| 2 | NESTED LOOPS | | 1 | 1030 | 4 (0)| 00:00:01 |然后是：| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 515 | 2 (0)| 00:00:01 |最后是：| 0 | SELECT STATEMENT | | 1 | 1030 | 4 (0)| 00:00:01 |这就是整条SQL的执行过程。然后翻译一下大概意思如下：从T2表第一行读取，查看每一行是否符合下面的条件T2.id =5 and t2.name = 'A';如果符合就拿出一行来，然后到索引IND_T1中找到ID=5的值，然后重复，直到把整个T2表都扫描结束，这个过程就是叫做 nested loops。当整个T2表被扫描完以后，会产生一个结果集，这个结果集是IND_T1的一个索引集，然后Oracle根据索引键值上的rowid去T1表中找到相应的记录，就是这里：Operation： TABLE ACCESS BY INDEX ROWID然后将结果返回回来：Operation：SELECT STATEMENT这就是执行计划告诉我们的关于这条SQL执行的全过程。执行计划还有一列值，含义如下：ID列：是一个序号，他的大小并不是执行的先后 顺序，其实在这里执行的先后顺序是：ID=3->4->2->1->0Operation列：是当前操作的内容。ROW列：就是当前操作cardinality，Oracle估算当前操作的返回结果集。Cost（CPU）：Oracle计算出来的一个数值（代价），用于说明SQL执行的代价。Time列：Oracle估算的当前操作的时间。当然还有非常重要的一些信息：Predicate Information (identified by operation id):---------------------------------------------------3 - filter("T2"."NAME"='A' AND "T2"."ID"=5)4 - access("T1"."ID"=5)这一段用来说明谓词信息和数据获取的方式，他的意思是，在第一步（ID=3）对表T2做全表扫描的时候，使用了下面的过滤条件3 - filter("T2"."NAME"='A' AND "T2"."ID"=5)意思就是说，在ID=3这一步中，对于全表访问T2上的每一行，只要同时符合这个条件，才能够拿出来和IND_T1索引上的项做nested loop 关联查询。这一项：4 - access("T1"."ID"=5)意思是，在执行计划ID=4这一步中，通过访问索引寻找ID=5的数据，来和T2上的数据做关联，而不是访问原表T1。这里可能会弄不清楚fiter和access的区别，因为看起来他们都按照谓词的条件对数据进行过滤。在这里，执行计划如果显示是access，就表示这个谓词条件的值将会影响数据的访问路径（表或者是索引，在这里是索引），在这里它说明是通过访问索引的方式和T2表做关联查询的，而filter表示谓词条件的值并不会影响数据访问路径，只能起到过滤的作用。弄清楚access和filter的区别，有助于我们更好地理解Oracle的执行计划。比如：create table t(x int,y int);set autotrace trace exp;select /*+ rule +*/ * from t where x = 5;现在使用RBO是为能让实验思路更加清晰：在上面的例子中，因为表T没有创建索引，执行计划没有选择数据访问路径的余地，谓词条件在这里只是起到了数据过滤的作用，所以使用了：1 - filter("X"=5)再比如：create index t_idx on t(x,y);select /*+ rule +*/ * from t where x = 5;这次谓词条件将影响到数据访问的路径 —— 选择索引，所以用access1 - access("X"=5)然后接着看：drop table t;create table t(x int, y int); select /*+ rule +*/ * from t where x = 5 and y =10;因为数据访问的方式没有选择的余地，只能是全表扫描（T表上没有索引），所以谓词中：x = 5 and y =10;仅仅起到了过滤的作用。1 - filter("Y"=10 AND "X"=5)然后在上面建好索引在看看：create index t_idx on t(x);sel ect /*+ rule +*/ * from t where x = 5 and y =10;因为在X列上有索引，X的值将会影响到数据的访问路径，所以使用了access，而y列上没有索引，y的值不会对执行计划中的数据访问路径产生影响，所以是filter：1 - filter("Y"=10)2 - access("X"=5)接着看下面的例子：drop index t_idx; create index t_idx on t(x,y);select /*+ rule +*/ * from t where x = 5 and y =10;谓词中x,y的值都可能影响到执行计划的路径选择，所以都是用了access。1 - access("X"=5 AND "Y"=10)小 总结 初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf ：上面介绍了关于access和filter的区别。当我们在看执行计划时，要注意谓词，如果是access的，就要考虑对于谓词的条件，使用的访问路径是否正确。然后执行计划最下面的一步是：Note------ dynamic sampling used for this statement这一步提示用户CBO当前使用的技术，需要用户在分析执行计划时候考虑到这些因素，比如现在提示注意的信息时，当前表使用了动态采样，通过这样的提示，我们就可以知道这个表可能没有做个分析。比如：create table t(x int);set autotrace trace exp;select * from t;exec dbms_stats.gather_table_stats(user,'t');select * from t;第一次执行SQL时，CBO发现表没有做分析，于是使用了动态采样的方式来获取表数据信息，然后对表进行了分析。SQL在第二次执行的时候，CBO发现表已经被分析过，于是就不会在使用动态采样，而是直接使用分析数据。这里就会出现这样两种情况：1.如果表没有分析，那么CBO可以通过动态采样的方式来获得分析数据，也可以获得正确的执行计划；2.如果表分析过，但是分析信息过旧，这时候CBO就不会在使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划。比如：create table t as select 1 id,object_name name from dba_objects;update t set id=99 where rownum=1;commit;create index t_ind on t(id);select id,count(*) from t group by id;set autotrace trace exp;select * from t where id=1;在没有统计信息时，CBO使用使用动态采样的方式获得了正确的分析信息，并做出了正确的执行计划。exec dbms_stats.gather_table_stats(user,'t',cascade=>true);select * from t where id = 1;有了分析数据之后，CBO可以根据分析数据做出正确的执行计划。set autotrace offupdate t set id = 99 where id = 1;update t set id = 1 where rownum=1;commit;select id,count(*) from t group by id;注意了！这个时候，表的数据已经更改，分析数据已经过时了······set autotrace trace exp;select * from t where id = 1;CBO看到的依旧是从前分析的数