第五章 且慢，感受体系结构让SQL飞

参考《收获，不止SQL优化》作者: 梁敬彬 / 梁敬弘

一、 Oracle体系结构

利用Oracle的体系结构来进行优化，主要是利用SGA共享内存区的使用原理。

Share pool：SQL语句执行最先访问到的内部部件，保存了SQL执行时选择的执行计划，结果集（/* resul_cache /、函数中有SQL）
Data buffer：查询表数据首先从data buffer找，找不到就从磁盘读入data buffer
Redo buffer：更新插入commit后，Data buffer中的数据变成了脏数据，需要写进磁盘，为了保护这些数据，有了Redo buffer

二、 SGA的分配情况和相关参数

三、共享池、日志缓冲区、数据缓存区与SQL优化的关系

Share pool

1、优化：硬解析在执行计划的统计信息里会有一个递归调用次数recursive calls，软解析没有这个过程
2、应用：
2.1 绑定变量《06-体系结构之share pool应用-绑定变量.sql》
2.2 缓存结果集
2.2.1《06-体系结构之share pool应用-缓存结果集-hint-result_cache》
2.2.2《06-体系结构之share pool应用-缓存结果集-函数结果集》

Data buffer

1、 优化：在执行计划的统计信息里会有物理读信息physical reads（从磁盘读到数据缓冲区），如果数据已经在缓冲区存在，就减少了从磁盘读取的这个过程。
2、 应用：
2.1 直接路径插入方式《06-体系结构之data buffer应用-直接路径插入方式.sql》
2.2 数据读进缓冲区并固定，防止被挤出《06-体系结构之data buffer应用-keep.sql》

redo buffer

1、优化：更新插入commit后，Data buffer中的数据变成了脏数据，需要写进磁盘，为了保护这些数据，有了Redo buffer
2、应用：
2.1 批量提交，减少日志切换的次数
2.2 日志关闭：ALTER TABLE T1 NOLOGGING;再插入。

SGA优化应用总结

1、 绑定变量减少硬解析次数；
2、 批量提交减少日志切换次数；
3、 特定场景（比如库表大量数据还原到本地进行测试）可结合直接路径插入和关闭日志。

四、习题

习题1：
好处：利用降低硬解析次数来减少SQL执行的时间。比如银行系统输入账号查账户名和余额信息，只有谓语部分变量，其余都相同，如果每次解析时间为0.001s，每天查询100W次，那么每天就可以节省1000s，单个查询看不出效果，整体来看节省了很多时间和内存资源。
坏处：写法相对复杂一点，可读性没有那么高。如果表数据变动量和频率比较大，那么会导致后面的SQL使用的执行计划不是效率最高的。也有时候表数据量一致，变量不同，数据库选择的是不同的执行计划（>10 考虑回表太多走全表，>10000 数据量较少走了索引）。
所以一般就知道执行计划一定的某一个的时候（输入手机号查客户信息等），用绑定变量。

习题2：
插入时绕过了将目标表数据再写入到SGA的过程。

习题3：
使用缓存结果集的场景：SQL中相关表数据很少更新，或者对系统表的一些比较固定的查询。
用keep内存的场景：该表使用频率很高，比如数据字典项。