plsql利用执行计划explain plan进行sql性能分析

发表于 | 分类于

执行计划简介

什么是执行计划 (Explain plan)？

: 执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述。

怎样查看Oracle执行计划？

以PLSQL为例：

  • 在 ：文件—>新建—> 解释计划窗口 打开解释计划窗口。
    如图所示
  • 在：工具 —> 首选项 —> 窗口类型 —> 计划窗口 根据需要配置要显示在执行计划中的列。
    如图所示

执行计划常用列介绍

  • 基数（Rows）：Oracle估计的当前操作的返回结果集行数
  • 字节（Bytes）：执行该步骤后返回的字节数
  • 耗费（COST）、CPU耗费：Oracle估计的该步骤的执行成本 用于说明SQL执行的代价，理论上越小越好（该值可能与实际有出入）

使用执行计划分析SQL

建立百万级别表sp

1
2
3
4
5
6
CREATE TABLE sp   AS
    SELECT rownum AS n, rpad('*',100,'*') AS pad FROM dual
    CONNECT BY level <= 1000000;
--建立索引并添加主键
 create unique index t_pk on sp(n);
 alter table sp add constraint t_pk primary key(n) using index t_pk
  1. 利用Oracle特有的“connect by”树形连接语法生成测试记录，“level <= 10”表示要生成10记录；
  2. 利用rownum虚拟列生成递增的整数数据；
  3. rpad(源字符串,length,填充字符串);当源字符串的长度大于定长length时，rpad为在源字符串的右侧填充指定字符或者空格；

    打开explain plan窗口进行sql分析

    未索引：select * from sp
    全表扫描：TABLE ACCESS FULL 、耗费：4222 、CPU耗费：271691255 、IO耗费：4213
    如图

使用索引后：select count() from sp –>count()的时候使用了索引快速扫描
扫描到索引： INDEX FAST FULL SCAN、耗费：584、CPU耗费:135197153、IO耗费：580
如图

以上是最直观的一个例子，也可以使用init函数或者move表之后进行索引重建以便直观的发现sql执行性能的问题
更多操作：
–进行move table索引失效,优化器表示为全表扫描
alter table sp move;
–重建索引,再次查询时，优化器扫描到索引，性能加快
alter index t_pk rebuild;
–返回了整个表的大部分数据使用了全表扫描
select count(pad) from sp where n<=799990;
–返回小部分数据时，使用的是索引扫描
select count(pad) from sp where n<=9990
–使用full，是进行全表扫描（虽然就查10条 但是很耗费资源）
select /+ full(sp) / count(pad) from sp where n<=10;
全表扫描：TABLE ACCESS FULL 、耗费：4222 、CPU耗费：284046613 、IO耗费：4213
如图

Hint函数简介

  • 什么是Hint？
    ： Hint是Oracle数据库提供的一种机制用来告诉优化器按照hint告诉它的方式生成执行计划。
    是很多DBA优化中常用的一个手段
  • 有哪些常用的Hint？
    • Hint分为优化器相关、访问路径相关、和查询转换相关等等
    • /+ALL_ROWS/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
    • /+FULL(TABLE)/ 表明对表选择全局扫描的方法.
    • /+INDEX(TABLE INDEX_NAME)/ 表明对表选择索引的扫描方法.
0%