今天,我来给各位做一个详细的一个讲解----看懂执行计划
首先,我们应该知道,对于看懂一个执行计划,对我们的SQL优化,有很大的帮助。
首先,我们应该知道这两个概念,一个是RBO,一个是CBO。RBO,是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
-----
--注:这里,我们没有对这个表 T进行统计分析,这个是 Oracle自动通过动态采样的方式收集分析数据), CBO估算的记录有 56425( Card),但是,这个也是比较接近表 T的实际统计数据, 50320, CBO走的是全表扫遍,这也是比较正确的。
当我们 统计一下。
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就不会通过动态采集方式,而是直接使用旧的分析数据,从而导致错误的执行计划。
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
这,我详细的讲一下 Card对 CBO 的影响。
这里,我举一下,嵌套查询和关联查询。
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.
这里,我开始对 T2的 card进行伪造。
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
Cardinality( t21000)的作用是告诉 CBO从 T2表获取数据到 1000行
dynamic_samping(t20) 的作用是禁止动态采集数据。
通过这种方法,我们就模拟了子查询的返回结果树,同时为了让 CBO完全依赖与这个信息生成实行计划,禁止了子查询的动态采集
这里, T2的数据过大,所以,采集了 HASHUNIQUE。
这里, 我们再把 Cardinality设置为( T2, 1)。看看效果。
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是正确的。
我又举个 Cardinality( T2 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
-----
我们这样看。最先缩进的,就是先执行的语句,语句在前,最先执行。故,应该是
“|*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=5和 T2.name='A'这个条件的记录读取出来,然后去找到索引 IND_T1 =5的值,知道把 T2读完。之后,根据索引的键值去 T1表,找到相应的数据,然后,输出出来。
对于后面有谓词的参数, filter, access这两个关键字
这样解释:如果执行计划显示的是 access,就表示这个谓词条件的值将会影响数据的访问路径(表还是索引,在这里,是索引),在这里它说明是通过访问索引的方式和 T2表做关联查询,而 filter表示谓词条件的值并不会影响数据访问的路径,而只是起到过滤的作用。
参照:谭怀远先生所著的《让Oracle跑得更快》
仅作为一个学习文档,有部分错误,请高手指点。请勿喷水,谢谢。
首先,我们应该知道,对于看懂一个执行计划,对我们的SQL优化,有很大的帮助。
首先,我们应该知道这两个概念,一个是RBO,一个是CBO。RBO,是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估算的记录有 56425( Card),但是,这个也是比较接近表 T的实际统计数据, 50320, CBO走的是全表扫遍,这也是比较正确的。
当我们 统计一下。
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就不会通过动态采集方式,而是直接使用旧的分析数据,从而导致错误的执行计划。
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
这,我详细的讲一下 Card对 CBO 的影响。
这里,我举一下,嵌套查询和关联查询。
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.
这里,我开始对 T2的 card进行伪造。
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
Cardinality( t21000)的作用是告诉 CBO从 T2表获取数据到 1000行
dynamic_samping(t20) 的作用是禁止动态采集数据。
通过这种方法,我们就模拟了子查询的返回结果树,同时为了让 CBO完全依赖与这个信息生成实行计划,禁止了子查询的动态采集
这里, T2的数据过大,所以,采集了 HASHUNIQUE。
这里, 我们再把 Cardinality设置为( T2, 1)。看看效果。
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是正确的。
我又举个 Cardinality( T2 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=5和 T2.name='A'这个条件的记录读取出来,然后去找到索引 IND_T1 =5的值,知道把 T2读完。之后,根据索引的键值去 T1表,找到相应的数据,然后,输出出来。
对于后面有谓词的参数, filter, access这两个关键字
这样解释:如果执行计划显示的是 access,就表示这个谓词条件的值将会影响数据的访问路径(表还是索引,在这里,是索引),在这里它说明是通过访问索引的方式和 T2表做关联查询,而 filter表示谓词条件的值并不会影响数据访问的路径,而只是起到过滤的作用。
参照:谭怀远先生所著的《让Oracle跑得更快》
仅作为一个学习文档,有部分错误,请高手指点。请勿喷水,谢谢。