谈谈‘执行计划“那些事

今天,我来给各位做一个详细的一个讲解----看懂执行计划


首先,我们应该知道,对于看懂一个执行计划,对我们的SQL优化,有很大的帮助。


首先,我们应该知道这两个概念,一个是RBO,一个是CBORBO,是Oracle8i之前使用的,一种基于规则的一种优化器。这种规则,太过死板,在Oracle10g 以后的版本就已经彻底“抛弃“了。
CBO,基于成本的优化器。它的思路是让Oracle获取所有的执行计划,通过对执行计划的分析,获取一个Cost最少的一个,然后,产生一个最终的执行计划。


对于CBO来讲,最重要的参数则是Cardinality .如果,CBO获取的Cardinality不够准确(或者是过期了),都可能导致制定错误的CBO执行计划。


这里,这展示的是,CBO无法获得准确的Cardinality 值时,将会发生什么。




SQL>create table t as select 1 id,object_name from dba_objects;


Tablecreated.


SQL>update t set id=99 where rownum=1;


1row updated.


SQL>commit;


Commitcomplete.


SQL>create index t_ind on t (id);


Indexcreated.


SQL>select count(*) from t;


  COUNT(*)
----------
     50320


SQL>set autotrace trace exp;
SQL>select * from t where id=1;


50319rows selected.




ExecutionPlan
----------------------------------------------------------
Planhash value: 1601196873


--------------------------------------------------------------------------
|Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------
|0 | SELECT STATEMENT  |      | 56425 |  4353K|    53   (4)| 00:00:01|
|*1 |  TABLE ACCESS FULL| T    | 56425 |  4353K|    53   (4)| 00:00:01|
--------------------------------------------------------------------------


PredicateInformation (identified by operation id):
---------------------------------------------------


   1- filter("ID"=1)


Note
-----

  • dynamicsampling used for this statement



--注:这里,我们没有对这个表 T进行统计分析,这个是 Oracle自动通过动态采样的方式收集分析数据), CBO估算的记录有 56425Card),但是,这个也是比较接近表 T的实际统计数据, 50320CBO走的是全表扫遍,这也是比较正确的。












当我们 统计一下。
SQL>SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);


PL/SQLprocedure successfully completed.


SQL>select * from t where id=1;


50319rows selected.




ExecutionPlan
----------------------------------------------------------
Planhash value: 1601196873


--------------------------------------------------------------------------
|Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------
|0 | SELECT STATEMENT  |      | 50311 |  1326K|    52   (2)| 00:00:01|
|*1 |  TABLE ACCESS FULL| T    | 50311 |  1326K|    52   (2)| 00:00:01|
--------------------------------------------------------------------------


PredicateInformation (identified by operation id):
---------------------------------------------------


    1- filter("ID"=1)










这里,就是比较准确数据了。 CBO选择的全表扫描很是正确。我们这样看一下,当 id=99.CBO的选择。
SQL>select * from t where id=99;




ExecutionPlan
----------------------------------------------------------
Planhash value: 1376202287


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


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


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


|0 | SELECT STATEMENT            |       |     1 |    27 |     2(0)| 00:00
:01|


|1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    27 |     2(0)| 00:00
:01|


|*2 |   INDEX RANGE SCAN          | T_IND |     1 |       |     1(0)| 00:00
:01|


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




PredicateInformation (identified by operation id):
---------------------------------------------------


    2- access("ID"=99)






事实上,当我们统计表的时候, CBO可以不但获得 T表的信息,也可以统计出来索引的信息。所以,当我们执行‘ whereid = 99’是, CBO,很明确的执行了索引浏览。




下面,我来显示一下第二种,当不即时统计数据(数据过旧), CBO的错误执行计划。


SQL>update t set id=99;


50320rows updated.




SQL>commit;


Commitcomplete.


SQL>select * from t where id=99;


50320rows selected.




ExecutionPlan
----------------------------------------------------------
Planhash value: 1376202287


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


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


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


|0 | SELECT STATEMENT            |       |     1 |    27 |     2(0)| 00:00
:01|


|1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    27 |     2(0)| 00:00
:01|


|*2 |   INDEX RANGE SCAN          | T_IND |     1 |       |     1(0)| 00:00
:01|


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




PredicateInformation (identified by operation id):
---------------------------------------------------


    2- access("ID"=99)


这里,我们能够清楚的得到这样的数据,我们没有即时的统计数据。 CBO依然从上一个执行计划中获得,导致,我们已经更新了所有记录,全表已经都是 id=99的记录了,但, CBO,一直认为,全表也还是只有一条 id=99的记录,选择了走索引扫描的错误信息。


SQL>set autotrace trace exp;
SQL>exec dbms_stats.gather_table_stats(user,'T',cascade=>true);


PL/SQLprocedure successfully completed.


SQL>select  *from t where id=99;


ExecutionPlan
----------------------------------------------------------
Planhash value: 1601196873


--------------------------------------------------------------------------
|Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------
|0 | SELECT STATEMENT  |      | 50315 |  1326K|    52   (2)| 00:00:01|
|*1 |  TABLE ACCESS FULL| T    | 50315 |  1326K|    52   (2)| 00:00:01|
--------------------------------------------------------------------------


PredicateInformation (identified by operation id):
---------------------------------------------------


    1– filter("ID"=99)


这样,我们统计一下数据, CBO就能清楚的得到执行计划。


总结:当第一次执行 SQL时, CBO发现没有做分析,所以,使用了动态采集的方式来估算数据信息,然后,对表做了分析。 SQL在执行第二次执行时, CBO,发现表已经分析过了,就不用使用动态采集了,而是直接分析数据。


这里,就会出现两种情况:
1.如果表没有做过分析,那么 CBO可以通过动态采集的方式来获得分析数据,也可以获得正确的执行计划;
2.如果表被分析过,但是分析数据信息过久,这时候, CBO就不会通过动态采集方式,而是直接使用旧的分析数据,从而导致错误的执行计划。




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

这,我详细的讲一下 CardCBO 的影响。
这里,我举一下,嵌套查询和关联查询。


SQL>create table t1 (id int,name varchar2(100));


Tablecreated.


SQL>create table t2 (id int,name varchar2(100));


Tablecreated.


SQL>create index ind_t1 on t1(id);


Indexcreated.


SQL>create index ind_t2 on t2(id);


Indexcreated.


SQL>create index ind_t2_name on t2(name);


Indexcreated.


SQL>insert into t1 select object_id ,object_name from dba_objects;


50321rows created.




SQL>execdbms_stats.gather_table_stats(user,'T1',cascade=>true,method_opt=>'forall indexed columns');


PL/SQLprocedure successfully completed.


这里,我开始对 T2card进行伪造。


SQL>select  * from t1 where  id in  (select /*+ dynamic_sampling(t2 0)cardinality(t2 10000) */ id from t2 where name ='AA');


ExecutionPlan
----------------------------------------------------------
Planhash value: 2727019379


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


|Id  | Operation                       | Name        | Rows  | Bytes |Cost (%C
PU)|Time     |


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


|0 | SELECT STATEMENT                |             |     1 |    41 |54
(4)|00:00:01 |


|1 |  TABLE ACCESS BY INDEX ROWID    | T1          |     1 |    28 |2
(0)|00:00:01 |


|2 |   NESTED LOOPS                  |             |     1 |    41 |54
(4)|00:00:01 |


|3 |    VIEW                         | VW_NSO_1    | 10000 |   126K|1
(0)|00:00:01 |


|4 |     HASH UNIQUE                 |             |     1 |   634K|
    ||


|5 |      TABLE ACCESS BY INDEX ROWID| T2          | 10000 |   634K|1
(0)|00:00:01 |


|*6 |       INDEX RANGE SCAN          | IND_T2_NAME |     1 |       |1
(0)|00:00:01 |


|*7 |    INDEX RANGE SCAN             | IND_T1      |     1 |       |1
(0)|00:00:01 |


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




PredicateInformation (identified by operation id):
---------------------------------------------------


    6- access("NAME"='AA')
    7- access("ID"="$nso_col_1")
我们发出带有子查询的 SQL,同时使用 HINT


Cardinalityt21000)的作用是告诉 CBOT2表获取数据到 1000
dynamic_samping(t20) 的作用是禁止动态采集数据。


通过这种方法,我们就模拟了子查询的返回结果树,同时为了让 CBO完全依赖与这个信息生成实行计划,禁止了子查询的动态采集


这里, T2的数据过大,所以,采集了 HASHUNIQUE




这里, 我们再把 Cardinality设置为( T21)。看看效果。




SQL>select  * from t1 where  id in  (select /*+ dynamic_sampling(t2 0)cardinality(t2 1) */ id from t2 where name ='AA');


ExecutionPlan
----------------------------------------------------------
Planhash value: 2727019379


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


|Id  | Operation                       | Name        | Rows  | Bytes |Cost (%C
PU)|Time     |


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


|0 | SELECT STATEMENT                |             |     1 |    41 |4  (
25)|00:00:01 |


|1 |  TABLE ACCESS BY INDEX ROWID    | T1          |     1 |    28 |2
(0)|00:00:01 |


|2 |   NESTED LOOPS                  |             |     1 |    41 |4  (
25)|00:00:01 |


|3 |    VIEW                         | VW_NSO_1    |     1 |    13 |1
(0)|00:00:01 |


|4 |     HASH UNIQUE                 |             |     1 |    65 |
    ||


|5 |      TABLE ACCESS BY INDEX ROWID| T2          |     1 |    65 |1
(0)|00:00:01 |


|*6 |       INDEX RANGE SCAN          | IND_T2_NAME |     1 |       |1
(0)|00:00:01 |


|*7 |    INDEX RANGE SCAN             | IND_T1      |     1 |       |1
(0)|00:00:01 |


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




PredicateInformation (identified by operation id):
---------------------------------------------------


    6- access("NAME"='AA')
    7- access("ID"="$nso_col_1")






Cardinality的数值对于 CBO的选择无效?)


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

我们看一下两个表关联查询的情况
SQL>select /*+ dynamic_sampling(t2 0) cardinality(t2 1000) */ * fromt1,t2 where t1.id=t2.id;


ExecutionPlan
----------------------------------------------------------
Planhash value: 2959412835


---------------------------------------------------------------------------
|Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------
|0 | SELECT STATEMENT   |      |  1000 | 93000 |    58   (4)| 00:00:01|
|*1 |  HASH JOIN         |      |  1000 | 93000 |    58   (4)| 00:00:01|
|2 |   TABLE ACCESS FULL| T2   |  1000 | 65000 |     2   (0)| 00:00:01|
|3 |   TABLE ACCESS FULL| T1   | 50321 |  1375K|    55   (2)| 00:00:01|
---------------------------------------------------------------------------


PredicateInformation (identified by operation id):
---------------------------------------------------


    1– access("T1"."ID"="T2"."ID")


这里例子, CBO认为 T2关联的数据足够多,而 T1又足够大,所以,在这样的情况下, HASHJOIN是正确的。




我又举个  CardinalityT2 1),大家再看看差别。




SQL>select /*+ dynamic_sampling(t2 0) cardinality(t2 1) */ * from t1,t2where t1.id=t2.id;


ExecutionPlan
----------------------------------------------------------
Planhash value: 828990364


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


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


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


|0 | SELECT STATEMENT            |        |     1 |    93 |     4(0)| 00:0
0:01|


|1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    28 |     2(0)| 00:0
0:01|


|2 |   NESTED LOOPS              |        |     1 |    93 |     4(0)| 00:0
0:01|


|3 |    TABLE ACCESS FULL        | T2     |     1 |    65 |     2(0)| 00:0
0:01|


|*4 |    INDEX RANGE SCAN         | IND_T1 |     1 |       |     1(0)| 00:0
0:01|


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




PredicateInformation (identified by operation id):
---------------------------------------------------


    4- access("T1"."ID"="T2"."ID")




T1足够大, T2足够小,而且 T2有索引,所以,选择 NESTEDLOOPS 是合适的。


所以,我们可以看到, Cardinality对与 CBO的执行结果是很重要的一个参数。


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



这里,我来教大家怎么看懂执行计划。


SQL>select t1.* from t1,t2 where t1.id=t2.id and t1.id=5 and t2.name='A';


ExecutionPlan
----------------------------------------------------------
Planhash value: 828990364


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


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


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


|0 | SELECT STATEMENT            |        |     1 |    93 |     4(0)| 00:0
0:01|


|1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    28 |     2(0)| 00:0
0:01|


|2 |   NESTED LOOPS              |        |     1 |    93 |     4(0)| 00:0
0:01|


|*3 |    TABLE ACCESS FULL        | T2     |     1 |    65 |     2(0)| 00:0
0:01|


|*4 |    INDEX RANGE SCAN         | IND_T1 |     1 |       |     1(0)| 00:0
0:01|


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




PredicateInformation (identified by operation id):
---------------------------------------------------


    3- filter("T2"."NAME"='A' AND "T2"."ID"=5)
    4- access("T1"."ID"=5)


Note
-----

  • dynamicsampling used for this statement





我们这样看。最先缩进的,就是先执行的语句,语句在前,最先执行。故,应该是
“|*3 |    TABLE ACCESS FULL       | T2     |     1 |    65 |     2(0)| 00:0

0:01|  ” 最先执行。
接着就是
|2 |   NESTED LOOPS              |        |     1 |    93 |     4(0)| 00:0
0:01|
其次,就是
|1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    28 |     2(0)| 00:0
0:01|


最后,再是


|0 | SELECT STATEMENT            |        |     1 |    93 |     4(0)| 00:0
0:01|


这就是整个的 SQL执行计划
ID=4—>ID=3—>ID=2—>ID=1—>ID=0


翻译下来,大概是如下。


对于 T2进行全表扫描,把符合 T2.id=5T2.name='A'这个条件的记录读取出来,然后去找到索引 IND_T1 =5的值,知道把 T2读完。之后,根据索引的键值去 T1表,找到相应的数据,然后,输出出来。


对于后面有谓词的参数, filteraccess这两个关键字
这样解释:如果执行计划显示的是 access,就表示这个谓词条件的值将会影响数据的访问路径(表还是索引,在这里,是索引),在这里它说明是通过访问索引的方式和 T2表做关联查询,而 filter表示谓词条件的值并不会影响数据访问的路径,而只是起到过滤的作用。




参照:谭怀远先生所著的《让Oracle跑得更快》


仅作为一个学习文档,有部分错误,请高手指点。请勿喷水,谢谢。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值