oracle wrost practics

Oracle最差实践

大量的讨论都是针对Oracle最佳实践的，很少有人讨论最差实践。

http://www.dba-oracle.com/t_worst_practices.htm

DBA的最差实践

欠佳的数据库设计

实用主义至上。<chmetcnv w:st="on" unitname="’" sourcevalue="1980" hasspace="False" negative="False" numbertype="1" tcsc="0"><span lang="EN-US">1980’</span></chmetcnv>s年代磁盘价格$200k/g，数据库设计符合3NF是最佳方法，因为3NF消除冗余，会节省昂贵的磁盘空间。今天磁盘越来越便宜，许多Oracle专家使用denormalization非范式和预连接，数据检索的越来越快。Oracle提供了一系列流行的非范式的工具，一些工具创建了非范式(0NF)的结构。

l 对象表-Oracle提供了嵌套表和varray表，破坏了1NF。

l 物化视图-表被预连接在一起，查询被重写为访问物化视图，Oracle快照同步范式表现的数据为非范式。

Oracle提供工具来管理物化视图，每个简单的事务获取都需要8个表连接是一种Oracle最差实践。

l 范式化数据

范式化数据意味着将数据分割到更小的表中，减少冗余，使得检索和管理数据更加高效。一般来说，如果你发现相同的数据位于多行中，就可能需要将这些数据组织到不同的表中。范式化数据的一些优点如下：

ü 节约磁盘存储，更小的表减少了重复数据，使得完整的数据库更小。

ü 易于维护，如果有数据变化，只需要更新一个地方，如果没有采用范式，就必须更新每个保存数据的地方。

ü 减少I/O，要检索需要的数据，会从磁盘中读取更少的数据块。

ü 易于查询，例如，都是保存“张三”这个姓名，并且想查询一下有多少姓张的朋友，如果姓名是作为一个字段来保存，则需要查询所有的以张开头的数据，但是如果姓和名是分开保存的，检索起来就方便多了。

ü 范式化数据的弊端，必须对表做连接才能重建完整的数据集。

l Oracle非范式化数据结构。

冗余的引入可以避免昂贵的表间连接，可以显著的提高Oracle查询的速度。对于Oracle设计专家来说，适当的引入冗余来提高查询的响应速度是一种挑战。那么何时增加冗余呢：

1. 冗余的引入可以减少重复的多表连接要求

2. 列比较少

3. 列是稳定的，很少更新

ü 快照。通过快照，将表的数据复制到不同区域的数据库中，可以极大的提高查询的响应速度，减少跨网络的访问。

ü VARRAYS。Oracle通过引入VARRAYS表结构，来保存冗余。

ü 物化视图

l Oracle的优化方法

数据库设计和性能的注意事项。最开始设计的Oracle表和索引是影响整个性能最关键的因素，一旦系统在生产环境中运行，几乎是无法改变的。

ü 数据库设计的演化

在最初设计数据库结构时，设计者往往会应用范式，范式可以很好的控制冗余。随着计算机技术的发展，冗余不再是代价昂贵了，设计人员增加一些冗余来提高查询速度。

ü 优化方法概述

数据库优化没有银弹，在优化数据库时，我们可以采用自顶向下的四步优化法。但也不一定完全按照下面的步骤顺序来做，对于一个经过规划的生产库而言，出现1、2问题的可能性要小于4的可能性，调试TOP SQL会更快见到效果。

<shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></path><lock aspectratio="t" v:ext="edit"></lock></shapetype><shape id="_x0000_i1025" style="WIDTH: 237pt; HEIGHT: 207.75pt" alt="" type="#_x0000_t75"><imagedata o:href="http://www.dba-oracle.com/images/big_pic.gif" src="file:///C:%5CDOCUME~1%5CADMINI~1%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image001.gif"></imagedata></shape>

1. 服务器和网络的优化。CPU过载、频繁的内存交换、磁盘I/O瓶颈，当出现这些问题时，应该优先考虑服务器和网络环境的优化。

2. 实例优化。优化Oracle SGA，检查所有的初始化参数，确认配置正确。在这一步主要查找db_block_buffers、shared_pool_size和sort_area_size这些参数的配置是否合理。也会考虑一些如optimizer_mode参数的设置。随着Oracle的进步，对SGA的管理也越来越简单。

3. 对象优化。这阶段的优化对象主要是表、索引，调整一些诸如PCTFREE、PCTUSED和FREELISTS等参数，Oracle在这方面也逐渐发展，比如使用本地管理表空间替代字典管理表空间。对象优化的范围还应包括存储过程、视图、序列等Oracle对象的优化，如将频繁使用的存储过程锁定到内存中。

4. SQL优化。这是最耗费时间和精力的优化阶段，因为会有成百上千个SQL访问数据库。我们可以针对最常出现问题的SQL进行调整，查看其执行计划，合理的使用hint。

不合适的索引

在SQL执行过程中，过度 I/O的最主要原因之一就是索引的缺失，特别是基于函数的索引。索引的不适用是最常见的一种最差实践。

ü Blocksize和索引树的结构。多种blocksize会提高Oracle索引的性能，也有案例表明重建索引后查询速度也得到了改善。现在Oracle<chmetcnv w:st="on" unitname="g" sourcevalue="10" hasspace="False" negative="False" numbertype="1" tcsc="0">10g</chmetcnv>的自动管理任务AMT会自动重建索引结构以及收集统计信息。

ü SQL聚集因子优化索引访问。dba_indexes.clustering_factor字段说明了Oracle优化器如何使表数据和索引同步。当这个字段接近数据块数量时，表行与索引同步。列值的可选择性、db_block_size参数和dba_tables.avg_row_len共同帮助优化器决定使用索引还是使用全表扫描。如果某个数据列值的可选择性很高，并且聚集因子很小，则索引扫描通常是执行最快的方法。

<shape id="_x0000_i1026" style="WIDTH: 315pt; HEIGHT: 141pt" alt="index scan" type="#_x0000_t75"><imagedata o:href="http://www.dba-oracle.com/images/art_otn_cbo_f4.gif" src="file:///C:%5CDOCUME~1%5CADMINI~1%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image002.gif"><font size="3"></font></imagedata></shape>

当聚集因子很高，db_block_size比较大而avg_row_len比较小的时候，DBA有时周期性的重新调整表行之间的顺序或者使用单表聚集来维护行的顺序。这种方法会将所有临近的行放到一个数据块中，去除全表扫描，查询速度最高可提升30倍。

当聚集因子很高

ü 索引重建I/O等待事件分析

ü 单独的SQL语句优化

不合适的初始化参数

不合适的统计管理

环境配置最差实践

一个服务器只有一个实例

ü 易于维护

ü 更好的共享计算资源

不恰当的变更控制测试

这是最差实践中的最差实践。

不合适的测试环境

导入导出统计值，使开发环境更接近生产环境。

没有变更控制过程

通过版本控制工具来控制过程、代码块、对象等。

没有足够的测试实例

建议要有4个环境，开发环境、测试环境(用于单元测试)、发布前测试环境、产品环境。

没有性能跟踪

使用STATSPACK，在<chmetcnv w:st="on" unitname="g" sourcevalue="10" hasspace="False" negative="False" numbertype="1" tcsc="0"><span lang="EN-US"><font face="Times New Roman">10g</font></span></chmetcnv>中使用AWR来跟踪性能问题。

缺乏安全管理

授权、角色、VPD来堵住安全漏洞。

没有预警机制

OEM有内建的预警机制。

非标准的外部环境

每个数据库使用不同的脚本、不同的别名以及非标准的文件存放位置是非常糟糕的实践。

PL/SQL最差实践

http://gojko.net/oracle/oraclebadprac.pdf

1. 超长的PL/SQL代码

2. 混乱的全局变量

3. 随处可见的SQL语句，对数据访问点封装

4. 起不到作用的异常处理，自定义的异常处理

5. 固定的变量长度和变量类型

6. 没有单元测试代码

7. 大量使用代码值而非代码名称

8. 滥用对象表

9. 不区分包中存储过程的可见性，以及不使用包

10. 不使用版本控制工具管理Oracle对象

11. 大量的IF/ELSE，使用type避免

12. PL/SQL在非自治事务中控制事务

13. 绑定变量

14. 基于函数的索引

15. 慎用ROWNUM=1

16. 慎用动态SQL

17. 异常发生时不判断游标状态

18. 对ROWID进行直接访问