作者介绍
梁敬彬，福富研究院副理事长、公司唯一四星级内训师，国内一线知名数据库专家，在数据库优化和培训领域有着丰富的经验。多次应邀担任国内外数据库大会的演讲嘉宾，在业界有着广泛的影响力。著有多本畅销书籍，代表作有《收获，不止SQL优化》等。
本章会较为简要地给大家介绍一下体系结构知识，然后描述体系结构和SQL优化的关系。最后通过系列扩展的相关优化案例来拓宽我们的视野，从而使我们更深入地了解体系结构的原理。
一、体系知识结构
1、组成
2、原理
Oracle体系结构由实例和数据文件两部分组成。其中实例又由Oracle开辟的共享内存区SGA和一组进程组成。
其中SGA被划分成3部分，这里是知识重点。首先Shared Pool区是用来解析SQL并保存相关执行计划的区域，接下来SQL根据对应的执行计划来获取数据时首先看Data buffer中有没有所需的数据，没有则从磁盘读进Databuffer，下次再访问时可能就不需要从磁盘读取了。当更新SQL语句出现时，Databuffer中的数据变成脏数据，必须要将其写进磁盘。而为了保护这些数据，才有了Log buffer区。
更多的细节就不再累述了，详情阅读《收获，不止Oracle》。
3、体会
（1）体会体系结构中的SGA
Oracle体系结构的SGA部分，如下图所示：
未启动数据库前的SGA分配情况：
启动数据库后的SGA分配情况：
为啥是2485125120字节，请看下图：
原来SGA开辟的就是这么大。
（2）体会体系结构中的进程
未启动数据库前的oracle进程情况：
启动数据库后的Oracle本地进程：
启动数据库后的应用连上来的进程：
启动数据库后查看Oracle应用连接数和总的连接数：
二、体系与SQL优化
接下来我们从共享池、日志及数据缓冲三个方面来描述体系结构与SQL优化的关系，总体思路如下图所示：
1、与共享池相关
首先我们看看体系结构SGA的三大组成之一的共享池Shared pool，这是SQL语句执行中最先访问到的内存部件。
（1）解析优化让第2次执行更快
由于SQL第1次执行时已经完成了语法、语义的判断，完成了解析，保存了优选过的执行计划，第2次执行时Shared Pool中的这些事就可以不用去做了，所以必然执行效率能够提升不少。请看如下例子，首先是环境准备。
第1次执行：
SQL第二次执行：
脚本1 解析优化让第2次执行更快
结论：
第1次执行时间是00: 00: 00.10，Recursive Calls次数为28；
第2次执行时间是00: 00: 00.07，recursive calls次数为0。
很显然，解析的优化让第2次执行更快。
（2）思考绑定变量带来的性能飞跃
现实生活中绝大部分用户都有输入自己手机相关信息来访问某系统的经历，如SQL简单构造：select * from t where nbr=18900000001。可以预见到，系统很快就会出现新的nbr取值的SQL，如select * from t where nbr=18900000002。用户一多，系统中将会出现大量高度相似仅nbr不同的SQL，这些不同的SQL实际上执行计划应该都是一样的，但是在Shared Pool里都要挨个解析，因而做了很多的无用功，由于存储在共享池中，也耗费宝贵资源。
如果用绑定变量，这些SQL都变成select * from t where nbr=:x，这下形成一条SQL，减少了系统大量的解析时间，也节省了共享池资源，性能得到大幅提升。下面我们看一组例子，如下：
未使用绑定变量：
脚本2 未使用绑定变量脚本
使用绑定变量：
脚本3 使用绑定变量脚本
可以看出性能差异非常明显，未使用绑定变量是43s，使用后仅4s。
（3）体会硬解析次数和执行次数
脚本4 体会硬解析次数和执行次数
（4）体会绑定变量的AWR试验
（5）思考绑定变量的TRACE试验
（6）注意静态SQL自动绑定变量
2、数据缓冲相关
（1）缓冲优化让第2次执行更快
脚本5 缓冲优化让第二次执行更快
通过试验一看出，第1次执行的physical reads的值为1038，第2次执行则值为0，物理读瞬间大幅度减少，性能自然就提高了。不过这里细心的读者可能会发现，recursive calls都为282，并没有变化，因为这里我们通过alter system flush shared_pool，特意将解析优化的缓存给取消了。
（2）解析和缓冲优化一起来
脚本6 解析和缓冲优化一起来
接下来的这个试验还请同时观察recursive calls与physical reads，第2次执行后这两个取值皆为0，这是解析和缓存同时优化后的情况。
（3）直接路径读性能略胜一筹
环境准备：
--环境准备（构造一个有100万左右记录的表） drop table t purge; create table t as select * from dba_objects; insert into t select * from t; insert into t select * from t; insert into t select * from t; insert into t select * from t; commit; |
测试普通插入：
测试直接路径插入方式：
脚本7 解析和缓冲优化一起来
可以看出，普通的插入需要的时间是00: 00: 14.81，而直接路径插入的时间是00: 00: 04.22，差异还是非常大的。为啥会有这个差别呢？继续分析上面的试验我们可以看出，普通插入后，继续查询的物理读为0，而直接路径插入后，物理读为27469。这是为啥呢？因为直接路径读写可以绕开SGA，这对于插入而言少做事了，所以性能当然更好。
不过话说回来，优化都是相对的，插入由于不缓存数据，所以插入快了，查询同样也会因为没有缓存数据，而导致接下来的查询就不会快了。
3、日志归档相关
Oracle的体系结构的log buffer部分，如下图所示：
（1）批量提交与否性能差异明显
脚本8 批量提交与否的性能差异
可以看出，未使用批量提交，耗时11.21s，使用后耗时4.26s，有天壤之别。
（2）日志关闭与否对性能的影响
测试直接路径写方式：