参考《收获,不止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内存的场景:该表使用频率很高,比如数据字典项。