本文为转载，作为学习笔记收藏。
基数（CARDINALITY）:某个列的唯一键（Distinct_Keys）的数量；
1.主键列的基数等于表的总行数；
2.一般情况下，当查询结果是返回表中5%以内的数据时，应该走索引；当查询结果返回的是超过表
中5%的数据时，应该走全表扫描；
选择性（SELECTIVITY):基数与总行数的比值再乘以100%就是某个列的选择性;
收集统计信息
exec dbms_stats.gather_table_stats('TJWEIXIN','CM_WECHAT_USER',estimate_percent=>1,no_invalidate=>FALSE,degree=>12,granularity=>'ALL',cascade=>true);
–收集所有分区
exec dbms_stats.gather_schema_stats('SCOTT',estimate_percent=>1,no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,method_opt=>'for all columns size auto',force=>TRUE,cascade=>TRUE,degree=>4);
–用户百分之一采样，收集所有列统计信息，收集索引统计信息，统计信息被锁也收集，执行计划是否失效设置为自动判断。
–estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE
–收集直方图 method_opt=>‘for all columns size auto’
个人常用查询字段选择性脚本
select 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 a.table_name = 'TEST';
一般字段选择性大于20%，则很适合创建索引
回表（TABLE ACCESS BY INDEX ROWID）
通过索引中记录的rowid访问表中的数据就叫回表；
回表一般是单块读；
回表次数太多会严重影响SQL性能；
查看列的选择性（根据列的统计信息）
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 = 'SCOTT' and a.table_name = 'TEST';
检查统计信息是否过期
select owner,table_name,num_rows,last_analyzed,stale_stats from dba_tab_statistics where (last_analyzed is null or stale_stats='YES') and owner=upper('&owner') and table_name=upper('&tabname');
如果sql语句中关联了很多表，如何查看sql语句中关联的表统计信息是否过期？
生成执行plan_table: explain plan for select * from scott.emp e, scott.dept d where e.deptno = d.deptno;
获取是否过期：
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%'));
查看执行计划方法：
set autot on : 该命令会运行统计信息；
set autot trace : 该命令会运行SQL，但不显示运行结果，会显示执行计划和统计信息；
set autot trace exp : 该命令查询语句不执行，DML语句会执行，只显示执行计划；
set autot trace stat : 该命令会运行SQL，只显示统计信息；
set autot off : 关闭AUTOTRACE ；
explain plan for SQL语句;
select * from table(dbms_xplan.display);
explain plan for SQL语句;
select * from table(dbms_xplan.display(NULL,NULL,‘advanced -projection’));
真实执行计划
alter session set statistics_level = all ;
或者在SQL语句中添加hint: /*+ gather_plan_statistics */
select * from table(dbms_xplan.display_cursor(null,null,‘allstats last’))
Starts表示这个操作执行的次数；
E-Rows表示优化器估算的行数，就是普通执行计划中的Rows；
A-Rows表示真实的行数；
A-Time表示累加的总时间，与普通执行计划不同的是，普通执行计划中的Time是假的，而ATime是真实的；
Buffers表示累加的逻辑读；
Reads表示累加的物理读；
select * from table(dbms_xplan.display_cursor(‘sql_id’,child_number));
SELECT * FROM table(dbms_xplan.display_awr(‘sql_id’));
表访问路径：
全表扫描
等待事件为 db file scattered read ，如果是并行全表扫描，等待事件为direct path read;
Oracle 11g中在对一个大表进行全表扫描的时候，会将表直接读入PGA，绕过buffer cache，事件
direct path read 在开启了异步I/O（ disk_asynch_io）的情况下统计是不准确的，可以alter system set “_serial_direct_read”=false"禁用direct path read;
db file sequential read 表示单块读；
TABLE ACCESS BY USER ROWID（性能最好）
表示直接用ROWID获取数据，单块读；
TABLE ACCESS BY ROWID RANGE
表示ROWID范围扫描，多块读；
TABLE ACCESS BY INDEX RANGE
表示回表，单块读；
INDEX UNIQUE SCAN
表示索引唯一扫描，单块读；
INDEX RANGE SCAN
表示索引范围扫描，单块读，返回的数据是有序的，默认升序；
对唯一索引或者主键进行范围查找，对非唯一索引进行等值查找，范围查找，就会发生INDEX RANGE
SCAN。等待事件为 db file sequential read ;
INDEX RANGE SCAN DESCENDING表示索引降序范围扫描；
INDEX SKIP SCAN
表示索引跳跃扫描，单块读，返回的数据是有序的（默认升序）；
HINT：INDEX_SS（表名/别名 索引名）
当组合索引的引导列（第一个列）没有在where条件中，并且组合索引的引导列/前几个的列的基数很
低，where过滤条件对组合索引中非引导列进行过滤的时候就会发生索引跳跃扫描，等待事件为 db
file sequential read ;
当执行计划中出现了INDEX SKIP SCAN，我们可以直接在过滤列上建立索引，使用INDEX RANGE
SCAN代替INDEX SKIP SCAN;
INDEX FULL SCAN
表示索引全扫描，单块读，返回的数据是有序的（默认升序）；
HINT：INDEX(表名/别名 索引名)
等待事件为db file sequential read 。
INDEX FAST FULL SCAN
表示索引快速全扫描，多块读；
HINT：INDEX_FFS(表名/别名 索引名)；
等待事件为db file scattered read，如果是并行扫描，等待事件为 direct path read；
INDEX FULL SCAN(MIN/MAX)
表示索引最小/最大值扫描，单块读；
发生在select max(column) from table 或 select min(column) from table等SQL中；
MAT_VIEW REWRITE ACCESS FULL
表示物化视图全表扫描，多块读；
单块读与多块读
从磁盘1次读取1个块到buffer cache就叫单块读，从磁盘1次读取多个块到buffer cache就叫多块读；
一次I/O最多只能读取或者写入1MB数据；
在判断哪个访问路径性能好的时候，通常是估算每个访问路径的I/O次数，谁的I/O次数少，谁的性能就
好；
嵌套循环
嵌套循环的算法：驱动表返回一行数据，通过连接列传值给被驱动表，驱动表返回多少行，被驱动表就
要被扫描多少次。
驱动表应该返回少量数据；
嵌套循环被驱动表必须走索引；
嵌套循环被驱动表索引只能走INDEX UNIQUE SCAN或者INDEX RANGE SCAN;
嵌套循环被驱动表的连接列基数应该很高；
两表关联返回少量数据才能走嵌套循环；
嵌套循环不需要消耗PGA；
hash
HASH连接的算法：两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表的“select
列和join列”读入PGA中的work area，然后对驱动表的连接列进行hash运算生成hash table，
当驱动表的所有数据完全读入PAG中的work area之后，再读取被驱动表（被驱动表不需要读入
PAG的work area），对被驱动表的连接列也进行hash运算，然后到PGA中的work area去探测
hash table，找到数据就关联上，没找到数据就没关联上。
哈希连接支持支等值连接；
执行计划中离HASH连接关键字最近的表就是驱动表；
HASH连接的驱动表和被驱动表的连接列都不需要创建索引；
因为HASH连接需要将驱动表的select列和join列放入PGA中，所以，应该尽量避免书写
select * from …语句，将需要的列放select list中，这样可以减少驱动表对PGA的占用，避免驱动
表溢出到临时表空间，从而提升性能。如果无法避免驱动表被溢出到临时表空间，我们可以将临
时表空间创建在SSD上或RAID 0上，加快临时数据的交换速度；
当PGA采用自动管理，单个进程的work area被限制在1G以内，如果PGA采用手动管理，单个进
程work area不能超过2GB。如果驱动包比较大，可以开启并行查询至少parallel（4），将表拆分
为至少4分，这样每个并行进程中的work area能够容纳1GB的数据，从而避免驱动表被溢出到临
时表空间；
排序合并
排序合并连接主要用于处理两表非等值关联，比如>，>=，<，<=，<>，但是不能用于instr、
substr、like、regexp_like关联，instr、substr、like、regexp_like关联只能走嵌套循环。
排序合并连接需要将两个表都放入PGA中；
排序合并连接的算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表（Oracle官
方认为排序合并连接没有驱动表，笔者认为是有的），然后从驱动表中取出连接列的值，到已经排好序
的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少
次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是
在内存中（PGA中的work area）匹配数据。
离MERGE JOIN关键字最近的表就是驱动表；
驱动表只扫描1次
思考：怎么优化排序合并连接？
回答：如果两表关联是等值连接，走的是排序合并连接，我们可以将表连接方式改为HASH连接；
如果是非等值连接，可以考虑增加两表的显示条件，将两个表数据量缩小；
笛卡尔积
在多表关联的时候，两个表没有直接关联条件，但是优化器错误的把某个表返回的Rows算为1行
（注意必须是1行），这个时候也可能发生笛卡尔连接；
如果两个表有直接关联条件，无法控制两个表进行笛卡尔连接；
如果两个表没有直接关联条件，我们在编写SQL的时候将两个表依次放在from后面并且添加
HINT：ordered，就可以使两个表进行笛卡尔积关联；
思考：当执行计划中有笛卡儿连接应该怎么优化？
回答：首先应该检查表是否有关联条件，如果没有关联条件，那么应该询问开发与业务人员为何
表没有关联条件，是否为满足业务需求而故意不写关联条件；其次应该检查离笛卡尔连接最近的
表是否真的返回1行数据，如果返回数据真的只有1行，那么走笛卡尔连接是没有问题的，如果返
回行数据超过1行，那就需要检查为什么Rows会估算错误。纠正错误的Rows之后，优化器就不
会走笛卡尔连接了；
可以使用/+* opt_param(’_optimizer_mjc_enable’,‘false’) */ 禁止笛卡尔连接；
标量子查询类似一个天然的嵌套循环；
等价改写为外连接：
select d.dname, d.loc, (select max(e.sal) from emp e where e.deptno = d.deptno) max_sal from dept d;
等价改写：
select d.dname, d.loc, e.max_sal from dept d left join (select max(sal) max_sal, deptno from emp e group by deptno) e on d.deptno = e.deptno;
半连接
两表关联只返回一个表的数据就叫半连接。半连接一般就是指的in和exists；
半连接in的写法如下：
select * from dept where deptno in (select deptno from emp);
半连接exists的写法如下：
select * from dept where exists (select null from emp where dept.deptno = emp.deptno);
反连接
两表关联只返回主表的数据，而且只返回主表与子表没有关联上的数据；
反连接一般就是指not in 和not exists；
注意，not in里面如果有null，整个查询会返回空，而in里面有null，查询不受null影响；
select * from dept where deptno not in (select deptno from emp where deptno is not null);
select * from dept where not exists (select null from emp where dept.deptno = emp.deptno);
select d.* from dept d left join emp e on d.deptno = e.deptno where e.deptno is null;
如果子查询（in/exists/not in/not exists）没能展开（unnest），在执行计划中就会产生FILTER，
FILTER类似嵌套循环，FILTER的算法与标量子查询一模一样；
in和exists是半连接，半连接也属于表连接，那么既然是表连接，我们需要关心两表的大小以及两表之间究竟走什么连接方式，才能去优化SQL；
SQL语句的本质
标量子查询可以改写为外连接（需要注意表与表之间关系，去重）；
半连接可以改写为内连接（需要注意表与表之间关系，去重）；
反连接可以改写为外连接（不需要注意表与表之间的关系，也不需要去重）；
优化核心思想：想方设法减少SQL的物理I/O次数（不管是单块读次数还是多块读次数）。
子查询非嵌套（Subquery Unnesting）：当where子查询中有in、not in、exists、not exists等，
CBO会尝试将子查询展开（unnest），从而消除FILTER，这个过程就叫作子查询非嵌套。子查询非嵌
套的目的就是消除FILTER。
为什么要消除FILTER呢？因为FILTER的驱动表是固定的，一旦驱动表被固定，那么执行计划也就被固定了。对于DBA来说这并不是好事，因为一旦固定的执行计划本身是错误的（低效的），就会引起性能问题，想要提升性能必须改写SQL语句，但是这时SQL已经上线，无法更改，所以，一定要消除
FILTER。
示例：
select ename, deptno from scott.emp where
exists (select deptno from scott.dept where dname = ‘CHICAGO’ and emp.deptno = dept.deptno
union
select deptno from scott.dept where loc = ‘CHICAGO’ and dept.deptno = emp.deptno);
改写如下：
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 = emp.deptno);
视图合并（View Merge）：当SQL语句中有内联视图（in-line view，from后面的子查询），或者
SQL语句中有用create view创建的视图，CBO会尝试将内联视图/视图拆开，进行等价的改写，这个过程就叫作视图合并。如果没有发生视图合并，在执行计划中，我们可以看到VIEW关键字，而且视图/子查询会作为一个整体。如果发生了视图合并，那么视图/子查询就会被拆开，而且执行计划中视图/子查询部分就没有VIEW关键字。
/*+ no_merge */
谓词推入（Pushing Predicate）：当SQL语句中包含不能合并的视图，同时视图有谓词过滤（也就是where过滤条件），CBO会将谓词过滤条件推入视图中，这个过程就叫作谓词推入。谓词推入的主要目的就是让Oracle尽可能早地过滤掉无用的数据，从而提升查询性能。
关闭连接谓词推入： alter session set “_push_join_predicate” = false ;
分页：
分页框架： select * from (select * from (select a.*, rownum rn from (需要分页的SQL) a) where rownum <= 10) where rn >= 1;
分页语句优化思路：
要利用索引已经排序特性，将order by的列包含在索引中，同时也要利用rownum的COUNT
STOPKEY特性来优化分页SQL。如果分页中没有排序，可以直接利用rownum的COUNT STOPKEY特
性来优化分页SQL。
分析函数
select ename, deptno, sal from scott.emp a where sal = (select max(sal) from scott.emp b where a.deptno = b.deptno);
select ename, deptno, sal from (select a.*, max(sal) over(partition by deptno) max_sal from scott.emp a) where sal = max_sal;
