Oracle数据库优化 另辟蹊径优化Ｏｒａｃｌｅ数据库

　　硬件升级并不是提高应用系统性能的惟一或者最佳方法，在无法对应用系统编码进行大范围更新的情况下，也许还有其他更好的途径来减少选型中的盲目性。 　　一个选用Oracle为数据库的应用系统的规模一般较大，用户一般也较多，效率对这样的系统来说是非常重要的，但是在实际应用中，要么由于应用程序设计的原因，要么由于数据库结构的不合理，或者是Oracle系统参数设置没有考虑实际情况等原因，往往造成应用系统运行效率不够高，性能没有达到最优状态。常用的解决办法是对Oracle主机进行系统硬件上的升级和扩容，带来投资的增加。实际上，合理的进行Oracle应用系统的优化可以解决这些问题。
　　应用系统的优化并不等同于应用程序优化，更不能等同于SQL的优化，它是一个综合的过程。应用程序的好坏对性能的影响是显而易见的。但是，所有的这些优化都应该在应用程序设计和编码的时候进行，一旦一个应用系统完成之后很难对它进行全部的重新设计和编码，因此我们必须探索其他的优化途径。
　　
　　数据库结构的优化
　　
　　作为应用程序展开的基础，库结构的设计和分布的好坏对应用系统的性能有直接的影响，因此库结构优化可以说是优化的最基本的一个部分。可以从以下两方面着手展开：一是逻辑结构的优化，二是物理结构的优化。
　　
　　● 逻辑结构优化
　　逻辑结构优化用通俗的话来说就是通过增加、减少或调整逻辑结构来提高应用的效率，下面就对应用作用很大的索引进行讨论。
　　索引可以极大提高检索性能，一个建有合理索引的数据库应用可能比一个没有索引的数据库应用效率高几十倍，但是并不是索引越多越好，在那些经常需要修改的数据列上建立索引，将会导致索引B+树的不断重组,必将造成系统性能的下降和存储空间的浪费，一个不合理的索引也将导致相同的结果，因此在实际应用中，应该对应用系统中的操作语句进行统计分析，并根据统计分析的结果合理的建立索引。
　　下面是一些使用索引的原则：
　　● 为了提高索引效率，应该在经常出现的索引条件中的数据域建立索引，如果某些数据是经常一起出现在检索条件中的，那么建立复合索引。
　　● 为了提高多表连接效率，对经常用于表连接操作的数据列应该建立索引，如果是多域连接则建立复合索引。
　　● Oracle在外键上并不建立索引，因此在利用外键连接的表格中，外键上也应该建立索引。
　　● 对于复合索引来说，索引中的数据域顺序十分重要，要和应用中实际应用的检索和连接顺序保持一致。
　　
　　● 物理结构优化
　　数据库的数据最终是存储在物理磁盘上的，对数据进行访问就会对这些物理磁盘进行读写，因此对于这些物理存储的优化是系统优化的一个重要部分。对物理存储进行优化虽然并不能减少对物理存储的读写次数，但却可以使这些读写尽量并行，减少磁盘读写竞争，从而提高效率，也可以通过对物理存储进行精密的计算减少不必要的物理存储结构扩充，从而提高系统效率。
　　
　　（1）磁盘读写并行优化
　　对于数据库的物理读写，Oracle系统本身会进行尽可能的并行优化，例如在一个最简单的表检索操作中，如果表结构和检索域上的索引不在一个物理结构上，那么在检索的过程中，对索引的检索和对表的检索就是并行进行的。物理存储结构优化的一个方面就是要合理地分配逻辑结构的物理存储地址，使Oracle对这些逻辑结构的访问可以并行进行，减少磁盘的I/O竞争。当然由于物理存储磁盘的限制，不可能无限制地分开这些可能的逻辑结构。
　　（2）操作并行的优化是基于操作语句的统计结果，因此操作并行的优化首先就要统计各个表的访问频率，表之间的连接频率，根据这些数据按如下原则分配表空间和物理磁盘：
　　减少系统进程和用户进程的磁盘I/O竞争;减少用户进程之间的磁盘I/O竞争;把需要连接的表格在空间/物理磁盘上分开;把高频访问的表格在表空间/磁盘上分开;把经常需要进行检索的表格的表结构和索引在表空间/物理磁盘上分开。
　　
　　（3）减少存储结构的扩展
　　如果应用系统的数据库比较脆弱，并在不断的增长或缩小，这样的系统经常出现这样的情况，在非动态变化周期内，效率合理，但是当在动态变化周期内的时候，性能却很差，这是由于Oracle的动态扩展造成的。在动态扩张的过程中，Oracle必须根据存储的要求，在创建行、行变化获取缺省值时，扩展和分配新的存储空间，而且表格的扩展往往并不是事情的终结，还可能导致数据文件、表空间的增长，而实际上如果不经过仔细的计算，所有的这些扩展在数据库系统中是经常发生的，正是这些扩展导致在线系统反应缓慢。
　　实际上，所有的这些扩展对应用逻辑没有任何意义，用户并不需要承担这些扩展。对于这样的系统，最好的办法就是在建立的时候预先分配足够的大小和合适的增长幅度。在一个对象建立的时候要根据应用充分地计算他们的大小，然后再根据这些数据来定义对象INITIAL、NEXT和MINEXTENTS的值，使数据库在物理存储上和动态增长次数上达到一个比较好的平衡点，即这些对象既不经常发生增长，也不过多的占用数据库。
　　
　　内存参数优化
　　
　　内存对于任何系统性能的影响都是很明显的，对Oracle系统也同样如此，正确设置内存参数的大小，可能是提高Oracle性能最重要的一个措施。主要有以下三个参数：
　　
　　● SHARED_POOL_SIZE
　　共享池的大小，保持一个高的高速缓存命中率对于数据库性能是非常重要的，如果高速缓存的命中率过低，那么就需要调整SHARED_POOL_SIZE的大小来提高命中率，可以用如下命令来查看是否需要调整这个参数：
　　Select sum(pins)/sum(reloads) form v$librarycache
　　如果得出的结果大于1，那么就需要调整这个参数。
　　
　　● DB_BLOCK_SIZE
　　DB_BLOCK_SIZE是Oracle内存块的大小，一般来说，这个值越大，对数据库性能越有利，它的取值范围一般为2KB,这个值最好在数据库建立之前就确定好，因为一旦数据库建立，那么改变这个值就比较麻烦，对大型数据库尤为如此。
　　
　　● DB_BLOCK_BUFFERS
　　DB_BLOCK_BUFFERS是在内存中保留的Oracle块的数量，每个缓冲器的大小为一个块的大小，这个值应该足够大以保证有效命中高速缓冲存储器，但也不是越大越好，这样会导致操作系统频繁执行页交换。
　　这三个参数的确定对数据库的性能有很大的影响，一般来说前两个参数应该在系统内存允许的范围内尽量大一点，第三个参数需要根据实际应用系统反复调整，确定一个适当的值。
　　例如，表1、2、3、4为典型的 TPC-C测试中DB_BLOCK_BUFFERS对数据值的影响 (操作系统：AIX ；数据库系统：Oracle 8i；处理器：4x375mHz；内存：4G，RAID)。
　　
　　并行参数优化
　　
　　在Oracle系统中，为了提高服务的效率，数据库自己可以对一些数据库操作进行优化使他们并行执行，为了更好地使大多数数据库能够并行，有必要对一些并行参数进行优化。
　　
　　● RALLEL_MIN_SERVERS
　　RALLEL_MIN_SERVERS是最小并行查询服务器进程参数，为了优化并行查询服务器进程，可以设置这个参数为想要的并行操作数目。可以通过观察V$PQ_SYSSTAT数据字典中的STATISTIC列，通过观察并行服务器是否过度提交(OVER_COMMITTED)或提交不足(UNDER_COMMITTED)来调整这个参数。
　　
　　● PARALLEL_MAX_SERVERS
　　PARALLEL_MAX_SERVERS最大并行查询服务器进程参数，一般这个参数应该为: 2×CPU数目×并行用户数。
　　除了这两个参数对并行有较显著的影响外，还有其他一些参数也影响Oracle的并行，包括：PARALLEL_SERVERS_IDLE_TIME（停止过多进程决策参数），OPTIMIZER_PERCENT_PARALLEL（在决定最好执行方案时是否考虑并行），ALWAYS_ANTI_JOIN（反连接并行参数）等等。
　　在合理的设置好这些参数之后，在操作语言（SQL）中就可以加上并行提示来提高执行效率，如在通过表来扫描数据时，可以用PARALLEL提示来进行并行扫描，在通过索引扫描，可以用PARALLEL_INDEX提示来进行并行索引扫描等等。
　　
　　数据库动态调整优化
　　
　　一个应用系统经过优化，但并不能保证这个系统就永远平稳高效运行，因为随着库的动态变化，数据库的物理存储会发生各种变化，其中一个比较影响数据库性能的因素就是数据库碎片的产生。碎片的整理包括表空间碎片的整理和对象碎片的整理。
　　
　　● 表空间碎片的整理
　　这种碎片来源于非正常计划终端和在表空间中重新创建数据库对象，一些比较小的自由空间分布在表空间中形成了表空间碎片，一般来说，表空间碎片对数据库性能的影响不是特别大。
　　可以用以下的命令消除表空间碎片：alter tablespace tablespacename coalescese,另外一种消除表空间碎片的方法就是导出数据库之后再导入数据库。
　　为了避免这种碎片，可以将对象分成大小和增长性质相似的组，将它们的扩展区定为相同的大小，这样所有对象就可以分享通过删除对象而重新分配或获得的扩展区。
　　
　　● 对象碎片的整理
　　当数据库表格中的某些行被删除后，块中就会留下自由空间，这些自由空间在它们所处的块返回自由列表之前是不会再被使用的，而要使块重新返回自由列表，那么必须在这块中删除足够的行。对象碎片主要有三种：行迁移碎片、行连接碎片和过度扩展段碎片。对象碎片在数据库中留下自由空间空洞，对性能十分有害，消除这种对象空间的方法有两种：一是导出导入，这是一种比较彻底的方法；如果表比较大，可以通过临时表的方法消除行连接和行迁移碎片，假如需要消除碎片的表为EMP。
　　第一步：运行$ORACLE_HOME/rdbws/admin目录下的utlchain.sql，这个命令建立chained_rows表，并把所有表的连接和迁移行的ID和表名存入这个表。
　　
　　第二步：create table temp_emp as
　　
　　select * from emp rowid in
　　
　　(select rowid from chained_emp where table_name=’emp’)
　　
　　第三步：delete from emp where rowid in
　　
　　(select head_rowid from chained_rows where table_name=’emp’)
　　
　　第四步: insert into emp select * from temp_emp。
　　
　　当然，如果数据库表格的预分配进行得好的话，可以避免很多的碎片，但是并不能消除它，所以碎片的整理对于Oracle的性能优化是必要的。
　　
　　总结
　　
　　采用什么优化手段与应用系统的程序编写、资源状况是紧密结合的，其中逻辑结构的设置与应用程序的SQL统计结果密切相关，物理存储的优化也是与SQL语句统计结果相关。效果则受Oracle服务器的磁盘数目的限制。内存多少、CPU个数则决定了参数设置的最优状态，而数据库的变化大小决定了是否需要碎片整理。同时这些优化手段的优化效果也不大一样，合理逻辑结构的优化可以在性能上有数量级的提高，而物理存储优化、参数设置优化的效果最好也只能提高几倍，而碎片的整理大多数情况下只是在性能要求苛刻或数据库极不稳定的情况下进行。所以在具体优化时，要分析系统情况，找出造成性能低下的原因和可以利用的资源，并采用相应的优化手段，提高系统的效率。
　　当然，优化也不是一劳永逸的，一旦系统有了变化，就要采取相应的手段重新优化，比如内存、CPU增加了，就要调整参数；磁盘增加了，就要重新分散数据库对象；碎片增加了，就要整理碎片，因此只要应用系统存在，优化就需要随时进行。