做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 ....
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 前面有* 该咋办？