Oracle 数据库最佳实践

孟利青 王升元 李海刚

应用设计

1.应用必须使用绑定变量（尤其是OLTP型应用）；

2.频繁使用的小表要放入缓存中；

3.频繁使用的index需要放入库缓存的keep池中；

4.不使用select * from xxxxx for update；如果可能的话，考虑使用select *from xxxxx for update no wait替代；

5.加大序列的cache值，可以减少对于X$SEQ等基表的锁争用，但是会造成序列的不连续性，应用程序不要将任何商业逻辑建立在序列的完全连续性上。

6.在RAC环境下使用sequence，sequence的cache属性不建议使用缺省值（20），需要增加 cache size，如cache size 10000（可以根据业务需求定，如使用较频繁的设置为更多）。

7.对于较小的表或者访问较快的表，不使用parallel且不设置degree；对于通常的并行操作，通过设置并行参数（instance_groups和parallel_instance_group）将各自节点发起的请求在一个节点完成；

8.建表时必须指明所存储的表空间。

9.表和索引建立在不同表空间上。

10.生成建表脚本时非空的列放在表的前部，可空的列放置在表的后部。

11.不允许将表建立在SYSTEM表空间上。

12.小表（数据量小于5000条记录为标准）不需要创建索引。

13.对于OLTP应用，分区表使用分区索引。

14.分区索引如必须包含分区列，通常将分区列按序放置在分区索引的末尾。

15.建立分区索引必须指明表空间，不允许只写一个LOCAL。

16.单个表上索引的个数不超过5个。

17.将记录差别数最多的列放在索引顺序的最前面。

18.索引数据的重复率不能超过20%。

19.进行order by column desc排序时，创建column desc索引。

20.物化视图的基表必须创建主键，刷新间隔时间最小为3分钟。

21.在有大量数据导入表中的情况下，不使用触发器。

22.只允许从其它数据库中查询少量数据时使用dblink。

23.不使用dblink更新其它数据库中的数据。

部署

1.尽可能主要是根据应用访问的数据进行划分，主要是减少不同数据库节点之间数据的交互；连接方式上，最好手工指定连接到特定节点，取消负载均衡，并打开failover；

2.对于表空间，建议使用自动段空间管理（ASSM）；

3.对于存储频繁更新的数据的表空间或者表，建议设置较大的pctfree，以避免行迁移和行链接；

4.尽可能把应用数据表空间、应用的索引表空间以及相应得分区表空间分布在独立的物理卷上。

5.把UNDO、TEMP、REDOLOG分布在不同的物理卷上。

6.RAC的private、public IP严格要求要在不同网段，两个IP都要求进行网卡绑定：HP使用APA，AIX使用EthernetChannel，按主备方式进行，需要保证网卡绑定后从ORACLE看到的是一个固定的逻辑设备。

SQL语句编写

索引

1.一般情况下，使用索引可以缩短查询语句的执行时间，提高系统的执行效率，但是要避免以下两种过度索引的情况出现：

2.对一个表建立了过多的索引，从而造成维护索引所需要的时间超过使用索引所降低的时间，从而造成整个系统效率的下降，这一般发生在对一些进行大量更新的表上面。因此一个联机表上的索引，最多不要超过5个。

3.由于索引数据的区分度不够，造成了使用索引而引起的效率的下降，这一般发生在对数据进行大的统计分析的时候。可以通过指定全表扫描等提示(hint)来避免。

4.复合索引是使用多个数据列的索引，第一个字段的数据区分度非常重要，也是影响一个联合索引效率的关键所在。

LIKE运算符

1.在应用程序中为了使用方便，对字符型变量进行比较时经常使用LIKE运算符进行字符模式的匹配。

2.需要注意的是对于LIKE运算，如果通配符%出现在字符串的尾部或中间，LIKE运算符将可以使用索引进行字符串的匹配，否则如果通配符%出现在字符串的开始，则LIKE必须使用全表扫描的方式去匹配字符串，这将产生较大的系统负荷。

3.一般情况下，为了提高系统的效率，我们希望用户能够在通配符的左端提供较多的数据信息以降低查询的数量。

NULL值

1.NULL值是系统中目前尚无法确定的值，在Oracle数据库系统中NULL是一个比所有的确定值都大的值，然而又不能用大于小于等于运算符来比较，对NULL值的处理只能用是与否来判定，所有的对NULL值的判定都会引起全表扫描，除非同时使用其它的查询条件。

改写查询语句

1.关联子查询与非关联子查询

a)对于一个关联子查询，子查询是每行外部查询的记录都要计算一次，然而对于一个非关联子查询，子查询只会执行一次，而且结果集被保存在内存中。

b)因此，通常在外部查询返回相对较少的记录时，关联子查询比非关联子查询执行得更快；而子查询中只有少量的记录的时候，则非关联子查询将会比关联子查询执行得更快。

2.尽量用相同的数据类型的数据进行比较，以避免发生数据转换

a)SQL语言对于数据类型不像JAVA和C++那样进行严格的数据类型检查，不同种数据间可以进行某些运算，但是在做数据操作时需要数据库进行隐含的类型转换，在大数据量的查询中，由于要对每一个数据项做同样的操作，会造成时间和CPU处理能力的浪费。

b)实际应用中通常发生的隐含的数据类型的转换有：

l字符型到数字型的转换，如：SELECT ‘1234’+3 FROM DUAL等

l数字型到字符型的转换，如：UPDATE DEPT SET EMPNO=5678等

l日期型到字符型的转换，如：UPDATE EMP SET DNAME=SYSDATE等

c)上述的转换都是隐含发生的，在实际使用中要避免使用不同类型的数据操作。

减少排序的发生

排序是数据库中执行频度比较大的一种操作，根据排序执行的范围不同又可以分为内排序和外排序。我们希望数据库中的排序操作的数量能够被尽量的减少同时每个排序的时间能够缩短。为此我们可以：

1.使用UNION ALL来代替UNION

2.添加索引。在表连接的时候使用索引可以避免排序的发生，比如添加了合适的索可以使连接方式由排序合并连接(Sort Merge Join)转变为索引的嵌套循环连接(IndexedNestted Loop Join)。

3.在DISTINCT，GROUPBY，ORDER BY子句涉及到的列上创建索引。

4.使用较大SORT_AREA_SIZE

5.在用户的临时表空间上使用大的extent大小。

使用并行查询

并行查询适合下列情况：

1.全表扫描的查询语句

2.返回大数据量的查询所改造的语句

3.其它一些数据操作中的查询子句

对于较大的数据量的查询，我们可以使用提示(hint)来强制数据库使用并行查询，在Oracle数据库中，并行查询的优先级为语句提示(hint)，表的定义，数据库初始化参数。

减少死锁的发生

在Oracle数据库中大量的数据库的锁都是行级锁，不同的会话间竞争同一条记录的可能性较小，同时Oracle数据库中提供了自动的死锁检测机制来避免数据库的死锁，保证数据库系统的可用性。因此一般情况下应用系统不需要特殊的设计来解决系统的死锁问题，但是在下列情况下系统可能出现死锁：

1.表A上的列n上有一个索引，表B上的列m使用A上的列n作为外键，然后表A的列n上的索引被删除，此时更新表B上列m将造成对表A的表级锁，会导致死锁的发生。

2.应用大量的使用SELECT ……FOR UPDATE语句造成系统不必要的加锁。

对于第一种情况要对出现死锁的相关表进行检查，确认是否相关索引被错误的删除。对于第二种情况要修改应用，避免对数据的不必要的加锁。

集合运算符的使用

Oracle数据库的集合运算包括: UNION， UNION ALL， INTERSECT和MINUS操作。

一般情况下当两个集合中的数据都比较多时，集合运算都是比较耗时的操作，使用时需要谨慎小心。如果可能，可以使用UNION ALL操作代替UNION操作。

限制表连接操作所涉及的表的个数

对于数据库的连接操作操作，我们可以简单的将其想象为一个循环匹配的过程，每一次匹配相当于一次循环，每一个连接相当于一层循环，则N个表的连接操作就相当于一个N-1层的循环嵌套。

一般的情况下在数据库的查询中涉及的数据表越多，则其查询的执行计划就越复杂，其执行的效率就越低，为此我们需要尽可能的限制参与连接的表的数量。

1.3-5个表的处理方法

a)对于较少的数据表的连接操作，需要合理的确定连接的驱动表，从某种意义上说，确定合理的驱动表就是确定多层循环嵌套中的最外层的循环，可以最大限度的提高连接操作的效率，可见选择合适的驱动表的重要性。

b)RBO模式下，在SQL语句中FROM子句后面的表就是我们要进行连接操作的数据表，Oracle 按照从右到左的顺序处理这些表，让它们轮流作为驱动表去参加连接操作，这样我们可以把包含参与连接的数据量最少的表放在FROM子句的最右端，按照从右到左的顺序依次增加表中参与连接数据的量。

c)CBO模式下，则不需要考虑表放置的位置。

2.5个表以上的处理方法

a)对于涉及较多的表(>5+)的数据连接查询，其查询的复杂度迅速增加，其连接的存取路径的变化更大，存取路径的个数与连接的表的个数的阶乘有关：当n=5时存取路径＝1X2X3X4X5=120个，而当连接的表的个数为6时存取路径变为1X2X3X4X5X6=720个，数据库优化器对于数据的存取路径的判断近乎为不可能，此时完全依赖与用户的语句书写方式。

b)对于较多的表的连接，要求开发人员查询返回的结果能够有所预测，同时判断出各个参与连接的表中符合条件的记录的数量，从而控制查询的运行时间。

c)同时为了提高查询的效率，此时可以把部分表的一些连接所形成的中间结果来代替原来的连接表，从而减少连接的表的数目。

3.对表连接操作涉及的表数目不应多于8个表

l如果查询语句拥有过多的表连接，那么它的执行计划过于复杂且可控性降低，容易引起数据库的运行效率低下，即使在开发测试环境中已经经过充分的测试验证，也不能保证在生产系统上由于数据量的变化而引发的相关问题。应该在应用设计阶段就避免这种由于范式过高而导致的情况出现。

限制嵌套查询的层数

应用中影响数据查询的效率的因素除了参与查询连接的表的个数以外，还有查询的嵌套层数。对于非关联查询，嵌套的子查询相当于使查询语句的复杂度在算术级数的基础上增长，而对于关联查询而言，嵌套的子查询相当于使查询语句的复杂度在几何级数的基础上增长。

因此，降低查询的嵌套层数有助于提高查询语句的效率。

对嵌套查询层数的限制要求：如果查询语句拥有过多的嵌套层数，那么会使该查询语句的复杂度高速增加，应该在数据库设计阶段就避免这种情况出现，不应多于5层。

灵活应用中间表或临时表

在对涉及较多表的查询和嵌套层数较多的复杂查询的优化过程中，使用中间表或临时表是优化、简化复杂查询的一个重要的方法。

通过使用一些中间表，我们可以把复杂度为M*N的操作转化为复杂度为M+N的操作，当M和N都比较大时M+N

下面显示了一个使用中间结果集来替代多表操作的例子。

改写复杂查询的技巧

1.转换连接类型顺序

2.把OR转换为UNION ALL

3.区分不同的情况使用IN或EXISTS对于主查询中包含较多条件而子查询条件较少的表使用EXISTS，对于主查询中包含较少条件而子查询条件较多的表使用IN。

4.使用合理的连接方式

操作系统和主机

1.内部互连的连接方式：RAC之间的内部通讯网络（inter-connect）建议不使用交叉直连(crosscable)，Oracle不支持这种模式，一定要使用SAN(switch)的连接方式(如，交换机)，直连方式的稳定性差，在网络故障时，两个节点都会down或hang ；需要使用千兆网线（光纤）连接千兆网卡（光纤卡）；

2.关闭操作系统CLUSTER 软件中网卡的failover功能， 如HACMP 中的IP failover功能，MC SERVERS GUARD如果有类似功能也建议关闭。可以采用网卡绑定的方式实现网卡的failover功能；

3.如果使用raw device，建议使用AIO；操作系统内核参数MAX_ASYNC_PORTS设置要大于数据库参数PROCESSES；

4.使用netstat –s检查，不可出现网络buffer overflow

5.平均单次I/O （以Oracle数据块大小为8K为例），响应时间不超过12毫秒。