思维导图

系列文章

Oracle-SQL Explain Plan解读

概述

如果要分析某条SQL的性能问题，通常来讲，我们首先要看SQL的执行计划，看看SQL的每一步执行计划是否存在问题。

如果某一条SQL平常执行的都很好，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本上可以判断是执行计划出现了问题。

看懂执行计划变成了SQL优化（其实在大多数的情况下，SQL优化指的是SQL的性能问题定位）的先决条件。

在讨论SQL执行计划之前，我们需要知道执行计划当中一个非常重要的概念–Cardinality基数。

Cardinality基数

在我们看执行计划的每一步操作的时候，当前操作的Cardinality值表示CBO预期从一个行源（row source）返回的记录数。

一个行源可能是一个表、一个索引、也可能是一个子查询。

比如：

当CBO无法准确的获取到Cardinality时，将会发生什么？

在执行计划中, card 就是Cardinality的缩写，它表示CBO估算当前操作预期获取的记录数。

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

下面演示下当CBO无法准确的获取到Cardinality时，将会发生什么？

创建一个数据分布非常不均匀的表T

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as xxx@xgj
SQL> drop table t;
Table dropped
SQL> create table t as select 1 id , object_name from dba_objects;
Table created
SQL> update t set t.id=99 where rownum=1;
1 row updated
SQL> commit;
Commit complete
SQL> create index t_ind on t(id);
Index created
SQL> select id ,count(*) from t group by id ;
        ID   COUNT(*)
---------- ----------
         1      35251
        99          1

查看执行计划

##执行SQL
SQL> select /*+  dynamic_sampling(t 0)  */  * from t where id=1;
SQL> SELECT sql_id, child_number, a.* from v$sql a  where a.SQL_TEXT  like '%select /*+  dynamic_sampling(t 0)  */  * from t where id=1%';
SQL> select * from table(DBMS_XPLAN.display_cursor('bb22rwn4604yj',0));

说明：

/+ dynamic_sampling(t 0) / ：目的是让CBO无法通过动态采样获取表中实际数据的情况，此时CBO只能根据数据字典中标T的非常有限的信息（比如表的extends数量，数据块的数量）来猜测表中的数据

从结果中可以看出，CBO猜测出id=1的数据为118条，而与实际上的数量 35251，相差甚远，所以CBO选择了索引而不是全表扫描

让我们看下实际的执行情况：

select  * from t where id=1;
SELECT sql_id, child_number, a.* from v$sql a  where a.SQL_TEXT  like '%select  * from t where id=1%';
select * from table(DBMS_XPLAN.display_cursor('3k9f6qpq9sbmn',0));

通过动态采样（10g及以上版本，如果表没有做过分析，ORACLE会自动通过动态采样的方式来收集分析数据），CBO估算出来的表中数量为39257 （Cardinality）和实际的数量 35251非常接近，CBO判断ID=1的数据基本上等同于表中的数据，所以选择了全表扫描。

下面我们做一下表和索引分析

##先查看下
SQL>   select num_rows, avg_row_len, blocks, last_analyzed  from user_tables  where table_name = 'T';
  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
    BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------
##对表和索引做分析
SQL>  exec   dbms_stats.gather_table_stats(user,'t',cascade => true);
PL/SQL procedure successfully completed
##重新查询信息 
SQL> select num_rows, avg_row_len, blocks, last_analyzed  from user_tables  where table_name = 'T';
  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------
     35252          22        144 2016-12-30 0:
SQL>  select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
    BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------
         1          69             2 2016-12-30 0:
SQL>

查看执行计划

SQL> select  *  from t a  where a.id=99;
SQL> SELECT sql_id, child_number, a.* from v$sql a  where a.SQL_TEXT  like '%select  *  from t a  where a.id=99%';
SQL> select * from table(DBMS_XPLAN.display_cursor('fa0r3kc8y5239',0));

通过对表的分析，CBO就可以获取到T表和索引的充足的信息。

上面的截图，CBO从分析数据中,获取到了id=99的数据6 rows , 所以选择了索引。

我们更新下数据

SQL>update t set id=99;
SQL>commit;

将id 全部更新为99 ，因为没有对表进行分析，所以CBO知道的信息还是旧的，重新查询下我们可以看到 CBO依然认为表T中的数据很少，依然选择的是索引。

SQL> exec   dbms_stats.gather_table_stats(user,'t',cascade => true);
PL/SQL procedure successfully completed

## 如果使用 DBMS_XPLAN.display_cursor 查询执行计划的话，需要清空shared_pool ,或者换个SQL（目的是不匹配到shared_pool中的缓存）
SQL> alter system flush shared_pool;
System altered
##重新执行SQL
SQL> select  *  from t a  where a.id=99;
##获取SQL_ID等
SQL> SELECT sql_id, child_number, a.* from v$sql a  where a.SQL_TEXT  like '%select  *  from t a  where a.id=99%';
##查看执行计划
SQL> select * from table(DBMS_XPLAN.display_cursor('fa0r3kc8y5239',0));

重新对表分析后，CBO获取了正确的Cardinality值。T表中id=99的数据3w+,所以全表扫描是最佳执行计划。

Cardinality是如何影响多表查询的？

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

对于多表查询，CBO使用每个关联表返回的行数（Cardinality）决定使用设么样的方式来做表关联（比如Nested loops ,sm 或者 hash join）,

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

举例说明：

数据如下：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as xxx@xgj
SQL> create table t1 (id  int , name varchar2(1000));
Table created
SQL> create table t2 (id  int , name varchar2(1000));
Table created
SQL> create index  ind_t1 on t1(id);
Index created
SQL> create index  ind_t2 on t2(id);
Index created
SQL> create index ind_t2_name on t2(name);
Index created
SQL> insert into t1 select object_id ,object_name from dba_objects ;
35258 rows inserted
SQL> commit;
Commit complete
SQL> insert into t2 values(1,'XGJ');
1 row inserted
SQL> commit;
Commit complete
##仅对t1的表和索引进行分析
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade => true,method_opt => 'for all indexed columns');
PL/SQL procedure successfully completed
SQL> select * from t1 where id in (select /*+  dynamic_sampling(t2 0)  cardinality(t2 10000) */   id  from t2  where name = 'XGJ');
  ID            NAME
------------- ----------------------
SQL>select * from v$sql a where a.SQL_TEXT 
like '% /*+  dynamic_sampling(t2 0)  cardinality(t2 10000) */  %';
##DBMS_XPLAN.display_cursor中查看执行计划
SQL>select * from table(dbms_xplan.display_cursor('2c7hx20a3bhxx',0));

解析：

我们发出的子查询的SQL，同时使用了hint

/*+  dynamic_sampling(t2 0)  cardinality(t2 10000) */

dynamic_sampling(t2 0)：禁止动态采用
cardinality(t2 10000)：告诉CBO从T2表中取10000条记录

通过这种方式，我们模拟子查询中返回的结果数，同时为了让CBO完全依赖这个信息生成执行计划，我们禁止了子查询使用动态采样（dynamic_sampleing 设置为0）。

可以看到，当CBO得到来自于子查询的结果集（Rows ）的记录为10000条时，采用了hash join semi的执行计划，hash join通常适用于两张关联的表都比较大的时候

如果我们把子查询的结果集变得很小会怎样呢？

来看我们下面的栗子：

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

同样的我们来看下执行计划：

我们在查询中将子查询的返回值设置为1，即

Cardinality（t2,1）

此时CBO选择了两个表通过nested loop join 进行关联的执行计划，因为子查询只有1条记录，这个时候CBO会选择最适合择偶张情况的netsted loops join关联方式。

从这个试验中我们可以得到如下结论：

子查询的Cardinality的值，直接影响了主查询的执行计划，如果CBO对子查询的Cardinality判断有误，那么饿主查询的执行计划很有可能是错误的。

再看量表关联的查询情况，还是用上面的栗子

##上面执行后 user_index有值了...清掉先
select num_rows, avg_row_len, blocks, last_analyzed  from user_tables  where table_name = 'T2';
select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T2';
SQL> analyze table t2 delete statistic
  2  ;
Table analyzed
SQL> alter system flush shared_pool;
System altered
SQL> select /*+  dynamic_sampling(t2 0)  cardinality(t2 10000) */  * from t1,t2 where t1.id=t2.id;
....省略输出

查看执行计划

这个栗子中，CBO认为T2关联的数据足够多，而且T1又足够大，所以在这种情况下，hash join 是最合适的。

SQL>select /*+  dynamic_sampling(t2 0)  cardinality(t2  1) */  * from t1,t2 where t1.id=t2.id;
....省略输出

查看执行计划：

这时候因为T2给CBO提供的信息，只有1条记录做关联查询，所以CBO选择了nested loop join .

总结

以上的例子主要说明Cardinality对CBO生成执行计划的影响，所以我们在看多表查询的时候，一定要注意每个操作返回的Cardinality的值，如果这个值明显的不对，那么很有可能操作的表的分析数据出了问题，或者没有分析。

比如在上面的栗子中，我们确切的知道T2表的数据很小，而在执行计划中却显示10000条，这显然不对了，这个时候叫就要检查问题所在，看看T2表是不是曾经有很多数据，删除之后没有做重新分析等等。

SQL的执行计划

如果一条SQL的性能出现了问题，首先应该看一下它的执行计划，以便确定（或者猜测）问题的所在。

生成SQL的执行计划时Oracle在对SQL做硬分析时的一个非常重要的步骤，它制定出一个方案告诉Oracle在执行这条SQL时以什么样的方式访问数据：索引扫描？全表扫描？，是hash join 还是 netsted loops join 等。

如何得到一个SQL的执行计划

explain plan for
SQLPLUS命令 set autotrace on
第三方软件提供的GUI工具，比如Toad ,PL/SQL Developer

具体看： Oracle-SQL Explain Plan解读

如何看懂一个SQL的执行计划

首先得到一个SQL的执行计划

我们使用select * from table(DBMS_XPLAN.display_cursor(.......))的方式获取select * from t1 ,t2 where t1.id = t2.id如下的执行计划：

其中，rows列就是我们上面说到的 Card(Cardinality) 9i以前的版本使用的是Card.

如何阅读呢？

我们首先从说缩进度最大的行读取，它是最先被执行的步骤

ID=3和ID=4是最先被执行的当两行缩进一样时，最上面的最先被执行，在这里就是 ID=3的先执行，然后是ID=4的。

然后是缩进次之的，

这里就是

紧接着锁进再次之的，以此类推

在这里就是是ID=1的，最后是ID=0的。

我们也可在PL/SQL中F5查看执行计划后，一步步的跟踪

这就是这个SQL的执行过程。

大致意思是：

从T2表读取第一行数据是否符合条件

如果符合就拿出一行来，然后到索引IND_T1 中找到对应的值，然后重复，直到把整个T2表全表扫描完，这个过程就叫NESTED LOOPS .

当T2表被扫描完之后，会产生一个结果集，这个结果集是 IND_T1的一个索引集，然后ORACLE根据说印键值上的rowid 去T1表找对应的记录，就是这一步： Operation TABLE ACCESS BY INDEX ROWID

然后还有个NESTED LOOPS .（疑惑待思考）

最后将结果返回： Operation SELECT STATEMENT

执行计划中的值说明

ID列：是一个序号，注意，它的大小并不是执行的先后顺序。
Operation列：是当前的操作内容。
Rows 列：就是当前操作的cardinality,Oracle估算当前操作的返回结果集
Cost(cpu): Oracle计算出来的一个数值（代价），用于说明SQL执行的代价
Time列： Oracle估算当前操作的时间。

还有些重要信息。比如

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."ID"="T2"."ID")

这一段是来说明谓词信息和数据获取的方式，它的意思在ID=4的那一列，通过访问索引寻找数据，而不是访问原表数据。

还有个常见的方式 filter ，我们这里来解释下access和filter的区别

如果执行计划显示access, 就表示这个谓词条件的值将会影响数据的访问路径（全表还是索引，这里是索引）

如果执行计划显示filter,表示谓词条件的值并不会影响数据的访问路径，只起到过滤的作用。

执行计划的最后一步是

这一步提示用户CBO当前使用的技术，需要用户咋分析执行计划时考虑到这些因素，比如现在提示这些信息时，当前表使用了动态采样，通过这个提示，我们就知道这个表可能没有做过分析。

举例：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as xxx@xgj
SQL> create table t(x int);
Table created
SQL>  begin
   for i in 1 .. 1000 loop
     insert into t values (i);
   end loop;
   commit;
 end;
 /
SQL> select a.SQL_ID ,a.CHILD_NUMBER from v$sql a where a.SQL_TEXT like 'select * from t';
SQL_ID CHILD_NUMBER
------------- ------------
89km4qj1thh13            0
SQL> select * from table(dbms_xplan.display_cursor('89km4qj1thh13',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  89km4qj1thh13, child number 0
-------------------------------------
select * from t
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
17 rows selected
SQL>

我们手工执行进行表分析后，重新查看下

##表分析
SQL> exec dbms_stats.gather_table_stats(user,'t');
PL/SQL procedure successfully completed
##因为是从shared_pool中加载数据，如果不清空，会影响执行计划
SQL> alter system flush shared_pool;
System altered

SQL>select * from t;
....
SQL>select a.SQL_ID ,a.CHILD_NUMBER,a.SQL_TEXT from v$sql a where a.SQL_TEXT like 'select * from t%';
....
SQL> select * from table(dbms_xplan.display_cursor('ckzaraqcmkr2f',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  ckzaraqcmkr2f, child number 0
-------------------------------------
select * from t
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| T    |  1000 |  4000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
13 rows selected
SQL>

执行计划中已经没有动态采样的提示信息了。

第一次执行SQL，CBO发现表没有做表分析，于是使用动态采样的方式获取数据信息。

SQL第二次执行时，CBO发现表已经分析过了，于是就不会再使用动态分析，而是直接使用分析数据。

这里会出现两种情况：

如果没做表分析，CBO可以通过动态采样的方式来分析数据，也可以获取到正确的执行计划

如果分析过，但是分析信息过旧，这时候CBO不会再使用动态采样，而是使用这些旧的分析数据，有可能导致错误的执行信息

总结

以上我们阐述了执行计划输出的全部内容。当我们在看执行计划时，不能只看执行计划的本身，还要看下面的谓词和提示信息，这都非常有帮助。

Oracle优化05-执行计划

思维导图

系列文章

概述

Cardinality基数

当CBO无法准确的获取到Cardinality时，将会发生什么？

Cardinality是如何影响多表查询的？

总结

SQL的执行计划

如何得到一个SQL的执行计划

如何看懂一个SQL的执行计划

执行计划中的值说明

总结

热门文章

最新文章

相关电子书

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Oracle优化05-执行计划

思维导图

系列文章

概述

Cardinality基数

当CBO无法准确的获取到Cardinality时，将会发生什么？

Cardinality是如何影响多表查询的？

总结

SQL的执行计划

如何得到一个SQL的执行计划

如何看懂一个SQL的执行计划

执行计划中的值说明

总结

热门文章

最新文章

相关电子书

推荐镜像