Oracle数据库SQL执行计划怎么查看？如何分析？

Oracle数据库SQL执行计划怎么查看？如何分析？ _ 潘子夜个人博客

数据库SQL执行的效率会直接影响整体应用的性能，在大数据量和复杂SQL语句的情况下，一定要查看数据库SQL的执行计划，并分析哪些语句耗费较多，值得优化！就Oracle数据库而言，下面潘老师来谈谈我们该如何去查询和分析执行计划？

一、什么是SQL执行计划

执行计划（explain plan）是指：一条查询语句在数据库中的执行过程或访问路径的描述。

二、Oracle数据库如何查看SQL执行计划

1）PLSQL工具查看方式
Oracle数据库查看SQL执行计划可以通过客户端工具快速实现，比如使用plsql直接快捷键F5或者点击菜单：工具->解释计划都可以查看对应的SQL的执行计划，效果类似如下：

另外PLSQL的默认显示首选项参数比较少，可以点击图中的首选项来配置：

2）Navicat For Oracle查看方式
Navicat 可以直接点击右上角的Explain就可以查看

3）SQL-PLUS可以使用如下sql语句进行查看

复制代码

1. # 解释计划
2. EXPLAIN PLAN FOR ELECT * FROM TEST_TABLE; --要解析的SQL脚本
3. # 查看解释计划
4. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

三、SQL执行计划常用概念解释

1、说明一下部分相关概念
相比较而言还是plsql较为好用，以plsql为例。
1）基数（Rows）：Oracle估计的当前操作的返回结果集行数
2）字节（Bytes）：执行该步骤后返回的字节数
3）耗费（Cost）、CPU耗费：Oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好（该值可能与实际有出入）
4）时间（Time）：Oracle估计的当前操作所需的时间
5）访问谓词：where后面的查询条件

2、执行顺序
在plsql中我们可以使用图中的箭头直接来调试执行顺序，非常方便，像Navicat没有就可以根据据Operation缩进来判断，缩进最多的最先执行（当缩进相同时，最上面的最先执行），同一级如果某个动作没有子ID就最先执行，同一级的动作执行时遵循最上最右先执行的原则。

3、一些动作的解释
如plsql那张图中类似INDEX RANGE SCAN 、TABLE ACCESS FULL等等，即描述的是该动作执行时表访问（或者说Oracle访问数据）的方式；

 

常见的几种表访问方式：

TABLE ACCESS FULL：全表扫描
TABLE ACCESS BY ROWID：通过ROWID的表存取
TABLE ACCESS BY INDEX SCAN：索引扫描

 

1）全表扫描：
Oracle会读取表中所有的行，并检查每一行是否满足SQL语句中的 Where 限制条件；全表扫描时可以使用多块读（即一次I/O读取多块数据块）操作，提升吞吐量；
使用建议：数据量太大的表不建议使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的 5% ~ 10% 或以上。

2）通过ROWID的表存取
ROWID是由Oracle自动加在表中每行最后的一列伪列，既然是伪列，就说明表中并不会物理存储ROWID的值，你可以像使用其它列一样使用它，只是不能对该列的值进行增、删、改操作，一旦一行数据插入后，则其对应的ROWID在该行的生命周期内是唯一的，即使发生行迁移，该行的ROWID值也不变。

让我们再回到 TABLE ACCESS BY ROWID 来：行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID可以快速定位到目标数据上，这也是Oracle中存取单行数据最快的方法；

3）索引扫描
在索引块中，既存储每个索引的键值，也存储具有该键值的行的ROWID。扫描其实分为两步：
Ⅰ：扫描索引得到对应的ROWID
Ⅱ：通过ROWID定位到具体的行读取数据

索引扫描又分五种：

INDEX UNIQUE SCAN：索引唯一扫描
INDEX RANGE SCAN：索引范围扫描
INDEX FULL SCAN：索引全扫描
INDEX FAST FULL SCAN：索引快速扫描
INDEX SKIP SCAN：索引跳跃扫描

 

a) INDEX UNIQUE SCAN（索引唯一扫描）：

针对唯一性索引（UNIQUE INDEX）的扫描，每次至多只返回一条记录；表中某字段存在 UNIQUE、PRIMARY KEY 约束时，Oracle常实现唯一性扫描；

b) INDEX RANGE SCAN（索引范围扫描）：

使用一个索引存取多行数据；发生索引范围扫描的三种情况：

在唯一索引列上使用了范围操作符（如：> < <> >= <= between） 在组合索引上，只使用部分列进行查询（查询时必须包含前导列，否则会走全表扫描） 对非唯一索引列上进行的任何查询[/list] c) INDEX FULL SCAN（索引全扫描）：
进行全索引扫描时，查询出的数据都必须从索引中可以直接得到（注意全索引扫描只有在CBO模式下才有效）
d) INDEX FAST FULL SCAN（索引快速扫描）:
扫描索引中的所有的数据块，与 INDEX FULL SCAN 类似，但是一个显著的区别是它不对查询出的数据进行排序（即数据不是以排序顺序被返回）
e) INDEX SKIP SCAN（索引跳跃扫描）：
Oracle 9i后提供，有时候复合索引的前导列（索引包含的第一列）没有在查询语句中出现，oralce也会使用该复合索引，这时候就使用的INDEX SKIP SCAN;

大概怎么用

当然还有一些表连接方式的动态在此就不再展开了，我们在优化SQL时重要的一步就是尽量避免了全表扫描，查看SQL是否命中索引，优化COST较大的执行步骤！

sql优化：

1.在数据库上建2张表tb_testl,tb_test2,表结构一致

create table TB_TEST2 (

    c_id VARCHAR2(100),

    c_date date,

    c_string VARCHAR2(100),

    c_amount NUMBER,

    c_guid VARCHAR2(100),

    c_type VARCHAR2(10)

);

2.在2张表中随机插入数据1000万行数据

insert into

    TB_TEST1

select

    to_char(rownum) c_id,

    sysdate + rownum / 24 / 3600 c_date,

    dbms_random.string('x', 20) c_string,

    trunc(dbms_random.value(0, 10000000)) c_amount,

    sys_guid() c_guid,

    to_char(trunc(dbms_random.value(0, 2))) as c_type

from

    dual connect by level <= 10000000;

 

创建 索引

create unique index idx_test_id_un on tb_test1 (c_id);

create unique index idx_test_guid_un on tb_test1 (c_guid);

create index idx_test_tp on tb_test1 (c_type);

create index idx_test_amt on tb_test1 (c_amount);

create index idx_test_str on tb_test1 (c_string);

create index idx_test_dt on tb_test1 (c_date, c_type);

 

表的访问方式

1.TABLE ACCESS FULL(全表扫描)

2.TABLE ACCESS BY ROWID(通过rowid的表存取)

3.TABLE ACCESS BY INDEX SCAN(索引扫描)

 

执行计划F5各项说明

description:执行描述

Obj own:项目拥有者

Obj name:项目名

cost: Oracle算法算出的值,越高代表性能越差,消耗越长

cardinality:扫描行数, Oracle预估的执行该sql会扫描的数据行数,注意是预估行数不是精准行数

 

1.Oracle会读取表中的所有行,并检查是否满足where语句中条件;使用建议:数据量太大的表不建议全表扫描；

2.ROWID是一个伪列,即是一个非用户定义的列,而又实际存储于数据库之中。每一个表都有一个ROWID列,一个ROWID值用于唯一确定数据库表中的的一条记录。因此通过ROWID方式来访问数据也是Oracle数据库访问数据的实现方式之一。一般情况下, ROWID方式的访问一定以索引访问或用户指定ROWID作为先决条件,因为所有的索引访问方式最终都会转换为通过ROWID来访问数据记录。由于Oracle ROWID能够直接定位一条记录,因此使用ROWID方式来访问数据,极大提高数据的访问效率；

3.在索引块中，即存储每个索引的键值,也存储具有该键值所对的ROWID.

索引的扫描分两步:首先是找到索引所对的ROWID,其次通过ROWID读取该行数据；

索引扫描种类：

1.INDEX UNIQUE SCAN (索引唯一扫描)

2.INDEX RANGE SCAN (索引范围扫描)

3.INDEX FULL SCAN (索引全扫描)

4.INDEX FAST FULL SCAN (索引快速扫描)

5.INDEX SKIP SCAN (索引跳跃扫描)

1.针对唯一性索引(UNIQUE INDEX)的扫描,每次至多只返回一条记录,主要针对该字段为主键或者唯一;

2.使用一个索引存取多行数据;

发生索引范围扫描的三种情况:

a.在唯一索引列上使用了范围操作符(如:> < <>>=<=between)

b.在组合索引上,只使用部分列进行查询(查询时必须包含前导列,否则会走全表扫描)

c.对非唯一索引列上进行的任何查询

3. 进行全索引扫描时,查询出的数据都必须从索引中可以直接得到

4. 扫描索引中的所有的数据块,与INDEX FULL SCAN类似,但是一个显著的区别是它不对查询出的数据进行排序(即数据不是以排序顺序被返回)

5.有时候复合索引的前导列(索引包含的第一列)没有在查询语句中出现, oralce也会使用该复合索引,这时候就使用的INDEX SKIP SCAN;

3.索引使用总结:

3.1对于查询中很少涉及的列或者重复值比较多的列,不要建立索引

举例:存放用户信息的表中包含了姓名、住址、性别、证件类型、证件号码用户号、手机号、出生日期等字段。

其中:性别不宜作为索引字段,因为重复值太多,不外乎男/女。

证件类型也不宜作为索引字段,同样是因为重复值太多,大多数是身份证。

同理,在某些表中,某些类型,状态之类的字段也不宜建立索引。

3.2对于按范围查询的列,最好建立索引

举例:表tb_test1 在c_date字段上有索引,可以看到他的执行计划如下

举例:表tb_test2 在c_date字段上没有索引;可以看到他的执行计划如下

3.3索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;

举例:某些表中存放类似备注、说明、问题、答案之类的字段长度可以达到2000,4000,这些字段就不宜作为索引字段。

一方面此类字段不常作为查询条件,另一方面此类索引需要占用较多的空间。

3.4经常与其他表进行连接的表,在连接字段上应该建立索引

举例:select a.c_string, b.c_type, b.c_amount from tb_test1 a, tb_test2 b where a.c_id = b.c_id and a.c_guid = 'CA824D8A19D6DA83E05383COBDOA9766'

在这个语句中a表与b表使用c_id进行连接。

以下是tb_test2的c_id字段没有索引的执行计划和执行耗费时间。可以看到子节点在tb_test2上的查询为全表扫描。

接下去我们在tb_test2的c_id字段上建立索引查看执行计划和执行耗费时间。create index idx_test2_id on tb_test2 (c_id);

可以看到子节点在tb_test2上的查询使用了索引范围扫描。且查询执行的时间大大减少。

 

3.5经常出现在Where子句中的字段,特别是大表的字段,应该建立索引

举例:交易系统数据库中经常出现的用户号、流水号、银行卡号、账号以及关键日期等字段就需要建立索引。

但也有例外的,也就是之前说过的状态类、类型类、标记类字段,虽然也是经常出现,但由于重复率太高,建立索引意义不大。

 

3.6索引应该建在选择性高的字段上;

举例:交易系统数据库中经常出现的用户号、流水号、银行卡号、账号等就是选择性高的字段,在表中通过这些类型的字段重复性很低甚至是唯一的,这些字段就可以建立索引。而之前说过的状态类、类型类、标记类字段由于重复率太高,建立索引意义不大。

示例:表TB_TEST1中字段C_TYPE分布行数如下:

执行语句select * from tb_test1 a where c_type='1'可以看到c_type='1'的行数占了整个表总行数的3成,虽然该表有c_type字段的索引,该语句的执行计划还是全表扫描,Oracle并未使用对应的索引。

3.7正确选择复合索引中的主列字段,一般是选择性较好的字段;

用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);复合索引在数据库操作期间所需的开销更小;主列字段就是复合索引中的第一个字段。

以交易系统数据库中的表为例,经常出现的用户号、流水号、银行卡号、账号,关键日期类字段等就是选择性高的字段,在表中通过这些类型的字段重复性很低甚至是唯一的,这些字段就可以作为复合索引的主列字段。

3.8如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;

按照之前说的复合索引的主列字段需要选择选择性强的字段,那么经过前几个字段的筛选得到的结果已经数据量不是太大,再为更多的条件为复合索引加入更多的字段意义不大,复合索引中使用过多的字段对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中。

这里我们做个实验,在tb_test1上建索引并执行查询语句create index idx_test_dt on tb_test1 (c_date) parallel 12;select * from tb_test1where c_date >= date '2021-12-21' + 1/ 1440 and c_date < date '2021-12-21' + 2/ 1440

{1/1440 表示1分钟，n/24/60 }

3.9 如果既有单字段索引,又有这几个字段上的复合索引,一般可以只保留一个;

按照之前说的复合索引的主列字段需要选择选择性强的字段,那么经过前几个字段的筛选得到的结果已经数据量不是太大,再为更多的条件为复合索引加入更多的字段意义不大,复合索引中使用过多的字段对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中。

这里说的单字段索引是指复合索引的主列字段单独存在索引,则2个索引共存的意义不大,可以视语句查询情况保留其中一个。

注:唯一性的复合索引较为特殊,某些表中可能需要超过3个字段进行唯一性约束。

3.10天下没有免费的午餐,使用索引是需要付出代价的。

索引固然可以提高相应的select的效率,但同时也降低了insert及update的效率,因为insert或update时会维护索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个若太多则应考虑一些不常使用到的列上建的索引是否有必要。

经过对比可以看到,同样插入100W行数据,具有5个索引的表TB_TEST1的耗时要明显大于只有一个索引的表TB_TEST2,此差距随着表中数据量的提高而加大。

表设计的一些建议

合理的表结构设计需要考虑以下的方面:

1.字段数量不宜过多

2.字段长度应预留足够长度

3.存储图片,视频,音频,文件,500字节以上文本等占用太多空间的字段(大对象字段),不能和其他字段存储在一个表中

4·大数据量的表需进行分区化

5.有些时候,我们可能会希望将一个完整的对象对应于一张数据库表这对于应用程序开发来说是很有好的,但是有些时候可能会在性能上带来较大的问题。

当我们的表中存在类似于TEXT或者是很大的VARCHAR类型的大字段的时候,如果我们大部分访问这张表的时候都不需要这个字段我们就该义无反顾的将其拆分到另外的独立表中,以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加,既减少物理10次数,也能大大提高内存中的缓存命中率。

6.不建议使用触发器自增字段作为主键(妨碍批量提交)

语句怎么写才高效

1.减少对表的查询

低效:select * from tb_test1 a where

a.c_date =(select c_date from tb_test2 b where b.c_guid = 'CA824D8A19D6DA83E05383COBDOA9766')

and a.c_type =(select c_type from tb_test2 bwhere b.c_guid = 'CA824D8A19D6DA83E05383COBDOA9766')

执行计划如下

高效:

select * from tb_test1 a where

(a.c_date , a.c_type) = (select c_date,c_type from tb test2 b where b.c_guid = 'CA824D8A19D6DA83E05383COBDOA9766')

1.减少对表的查询例2

低效:

select sum(1) from

(select c_string,c_amount,c_type,c_date from tb test1 where c date >= date '2021-12-21' + 1/ 24 and c_date < date '2021-12-21' + 2/ 24 and c_type='1') a,

(select c_string,c_amount,c_type,c_date from tb test1 where c date >= date '2021-12-21' + 1/ 24 and c date < date '2021-12-21' + 2/ 24 and c_type='1') b

where a.c_amount<b.c_amount

优化后:

with tmp as

(select c_string,c_amount,c_type,c_date from tb test1 where c date >= date '2021-12-21' + 1/ 24 and c_date < date '2021-12-21' + 2/ 24 and c_type='1')

select sum(1) from tmp a,tmp b where a.c_amount<b.c_amount

语句怎么写才高效2.避免在索引列上使用函数

低效:select * from tb_test1 b where substr(b.c_id, 1,3) ='123'

高效:select * from tb test1 b where b.c_id like '123%"

下面我们对比下执行计划和执行时间

2.避免在索引列上使用函数

诸如此类的sql语句select * from tb_test1 a where to_char(c_date,'YYYYMM')='202108'

这个同样是不走索引的,需要改写如下

select * from tb test1 a where c date>=date'2021-8-1' and c date<date'2021-9-1'

3.避免出现索引列自动转换

当比较不同数据类型的数据时,ORACLE自动对列进行简单的类型转换.

在表tb_test1中c_id是一个字符类型的索引列

select * from tb_test1 where c_id=122222212;

这个语句被ORACLE转换为:select * from tb_test1 where to_number(c_id)=122222212;

因为内部发生的类型转换,这个索引将不会被用到!

正确的写法:select * from tb_test1 where c_id='122222212";

这里我们可以看两者执行计划不一样耗时也不一样,前者无视了该字段上的索引,走了全表扫描

4,查询尽量用确定的列名,少用*号。

在使用SQL进行查询的时候,通常我们为了省事,就使用SELECT * FROM,这种写法本身是没问题的,但是在程序开发中不建议这么使用。

a.使用,则Oracle必须多做一步,要查阅内部系统目录以便把表中所有字段名查找出来。

b.明确字段名,将来在表增加字段的时候,不会导致程序报错。

c.可能会存在不需要的列,传输过程中有不必票的性能损耗;

5.对于有比较多or运算的查询,建议分成多个查询,用union all联结起来;

案例:select sum(c_amount) from tb_test1 a where

(a.c_id like '12345%' or a.c_id like '23456%' or a.c_id like '34567%' or a.c_string = '4SFCV4DRNSSXXXOVPNGY')

and a.c_guid in (select c_guid from tb_test2)

5. 优化后:

select sum(c_amount) from (

select sum(c_amount) c_amount from tb_test1 a where a.c_id like '12345%" and a.c_guid in (slect c_guid from tb_test2) union all

select sum(c_amount) from tb test1 a where a.c id like '23456%" and a.c_guid in (select c_guid from tb_test2) union all

select sum(c_amount) from tb_test1 a where a.c_id like '34567%" and a.c_guid in (select c_guid from tb_test2) union all

select sum(c_amount) from tb_test1 a where a.c_string = '4SFCV4DRNSSXXXOVPNGY and a.c_guid in (select c_guid from tb_test2)

)

6.下面的查询也将导致全表扫描:

select * from tb test1 b where b.c id like '%123%"

select * from tb test1 b where b.c id like '%123'

若要提高效率,可以考虑全文检索。而只有select * from tb_test1 b where b.c_id like '123%"才用到索引

7. not exists代替 not in (not in字句将执行一个内部的排序和合并,任何情况下, not in是最低效的,子查询中全表扫描了。为了避免使用not in,可以改写成not exists)

案例:select * from tb_test1 a where a.c_guid not in

(select c_guidfrom tb_test2 b where b.c_date >= date '2021-9-21' + 1/ 1440 and b.c_date < date '2021-12-21' + 2/ 1440)

我们看一下执行计划,可以看到该查询使用的是filter,CPU耗费也达到惊人的13位数

优化后改写如下:select  *from tb test1 a where not exists (select 1 from tb test2 b where a.c_guid = b.c_guid and b.c_date >= date '2021-9-21' + 1/ 1440 and b.c_date < date '2021-12-21' + 2/ 1440)

可以看到使用了hash join, 且.cpu消耗也大大降低了,

8. 用UNION-ALL 替换UNION (如果有可能的话):

案例:   select * from tb_test1 b where b.c_id like '123%"

union select * from tb_test1 b where b.c_id like '234%"

union select * from tb test1 b where b.c id like '345%'

union select * from tb_test1 b where b.c id like '456%"

union select * from tb_test1 b where b.c_id like '567%

优化后:    select * from tb_test1 b where b.c_id like '123%"

union all select * from tb_test1 b where b.c_id like '234%"

union all select * from tb test1 b where b.c_id like '345%'

union all select * from tb_test1 b where b.c_id like '456%"

union all select * from tb_test1 b where b.c_id like '567%"

对比下执行计划:可以看到UNION 比UNION ALL多了一个排序的CPU消耗

关于热点块

热点块会导致cache buffers chains竞争等待,但并不是说cache buffer chains一定是因为热点块而起,在特别情况下有可能是因为latch数量的问题导致的,也就是一个latch管理的buffers数量太多而导致竞争激烈。

实际上最有效的办法,是从优化sql入手,不良的sql往往带来大量的不必要的访问,这是造成热点块的根源。比如本该通过全表扫描的查询却走了索引的range scan,这样将带来大量的对块的重复访问。从而形成热点问题。

再或者比如不当地走了nested loops的表连接也可能对非驱动表造成大量的重复访问。那么在这个时候,我们的目标就是找出这些sql来并尝试优化。

初识执行计划

1.如何查看执行计划

在SQL窗口执行完一条select语句后按F5即可查看刚刚执行的这条查询语句的执行计划(不用执行完)

2.各项说明

description:执行描述

Obj own:项目拥有者

Obj name:项目名

cost: Oracle算法算出的值,越高代表性能越差,消耗越长

cardinality:扫描行数, Oracle预估的执行该sql会扫描的数据行数,注意是预估行数不是精准行数

初识执行计划

关注执行计划的目的:

检查sql执行过程,数据扫描方式,表连接方式,消耗资源值,以此来定位sql执行过慢出现的异常点

执行计划主要操作目的:想办法优化sql降低cost值, cost是Oracle通过算法计算出的预估执行sql所需消耗的资源, cost越高,占用资源越大,执行效率也越慢

优化cost值方式:通过分析description来分析异常点和优化异常点

description中主要包含信息:

1、表关联方式

2、数据扫描方式

表关联方式一般常见的是两种:

1、嵌套循环(NESTED LOOPS)

2、哈希连接(HASH JOIN)

3、排序-合并连接(SORT MERGE JOIN)

由于合并连接不常见,这里不做讨论,那么表关联方式中, nested和hash到底使用哪一个比较好?

答:关联方式没有哪一种绝对的好,根据实际情况优化关联方式才是最正确的选择;

nested最适用也是最简单的连接方式。类似于用两层循环处理两个游标,外层游标称作驱动表,Oracle检索驱动表的数据,一条一条的代入内层游标,查找满足WHERE条件的所有数据,因此内层游标表中可用索引的选择性越好,嵌套循环连接的性能就越高。

hash先将驱动表的数据按照条件字段以散列的方式放入内存,然后在内存中匹配满足条件的行。哈希连接需要有合适的内存,而且必须在CBO优化模式下,连接两表的WHERE条件有等号的情况下才可,以使用。

哈希连接在表的数据量较大,表中没有合适的索引可用时比嵌套循环的效率要高;

数据扫描方式,常见如下:

1. index unique scan主键扫描

2. index range scan 索引范围扫描

3. index skip scan 索引跳跃扫描

4. index fast full scan 引快速全扫

5. index full scan索引全扫

6. table access full 全表扫描

性能从快到慢,遇到全扫,肯定要想办法优化;

重要点(敲小黑板) :

当然,全扫不是一定就慢,即使数据量很大的表;

如果开了并行,注意;开了并行;大多数都会走上全扫,这时候不一定说全扫就很慢了,关注description关注的目的是为了降低cost,开了并行cost不高的情况下全扫性能也会比较快;

那么优化sql主要优化啥?

第一步主要优化的是扫描方式,选择是否走索引,是否开启并行等,

第二部就是选择join的连接方式了,是left还是inner等等,根据实际情况选择连接方式

总结:

看执行计划:

目标:降低cost,提高性能

方式:关注description执行描述,

通过具体问题可以选择不同方式,例如:

1、添加索引

2、优化关联方式

3、开启并行

4、添加更多where条件缩小数据集范围等等