思维导图

系列文章

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的执行计划

执行计划中的值说明

总结

热门文章

最新文章

相关电子书

推荐镜像