Oracle优化配置0001

1、数据库系统和操作系统一样，在计算机上安装成功后，还需要进一步配置和优化，从而使其具有更强 大的功能和运行在最佳状态。如果在设计阶段因为各种因素没有进行较为合理的配置和计划，那么就需要 在后期对数据库系统进行优化。 数据库系统性能的优化，除了在设计阶段对其逻辑存储结构和物理存储结构设计进行优化，使之在满足需 求条件下，时空开销性能最佳外，还可在运行阶段，采取一些优化措施，使系统性能最佳。本专题所讨论 的性能优化主要指运行阶段的性能优化，即讨论如何使用 Oracle 所提供的优化手段来提高系统性能。大多 数性能问题并不是一种孤立的症状，而是系统设计的结果，因此性能优化就集中在那些导致不可接受特征

2、的同一的、固定的和潜在的问题上。优化是数据库设计中 “计划 ”、“设计”、 “监视 ”和“优化”四大步骤的最后 一步。 除了用 Oracle 优化器来优化数据库的性能外， DBA 还可通过优化 Oracle 的参数设置等手段来优化 数据库的性能，对参数的细微优化便能影响系统整体性能。 为了有目的优化系统性能，首先应明确优化目标，然后再根据目标优化各种初始参数的设置，以达到更好 效果。可有如下几个优化目标： 应用程序设计的优化 指定类型 SQL 语句的优化 内存使用的优化 数据存储、物理存储和逻辑存储的优化 网络通信量的优化DBA 可选定上述一个或多个目标来实施优化。性能优化主要是通过优化初始化

3、参数来实现。本专题从以下 几个方面来谈谈如何优化 Oracle 数据库，使其具有最佳性能。(1) 优化初始参数(2) 优化内存(3) 优化 I O(4) 优化资源争用(5) 其它参数优化可变参数的优化在对 Oracle 数据库进行优化时，需要用到许多的参数，其中有一部分参数对系统性能影响较大，这部 分参数叫可变参数。可变参数按其作用可以分为两大类，一大类是起限制作用的，如 OPEN_CURSORS 另一大类是影响系统性能的，如 DB_BLOCK_BUFFERS 。 在进行数据库系统性能优化时，需要熟练掌握和了解一些可变参数。本文讨论了一些对系统性能有较大影 响的参数。限制类可变参数(1) DM

4、L_LOCKS 该参数表明多少个用户，可同时能修改多少张表。例如：有三个用户同时修改二张表，则要求表上的总数 为 6。若置为 0，则组织队列不起作用，其性能会稍有提高。使用该参数时不能用DROP TABLE 、CREATE INDEX 或显式封锁。(2) LICENSE_MAX_SESSION 该参数指出允许并发用户会话的最大数。若此参数为0 ，则不能实施并发。若并发的用户会话数已达到此极限，则只有具有 RESTRICTED SESSION 权限的用户才能连接到服务器。(3) LICENSE_MAX_USERS 该参数指出在一个数据库上可建立的最大用户数。当达到最大值时，便不能再建新用户，可改

5、变此值以放 松限制。在 LICENSE_MAX_SESSION 或 LICENSE_MAX_USER 为 0 时，则并发会话或任何用户都不 能用。若对不同的实例，此参数不同时，则以第一个登录的数据库实例的参数为准。(4) MAX_DUMP_FILE_SIZE 该参数指定操作系统中写跟踪文件的块的最大值。可用此值来限制跟踪文件的空间。(5) OPEN_CURSORS 该参数指明一个用户进程能同时打开光标的最大数，它能限制每个用户进程占用的内存空间量。(6) OPEN_LINKS 该参数指定并发连接到远程数据库的最大用户进程数。若同时引用多个数据库，则应该增大该值。例如： 同时交替访问 A、B 和

6、 C 三个数据库时，若 OPEN_LINKS 设置为 2，则需花费等待连接时间。此参数只用 于分布事务。若该参数设置为 0 ，则不允许进行分布事务处理。(7) PROCESS该参数指定同时连接到 Oracle 服务器上的最大用户进程数 。该参数值包括 6 个后台进程和一个登录 ，因此， 该参数值为 20 ，则只能有 13或14 个并发用户连接到服务器。(8) ROW_LOCKING 该参数指定行封锁方式。若设置为 “ALWAY”S ，则在修改表时只实施行封锁。若设置为 “INTENT”时，则行 封锁只适用于 SELECT FOR UPDATE ，而在修改时实施表封锁。影响系统性能类可变参数(1

7、) CHECKPOINT_PROCESS 该参数根据是否要求检查点而设置成 TRUE 或者 FALSE 。当所有缓冲区的信息写到磁盘时，检查点进程 (CHPT )建立一个静态的点。在归档日志文件中做一个记号表示有一个检查点发生。检查点发生在归档 日志转换的时候或当达到 log_checkpoint_interval 定义的块数的时候。当设置此参数为 TRUE 时，后台进 程 CHPT 便可工作。在检查点期间内，若日志写进程 (LGWR )的性能减低，则可用 CHPT 进程加以改善。(2) DB_BLOCK_CHECKPOINT_BATCH 该参数的值设置得较大时 ，可加速检查点的完成。 当指定

8、的值比参数 DB_BLOCK_CHECKPOINT_BATCH 大时，其效果和指定最大值相同。(3) DB_BLOCK_BUFFERS 该参数是在 SGA 中可作缓冲用的数据库块数。该参数决定 SGA 的大小，对数据库性能具有决定性因素。 若取较大的值，则可减少 I/O 次数，但要求内存空间较大。每个缓冲区的大小由参数 DB_BLOCK_SIZE 决 定。(4) DB_BLOCK_SIZE该参数表示 Oracle 数据库块的大小，以字节为单位，典型值为2048 或 4096 。该值一旦设定则不能改变。它影响表及索引的 FREELISTS 参数的最大值。(5) DB_FILES 该参数为数据库运

9、行时可打开的数据文件最大数目。(6) DB_FILE_MULTIBLOCK_READ_COUNT 该参数表示在顺序扫描时一次 I O 操作可读的最大块数，该最大块数取决于操作系统，其值在4 至 16 或者 32 是比较好。(7) D1SCRETE_TRANSACTION_ENABLED 该参数实现一个更简单、更快的回滚机制，以改进某些事务类型的性能。 当设置为 TRUE 时，可改善某些 类型的事务性能。(8) LOG_ARCHIVE_BUFFER_SIZE 此参数的值依赖于操作系统，它与 LOG_ARCHIVE_BUFFER 参数一起用于调整有归档日志的运行，使其 运行速度尽量加快，但不能快到

10、降低性能。仅当直接归档到磁带设备时才需要增加这些参数的值，重做日 志缓冲区要等待归档日志缓冲区变得可用。( 9) LOG_ARCHIVE_BUFFER该参数指定用于归档的日志时的缓冲区数。(10) LOG_BUFFER该参数指明分配给 SGA 中的日志缓冲区的字节数，该参数值较大时，可减少日志I O 的次数。对于繁忙的系统不宜采用大于或等于 64K 的值。缺省值 般为数据库块的 4 倍。(11)LOG_CHECKPOINT_TIMEOUT 该参数指明两个检查点之间的时间间隔，若指定为 0 时，则说明不允许进行基于时间的检查点。(12 ) LOG_CHECKPOINT_INTERVAL 该参数用

11、来确定检查点进程的执行频率。这个数值设置成取检查点之前处理的重做缓冲区块的数量。(13) LOG_FILES该参数指定运行期间数据库可打开的日志文件数。若需要较大的 SGA 空间，而不需多个日志文件，则可减 少该值。(14) LOG_SIMULTANEOUS_COPIES 该参数是日志缓冲区副本闩锁的最大数，为同时写日志项所用。为提高性能，可设置此参数为两倍的 CPU 数，对单进程系统，该值多数设置为0 ，此时断开闩锁。(15) LOG_SMALL_ENTRY_MAX_SIZE该参数与 LOG_SIMULTANEOUS_COPIES 参数配合使用。若日志项大于此项，则在给缓冲区分配空间并 获得

12、日志复制闩锁之后，用户进程释放日志复制闩锁。(16) OPTIMIZRER_MODE若该参数的值为 RULE ，则 ORACLE 优化器选择基于规则的优化；若设置为 COST ，并且在数据字典中存 在有统计信息，则优化器选择基于代价的优化方法进行优化。(17) SEQUENCE_CACHE_ENTRIES该参数指明在 SGA 中可进行高速缓存的序列数，用于直接存取。该高速缓存区是基于最近最少使用 (LRU) 的算法进行管理的。若此值设置得较高，则可达到较高的并发性。(18) SEQUENCE_CACHE_HASH_BUCKETS 该参数用于加速查看高速缓冲区最近请求的最新序列的桶式地址数，每个

13、桶式地址占 8 个字节。高速缓冲 区以散列表排列，该参数应为质数。(19) SERIALIZEABLE此参数用于保证重复读的一致性。当它设置为 TRUE 时，查询可保证表级读一致，以防止在此查询提交之 前的修改。(20) SHARED_POOL_SIZE该参数指定共享池的大小 ，其中包括共享光标及存储过程 。在多用户系统中 ，较大的 SHARED_POOL_SIZE 值可改善 SQL 语句的执行性能，但较小的值可节省内存。(21) SMALL_TABLE_THRESHOLD该参数决定 SGA 中用于扫描的缓冲区的数目，若表的数目小于该值，则该表可整个地读入高速缓存区。若 表大于该值，则立即重用

14、该缓冲区。一般用缺省值可使性能最好。(22) SORT_AREA_TETAINED_SIZE 这是会话内存的最大数量 ,用于内存排序。当从排序空间提出最后 行时，便释放该内存。若排序要较大的 内存，则分配一临时段，排序便可在盘上进行。用于排序的最大总量可由 SORT_AREA_SIZE 指定，而不 用此参数。可以分配同样大小的多个排序空间，不过一般对于复杂的查询才需要。(23) SORT_AREA_SIZE 该参数用于指定进行外排序 (磁盘 )时所需 PGA 内存的最大数量，以字节为单位。当排序行写入磁盘时，该 内存被释放。增大该参数的值，可改进排序效率。一般不调整该参数，除非排序量很大时才调

15、整。(24) SORT_SPACEMP_SIZE 该参数仅在排序量很大时才调整该参数。可用下式设置该参数，使排序能最佳地使用盘空间：(total_sort_bytes) (SORT_AREA_SIZE) 十 64其中， total_sort_bytes 为： (number_of_records)*sum_of_aver_average_column_sizes+(12*number of(al)(25) SQLTRACE该参数设置为 TRUE 时，便可跟踪，以获得改善性能的信息。因为跟踪会增加开销，所以一般仅在收集信 息时才置为 TRUE 。在实际使用时，可用 ALTER SESSION 命

16、令覆盖它。(26) TRANSACTION该参数设置并发事务的最大数。若此值较大，则需增加 SGA 空间和分配的回滚段数量。缺省值大于 PROCESS 时，可允许递归事务。在 Oracle 数据库体系结构中内存优化也是优化内存内存的优化历来都是数据库或者操作系统优化的重中之重 性能优化的关键 。优化内存主要是通过优化内存结构来提高系统性能 。这里所说的内存结构主要由专用 SQL 及 PL SQL 区、共享池、日志缓冲区和高速缓冲存储区构成。SQL 语句和应用程序优化之后以及SQL 和 PL/SQL由于 0racle 的内存结构需求与应用密切相关，所以内存结构优化应在 输入/输出( I/O )优

17、化之前进行。而且首先要优化操作系统，并在处理共享池前考虑私有 区。优化 SGASGA 本身需要频SGA 全部被驻SGA 是对数据库数据进行快速访问的一个系统全局区，其大小在实例启动时被确定。若 繁地进行释放、分配，则不可能达到快速访问数据的目的。因此为了优化系统性能，应确保 留在实际内存中，而不应采用虚拟内存，以免使整个系统性能下降。SGA 小常识SGA 是 Oracle 为一个实例分配的一组共享内存缓冲区。它包含该实例的数据和控制信息。如果有多个用 户同时与同一个实例相连接时，则其 SGA 中的数据可为多个用户共享，所以也叫它共享全局区。 SGA 在 实例启动时被自动分配，当实例关闭时被收回

18、。 SGA 中的数据和控制信息可被多个进程所使用，但只能由 几个持殊进程能对其进行写操作。 SGA 中所包含的存储区根据其存放的信息类型大致可分为 “数据库缓冲 存储区”、“日志缓冲区 ”、“共享池 ”和“请求和响应队列 ”四大区域。查看 SGA 大小 用户可以通过两种命令方式来查看 SGA 的大小。(1)使用 “SHOWSGA”命令 说明：用户可以连接到 Sever Manager 和数据库，然后执行 “SHOW SGA” 命令来查看 SGA 的大小。如果 数据库实例没有启动，那么使用 “SHOWSGA” 命令将会报错。(2)使用 “SELECT * FROM V$SGA ； ”查询试图命令

19、 说明：使用 “SELECT * FROM V$SGA”; 要在“SVRMGR” 提示符下。预装 SGA可以在 init.ora 文件中将参数 PRE_PAGE_SGA 的值设为 “yes，”而将 SGA 预装到物理内存中，即设置 PRE_PAGE_SGA=yes 。优化专用 SQL 区及 PL SQL 区要优化专用的 SQL 区及 PL SQL 区，就必须知道是否有不必要的语法分析调用，用户必须尽量少的调用Parse 。如果有过多的不必要的语法分析调用，就应当设法减少它。 可用跟踪功能来为每个 SQL 语句输出跟踪信息 ，以检测其语法分析步的统计数字 count 。若分析步的 count统计

20、值与执行步的 count 统计值非常接近，则应减少语法分析调用。 减少语法分析调用的方法是通过所用的应用开发工具（如 PRO*C 、OCI 和 SQL*Forms 等）来控制语法分析调用以及分配和释放专用的 SQL 区的频率，例如：在用 PRO*C 时，可用 HOLD_CURSOR 、 RELEASE_CURSOR 以及 MAX_OPENCURSOR 参数来控制专用 SQL 区。在使用 OCI 时，可使用 OSQL3 或 OPARSE 调用来为 SQL 语句分配专用 SQL 区，用 OCLOSE 调用关闭光标和释放专用 SQL 区。在使 用 SQL *Forms 时，同样也可控制是否重用专用

21、SQL 区：可在触发器级、 Form 级或在运行时减少语法分 析调用，以减少或重用专用 SQL 区。优化日志缓冲区 日志缓冲区也称为重做日志缓冲区，它包含所有变化了的数据块。这些变化的数据块通过 Oracle 日志书写 进程以一种邻接的方式写到重做日志文件中。（1）获取缓冲区活动情况 优化日志缓冲区时，首先应了解缓冲区的活动情况 。这对通过查询动态性能表 （需有 SELECT ANY TABLE 特权）V$SYSSTAT 来获得。SVRMGR select sum（value）Redo Buffer Waits from v$sysstat2 where name=redo log space

22、 wait time;（2 ）计算日志缓冲区的申请失败率 数据库管理员可以通过执行下述语句： SQLselect name,value from v$sysstat where name in（redo entries,redo log space requests） ；查看日志缓冲区的使用情况。查询出的结果可以计算出日志缓冲区的申请失败率。申请失败率 requests/entries 。申请失败率应该接近于 0，否则说明日志缓冲区开设太小，需要增加 ORACLE 数据库的日志缓冲区。（3 ）优化日志缓冲区 如果重做缓冲区的等待大于零，进程就要等待重做日志缓冲区的空间变成可用。此时就会影响数据

23、库的性 能。可以通过逐步提高 init.ora 中参数 LOG_BUFFER 的值来达到优化日志缓冲区的目的。在增加 LOG_BUFFER 的值时，可通过查询动态性能表来了解和检查日志缓冲区的活动情况。优化共享池 共享池由库高速缓存、字典高速缓存和 SQL 区组成。整个共享池的大小由参数 SHARED_POOL_SIZE 确 定。共享池的优化主要考虑库高速缓冲区、数据字典高速缓冲区以及会话期间信息的优化。优化库高速缓冲区 要优化库高速缓冲区，必须首先了解该缓冲区的活动情况。库高速缓冲区的活动统计信息保留在动态性能 表 V$LIBRARYCACHE 中。优化时应使数据字典高速缓存里的内存数据库块

24、尽可能多。（1）确定库高速缓存的性能通过查询 V$LIBRARYCACHE 表（需有 SELECT ANY TABLE 特权）来了解其活动情况，以决定如何优化。 例如：SQL SELECT SUM（pins） ， SUM（reloads） FROM V$LIBRARYCACHE；说明：动态性能表 V$LIBRARYCACHE 中包含有 NAMESPACE 、PINS 和 RELOAD 等列。其中 NAMESPACE 列反映了 SQL 语句及 PLSQL 块的库缓冲区的活动，其值可能是 SQLAREA，TABLE PROCEDURE ，BODY和TRIGGER 等；而 PINS 和 RELOAD

25、S 列给出执行调用时的错误信息 。PINS 列给出 SQL 语句、PL SQL 块及被访问对象定义的总次数； RELOADS 给出 SQL 语句或 PL SQL 块的 隐式分析或对象定义重装载时在库程序缓冲区中发生的错误。（2）查看 reloads 和 pins 的比率 可以使用以下查询语句来查看 reloads 和 pins 的比率：SELECT (SUM(reloads)/SUM(pins) ) 100“Librar yCache Ratio ”FROM V$LIBRARYCACHE ； 用户必须保证 reloads 和 pins 的比率尽可能低，其值应低于 1%, 若 RELOADS P

26、INS 1，则应给缓冲 区分配附加的存储及写等同的 SQL 语句，使 SQL 语句与 PL/SQL 块共享一个共享 SQL 区，这样可减少 错误。如果库高速缓冲区无错误，则可设置初始化参数 CUTSOR_SPACE_FOR_TIME 为 TRUE ，以加速 执行调用。这可使性能稍有改善。若每个用户可用的专用 SQL 区不足时，则不要将 CUTSOR_SPACE_FOR_TIME 设置为 TRUE 。(3 )优化库高速缓存区 优化库高速缓存时，可以通过增加 init.ora 文件中 SHARED_POOL_SIZE 或 OPEN_CURSORS 的参数值 而达到满意的优化比率。优化数据字典缓冲区

27、 数据字典缓冲区在功能上与库高速缓存类似，但主要用于 Oracle 字典高速 SQL 语句。要优化数据字典缓 冲区，也必须先查看数据字典缓冲区的使用情况及效果，然后依此来进行优化。数据字典缓冲区的使用情 况记录在动态性能表 V$ROWCHACHE 中，其中有如下几列：PARAMETER ：记录某类特写数据字典项的统计，其值以 de_开始，例如某文件描述的统计为 de_files。GETS ：是对相应项请求次数的统计。GETTMISSES ：是引起缓冲区出错的数据请求次数。 可用如下语句来查询 V$ROWCHACHE 表：SQLSELECT (SUM(getmisses)/SUM(gets)*1

28、00”DaT aDictionary Cache Ratio ”FROM V$ROWCHACHE ；对于贫繁访问的数据字典缓冲区， GETMISSES 与 GETS 之比要小于 10到 15% 。若大于此百分数时， 则应考虑增加数据字典缓冲区的容量，即要增加 SHARED_POOL_SIZE 或者 DB_BLOCK_BUFFERS 初 始化参数的值。使用多线索服务器时共享池的优化 在多线索服务器结构中，会话期信息被存放在共享池中，它包括 SQL 专用区和排序区。在使用多线索服务 器时，要增大共享池，以满足需要。这也可通过增加 SHARED_POOL_SIZE 的参数值来实现。可通过查询 (要求有 SELECT ANY TABLE 特权)动态性能表 V$SESSTAT 来衡量会话信息的多少。例如： SQ