一个PostgreSQL用户眼里的Oracle性能优化

最近刚看完《Oracle性能优化求生指南》。这真是一本好书，全面而又深入的阐述了Oracle性能优化的方方面面，并且全书结构清晰，语言生动易懂。除了感叹作者非凡的功力，也由衷感谢三位译者（都是技术牛人）的辛勤付出。（这让我联想到了手边的《PostgreSQL9.0性能调校》中译版，书本是好书，可惜翻译的太不给力。）
其实我对Oracle一直很少接触，了解不多，这回算是比较正经的窥探了一下Orcale的大概，发现很多东西和PostgreSQL是相通的，但又有不少的差异。下面列举一些，不当之处还望各位网友指正。
查询优化器相关
1，SQL语句缓存
为了避免不必要的解析，Oracle在SGA的共享池中维护一个缓存，来保存最近执行的SQL语句以及对应的执行计划。通过这个缓存(V$SQL等相关视图)还可了解最近执行的SQL的一些相关信息，比如哪个SQL消耗的时间最长。
严格来讲PostgreSQL中好像没有对应的功能。为了避免重复解析，客户端（或者客户端驱动）需要和服务端共同维护一个命名的预备语句（对应于JDBC中的PreparedStatement），并且预备语句的有效范围是会话而不是全局，所以即使利用了预备语句也不能避免重复的解析(另外，目前的pgjdbc驱动不支持StatementCache，即不能跨逻辑连接共享预备语句)。为了查找消耗的时间最长的SQL，PostgreSQL中可以使用pg_stat_statements扩展模块。
点评:
SQL语句缓存确实是个不错的功能，但是如果PostgreSQL的客户端应用程序很好的使用了预备语句也可以在一定程度上达到相同的目的。
2，SQL中的字面常量自动替换为绑定变量
如果参数COURSOR_SHARING被设置成TURE或SIMILAR的话，Oracle可以自动将SQL中的字面常量变换为绑定变量。这样那些只有字面常量不同的多个SQL语句就变成了同一个SQL语句，从而可以利用SQL语句缓存避免重复解析。并且通过查询V$SQL还可以从SQL缓存中识别出是否存在大量仅仅字面常量不同的SQL语句。
PostgreSQL中没有对应的功能。
 点评:
不管从性能上，代码维护性，还是安全角度考虑，应用程序都应该尽量使用绑定变量来处理不同的输入，这可以说是个常识。所以，即使遇到这样的问题也应该先考虑修改应用，Oracle的这个功能真正适用的场景应该不多。

3，绑定变量窥视和自适应游标共享
哪个执行计划最优有时候依赖于绑定变量的具体值，所谓”绑定变量窥视”是指根据第一次绑定的变量值确定最优执行计划。然而这个执行计划可能并不适合于随后绑定的变量值。
”自适应游标共享”是一种比较完美的解决方案，当绑定变量的不同值适合用不同的执行计划来处理时，Oracle为一个SQL语句生成多个不同的执行计划。当给这个SQL传入绑定变量并执行时，Oracle会为其选择合适的执行计划。
根据PostgreSQL手册的前后协议部分的说明，PostgreSQL通常在变量绑定时每次生成一个新的执行计划而不是重用以前的。除非存在一个不依赖于绑定变量值的效率不错的执行计划或根本就没有绑定变量。
需要指出的是，PostgreSQL中SQL的处理通常分为解析，绑定，执行三个步骤。即使每次绑定时都新产生一次执行计划，解析阶段生成的预备语句还是可以重用的。但这有个前提，绑定变量时传入的数据类型必须和解析时提供的变量类型一致，否则这个预备语句也不能重用。（pgjdbc在绑定变量前，会检查一下数据类型和上次发解析时是否相同，不同的话会重发一次解析请求。Npgsql没有类似的逻辑，但我担心可能会带来一些别的问题。）
点评:
Oracle确实处理的非常周到，当然也更复杂。
4，提示
提示(hit)就是一组指示，以注释的形式嵌入到SQL中“指导”优化器如何生成执行计划。比如:
SELECT /*+ FULL(tb1) */  * from tb1 where id < 1
PostgreSQL中如果要人工干预优化器，可以使用enable_bitmapscan之类的参数，但这些参数是针对会话的，对优化器的控制没有提示来的直接，也没有提示那么精细。（注：最近发现有个叫pg_hint_plan的PG扩展已经实现了相同的功能，而且语法形式也是和Oracle一样的）
点评:
如果优化器足够聪明的话，不需要人工干预就应该可以生成最优的执行计划。如果不是，先看看其它地方是不是出了问题，比如统计信息过时，直接干预优化器的做法建议作为最后的选择。考虑到这一点，提示就不是一个很常用的功能了（除非优化器太笨了）。
5，存储提纲（stored outline）
可以用存储提纲保存一个SQL语句的执行计划并在随后应用到指定的会话中，确保该SQL的执行计划是稳定的，不会随着统计信息或数据库配置的改变而改变。
另外还可以使用提示加上一个小技巧篡改存储提纲中的执行计划，这样就可以在无法修改SQL(比如在线系统的SQL)的情况下使用提示干预优化器了。
PostgreSQL中没有对应的功能。
点评:
可以说存储提纲是一项反常规的功能，通常的情况下，我们正是希望优化器能够根据环境的变化选择更优的执行计划。存储提纲只适用在你确信现在执行计划在任何条件下都是最优的，同时又非常害怕优化器会神经错乱地选择其它性能糟糕的执行计划的情况下。这个功能看上去像是个鸡肋，Oracle也已经宣布存储提纲将逐渐被放弃。然而，篡改存储提纲的技巧确实对在线系统的SQL调优提供了一个重要的途径。

6, SQL Profile和SQL调优顾问
SQL Profile是一个由SQL调优顾问产生的与该SQL相关的特定统计信息的集合(比通常的统计信息更丰富)，随后它可以在不修改SQL语句和会话执行环境的情况下影响查询优化器的决定。
SQL调优顾问可以被看作一个离线的优化器，相比运行时优化器，它有很多独特的优点。
1）它可以比运行时优化器有更多的时间去优化SQL语句
2） 在决定一个最终路径之前，它可以运行（至少是部分运行）SQL语句
3） 在索引缺失时它会提出建议
4） 它可以创建SQL Profile以帮助优化器在未来做出更好的决定
5） 可以被逻辑上类似的语句共享，即使这些语句的文本并不完全匹配
因此，在特定的场景下，借助SQL调优顾问生成的SQL Profile，优化器生成的执行计划可能比没有SQL Profile时好很多。
PostgreSQL中没有相对应的功能。也许对PostgreSQL的实现来说，即使多花费了时间和资源，进一步优化SQL语句的效果也不明显吧。
点评：
Oracle的SQL调优顾问还有其他的一些什么顾问（比如访问顾问，内存分配顾问），真的很智能，很高端的样子。(有了这些顾问，是不是连小白都能轻松调优Oracle了)
7，SQL 基线
SQL 基线和存储提纲的目的相似，都是为了确保执行计划的稳定性，并最终要代替存储提纲。可以为特定的SQL创建SQL基线（使用dbms_spm.load_plans_from_coursor_cache），之后这个执行计划就被固化了。以后如果由于环境发生变化，导致有更好的执行计划，优化器可能会生成新的基线，但这个新的基线还处于未接受状态不会立即生效。要使新的SQL基线生效，需要 调用dbms_spm.evolve_sql_plan_baseline,这个过程称为进化基线。
PostgreSQL中没有对应的功能。
点评：
只能说这个功能听上去真的很高大上的样子，我不清楚实际的效果如何。
SQL执行相关:
8，索引和聚簇类型
Oracle提供以下几种主要的索引和聚簇类型。
1)B*树
   默认的索引类型，适用范围最广。
2)位图索引
   适用于数据仓库环境。可以非常高效地处理低基数的列，还能高效的合并多个索引。但是锁的粒度比较大，不建议在OLTP环境中使用。
3)位图联结索引
   通过两张表的联结构造出来的索引。和位图索引一样适用于数据仓库环境。作为联结事实表和维表的索引时，从性能上看，相当于把维表的某一列移动到事实表，再在这一列上建位图索引。
4)散列聚簇表
   记录的位置由主键决定。这样不需要进行索引查找，就可以通过主键快速定位一条记录。有序散列聚簇是它的一个变体，可以连排序都省掉了。
5)索引组织表
   按照B*tree索引的方式组织表数据，和索引的区别在于”叶子”放的是完整的记录而不是指向记录的指针。
6)索引聚簇
   同一个段中保存多张表的内容，共享同一个键的记录保存在一起，可以认为它属于“预联接”的数据。Oracle的很多系统表采用这种聚簇方式存储。
点评:
PostgreSQL没有位图索引，个人认为这是比较欠缺的。另外，PostgreSQL聚簇的形式和通常的不太一样。PostgreSQL的聚簇是一个松散的聚簇，只是临时通过执行一个CLUSTER语句使表的数据有序排列，之后就不维护这个顺序了，这避免了更新的额外消耗。
9，结果集缓存
Oracle中可以使用以下2种结果集缓存优化性能。
1)客户端结果集缓存
   只有使用OCI的驱动程序的11g客户端才可以利用此缓存。客户端结果集缓存不能保证数据是最新的。可以为客户端结果集缓存定义一个最大的有效时间。
2)服务端结果集缓存
   为了确保数据的一致性，可以定义结果集依赖的表，当依赖的表被更新时，该结果集缓存被删除。
PostgreSQL没有提供原生的结果集缓存支持，但通过某些中间件或插件可以达到类似目的。
点评：
PostgreSQL社区的开发态度非常谨慎，也许社区觉得，结果集缓存还没有重要到必须要内置的程度。
10，批量处理
在PL/SQL中可以使用“BULK COLLECT”和“FORALL”以数组的方式成批的处理数据获取和插入，以提高性能。Oralce的jdbc和.Net Data Provider驱动中也有类似的接口或处理。
“BULK COLLECT”和“FORALL”可以说是Oralce在PL/SQL中特有的SQL扩展。但是对jdbc和.Net Data Provider驱动，基本上其他数据库也都有类似的批量处理优化。
11，并行SQL
通过使用PARALLEL提示,可以让Oracle利用并行方式执行语句。
select /*+ PARALLEL(c,2) */ * from c order by …
处理上面这个语句，Oralce会用2个进程扫描，2个进程排序，1个查询协调器进程合并结果。
想要有效的使用并行处理，机器要有多个CPU（或多核），数据分散在多个存储磁盘上。
PostgreSQL尚不支持并行SQL。相关讨论参照:https://wiki.postgresql.org/wiki/Parallel_Query_Execution
12，MERGE语句
MERGE语句处理源表的记录，并根据在目标表中是否有匹配的记录来决定对目标表是应用INSERT还是UPDATE。MERGE是SQL:2003引入的，如果没有MERGE语句，需要先执行一次UPDATE，再有条件的执行一个INSERT。使用MERGE语句不仅应用的逻辑更简单，而且效率更高。
PostgreSQL还没有实现MERGE语句。相关讨论参照: https://wiki.postgresql.org/wiki/SQL_MERGE

13，COMMIT优化
COMMIT发生时为了确保ACID的数据一致性，需要将REDO记录同步到磁盘，但响应缓慢的磁盘会极大的影响事务处理速度。出于性能的考虑，在应用的数据一致性要求可以容忍的情况下可以通过使用BATCH和NOWAIT参数，设置成批量提交或异步提交。
PostgreSQL中也有相似的服务端参数commit_delay和synchronous_commit。
14，直接路径IO
当Oracle认为选择直接路径读取有更多的好处时，在Oralce 11g中引入的自适应直接路径读取算法支持选择直接路径IO。另外，通过APPEND提示还可以启动直接路径插入。
在一些场合，但肯定不是所有的场合，直接路径IO可以通过减少缓冲区高速缓存和重做日志IO的开销来提高性能。
PostgreSQL中对临时表毫无疑问应该也采用的直接路径IO，对于普通的表我不太清楚，可能在特定条件下(比如数据加载)也会用吧。
其他:
15， MTS模式
Oracle和PostgreSQL都是多进程架构，N个后端进程加几个辅助进程通过共享内存联接在一起。但是Oracle多了一个可选的MTS模式。然而细想一下MTS模式有点像PostgreSQL的后端连接池pgbouncer,目的都是为了后端复用进程，减少物理资源消耗，但在处理方式上有比较大的区别。
Pgbouncer是PostgreSQL的外部组件，不像MTS模式是内建在Oracle中的。Pgbouncer本质上提供了一个物理连接池。每个物理连接和一个后端进程静态绑定，会话状态(包括事务状态)又绑定在后端进程中。所以虽然Pgbouncer支持逻辑上的会话连接池，事务连接池和语句连接池这3种不同级别的池，但只有会话连接池能做到完全透明的连接复用，其他的都有一定的限制条件。
MTS相当于提供了一个工作进程池(Dispatcher进程和server进程)，在语句级别复用进程资源。会话的状态保存在SGA里，和工作进程动态绑定。
点评:
Oracle的MTS模式看上去很酷，复用的粒度达到语句级，并且对应用完全透明。Pgbouncer则是一种通用的，非侵入式的架构，更加简单。另外，从减少后端进程数这个目标来讲，客户端连接池有时是更好的选择。

16，闪回日志
将事务修正前的数据保存在闪回日志里，这样万一由于人为失误导致数据丢失，可以从闪回日志里找回来。比起从基础备份捞回数据，闪回日志更方便快捷，但也会带来性能上的额外消耗。
PostgreSQL中没有对应功能。
17，ASMM和AMM
Oracle的内存分为SGA和PGA，SGA内部又分缓冲池，共享池，Java池，大池等。SGA内部如何分配内存使用以及PGA和SGA之间如何分配内存使用才能达到性能最大化依赖于环境，并且是个繁琐的调优任务。ASMM(自动共享内存管理)和AMM(自动内存管理)极大的简化了这一过程，只要提供一些主要的设定目标(比如sga_target=1024M等)，Oralce就可自动并且动态的平衡内存的分配。
PostgreSQL在内存方面没有Oracle那么多种缓存，配置相对简单。
18，ASM
Oracle的自动存储管理(ASM)提供了与Oracle软件包紧密集成在一起的一个存储管理层。DBA可以在直连的普通磁盘或通过存储阵列构成的逻辑磁盘上实现条带，镜像和负载均衡。ASM节省了DBA的时间，以及提供了管理动态数据库环境的灵活性。
PostgreSQL没有对应的功能，DBA可以配置RAID等方式达到相同的目的。
19，RAC
RAC采用是的共享存储的HA和负载均衡架构。每个实例（节点）是对等的，都可以提供读写访问。Oracle为每个数据块指定一个主实例，这个实例负责记录哪个实例最后访问了这个数据块。Oralce实例需要某个数据块时，按照以下的顺序获取。
1） 自己的缓存区
  如果缓存区中已经有此数据块，直接获取。
2）主实例的缓存区
  如找到，被记录为“二路等待”，也称为”二路获得”。
3）另一个实例的缓存区
  主实例的内存中如果不含这个数据块，但记录了另一个实例已经访问过此数据库，则转发这个请求给第3方实例。然后第3方实例返回这个数据块给请求的实例。这被记录为“三路等待” ，也称为”三路获得”。
4）共享磁盘
  如果任何实例的内存中都不包含该数据块，主实例建议请求实例从磁盘获取此数据块。这被称为授权。
块请求既可以要求块的当前读副本，也可以要求一个一致读副本。处理一致读块请求时，所有的块和查询或只读事务开始时一样，是一致的。
PostgreSQL的集群主要是主从架构。支持多个节点同时写入的实现(比如PostgreSQL-XC)好像还不太成熟。
点评：
RAC的集群架构，对HA来说，存在单一的共享存储，可用性要打折扣。对扩展性来讲，它基于一个假设，即性能的瓶颈在CPU和内存。然而更一般的情况下IO才是性能瓶颈，为解决IO瓶颈，必须增强共享存储的吞吐量和网络带宽，直到随着共享存储能力的提升Oracle实例成了新的瓶颈，这时才需要增加Oracle实例的数量。共享存储的吞吐量和网络带宽的能力提升不是水平扩展而是垂直扩展，性能成本曲线不是线性的，这也决定了整体上RAC的扩展不可能带来线性的性能提升。极端的场景下RAC甚至不如单个实例的性能。

感慨：
上面只是罗列了一部分Oralce的功能，足以看出Oracle不愧是高富帅的选择，但是伴随强大功能而来的复杂性也是令人生畏的。