思维导图

Oracle优化05-执行计划_Explain-Pl


系列文章

​Oracle-SQL Explain Plan解读​

概述

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

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

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

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


Cardinality基数

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

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

比如:

Oracle优化05-执行计划_优化_02


当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));

Oracle优化05-执行计划_Explain-Pl_03

说明:

  • /+ 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));

Oracle优化05-执行计划_Explain-Pl_04

通过动态采样(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));

Oracle优化05-执行计划_执行计划_05

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

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

我们更新下数据

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

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

Oracle优化05-执行计划_执行计划_05

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));

Oracle优化05-执行计划_执行计划_07

重新对表分析后,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));

Oracle优化05-执行计划_sql_08

解析:

我们发出的子查询的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
------ ----------

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

Oracle优化05-执行计划_Explain-Pl_09

我们在查询中将子查询的返回值设置为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;

....省略输出

查看执行计划

Oracle优化05-执行计划_优化_10

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

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

....省略输出

查看执行计划:

Oracle优化05-执行计划_优化_11

这时候因为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​​如下的执行计划:

Oracle优化05-执行计划_优化_12

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


如何阅读呢?

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

ID=3和ID=4是最先被执行的。

Oracle优化05-执行计划_Explain-Pl_13

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

然后是缩进次之的,

这里就是

Oracle优化05-执行计划_Explain-Pl_14

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

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

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

Oracle优化05-执行计划_执行计划_15

这就是这个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,表示谓词条件的值并不会影响数据的访问路径,只起到过滤的作用。


执行计划的最后一步是

Oracle优化05-执行计划_优化_16

这一步提示用户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不会再使用动态采样,而是使用这些旧的分析数据,有可能导致错误的执行信息

总结

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