前话:
本文是对近两三周来对两本书籍的读后感小结，两本书分别为《SQL优化核心思想》《高性能SQL调优精要与案例解析》

也会大量套用书籍中的经典结论（90%），建议一翻，个人只是整理一下结构，有新知识会想起修补一下本文

为什么会想起学习这个：一是本学期的课程，个人相信这点东西不算什么；二是数据库这东西，做哪方面都很重要，有这个契机，不妨一学

本文结构整体构成：
oracle优化整体构成

第一部分:oracle相关知识与基础
一.整体架构
1.1-实例
1.2-数据库
1.3-整体架构图解

二.内存架构
2.1-系统全局区(SGA)
2.1.1 共享池
2.1.2 数据缓冲
2.1.3 重做日志缓冲
2.1.4 系统全局区相关信息
2.2-程序全局区(PGA)
2.2.1 会话区
2.2.2 SQL工作区
2.2.3 程序全局区相关信息

三.存储架构
3.1-块
3.2-区间
3.3-段
3.4-表空间

四.对象类型
4.1-表
4.1.1 表的概念
4.1.2 表的注解
4.1.3 表与段
4.1.4 表分区
4.2-索引
4.2.1 索引的概念
4.2.2 索引的注解
4.2.3 本地索引与全局索引
4.2.4 B*Tree索引组织结构
4.2.5 位图索引
4.3-簇
4.3.1 簇的概念
4.3.2 簇的注解
4.4-视图
4.4.1 视图的概念
4.4.2 视图的注解
4.5-物化视图
4.5.1 物化视图的概念
4.5.2 物化视图的注解
4.6-同义词
4.6.1 同义词的概念
4.6.2 同义词的注解
4.7-序列
4.7.1 序列的概念
4.7.2 序列的注解
4.8-索引组织表
4.8.1 索引组织表的概念
4.8.2 索引组织表的注解
4.9-过程与函数
4.9.1 过程与函数的概念
4.9.2 过程与函数的注解
4.10-触发器
4.10.1 触发器的概念
4.10.2 触发器的注解
4.11-包
4.11.1 包的概念
4.11.2 包的注解
4.12-约束
4.12.1 约束的概念
4.12.2 约束的注解

五.事物
5.1-事物的概念
5.2-事物的特性
5.3-ANSI/ISO SQL标准定义的事物隔离级别
5.4-oracle支持的事物隔离级别
5.5-事务的开始与结束
5.6-事物的相关信息

第二部分:oracle优化相关知识与基础
一.SQL执行过程

二.SQL优化核心思想

三.统计信息中几个参数概念
1.基数(CARDINALITY)
2.选择性(SELECTIVITY)
3.直方图(HISTOGRAM)
4.回表(TABLE ACCESS BY INDEX ROWID)
5.集群因子(CLUSTERING FACTOR)

四.统计信息

五.执行计划
3.2-执行计划中访问路径

六.表连接方式
4.1-嵌套循环(NESTED LOOPS)
4.2-HASH连接(HASH JOIN)
4.3-排序合并连接(SORT MERGE JOIN)
4.4-笛卡尔连接(CARTESIAN JOIN)
4.5-标量子查询(SCALAR SUBQUERY)
4.6-半连接(SEMI JOIN)
4.7-反连接(ANTI JOIN)
4.8-FILTER
4.9-IN与EXISTS谁快谁慢
4.10-SQL语句的本质

第三部分:oracle优化开始前的准备工作
一.收集统计信息与统计信息知识
1.1-统计信息收集方法
1.1.1 自动收集统计信息
1.1.2 手动收集统计信息
1.1.2.1 统计信息重要参数设置
1.2-统计信息的参数知识
1.3-检查是否过期
1.4-扩展统计信息
1.5-动态采样
1.6-定制统计信息收集

二.收集执行计划信息与执行计划的信息分析
2.1-获取执行计划常用方法
2.1.1 使用autotrace
2.1.2 使用explain plan for
2.1.3 使用dbms_xplan包
2.1.4 查看带有A-TIME的执行计划
2.1.5 查看正在执行的sql的执行计划
2.2-定制执行计划
2.3-运用光标移动大法阅读执行计划

三.利用hint进行优化

第四部分:oracle具体优化方法
一.sql语句改写优化
1.1-消除视图
1.2-标准子查询改为外连接
1.3-update改为merge into
1.4-正确使用分析函数
1.5-with as 去除多次扫描
1.6-union改为or
1.7-or改为union
1.8-in改为join
1.9-in改为exists
1.10-not in改为not exists
1.11-not exists改为not in
1.12-exists改为join
1.13-not exists改为join
1.14-join改为exists
1.15-join改为not exists
1.16-改写为集合运算符
--
1.17-同时有or和子查询
1.18-分页语句优化思路
1.19-使用分析函数优化自连接
1.20-超大表与超小表关联优化方法
1.21-超大表与超大表关联优化方法
1.22-like语句优化方法
1.23-DBLINK优化
1.24-对表进行rowid切片
1.25-sql三段分析法

二.合理利用索引
2.1-索引应用的一般原则
2.1.1 索引越少越好
2.1.2 索引列越少越好
2.1.3 尽量少用函数索引
2.1.4 选择正确的索引类型
2.1.5 为复合索引选择正确的列顺序
2.1.6 为分区表选择正确的索引类型

三.合理使用表分区
3.1-合理选择表分区类型
3.2-合理选择索引类型

四.数据库开发常识
4.1-慎用多视图连接
4.2-慎用循环DELETE
4.3-考量绑定变量的应用
4.4-减少参与连接的表数
4.5-慎用触发器
4.6-慎用临时表
4.7-表连接写法选择和排序

五.表连接方式相关的优化
4.1-嵌套循环(NESTED LOOPS)
4.2-HASH连接(HASH JOIN)
4.3-排序合并连接(SORT MERGE JOIN)
4.4-笛卡尔连接(CARTESIAN JOIN)
4.5-标量子查询(SCALAR SUBQUERY)
4.6-半连接(SEMI JOIN)
4.7-反连接(ANTI JOIN)
4.8-FILTER
4.9-IN与EXISTS谁快谁慢
4.10-SQL语句的本质

第五部分:具体实例

第六部分:自动化脚本
抓出外键没创建索引的表
抓出需要收集直方图的列
抓出必须创建索引的列
抓出select * 的sql
抓出有标量子查询的sql
抓出带有自定义函数的sql
抓出表被多次反复调用的SQL
抓出走了filter的sql
抓出返回行数较多的嵌套循环sql
抓出NL被驱动表走了全表扫描的SQL
抓出走了table access full的sql
抓出走了index full scan 的sql
抓出走了index skip scan 的sql
抓出索引被哪些sql引用
抓出走了笛卡儿积的sql
抓出走了错误的排序合并连接的sql
抓出loop套loop的psql
抓出走了低选择性索引的sql
抓出可以创建组合索引的sql（回表再过滤选择性高的列）
抓出可以创建组合索引的sql（回表只访问少数字段）

第一部分:oracle相关知识与基础
一.整体架构
1.1-实例

即数据库实例，由服务器上的一组内存结构和进程组成，用于支撑和完成数据库的正常运行和操作，实例可以独立于数据库存在，主要包括下列组件：内存、后台进程、服务进程

1.2-数据库
参数文件、控制文件、数据文件、回滚文件、临时文件、重做日志文件、归档日志文件、警告日志文件、跟踪文件

三.存储架构
3.1-块
Block，块是oracle数据库读写的最小单位，块大小是操作系统层面块大小的整数倍，可通过参数db_block_size设置

3.2-区间
Extent，区间是oracle有关存储空间的一个逻辑单位，由多个地址连续的块组成，也是oracle存储空间分配的最小单位

3.3-段
实际存储数据库中数据对象数据的逻辑结构和单元，段由一个或多个区间组成

3.4-表空间
Oracle数据库中最大的存储空间相关的逻辑概念和容器

四.对象类型
4.1.1 表的概念
表只是一个逻辑概念，数据库组织和管理数据的基本单位

4.1.3 表与段
对非分区表，一个表对应一个段

对分区表，一个分区对应一个段

对有子分区的表，一个子分区对应一个段

对其他数据对象（例如索引、物化视图等），情况与表类似

4.1.4 表分区
根据用户的数据情况和业务需求，将表里的数据由原来存储在一个段中变为存储在多个段中

4.5-物化视图
物化视图是一种实的、定义的逻辑对象，物化视图和视图是有区别的，视图不实际存储数据，而物化视图实际存储数据，它和表一样对应数据的段

4.6-同义词
同义词是虚的、定义的逻辑对象，它不存储任何数据，仅仅是其他对象的一个别名

4.7-序列
序列也是虚的、定义的逻辑对象，它不存储任何数据，用户通过它可以获取到一系列有序的数值

4.8-索引组织表
索引组织表（IOT）是实的对象，它兼具索引和表的特性；说它具备索引的特性，是因为它的存储结构是个B*Tree，且数据在索引列上是有序的，说它具备表的特性，是因为它实际存储了表数据

4.10-触发器
触发器是虚的、定义的逻辑对象，它不存储数据，是功能是通过数据库编码和事件自动触发运行来解决问题或执行任务

五.事物
5.1-事物的概念
是关系数据库的重要机制，是数据库操作的最小单位，数据库通过它实现了相关数据的一致性，也实现了数据库层面的并行操作

在不考虑保存点的情况下，以commit与rollback语句结束

5.2-事物的特性
ACID

原子性（Atomicity）

一致性（Consistency）

隔离性（Isolation）

持久性（Durability）

5.3-ANSI/ISO SQL标准定义的事物隔离级别
未提交读（Read Uncommitted）

提交读（Read Committed）

可重复读（Repeatable）

串行（Serializable）

5.4-oracle支持的事物隔离级别
提交读（Read Committed）

可重复读（Repeatable）

串行（Serializable）

第二部分:oracle优化相关知识与基础
一.SQL执行过程
参考：
https://blog.csdn.net/w892824196/article/details/81812388
https://www.cnblogs.com/zzl-156783663/p/8506488.html

1.SQL语句执行过程
1.1 SQL语句的执行步骤 http://www.dede58.com/web/oracle/15809.html
1）语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。
2）语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。
3）视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。
4）表达式转换， 将复杂的 SQL 表达式转换为较简单的等效连接表达式。
5）选择优化器，不同的优化器一般产生不同的“执行计划”
6）选择连接方式， ORACLE 主要有三种连接方式，对多表连接ORACLE会选择适当的连接方式。
7）选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪张表做为基础数据表。
8）选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，比如，是选用全表搜索还是利用索引或是其他的方式。
9）运行“执行计划”

二.SQL优化核心思想
书籍中有很经典的一句：减少io。也确实，任何速率相关都是减少io次数，充分利用内存的优势

三.统计信息中几个参数概念
基数(CARDINALITY)
表中主键id，有100w个dintinct，则主键列基数为100w；性别列的基数为2

选择性(SELECTIVITY)
基数/总行数所占的百分比

直方图(HISTOGRAM)
帮助CBO在对基数很低、数据分布不均衡的列进行rows估算的时候，可以得到更精确的rows

回表(TABLE ACCESS BY INDEX ROWID)
通过索引中记录的rowid访问表中的数据就叫回表

集群因子(CLUSTERING FACTOR)
集群因子用于判断索引回表需要消耗的物理I/O次数

四.统计信息
书籍中给出了很多统计信息收集的sql语句，这里记录几种；有一键收集更好

http://blog.itpub.net/31485142/viewspace-2154488/:
select count(distinct column_name),count() total_rows,count(distinct column_name) / count() * 100 selectivity from table_name;
select count(distinct sno),count() total_rows,count(distinct sno)/count()*100 selectivity from mytest;

select count(distinct sno) from test;

//查看基数与选择性
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = upper('&owner')
and a.table_name = upper('&table_name')
and a.column_name = upper('&column_name');

select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'system'
and a.table_name = 'sc'
and a.column_name = 'sno';

//查看数据分布情况
select * from (select sno,count(*) from sc group by sno order by 2 desc) where rownum<=10;

--选择性大于等于20%的列
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'system'
and a.table_name = 'sc'
and a.column_name = 'sno'
and a.num_distinct / b.num_rows >=0.2

--确保没有创建索引
select table_owner,table_name,column_name,index_name
from dba_ind_columns
where table_owner='system'
and table_name='mytest';

select table_owner,table_name,column_name,index_name
from dba_ind_columns where table_name='sys%';

//基数与选择性的自动化脚本

select owner,column_name,num_rows,Cardinality,selectivity 'Need index' as notice
from
(select 
b.name owner,
a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'system'
and a.table_name = 'sc')
        where selectivity>=20
            and column_name not in (select table_owner,table_name,column_name,index_name
                from dba_ind_columns
                where table_owner='system'
                and table_name='sc')
            and column_name in(select c.name
                from sys.col_usage$ u,sys.obj$ o,sys.col$ c,sys.user$ r
                    where o.obj#=u.obj#
                    and c.obj#=u.obj#
                    and c.col#=u.intcol#
                    and r.name='system'
                    and o.name='sc';)

--回表
set arraysize=5000

--集群因子
select sum(case when block#1=block#2 and file#1=file#2 then 0 else 1 end) clustering_factor
from (select dbms_rowid.rowid_relative_fno(rowid) file#1,lead(dbms_rowid.rowid_relative_fno(rowid),1,null)
over(order by id) file#2,dbms_rowid.rowid_block_number(rowid) block#1，
lead(dbms_rowid.rowid_block_number(rowid),1,null) over(order by id) block#2
from mytest where id is not null
);
--查询表的总块数
select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from mytest;

--num_rows 多少行数据
--avg_row_len 多少个数据块
select owner,table_name,num_rows,blocks,avg_row_len
from dba_tables
where owner=upper('scott') and table_name =upper('emp');

select column_name,num_distinct,num_nulls,num_buckets,histogram
from dba_tab_col_statistics
where owner=upper('scott') and table_name=upper('emp');

select column_name,num_distinct,num_nulls,num_buckets,histogram
from dba_tab_col_statistics
where owner=upper('scott') and table_name='mytest';

--收集索引的信息
dba_indexes

dbms_stats.gather_table_stats
begin
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'TAB_OWNER',
tablename => 'TAB_NAME',
estimate_percent=>100,
method_opt=>'for all columns size 1',
no_invalidate=>FALSE,
degree=>1,
cascade=>TRUE
);
end;
/
五.执行计划
5.2-执行计划中访问路径
TABLE ACCESS FULL
TABLE ACCESS BY USER ROWID
TABLE ACCESS BY ROWID RANGE
TABLE ACCESS INDEX ROWID
INDEX UNIQUE SCAN
INDEX RANGE SCAN
INDEX SKIP SCAN
INDEX FULL SCAN
INDESX FAST FULL SCAN
INDEX FULL SCAN(MIN/MAX)
MAT_VIEW REWRITE ACCESS FULL

六.表连接方式
第三部分:oracle优化开始前的准备工作
一.收集统计信息与统计信息知识
1.1-统计信息收集方法
1.1.1 自动收集统计信息
1.1.2 手动收集统计信息
begin
dbms_status.gather_table_stats(
ownname=>'USER_NAME', --用户名
tabname=>'TAB_NAME', --表名
estimate_percent=>30, --需要评估的数据行的比例
method_opt=>'for all columns size auto', --统计数据的收集方法
degree=>DBMS_STATS.AUTO_DEGREE, --统计数据收集操作的并行度
cascade=>TRUE, --是否收集表上索引的统计信息
granularity=>'AUTO', --收集统计信息的粒度，一般为AUTO
);
END;
/

begin
dbms_status.delete_table_stats(
ownname=>'USER_NAME', --用户名
tabname=>'TAB_NAME'); --表名
end;
/
1.1.2.1 统计信息重要参数设置
begin
dbms_status.gather_table_stats(
ownname=>'USER_NAME', --用户名
tabname=>'TAB_NAME', --表名
estimate_percent=>30, --需要评估的数据行的比例
method_opt=>'for all columns size auto', --统计数据的收集方法
degree=>DBMS_STATS.AUTO_DEGREE, --统计数据收集操作的并行度
cascade=>TRUE, --是否收集表上索引的统计信息
granularity=>'AUTO', --收集统计信息的粒度，一般为AUTO
);
END;
/
1.2-统计信息的参数知识
1.3-检查是否过期
刷新数据库监控信息
begin
dbms_stats.flush_database_monitoring_info;
end;
/

stale_stats如果是no 没有过期
select owner,table_name,object_type,stale_stats,last_analyzed from
all_tab_statistics where owner='SCOTT'
and table_name='';

检查为什么过期
select table_owner,table_name,inserts,updates,deletes,timestamp
from all_tab_modifications
where table_owner='SCOTT'
and table_name='';
这里是另一本书的语句：

—检查sql语句中的所有表 统计信息是否过期
Select owner, table_name,object_type,stale_stats,last_analyzed
From dba_tab_statistics
Where (owner,table_name) in
(Select object_owner,object_name from plan_table
Where object_type like ‘%table%’
Union select table_owner,table_name from dba_indexes
where(owner,index_name) in
(Select object_owner,object_name from plan_table where object_type like ‘%index%’)
)

—检查统计信息过期的原因
Select * from all_tab_modifications
Where (table_owner,table_name) in
(select object_owner,object_name from plan_table
where object_type like ‘%table%’
Union select table_owner,table_name from dba_indexes
where(owner,index_name) in
(Select object_owner,object_name from plan_table
where object_type like ‘%index%’))
1.4-扩展统计信息

获取扩展的相关信息
select * from dbms_stat_extensions where
1.5-动态采样
1.6-定制统计信息收集
这里记录几句书籍给出的语句

—定制统计信息收集策略
收集过期或者还没有收集的

Declare
  cursor stale_table is
    select owner,segment_name,
    case  when segment_size <1 then 100
      when segment_size>=1 and segment_size<=5 then 50
      when segment_size>5 then 30
    end as percent,
    6 as degree
    from (select owner, segment_name,sum(bytes/1024/1024/1024) segment_size
        from dba_segments
        where  owner='scott' 
        and segment_name in
          (select table_name from dba_tab_statistics
          where (last_analyzed is null or stale_stats='YES')   and owner='scott')     
    group by owner,segment_name);

Begin
  dbms_stats.flush_database_monitoring_info;
  for stale in stale_table loop
    dbms_stats.gather_table_stats(ownname          => stale.owner,
                                  tabname          => stale.segment_name,
                                  estimate_percent => stale.percent,
                                  method_opt       => 'for all columns size repeat',
                                  degree           => stale.degree,
                                  granularity      => 'ALL',
                                  cascade          => TRUE);
  end loop;
End;

—抓取使用了全局临时表的sql

Select b.object_owner,b.object_name,a.temporary,sql_text
    from dba_tables a,v$sql_plan b,v$sql c
        where a.owner=b.object_owner
        and a.temporary=‘Y’
        and a.table_name=b.object_name
        and b.sql_id=c.sql_id;

二.收集执行计划信息与执行计划的信息分析
2.1-获取执行计划常用方法
2.1.1 使用autotrace
set autot on 运行结果、执行计划、统计信息
set autot trace 不显示运行结果、执行计划、统计信息
set autot trace exp 查询语句不不执行、DML执行、只显示执行计划
set autot trace stat 会执行sql，只显示统计信息
set autot off
2.1.2 使用explain plan for
explain for sql语句
2.1.3 使用dbms_xplan包
查看执行计划
select * from table(dbms_xplan.display);
查看高级执行计划
select * from table(dbms_xplan.display(NULL,NULL,'advanced -projection'));
select * from table(dbms_xplan.display(NULL,NULL,'allstats last'));
2.1.4 查看带有A-TIME的执行计划
alter session set statistics_level=all 或者 /+ gather_plan_statistics/
select * from sc;
select /+ gather_plan_statistics/ * from sc;
select * from table(dbms_xplan.display(null,null,'allstats last')); --没有找到last表
2.1.5 查看正在执行的sql的执行计划
查看正在执行的sql
select a.sid,a.event,s.sql_id,a.sql_child_number,b.sql_text
from vsql b
where a.sql_address=b.address
and a.sql_hash_value=b.sql_hash_value
and a.sql_child_number=b.child_number
order by 1 desc;
=>ID
select * from table(dbms_xplan.display_cursor('ID'),0);
2.2-定制执行计划
2.3-运用光标移动大法阅读执行计划
三.利用hint进行优化
第四部分:oracle具体优化方法
一.sql语句改写优化
1.1-消除视图

1.2-标准子查询改为外连接

1.3-update改为merge into

1.4-正确使用分析函数

1.5-with as 去除多次扫描

1.6-union改为or

1.7-or改为union

1.8-in改为join

1.9-in改为exists

1.10-not in改为not exists

1.11-not exists改为not in

1.12-exists改为join

1.13-not exists改为join

1.14-join改为exists

1.15-join改为not exists

1.16-改写为集合运算符

—

1.17-同时有or和子查询

1.18-分页语句优化思路

1.19-使用分析函数优化自连接

1.20-超大表与超小表关联优化方法

1.21-超大表与超大表关联优化方法

1.22-like语句优化方法

1.23-DBLINK优化

1.24-对表进行rowid切片

1.25-sql三段分析法

二.合理利用索引
2.1-索引应用的一般原则

2.1.1 索引越少越好

2.1.2 索引列越少越好

2.1.3 尽量少用函数索引

2.1.4 选择正确的索引类型

2.1.5 为复合索引选择正确的列顺序

2.1.6 为分区表选择正确的索引类型

三.合理使用表分区
3.1-合理选择表分区类型

3.2-合理选择索引类型

四.数据库开发常识
4.1-慎用多视图连接

4.2-慎用循环DELETE

4.3-考量绑定变量的应用

4.4-减少参与连接的表数

4.5-慎用触发器

4.6-慎用临时表

4.7-表连接写法选择和排序

五.表连接方式相关的优化
4.1-嵌套循环(NESTED LOOPS)
嵌套循环的算法：驱动表返回一行数据，通过连接列传值给被驱动表，驱动表返回多少行，被驱动表就要被扫描多少次
4.2-HASH连接(HASH JOIN)
Hash连接算法：两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表”select列和join列”读入PGA中的work area，然后对驱动表的连接列进行hash运算生成hash table，当驱动表的所有数据完全读入PGA中的work area之后，再读取被驱动表（被驱动表不需要读取到PGA的work area），对被驱动表的连接列也进行hash运算，然后到PGA中的work area去探测hash table，找到数据就关联上，没找到数据就没关联上。Hash连接只支持等值连接。
4.3-排序合并连接(SORT MERGE JOIN)
排序合并连接算法：两表关联，先对两个表根据连接列进行排序，将较小的表进行驱动表（oracle官方认为排序合并连接没有驱动表），然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从驱动表的索引中匹配数据，而排序合并连接时在内存中（PGA中work area）匹配数据
4.4-笛卡尔连接(CARTESIAN JOIN)
两个表关联没有连接条件的时候会产生笛卡尔积，这种连接方式就叫笛卡尔连接
4.5-标量子查询(SCALAR SUBQUERY)
当一个子查询介于select与from之间，这种子查询就叫标量子查询
4.6-半连接(SEMI JOIN)
两表关联只返回一个表的数据就叫半连接，半连接一般就是指的in和exists，最复杂。。
4.7-反连接(ANTI JOIN)
两表关联只返回主表的数据，而且只返回主表与子表没关联上的数据，反连接一般就是指的not in和not exists
4.8-FILTER
如果子查询（in/exists/not in/not exists）没能展开，在执行计划中就会产生FILTER，FILTER类似嵌套循环，FILTER的算法与标量子查询一模一样
六.网上的一篇
参考：https://www.cnblogs.com/kliine/p/9515799.html
1、SQL语句尽量用大写的
2、使用表的别名
3、选择最有效率的表名顺序(只在基于规则的优化器(RBO)中有效)
4、WHERE子句中的连接顺序
5、SELECT子句中避免使用 *
6、减少访问数据库的次数
7、整合简单、无关联的数据库访问
8、在SQLPlus,SQLForms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问
9、删除重复记录
10、减少对表的查询
11、避免使用耗费资源的操作
12、优化GROUP BY
13、根据需要用UNION ALL替换UNION
14、用EXISTS替换DISTINCT
15、尽量多使用COMMIT
16、用Where子句替换HAVING子句
17、用TRUNCATE替代DELETE
18、使用DECODE函数来减少处理时间
19、用EXISTS替代IN、用NOT EXISTS替代 NOT IN
20、用索引提高效率
21、用>=替代>
22、避免在索引列上使用NOT
23、避免在索引列上使用计算
24、用UNION替换OR(适用于索引列)
25、避免在索引列上使用IS NULL和IS NOT NULL
26、总是使用索引的第一个列
27、用WHERE替代ORDER BY
28、避免改变索引列的类型
29、WHERE子句

第五部分:具体实例
一.具体的sql语句优化
书籍第七章三个例子、第八章10个例子

第七章
第一道例题：子循环非嵌套
exist 容易产生filter
Select ename,deptno
from scott.emp
where exists(select deptno from scott.dept
where dname='CHICAGO'
and scott.emp.deptno=scott.dept.deptno
union
select deptno from scott.dept
where loc='CHICAGO'
and scott.dept.deptno=scott.emp.deptno);
消除filter，消除emp固定驱动表
Select ename,deptno
from scott.emp
where exists(select 1 from (select deptno from scott.dept
where dname='CHICAGO'
union
select deptno from scott.dept
where loc='CHICAGO') a
where a.deptno=scott.emp.deptno);

再改写：
Select ename,deptno
from scott.emp
where deptno in(select deptno from scott.dept where dname='CHICAGO'
union
select deptno from scott.dept
where loc='CHICAGO');

添加filter(hint:/+ no_unnest/,无法通过unnest来消除filter)：
Select ename,deptno
from scott.emp
where deptno in(select /+ no_unnest/ deptno from scott.dept where dname='CHICAGO'
union
select deptno from scott.dept
where loc='CHICAGO');

第二道例题：视图合并
Select a.*,c.grade from(
select ename,sal,a.deptno,b.dname
from scott.emp a,scott.dept b
where a.deptno=b.deptno) a,
scott.salgrade c
where a.sal between c.losal and c.hisal;

hint禁止视图合并：
Select /+ no_merge(a)/ a.*,c.grade from(
select ename,sal,a.deptno,b.dname
from scott.emp a,scott.dept b
where a.deptno=b.deptno) a,
scott.salgrade c
where a.sal between c.losal and c.hisal;

在子查询hint禁止视图合并：
Select a.,c.grade from(
select /+ no_merge*/ ename,sal,a.deptno,b.dname
from scott.emp a,scott.dept b
where a.deptno=b.deptno) a,
scott.salgrade c
where a.sal between c.losal and c.hisal;

union all改写
Select a.*,c.grade from(
select ename,sal,a.deptno,b.dname
from scott.emp a,scott.dept b
where a.deptno=b.deptno
union all
select 'SMITH',1600,10,'ACCOUNTING' from dual) a,
scott.salgrade c
where a.sal between c.losal and c.hisal;

Select a.*,c.grade from(
select ename,sal,a.deptno,b.dname
from scott.emp a,scott.dept b
where a.deptno=b.deptno and rownum>=1) a,
scott.salgrade c
where a.sal between c.losal and c.hisal;

第三道例题：谓词推入
创建一个不能被合并的视图：
create or replace view v_pushpredicate as
select * from scott.emp
union all
select * from scott.emp where rownum>=1;

select * from v_pushpredicate where empno<10;
执行计划view前面有*
select * from v_pushpredicate where empno<10;
select /*+ push_pred(列)/ empno from v_pushpredicate where empno<10; 执行错误

第八章
第一道：查看真实的基数
select * from table(dbms_xplan.display);
根据谓词信息与表 查看执行计划某个id的真实rows
select count(*) from table where '谓词情况';

第二道例题：
同时有or和子查询
使用union代替or =》消除filter
Select * from scott.emp where ename='job' or empno in (select empno from scott.emp);
第二道例题修改后 消除了filter
Select * from scott.emp where ename='job' union select * from scott.emp where empno in (select empno from scott.emp);

第三道例题：分页语句优化思路
创建测试表：create table t_page as select * from dba_objects;
Select object_id from t_page order by object_id;
错误的分页框架：
Select * from (select t.,rownum rn from (sql语句) t)
Where rn>=1
And rn <=10;
将sql语句代入到分页框架中(t_page走全局扫描)：
Select * from (select t.,rownum rn from (Select object_id from t_page order by object_id) t)
Where rn>=1
And rn <=10;
第二种包括index
修改后：还待测试
正确的分页框架：
select * from (select * from (select a.*,rownum rn from (Select
object_id from t_page order by object_id) a )where rownum<=10)
Where rn>1;
查看：select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
接下来强制走索引

8.4使用分析函数优化自连接
减少io访问

8.5超大表与超小表关联优化方法
a是小表 对小表进行并行广播:/+ parallel(6) use_hash(a,b) pq_distribute(a none,broadcast)/

8.6超大表与超大表关联优化方法
b>a /+ parallel(6) use_hash(a,b) pq_distribute(b hash,hash)/

8.7like语句优化方法
创建测试表：create table t as select * from dba_objects;
select * from t where object_name like '%SEQ%';
p159有个知识点
创建index: create index idx_ojbname on t(object_name);
select /+ index(t) * / from t where object_name like '%SEQ%';
解决index fast full scan 不能回表的原因（select ）create table index_t as select object_name,rowid rod from t;
select /+ leading(index_t@a) use_nl(index_t@a,t)/ * from t
where rowid in (select /+ qb_name(a)*/ rid from index_t where object_name like '%SEO%');
index_t 物化视图 on commit刷新

8.8 DBLINK优化

8.9 对表进行rowid切片

8.10 sql三段分析法
二.具体事例
第六部分:自动化脚本
sql语句来自书籍第十章，目前仅测试了前7、8个：

第十章
1.抓出外键没创建索引的表

with cons as (select /*+ materialize */ owner,table_name,constraint_name
from dba_constraints where owner='SCOTT'
and constraint_type='R'),
idx as (
select /*+ materialize */ table_owner,table_name,column_name
from dba_ind_columns where table_owner='SCOTT')
select owner,table_name,constraint_name,column_name
from dba_cons_columns
where (owner,table_name,constraint_name) in
    (select * from cons)
and (owner,table_name,column_name) not in 
(select * from idx);

with cons as (select /*+ materialize */ owner,table_name,constraint_name
from dba_constraints where owner='SYSTEM'
and constraint_type='R'),
idx as (
select /*+ materialize */ table_owner,table_name,column_name
from dba_ind_columns where table_owner='SYSTEM')
select owner,table_name,constraint_name,column_name
from dba_cons_columns
where (owner,table_name,constraint_name) in
    (select * from cons)
and (owner,table_name,column_name) not in 
(select * from idx);

2.抓出需要收集直方图的列

select a.owner,a.table_name,a.column_name,b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows*100,2) selectivity
from dba_tab_col_statistics a,dba_tables b
where a.owner =b.owner
and a.table_name = b.table_name
and a.owner='SCOTT'
and round(a.num_distinct / b.num_rows*100,2) < 5
and num_rows>50000
and (a.table_name,a.column_name) in
    (select o.name,c.name
        from sys.col_usage$ u,sys.obj$ o,sys.col$ c,sys.user$ r
        where o.obj#=u.obj#
        and c.obj#=u.obj#
        and c.col#=u.intcol#
        and r.name='SCOTT');

3.抓出必须创建索引的列

select owner,table_name,column_name,num_rows,Cardinality,selectivity
from(select a.owner,
            a.table_name,
            a.column_name,
            b.num_rows,
            a.num_distinct Cardinality,
            round(a.num_distinct / b.num_rows*100,2) selectivity
        from dba_tab_col_statistics a,dba_tables b
        where a.owner=b.owner
        and a.table_name=b.table_name
        and a.owner='SCOTT')
where selectivity >= 20
and num_rows>50000
and (table_name,column_name) not in
      (select table_name,column_name
        from dba_ind_columns
        where table_owner='SCOTT' and column_position=1)
and (table_name,column_name) in
    (select o.name,c.name
      from sys.col_usage$ u,sys.obj$ o,sys.col$ c,sys.user$ r
        where o.obj#=u.obj#
          and c.obj#=u.obj#
          and c.col#=u.intcol#
          and r.name='SCOTT');

mytest表

4.抓出select * 的sql

select a.sql_id,a.sql_text,c.owner,d.table_name,d.column_cnt,c.size_mb
from v$sql a,
      v$sql_plan b,
      (select owner,segment_name,sum(bytes/1024/1024) size_mb
      from dba_segments
      group by owner,segment_name) c,
      (select owner,table_name,count(*) column_cnt
      from dba_tab_cols
      group by owner,table_name) d
where a.sql_id=b.sql_id
and a.child_number=b.child_number
and b.object_owner=c.owner
and b.object_name=c.segment_name
and b.object_owner=d.owner
and b.object_name=d.table_name
and regexp_count(b.projection,']')=d.column_cnt
and c.owner='SCOTT'
order by 6 desc;

5.抓出有标量子查询的sql

select sql_id,sql_text,module from v$sql
where parsing_schema_name='SCOTT'
and module='SQL*Plus'
and sql_id in
(select sql_id from
  (select sql_id,count(*) over(partition by sql_id,child_number,depth) cnt
  from V$SQL_PLAN
  where depth=1
  and (object_owner='SCOTT' or object_owner is null))
  where cnt>=2);

6.抓出带有自定义函数的sql

select distinct sql_id,sql_text,module
from v$sql,
  (select object_name from dba_objects O
   where owner='SCOTT'
   and object_type in ('FUNCTION','PACKAGE'))
where (instr(upper(sql_text),object_name)>0)
and plsql_exec_time >0
and regexp_like(upper(sql_fulltext),'^[SELECT]')
and parsing_schema_name='SCOTT';

7.抓出表被多次反复调用的SQL

select a.parsing_schema_name schema,
  a.sql_id,
  a.sql_text,
  b.object_name,
  b.cnt
from v$sql a,
    (select *
      from (select sql_id,
                    child_number,
                    object_owner,
                    object_name,
                    object_type,
                    count(*) cnt
            from v$sql_plan
            where object_owner='SCOTT'
            group by sql_id,
            child_number,
            object_owner,
            object_name,
            object_type)
      where cnt>=2) b
where a.sql_id=b.sql_id
and a.child_number=b.child_number;

8.抓出走了filter的sql

select parsing_schema_name schema,
  sql_id,
  sql_text
from v$sql
  where parsing_schema_name='SCOTT'
    and (sql_id,child_number) in
      (select sql_id,child_number from v$sql_plan
      where operation='FILTER'
      and filter_predicates like '%IS NOT NULL%'
      minus
      select sql_id,child_number
      from v$sql_plan
      where object_owner='SYS');

9.抓出返回行数较多的嵌套循环sql

select * from 
(select parsing_schema_name schema,
        sql_id,
        sql_text,
        rows_processed/executions rows_processed
    from v$sql
    where parsing_schema_name='SCOTT'
        and executions >0
        and rows_processed/executions>10000
        order by 4 desc)a
where a.sql_id in (select sql_id
            from v$sql_plan
            where operation like '%NESTED LOOPS%'
                and id <=5);

10.抓出NL被驱动表走了全表扫描的SQL

select c.sql_text,a.sql_id,b.object_name,d.mb
    from v$sql_plan a,
    (select *
        from (select sql_id,
                child_number,
                object_owner,
                object_name,
                parent_id,
                operation,
                options,
                row_number() over(partition by sql_id,child_number,parent_id 
            order by id)rn
            from v$sql_plan)
            where rn=2)b,
            v$sql c,
            (select owner,segment_name,sum(bytes/1024/1024) mb
            from dba_segments
            group by owner,segment_name)d
        where b.sql_id=c.sql_id
            and b.child_number=c.child_number
            and b.object_owner='SCOTT'
            and a.sql_id=b.sql_id
            and a.child_number=b.child_number
            and a.operation like '%NESTED LOOPS%'
            and a.id=b.parent_id
            and b.operation='TABLE ACCESS'
            and b.options='FULL'
            and b.object_owner=d.owner
            and b.object_name=d.segment_name
        order by 4 desc;

11.抓出走了table access full的sql

select a.sql_id,
    a.sql_text,
    d.table_name,
    regexp_count(b.projection,']') || '/' || d.column_cnt column_cnt,
    c.size_mb,
    b.filter_predicates filter
from v$sql a,
    v$sql_plan b,
    (select owner,segment_name,sum(bytes/1024/1024) size_mb
    from dba_segments
    group by owner,segment_name)c,
    (select owner,table_name,count(*) column_cnt
    from dba_tab_cols
    group by owner,table_name)d
where a.sql_id=b.sql_id
and a.child_number=b.child_number
and b.object_owner=c.owner
and b.object_name=c.segment_name
and b.object_owner=d.owner
and b.object_name=d.table_name
and c.owner='SCOTT'
and b.operation='TABLE ACCESS'
and b.options='FULL'
order by 5 desc;

12.抓出走了index full scan 的sql

select c.sql_text,c.sql_id,b.object_name,d.mb
from v$sql_plan b,
    v$sql c,
    (select owner,segment_name,sum(bytes/1024/1024) mb
    from dba_segments
    group by owner,segment_name) d
where b.sql_id=c.sql_id
and b.child_number=c.child_number
and b.object_owner='SCOTT'
and b.object_name=d.segment_name
and b.object_owner=d.owner
and b.operation='FULL SCAN'
and b.options='INDEX'
order by 4 desc;

13.抓出走了index skip scan 的sql

select c.sql_text,c.sql_id,b.object_name,d.mb
from v$sql_plan b,
    v$sql c,
    (select owner,segment_name,sum(bytes/1024/1024) mb
    from dba_segments
    group by owner,segment_name)d
where b.sql_id=c.sql_id
and b.child_number=c.child_number
and b.object_owner='SCOTT'
and b.object_name=c.segment_name
and b.object_owner=d.owner
and c.owner='SCOTT'
and b.operation='SKIP SCAN'
and b.options='INDEX'
order by 4 desc;

14.抓出索引被哪些sql引用

select a.sql_text,a.sql_id,b.object_owner,b.object_name,b.object_type
from v$sql a,v$sql_plan b
where a.sql_id=b.sql_id
and a.child_number=b.child_number
amd object_owner='SCOTT'
and object_type like '%INDEX%'
order by 3,4,5;

15.抓出走了笛卡儿积的sql

select c.sql_text,
    a.sql_id,
    b.object_name,
    a.filter_predicates filter,
    a.access_predicates predicate,
    d.mb
    from v$sql_plan a,
        (select * 
            from (select sql_id,
                child_number,
                object_owner,
                object_name,
                parent_id,
                operation,
                options,
                row_number() over(partition by sql_id,child_number,parent_id
    order by id) rn 
        from v$sql_plan)
        where rn=1) b,
        v$sql c,
        (select owner,segment_name,sum(bytes/1024/1024) mb
            from dba_segments
            group by owner,segment_name) d
    where b.sql_id=c.sql_id,
        and b.child_number=c.child_number
        and b.object_owner='SCOTT'
        and a.sql_id=b.sql_id
        and a.child_number=b.child_number
        and a.operation='MEGRE JOIN'
        and a.id=b.parent_id
        and a.options='GARTESIAN'
        and b.object_owner=d.owner
        and b.object_name=d.segment_name
    order by 4 desc;

16.抓出走了错误的排序合并连接的sql

select c.sql_id,c.sql_text,d.owner,d.segment_name,d.mb
    from v$sql_plan a,
        v$sql_plan b,
        v$sql c,
        (select owner,segment_name,sum(bytes/1024/1024) mb)
            from dba_segments
            group by owner,segment_name) d
    where a.sql_id=b.sql_id,
        and a.child_number=b.child_number
        and b.operation='SORT'
        and b.options='JOIN'
        and b.access_predicates like '%"="%'
        and a.parent_id=b.id
        and a.opject_owner='SCOTT'
        and b.sql_id=c.sql_id
        and b.child_number=c.child_number
        and a.object_owner=d.owner
        and a.object_name=d.segment_name
    order by 4 desc;

17.抓出loop套loop的psql

with x as 
(select /*+ materialize */ owner,name,type,line,text,rownum rn from dba_source 
where 
(upper(text) like '%END%LOOP%' or upper(text) like '%FOR%LOOP%'))
select a.owner,a.name,a.type from x a,x b
where ((upper(a.text) like '%END%LOOP%'
and upper(b.text) like '%END%LOOP%'
and a.rn+1=b.rn)
or ((upper(a.text) like '%FOR%LOOP%'
and (upper(b.text) like '%FOR%LOOP%'
and a.rn+1=b.rn))
and a.owner=b.owner
and a.name=b.name
and a.type=b.type
and a.owner='SCOTT';

18.抓出走了低选择性索引的sql

select c.sql_id,
    c.sql_text,
    b.index_name,
    e.table_name,
    trunc(d.num_distinct/e.num_rows*100,2) selectivity
    d.num_distinct,
    e.num_rows
    from v$sql_plan a,
        (select *
            from (select index_owner,
                index_name,
                table_owner,
                table_name,
                column_name,
                count(*) over(partition by index_owner,index_name,table_owner,
                        table_name) cnt
                            from dba_int_columns)
                where cnt=1) b,
                v$sql c,
                dba_tab_col_statistics d,
                dba_tables e
    where a.object_owner=b.index_owner
        and a.object_name=b.index_name
        and b.index_owner='SCOTT'
        and a.access_predicates is not null
        and a.sql_id=c.sql_id
        and a.child_number=c.child_number
        and d.owner=e.owner
        and d.table_name=e.table_name
        and b.table_owner=e.owner
        and b.table_name=e.table_name
        and d.column_name=b.column_name
        and d.table_name=b.table_name
        and d.num_distinct/e.num_rows<0.1;

19.抓出可以创建组合索引的sql（回表再过滤选择性高的列）

select a.sql_id,
    a.sql_text,
    f.table_name,
    c.size_mb,
    e.column_name,
    round(e.num_distinct/f.num_rows*100,2) selectivity
    from v$sql a,
        v$sql_plan b,
        (select owner,segment_name,sum(bytes/1024/1024) size_mb
            from dba_segments
            group by owner,segment_name) c,
        dba_tab_col_statistics e,
        dba_tables f
    where a.sql_id=b.sql_id
        and a.child_number=b.child_number
        and b.object_owner=c.owner
        and b.object_name=c.segment_name
        and e.owner=f.owner
        and e.table_name=f.table_name
        and b.object_owner=f.owner
        and b.object_name=f.table_name
        and instr(b.filter_predicates,e.column_name)>0
        and (e.num_distinct/f.num_rows)>0.1
        and c.owner='SCOTT'
        and b.operation='TABLE ACCESS'
        and b.options='BY INDEX ROWID'
        and e.owner='SCOTT'
    order by 4 desc;

20.抓出可以创建组合索引的sql（回表只访问少数字段）

select a.sql_id,
    a.sql_text,
    d.table_name,
    REGEXP_COUNT(b.projection,']') || '/' || d.column_cnt column_cnt,
    c.size_mb,
    b.FILTER_PREDICATES filter
    from v$sql a,
        v$sql_plan b,
        (select owner,segment_name,sum(bytes/1024/1024) size_mb
        from dba_segments
        group by owner,segment_name) c,
        (select owner,table_name,count(*) column_cnt from dba_tab_cols
        group by owner,table_name) d
    where a.sql_id=b.sql_id
        and a.child_number=b.child_number
        and b.object_owner=c.owner
        and b.object_name=c.segment_name
        and b.object_owner=d.owner
        and b.object_name=d.table_name
        and c.owner='SCOTT'
        and b.operation='TABLE ACCESS'
        and b.options='BY INDEX ROWID'
        and REGEXP_COUNT(b.projection,']')/d.column_cnt<0.25
    order by 5 desc;

oracle优化总结–读后感小结http://www.youknowi.xin/oracle%E4%BC%98%E5%8C%96%E6%80%BB%E7%BB%93-%E8%AF%BB%E5%90%8E%E6%84%9F%E5%B0%8F%E7%BB%93/