oracle存储：block extent segment (clob字段影响：单独存放一个段）
10046事件监控等待事件 10046 trace文件
等待事件为db file sequential read。
多块读 单块读
索引列含null
每次I/O都读取1MB数据
索引的集群因子很小
PGA中的work area vs SGA
https://blog.csdn.net/robinson1988/article/details/51148332
如何避免执行计划中产生FILTER以及执行计划中产生了FILTER怎么优化
标量子查询
子查询无法展开 unnest no_unnest
本地索引 vs global索引

集群因子：
是通过一个索引扫描一张表时需要访问的表的数据块的数量
(1) 扫描一个索引
(2) 比较某行的ROWID和前一行的ROWID，如果这两个ROWID不属于同一个数据块，那么集群因子增加1；
(3) 整个索引扫描完毕后，就得到了该索引的集群因子。
集群因子反映了索引范围扫描可能带来的对整个表访问过程的IO开销情况
如果集群因子接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。
如果集群因子接近于行的数量，那说明这张表不是按索引字段顺序存储的。
在计算索引访问成本时，集群因子十分有用。集群因子乘以选择性参数就是访问索引的开销。

SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。
PGA：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA正相反,PGA是只被一个进程使用的区域，PGA在创建进程时分配,在终止进程时回收。

unnest：展开子查询 ，会与其它表作连接
no_unnest：不展开子查询，本身单独运行，成为FILTER

谓词filter和access：
执行计划如果显示是access，就表示这个谓词条件的值将会影响数据的访问路径(表还是索引)
filter表示谓词条件的值并不会影响数据访问路径，只起到过滤的作用。

逻辑读：扫描块的个数
物理读：实际I/O次数，可能少于逻辑读（多块读的时候可以一次读多块）

各访问路径对比

第三章 执行计划

3.1、获取和查看执行计划

第一种方式 ：set autot

set autot on：该命令会运行SQL并且显示运行结果，执行计划和统计信息。
set autot trace：该命令会运行SQL，但不显示运行结果，会显示执行计划和统计信息。
set autot trace exp：运行该命令查询语句不执行，DML语句会执行，只显示执行计划。
set autot trace stat：该命令会运行SQL，只显示统计信息。
set autot off：关闭AUTOTRACE。

统计信息解读

recursive calls
表示递归调用的次数。一个SQL第一次执行就会发生硬解析，在硬解析的时候，优化器会隐含地调用一些内部SQL，因此当一个SQL第一次执行，recursive calls会大于0；第二次执行的时候不需要递归调用，recursive calls会等于0。
SQL语句中有自定义函数，recursive calls就会显示为自定义函数被调用了多少次

db block gets
表示有多少个块发生变化，一般情况下，只有DML语句才会导致块发生变化，所以查询语句中db block gets一般为0。如果有延迟块清除，或者SQL语句中调用了返回CLOB的函数，db block gets也有可能会大于0，不要觉得奇怪。

consistent gets
表示逻辑读，单位是块。在进行SQL优化的时候，我们应该想方设法减少逻辑读个数。通常情况下逻辑读越小，性能也就越好。需要注意的是，逻辑读并不是衡量SQL执行快慢的唯一标准，需要结合I/O等其他综合因素共同判断。

physical reads
表示从磁盘读取了多少个数据块，如果表已经被缓存在buffer cache中，没有物理读，physical reads等于0。

redo size
表示产生了多少字节的重做日志，一般情况下只有DML语句才会产生redo，查询语句一般情况下不会产生redo，所以这里redo size为0。如果有延迟块清除，查询语句也会产生redo。

bytes sent via SQLNet to client
表示从数据库服务器发送了多少字节到客户端。
bytes received via SQLNet from client
表示从客户端发送了多少字节到服务端。

SQL*Net roundtrips to/from client
表示客户端与数据库服务端交互次数，我们可以通过设置arraysize减少交互次数。

sorts (memory)和sorts (disk)
分别表示内存排序和磁盘排序的次数。

rows processed
表示SQL一共返回多少行数据。我们在做SQL优化的时候最关心这部分数据，因为可以根据SQL返回的行数判断整个SQL应该是走HASH连接还是走嵌套循环。如果rows processed很大，一般走HASH连接；如果rows processed很小，一般走嵌套循环。

第二种方式：explain plan for sql_str

还可设置advanced-projection, 查看高级执行计划，高级执行计划比普通执行计划多了Query Block Name/Object Alias和Outline Data。

第三种方式：带有A-TIME的执行计划

alter session set statistics_level=all
Starts表示这个操作执行的次数。
E-Rows表示优化器估算的行数，就是普通执行计划中的Rows。
A-Rows表示真实的行数。
A-Time表示累加的总时间。
与普通执行计划不同的是，普通执行计划中的Time是假的，而A-Time是真实的。
Buffers表示累加的逻辑读。
Reads表示累加的物理读。

三种方式不同点：

• 使用AUTOTRACE或者EXPLAIN PLAN FOR获取的执行计划来自于PLAN_TABLE；带有A-Time的执行计划来自于V$SQL_PLAN。
• PLAN_TABLE的执行计划并不是SQL真实的执行计划，它只是优化器估算出来的；V$SQL_PLAN，是真实的执行计划

查看正在执行的SQL的执行计划

需要获取SQL的SQL_ID以及SQL的CHILD_NUMEBR

第四章 访问路径 access path

4.1常见访问路径

TABLE ACCESS FULL

表示全表扫描，一般情况下是多块读，HINT: FULL(表名/别名)

TABLE ACCESS BY USER ROWID

表示直接用ROWID获取数据，单块读。该访问路径在Oracle所有的访问路径中性能是最好的。

TABLE ACCESS BY ROWID RANGE

表示ROWID范围扫描，多块读。因为同一个块里面的ROWID是连续的，同一个EXTENT里面的ROWID也是连续的，所以可以多块读。
where条件中直接使用rowid进行范围扫描就会使用该执行计划。

TABLE ACCESS BY INDEX ROWIDT

表示回表，单块读。

INDEX UNIQUE SCAN

表示索引唯一扫描，单块读。对唯一索引或者对主键列进行等值查找，就会走INDEX UNIQUE SCAN。因为对唯一索引或者对主键列进行等值查找，CBO能确保最多只返回1行数据，所以这时可以走索引唯一扫描。
其性能仅次于TABLE ACCESS BY USER ROWID

INDEX RANGE SCANINDEX RANGE SCAN

表示索引范围扫描，单块读，返回的数据是有序的（默认升序）。HINT: INDEX（表名/别名 索引名）。对唯一索引或者主键进行范围查找，对非唯一索引进行等值查找，范围查找，就会发生INDEX RANGESCAN。
在检查执行计划的时候我们要注意索引范围扫描返回多少行数据，如果返回少量数据，不会出现性能问题；如果返回大量数据，在没有回表的情况下也还好；如果返回大量数据同时还有回表，这时我们应该考虑通过创建组合索引消除回表或者使用全表扫描来代替它。

INDEX SKIP SCAN

表示索引跳跃扫描，单块读。返回的数据是有序的（默认升序）。HINT: INDEX_SS（表名/别名 索引名）。当组合索引的引导列（第一个列）没有在where条件中，并且组合索引的引导列/前几个列的基数很低，where过滤条件对组合索引中非引导列进行过滤的时候就会发生索引跳跃扫描

INDEX FULL SCAN

表示索引全扫描，单块读，返回的数据是有序的（默认升序）。HINT: INDEX（表名/别名 索引名）。索引全扫描会扫描索引中所有的叶子块（从左往右扫描），如果索引很大，会产生严重性能问题（因为是单块读）

INDEX FAST FULL SCANI

表示索引快速全扫描，多块读。HINT：INDEX_FFS（表名/别名 索引名）。当需要从表中查询出大量数据但是只需要获取表中部分列的数据的，我们可以利用索引快速全扫描代替全表扫描来提升性能。索引快速全扫描的扫描方式与全表扫描的扫描方式是一样，都是按区扫描，所以它可以多块读，而且可以并行扫描。为了解决oracle 是行存储需要扫描所有列而产生的。
INDEX FAST FULL SCAN可以多块读，而INDEX FULL SCAN是单块读

INDEX FULL SCAN（MIN/MAX）

表示索引最小/最大值扫描、单块读，该访问路径发生在 SELECT MAX（COLUMN）FROM TABLE 或者SELECT MIN（COLUMN）FROM TABLE等SQL语句中。INDEX FULL SCAN（MIN/MAX）只会访问“索引高度”个索引块，其性能与INDEX UNIQUE SCAN一样，仅次于TABLE ACCESS BY USER ROWID。

4.2单块读与多块读

从磁盘1次读取1个块到buffer cache就叫单块读，
从磁盘1次读取多个块到buffer cache就叫多块读。

如果数据块都已经缓存在buffer cache中，那就不需要物理I/O了，没有物理I/O也就不存在单块读与多块读。

绝大多数的平台，一次I/O最多只能读取或者写入1MB数据，Oracle的块大小默认是8k，那么一次I/O最多只能写入128个块到磁盘，最多只能读取128个块到buffer cache。
在判断哪个访问路径性能好的时候，通常是估算每个访问路径的I/O次数，谁的I/O次数少，谁的性能就好。

4.3为什么有时候索引扫描比全表扫描更慢

索引也需要耗费大量的物理I/O

第五章 表连接方式

驱动表 与 被驱动表

驱动表，即需要从驱动表中拿出来每条记录，去与被驱动表的所有记录进行匹配探测。
驱动表，作为外层循环，若能只进行一次IO把所有数据拿出来最好，这就比较适合顺序读取 。
被驱动表，即里层循环，由于需要不断的拿外层循环传进来的每条记录去匹配，所以如果是适合随机读取的，那么效率就会比较高。如果表上有索引，实际上就意味着这个表是适合随机读取的。
理解驱动表和被驱动表的差异，最本质的问题，需要理解顺序读取和随机读取的差异，内存是适合随机读取的，但是硬盘就不是，对于硬盘来说顺序读取的效率比较好。

5.1　嵌套循环（NESTED LOOPS）

嵌套循环被驱动表必须走索引，走INDEX UNIQUE SCAN或者INDEX RANGE SCAN。
两表关联返回少量数据才能走嵌套循环
外连接走嵌套循环的时候驱动表只能是主表。Hint设置从表为驱动表不会生效。

提问：两表关联走不走NL是看两个表关联之后返回的数据量多少?还是看驱动表返回的数据量多少？
回答：看两表关联之后返回的数据量。
如果两个表是1∶N关系，驱动表为1，被驱动表为N并且N很大，这时两表关联之后返回的数据量会很多。
返回的数据量多，应该走HASH连接。

提问：大表是否可以当嵌套循环（NL）驱动表？
回答：可以，如果大表过滤之后返回的数据量很少就可以当NL驱动表。

5.2　HASH连接（HASH JOIN）

两表关联返回少量数据应该走嵌套循环
两表关联返回大量数据应该走HASH连接。

HASH连接的算法：两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表的“select列和join列”读入PGA中的work area，然后对驱动表的连接列进行hash运算生成hash table，当驱动表的所有数据完全读入PGA中的work area之后，再读取被驱动表（被驱动表不需要读入PGA中的workarea），对被驱动表的连接列也进行hash运算，然后到PGA中的work area去探测hash table，找到数据就关联上，没找到数据就没关联上。

哈希连接只支持等值连接。

嵌套循环被驱动表需要扫描多次，
HASH连接的被驱动表只需要扫描一次。
嵌套循环不需要消耗PGA。
HASH连接的驱动表与被驱动表的连接列都不需要创建索引。
HASH连接没有传值的过程。

OLTP环境一般是高并发小事物居多，此类SQL返回结果很少，SQL执行计划多以嵌套循环为主，因此OLTP环境SGA设置较大，PGA设置较小（因为嵌套循环不消耗PGA）。
而OLAP环境多数SQL都是大规模的ETL，此类SQL返回结果集很多，SQL执行计划通常以HASH连接为主，往往要消耗大量PGA，所以OLAP系统PGA设置较大。

思考：怎么优化HASH连接？
回答：因为HASH连接需要将驱动表的select列和join列放入PGA中，所以，我们应该尽量避免书写select * from....语句，将需要的列放在select list中，这样可以减少驱动表对PGA的占用，避免驱动表被溢出到临时表空间，从而提升查询性能。如果无法避免驱动表被溢出到临时表空间，我们可以将临时表空间创建在SSD上或者RAID 0上，加快临时数据的交换速度。

5.3　排序合并连接（SORT MERGE JOIN）

HASH连接主要用于处理两表等值关联返回大量数据。排序合并连接主要用于处理两表非等值关联，比如>，>=，<，<=，<>，但是不能用于instr、substr、like、regexp_like关联，instr、substr、like、regexp_like关联只能走嵌套循环。
排序合并连接的算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表（Oracle官方认为排序合并连接没有驱动表，笔者认为是有的），然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中（PGA中的work area）匹配数据。

如果两表是等值关联，一般不建议走排序合并连接。因为排序合并连接需要将两个表放入PGA中，而HASH连接只需要将驱动表放入PGA中，排序合并连接与HASH连接相比，需要耗费更多的PGA。即使排序合并连接中有一个表走的是INDEX FULL SCAN，另外一个表也需要放入PGA中，而这个表往往是大表，如果走HASH连接，大表会作为被驱动表，是不会被放入PGA中的。因此，两表等值关联，要么走NL（返回数据量少），要么走HASH（返回数据量多），一般情况下不要走SMJ。

5.4　笛卡儿连接（CARTESIAN JOIN）

两个表关联没有连接条件的时候会产生笛卡儿积，这种表连接方式就叫笛卡儿连接。

5.5　标量子查询（SCALAR SUBQUERY）

当一个子查询介于select与from之间，这种子查询就叫标量子查询

5.9　IN与EXISTS谁快谁慢

我相信很多人都受到过in与exists谁快谁慢的困扰。如果执行计划中没有产生FILTER，那么我们可以参考以下思路：in与exists是半连接，半连接也属于表连接，那么既然是表连接，我们需要关心两表的大小以及两表之间究竟走什么连接方式，还要控制两表的连接方式，才能随心所欲优化SQL，而不是去记什么时候in跑得快，什么时候exists跑得快。如果执行计划中产生了FILTER，大家还需阅读7.1节才能彻底知道答案。

5.10　SQL语句的本质

标量子查询可以改写为外连接（需要注意表与表之间关系，去重），半连接可以改写为内连接（需要注意表与表之间关系，去重），反连接可以改写为外连接（不需要注意表与表之间关系，也不需要去重）。SQL语句中几乎所有的子查询都能改写为表连接的方式，所以我们提出这个观点：SQL语句其本质就是表连接（内连接与外连接），以及表与表之间是几比几关系再加上GROPU BY。