6 执行计划--优化主题系列

做SQL优化最核心的地方就是执行计划

要达到一眼就能看SQL问题的能力

就需要彻底搞懂执行计划

 

在做SQL优化的时候，都必须查看执行计划，看执行计划的时候一定要用SQLPLUS去看，千万不要用PL/SQLDEV/TOAD/EM等工具去看执行计划。因为这些工具只能看到访问路径，无法看到谓词过滤信息，而恰巧，谓词过滤信息是执行计划中的重中之重。可以这样说，如果有谁用PL/SQLDEV/TOAD/EM等工具查看执行计划，那他一定是个SQL优化菜鸟。

 

提问：为什么 PL/SQL 按住F5 没有其他的谓词过滤信息？？

另外没事，也不要用10046以及10053，用这两个工具的人要么就是SQL优化菜鸟，要么就是不懂装懂装逼我们只有在进行troubleshooting 或者是研究CBO行为的时候才进行10046/10053

即：10046是TroubleShooting10053是研究CBO行为及查询CBO BUG的

 

提问：用AUTOTRACE查看执行计划是真实的吗？？

 

AUTOTRACE 是假的它是从PLAN_TABLE里面来的而不是从V$SQL_PLAN来的

 

利用AUTOTRACE查看执行计划

注意：AUTOTRACE所查询的执行计划并不是真实的执行计划，是CBO预估的。

 

用法: SETAUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

方括号内的字母都可以省略。

set autot on ----执行SQL 并且显示执行计划和统计信息

set autot trace ----执行SQL 但不显示运行结果，显示执行计划和统计信息

set autot trace exp ----如果SELECT 就不执行SQL(dml 执行)，只显示执行计划

set autot trace stat ----执行SQL，只显示统计信息

 

利用AUTOTRACE查看执行计划我们最关心的就是consistent gets、physicalreads部分以及返回的行数部分。有时候一个SQL跑很久，利用AUTOTRACE去查看执行计划不现实，因为要等很久。你愿意等吗？

所以这个时候要利用DBMS_XPLAN包来查看执行计划。对OLAP环境进行SQL优化的时候，一般用DBMS_XPLAN，因为OLAP的SQL一般都跑很长。

提问：

一般在什么情况下用AUTOTRACE查看执行计划？？

利用AUTOTRACE查看执行计划的好处是什么？？

在什么情况下不用AUTOTRACE查看执行计划？？

 

一个SQL跑很久你用AUTOTRACE吗？？

 

利用DBMS_XPLAN包查看执行计划

注意：EXPLAINPLAN FOR ......所查询的执行计划并不是真实的执行计划，是CBO预估的。

首先 EXPLAINPLAN FOR SQL_TEXT;

显示普通执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

显示高级执行计划

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'ADVANCED-PROJECTION'));

 

高级执行计划可以看到QUERYBLOCK + OUTLINE DATA

AUTOLINE DATA里面可以看到CBO是否进行查询变换

 

例子如下(基于Scott用户)：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'ADVANCED-PROJECTION'));

高级执行计划就可以看到CBO对这个SQL进行了什么样的查询转行。这里CBO对这个SQL进行了subqueryunnesting ,请看AUTLINEDATA部分的UNNEST(@"SEL$2")这里也只是让大家有个查询变换的概念

显示高级执行计划在对付复杂SQL的时候特别有用

 

特别是几百行的SQL 特别是自动生成的SQL 以及用了RLS(Row LevelSecuriy)

AUTOLINE DATA其实久是一堆的HINT

这一堆的HINT 其实就是SQLPROFILE

 

有时候写HINT 要看QUERYBLOCK

一个SELECT 就是一个QUERYBLOCK 此处明显是两个查询块

一个HINT 一般只对一个查询块有效

QUERY BLOCK命名通常是 SEL$数字

 

刚才讲了高级执行计划，现在讲一下特殊执行计划

显示特殊执行计划（会运行SQL,包含真实的行数，某个操作的执行时间）

 

SELECT /*+ GATHER_PLAN_STATISTICS */ ....

OR

ALTER SESSION SET STATISTICS_LEVEL=ALL;

---再运行SQL

SELECT * FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

 

做个试验：随便在任何用户下

create table test as select * from dba_objects;

create index idx on test(owner);

 

ALTER SESSION SET STATISTICS_LEVEL=ALL;

 

select count(*) from test where owner='SYS';   --31148

 

SELECT * FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

这种执行计划对付OLTP 的SQL 非常有用

这种执行计划对付 SQL 跑不超过10分钟的非常有用

Starts表示这个操作执行了几次

E-Rows表示优化器估算的行数

A-Rows 表示实际的行数

A-Time 表示这个操作执行的时间(累加的)

Buffers 表示逻辑度(累加的)

 

如果 E-ROWS 与A-ROWS 相差太大了是不是说明这个步骤也有问题？

这里不该走索引对吧？？哪个地方A-TIME最大就是哪里慢对不对

这种问题一般是表统计信息过期了或者没收集直方图或者 CBO 算错了

再一次强调

如果你优化SQL 要依赖上面的 STARTS 和上面的 E-ROWS 那完蛋了

 

一般实战中不用这个看执行计划一旦一个SQL跑很久几个小时坑爹啊

在查询变换的时候一般用ALL_ROWS

 

提问：什么叫真实的执行计划？？

要 SQL 真正的运行过了的

并且执行计划保存在共享池(librarycache/shared_pool)里

并且要通过V$SQL_PLAN能查到

 

SELECT * FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID',SQL_CHILD_NUMBER));

通过v$session可查到SQL_ID和SQL_CHILD_NUMBER

 

阅读执行计划要学会光标移动大法

 

explain plan for select e.ename,e.job,d.dname from emp e,deptd  where e.deptno=d.deptno and e.sal<2000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

提问：注意过执行计划中的"*"没有？？

做SQL优化用PL/SQLdeveloper F5能看到*吗？？

所以切记看执行计划别用工具

 

执行计划分为两部分：

一部分是访问路径+表连接方式这个位于Plan hashvalue和PredicateInformation(identified by operation id)之间

另外一部分是谓词过滤信息这部分位于PredicateInformation(identified by operation id)下面，谓词过滤信息非常重要，用工具PL/SQL DEV/TOAD/EM等等往往会忽略这类信息。

 

提问：全表扫描前面没有*意味着什么？？没有谓词过滤信息对吧？？

如果以后遇到全表扫描前面没*那么要引起强烈关注

 

set autot trace

select object_id from test;

如上执行计划中全表扫描前没*没有谓词过滤信息对吧？？

 

假设test表有10GB这么大且test表的列很多

那么我们创建一个所以在object_id列上

是不是object_id这个索引就没10GB这么大了或许1GB？？或许几百MB

假设这个表才几十MB数据一共就3-4个列创建索引有用吗？

一定要这个表列很多并且segment_size很大上几百MB到GB级别

如segment_size很大列很少那么可考虑组合索引组合索引不要超过4个列

 

假设一个表30个字段 10GB 三个字段可能就1GB了节约了9GB的扫描空间对吧

假设30个字段的表但要访问8个字段并且前面没* 这个时候要用并行而且是OLAP

OLTP系统则可考虑MV 较少了1/3的体积

 

提问：全表扫描前面有*如何处理？？做个试验

drop table test purge;

create table test as select * from dba_objects;

create index idx on test(object_id);

select * from test where object_id=10;

看这里如何走的索引

假设：test表很大在创建索引前注意什么？？选择性对吧？？5%以下建索引

 

提问：执行计划中tableaccess by index rowid前有*如何处理？做个试验

drop table test purge;

create table test as select * from dba_objects;

create index idx on test(object_id);

select * from test where object_id=10 and object_name='SCOTT';

需要建组合索引防止回表对吧？？

create index idx1 on test(object_id,object_name);

select * from test where object_id=10 and object_name='SCOTT';

之前只在object_id列有索引对吧

假设返回1000条数据是不是有1000个ROWID 要1000次回表？？

完后要看owner='SCOTT'进行过滤最终发现只有10条数据符合

现在建立组合索引是不是仅10次回表？？

 

select * from test where object_id=10 and object_name='SYS';

假设object_id=10返回1000条记录每条都是SYS 是不是还是1000次回表？？

建组合索引是不是没必要？？

 

解释下什么时候走索引什么时候走全表扫描？？

举个例子：假设一个表有1GB大小走全表扫描要进行多少次I/0？？

假设多块读是16 块大小8KB 扫描完是不是需要8192次I/O

select 1024/1024/16/8 from dual; --8192

假设索引高度为3 索引扫描是单块读除了indexfast full scan

通过索引扫描返回多少数据？？

假设一个索引块大约存储100条数据假设1GB的表有1000W条记录

假设一个索引块只存储100条记录

8192能存储多少条记录呢？？

要减去3 即8189个块 818900条记录对吧

818900/1000W=8% 每个ROWID回表也是单块读大约8%/2=4%

ROWID回表有个聚簇因子加进去基本上5%的样子对吧

5%以下的数据才能走索引就是这么来的

 

为什么走索引慢？？就是I/O次数多了

SQL优化的精髓就是减少I/O扫描次数

 

OLAP 一般不建索引但BI报表的时候走索引数据量小

大家看 ID=5 这个地方是不是说 全表扫描前面有个 * 那 ID=5 这个地方的过滤条件是什么

你觉得返回4行 是准确的还是？不准确？

我现在 想要立刻知道 返回多少行 怎么算？？？

当你们看到 全表扫描前面有 * 你应该想到怎么办？？？

假如说 EMP 表有100W行上面的where 条件 返回4行咋搞？

怎么建立索引？在哪个列建立索引？

你们遇到 了 全表扫描 前面有 * 是不是 要先 

SELECT COUNT(*) FROM 表 where 条件 看返回多少行

如果返回行数 低于 总行数的5% 以下那么立即建立索引

如果返回行数超过 20% 那么别建立索引了

如果返回的行数 特别多 是不是 不适合走 索引的 范围扫描了？？？

SELECT OWNER, OBJECT_ID FROM TEST WHERE OBJECT_ID<1000;

现在 TEST表有 1000W行那 你们看到上面的SQL 怎么做优化？

全表扫描前面没有*意味什么？是不是没有过滤条件

对于 没有 * 的 TABLE ACCESS FULL 如何优化？

全表扫描前面 没有 *  是不是 要 查看这个SQL SELECT 后面的列以及 JOIN列全都放在 组合索引里面

是不是让它走INDEX FAST FULL SCAN?

表的列 一般很多但是 真正的 查询的列以及JOIN的列并不多

这个时候扫描 索引的体积 是不是 比表的体积小了？

那么这个时候 扫描的 I/O 就减少了？

有时候 组合索引 体积比表大叶子节点还有 branch 节点索引还存储了ROWID 是不是多存了一个列

那么索引是不是多了一个列以及多了一个 branch 

什么时候索引体积比表大？组合索引列多了 就比表大了

一般来说一个表的列 就 10多列 以及到 几十列一般来说这种 索引不要超过5个列

执行计划中你看到了table access by index rowid 前面有* 该咋办？