Oracle的性能优化Word下载.docx

Oracle的性能优化Word下载.docx

《Oracle的性能优化Word下载.docx》由会员分享，可在线阅读，更多相关《Oracle的性能优化Word下载.docx（19页珍藏版）》请在冰豆网上搜索。

一般情况，我们都会为系统分配一定的虚拟内存，用以Oracle数据交换、排序、HASH应用等，即用部分磁盘区域补充物理内存。

为了避免I/O争用，不要将虚拟内存空间与其他Oracle数据库数据文件放在同一分区上。

合理设置磁盘I/O：

Oracle的运行是离不开磁盘读写的，Oracle的配置文件、数据文件、控制文件、日志文件都存储于磁盘上。

大家都知道，I/O请求包括数据定位、等待、数据传输三个主要部分，其中又属数据定位花费的代价最高。

合理的设置服务器磁盘I/O，可以避免I/O争用，提高Oracle的性能。

通常，我们采用将数据文件、日志文件分磁盘（分分区）存储，以避免I/O争用。

但是，现代技术的发展，磁盘RAID技术已经我们提供了很好的优化方案。

选择合理的RAID方案，可以提高Oracle性能。

常用RAID方案：

RAID0：

增强I/O，提高读写性能。

但是，不具备可恢复性。

RAID1：

提供镜像和全数据冗余，增强备份；

但是，需要两倍的存储量。

RAID0+1：

先进行磁盘并用，再实现镜像。

提供了良好的读写性能和数据冗余；

但是需要较大的存储代价。

RAID1+0：

先实现镜像，再实现磁盘并用。

与RAID0+1相同，但是更具可用性。

RAID5：

既实现了磁盘并用，并用最少的代价实现了镜像。

为目前最多采用的RAID方案。

但是，RAID5在写入数据时会造成性能损失；

所以适用于读多写少的操作。

3、操作系统

增加虚拟内存，提高排序、HASH操作性能。

合理进行磁盘碎片整理，提高可读效率。

为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。

如果你有足够的连续磁盘空间建立数据文件，那么你就很容易避免数据文件产生碎片。

运行控制面板的“系统”，在高级选项卡中设置“性能选项”到“后台程序”，这提供优先权给后台应用程序。

删除不需要的网络协议，只保留TCP/IP协议。

最好禁止系统里非必须的服务，如果时而需要某些服务功能，可将启动类型设置为“手动”，要做到这一点首先同网络管理员验证实际的服务需求。

Oracle是内存消耗大户，不要在执行下列功能的系统上运行Oracle数据库：

主域或备份域控制器（Windows2000下都称域控制器）、文件服务器、打印服务器、远程访问服务器、路由器、代理或防火墙。

4、其他软件的影响

最好关闭占用系统资源较多的程序。

二、Oracle数据库性能参数的调整

在对Oracle数据库进行优化时，需要用到许多的参数，其中有一部分参数对系统性能影响较大，这部分参数叫可变参数。

可变参数按其作用可以分为两大类，一大类是起限制作用的，如OPEN_CURSORS；

另一大类是影响系统性能的，如DB_BLOCK_BUFFERS。

限制类可变参数：

（1）DML_LOCKS

该参数表明多少个用户，可同时能修改多少张表。

例如：

有三个用户同时修改二张表，则要求表上的总数为6。

若置为0，则组织队列不起作用，其性能会稍有提高。

使用该参数时不能用DROPTABLE、CREATEINDEX或显式封锁。

（2）LICENSE_MAX_SESSION

该参数指出允许并发用户会话的最大数。

若此参数为0，则不能实施并发。

若并发的用户会话数已达到此极限，则只有具有RESTRICTED 

SESSION权限的用户才能连接到服务器。

（3）LICENSE_MAX_USERS

该参数指出在一个数据库上可建立的最大用户数。

当达到最大值时，便不能再建新用户，可改变此值以放松限制。

在LICENSE_MAX_SESSION或LICENSE_MAX_USER为0时，则并发会话或任何用户都不能用。

若对不同的实例，此参数不同时，则以第一个登录的数据库实例的参数为准。

（4）MAX_DUMP_FILE_SIZE

该参数指定操作系统中写跟踪文件的块的最大值。

可用此值来限制跟踪文件的空间。

（5）OPEN_CURSORS

该参数指明一个用户进程能同时打开光标的最大数，它能限制每个用户进程占用的内存空间量。

（5）OPEN_LINKS

该参数指定并发连接到远程数据库的最大用户进程数。

若同时引用多个数据库，则应该增大该值。

同时交替访问A、B和C三个数据库时，若OPEN_LINKS设置为2，则需花费等待连接时间。

此参数只用于分布事务。

若该参数设置为0，则不允许进行分布事务处理。

（6）PROCESS

该参数指定同时连接到Oracle服务器上的最大用户进程数。

该参数值包括6个后台进程和一个登录，因此，该参数值为20，则只能有13或14个并发用户连接到服务器。

影响系统性能类可变参数：

（1）CHECKPOINT_PROCESS

该参数根据是否要求检查点而设置成TRUE或者FALSE。

当所有缓冲区的信息写到磁盘时，检查点进程（CHPT）建立一个静态的点。

在归档日志文件中做一个记号表示有一个检查点发生。

检查点发生在归档日志转换的时候或当达到log_checkpoint_interval定义的块数的时候。

当设置此参数为TRUE时，后台进程CHPT便可工作。

在检查点期间内，若日志写进程（LGWR）的性能减低，则可用CHPT进程加以改善。

（2）DB_BLOCK_CHECKPOINT_BATCH

该参数的值设置得较大时，可加速检查点的完成。

当指定的值比参数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

该参数为数据库运行时可打开的数据文件最大数目。

（6）DB_FILE_MULTIBLOCK_READ_COUNT

该参数表示在顺序扫描时一次I／O操作可读的最大块数，该最大块数取决于操作系统，其值在4至16或者32是比较好。

（7）D1SCRETE_TRANSACTION_ENABLED

该参数实现一个更简单、更快的回滚机制，以改进某些事务类型的性能。

当设置为TRUE时，可改善某些类型的事务性能。

（8）LOG_ARCHIVE_BUFFER_SIZ

此参数的值依赖于操作系统，它与LOG_ARCHIVE_BUFFER参数一起用于调整有归档日志的运行，使其运行速度尽量加快，但不能快到降低性能。

仅当直接归档到磁带设备时才需要增加这些参数的值，重做日志缓冲区要等待归档日志缓冲区变得可用。

（9）LOG_ARCHIVE_BUFFER

该参数指定用于归档的日志时的缓冲区数。

（10）LOG_BUFFER

该参数指明分配给SGA中的日志缓冲区的字节数，该参数值较大时，可减少日志I／O的次数。

对于繁忙的系统不宜采用大于或等于64K的值。

缺省值-般为数据库块的4倍。

（11）LOG_CHECKPOINT_TIMEOUT

该参数指明两个检查点之间的时间间隔，若指定为0时，则说明不允许进行基于时间的检查点。

（12）LOG_CHECKPOINT_INTERVAL

该参数用来确定检查点进程的执行频率。

这个数值设置成取检查点之前处理的重做缓冲区块的数量。

（13）LOG_FILES

该参数指定运行期间数据库可打开的日志文件数。

若需要较大的SGA空间，而不需多个日志文件，则可减少该值。

（14）LOG_SIMULTANEOUS_COPIES

该参数是日志缓冲区副本闩锁的最大数，为同时写日志项所用。

为提高性能，可设置此参数为两倍的CPU数，对单进程系统，该值多数设置为0，此时断开闩锁。

（15）LOG_SMALL_ENTRY_MAX_SIZE

该参数与LOG_SIMULTANEOUS_COPIES参数配合使用。

若日志项大于此项，则在给缓冲区分配空间并获得日志复制闩锁之后，用户进程释放日志复制闩锁。

（16）OPTIMIZRER_MODE

若该参数的值为RULE，则ORACLE优化器选择基于规则的优化；

若设置为COST，并且在数据字典中存在有统计信息，则优化器选择基于代价的优化方法进行优化。

（17）SEQUENCE_CACHE_ENTRIES

该参数指明在SGA中可进行高速缓存的序列数，用于直接存取。

该高速缓存区是基于最近最少使用（LRU）的算法进行管理的。

若此值设置得较高，则可达到较高的并发性。

（18）SEQUENCE_CACHE_HASH_BUCKETS

该参数用于加速查看高速缓冲区最近请求的最新序列的桶式地址数，每个桶式地址占8个字节。

高速缓冲区以散列表排列，该参数应为质数。

（19）SERIALIZEABLE

此参数用于保证重复读的一致性。

当它设置为TRUE时，查询可保证表级读一致，以防止在此查询提交之前的修改。

（20）SHARED_POOL_SIZE

该参数指定共享池的大小，其中包括共享光标及存储过程。

在多用户系统中，较大的SHARED_POOL_SIZE值可改善SQL语句的执行性能，但较小的值可节省内存。

（21）SMALL_TABLE_THRESHOLD

该参数决定SGA中用于扫描的缓冲区的数目，若表的数目小于该值，则该表可整个地读入高速缓存区。

若表大于该值，则立即重用该缓冲区。

一般用缺省值可使性能最好。

（22）SORT_AREA_TETAINED_SIZE

这是会话内存的最大数量,用于内存排序。

当从排序空间提出最后-行时，便释放该内存。

若排序要较大的内存，则分配一临时段，排序便可在盘上进行。

用于排序的最大总量可由SORT_AREA_SIZE指定，而不用此参数。

可以分配同样大小的多个排序空间，不过一般对于复杂的查询才需要。

（23）SORT_AREA_SIZE

该参数用于指定进行外排序（磁盘）时所需PGA内存的最大数量，以字节为单位。

当排序行写入磁盘时，该内存被释放。

增大该参数的值，可改进排序效率。

一般不调整该参数，除非排序量很大时才调整。

（24）SQLTRACE

该参数设置为TRUE时，便可跟踪，以获得改善性能的信息。

因为跟踪会增加开销，所以一般仅在收集信息时才置为TRUE。

在实际使用时，可用ALTERSESSION命令覆盖它。

TRANSACTION

该参数设置并发事务的最大数。

若此值较大，则需增加SGA空间和分配的回滚段数量。

缺省值大于PROCESS时，可允许递归事务。

三、数据库设计的调整

1、合理设计和管理表

Oracle数据库数据应当按两种类别进行组织：

频繁访问的数据和频繁修改的数据。

对于频繁访问但是不频繁修改的数据，内部设计应当充分考虑数据的存取需求、常用表的大小、特殊的计算、数据的物理存储等，合理增加冗余列、派生列，或重新组表等，尽量降低连接操作的需求、索引数目，减少表的个数，从而提高查询速度。

对于频繁修改但并不频繁访问的数据，内部设计应当以减少了数据冗余，节约存储空间，相应逻辑和物理的I/O次数减少，同时加快增、删、改速度为设计思想。

只有把两者合理地结合在一起，才能相互补充，发挥各自的优点。

合理利用表分区：

分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。

这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。

避免出现行连接和行迁移：

在建立表时，由于参数pctfree和pctused不正确的设置，数据块中的数据会出现行链接和行迁移，也就是同一行的数据不保存在同一的数据块中。

如果在进行数据查询时遇到了这些数据，那么为了读出这些数据，磁头必须重新定位，这样势必会大大降低数据库执行的速度。

因此，在创建表时，就应该充分估计到将来可能出现的数据变化，正确地设置这两个参数，尽量减少数据库中出现行链接和行迁移。

控制存储碎片：

碎片（fragmentation）是对一组非邻接的数据库对象的描述。

碎片意味着在执行数据库的功能时要耗费额外的资源（磁盘I/O，磁盘驱动的循环延迟，动态扩展，链接的块等），并浪费大量磁盘空间。

当两个或多个数据对象在相同的表空间中，会发生区间交叉。

在动态增长中，对象的区间之间不再相互邻接。

为了消除区间交叉将静态的或只有小增长的表放置在一个表空间中，而把动态增长的对象分别放在各自的表空间中。

在createtable、、createindex、createtablespace、createcluster时，在storage子句中的参数的合理设置，可以减少碎片的产生。

4、别名的使用

别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快1.5倍。

5、回滚段的交替使用

由于数据库配置对应用表具有相对静止的数据字典和极高的事务率特点。

而且数据库的系统索引段、数据段也具有相对静止，并发现在应用中最高的负荷是回滚段表空间。

把回滚段定义为交替引用，这样就达到了循环分配事务对应的回滚段，可以使磁盘负荷很均匀地分布。

6、分离表和索引

总是将你的表和索引建立在不同的表空间内（TABLESPACES）.决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里.同时,确保数据表空间和索引表空间置于不同的硬盘上.

五、索引Index的优化设计

1、管理组织索引

索引可以大大加快数据库的查询速度，索引把表中的逻辑值映射到安全的RowID，因此索引能进行快速定位数据的物理地址。

但是有些DBA发现，对一个大型表建立的索引，并不能改善数据查询速度，反而会影响整个数据库的性能。

这主要是和SGA的数据管理方式有关。

ORACLE在进行数据块高速缓存管理时，索引数据比普通数据具有更高的驻留权限，在进行空间竞争时，ORACLE会先移出普通数据。

对一个建有索引的大型表的查询时，索引数据可能会用完所有的数据块缓存空间，ORACLE不得不频繁地进行磁盘读写来获取数据，因此在对一个大型表进行分区之后，可以根据相应的分区建立分区索引。

如果对这样大型表的数据查询比较频繁，或者干脆不建索引。

另外，DBA创建索引时，应尽量保证该索引最可能地被用于where子句中，如果对查询只简单地制定一个索引，并不一定会加快速度，因为索引必须指定一个适合所需的访问路径。

2、聚簇的使用

Oracle提供了另一种方法来提高查询速度，就是聚簇（Cluster）。

所谓聚簇，简单地说就是把几个表放在一起，按一定公共属性混合存放。

聚簇根据共同码值将多个表的数据存储在同一个Oracle块中，这时检索一组Oracle块就同时得到两个表的数据，这样就可以减少需要存储的Oracle块，从而提高应用程序的性能。

四、SQL语句的调整

Oracle在解析SQL语句时采用三种优化器（两种优化模式）：

a.RULE（基于规则）b.COST（基于成本）c.CHOOSE（选择性）

设置缺省的优化器,可以通过对OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS。

你当然也在SQL句级或是会话（session）级对其进行覆盖设定。

为了使用基于成本的优化器（CBO,Cost-BasedOptimizer）,你必须经常运行analyze命令,以增加数据库中的对象统计信息（objectstatistics）的准确性.如果数据库的优化器模式设置为选择性（CHOOSE）,那么实际的优化器模式将和是否运行过analyze命令有关.如果table已经被analyze过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器（RBO，Rule-BasedOptimizer）。

在缺省情况下,ORACLE采用CHOOSE优化器,为了避免那些不必要的全表扫描（fulltablescan）,你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.

OPTIMIZER_MODE初始化参数

其值列表及说明如下：

参数

说明

CHOOSE

此为缺省值。

优化器既可以使用基于成本的优化方法（CBO），也可以使用基于规则的优化方法（RBO），其决定于是否有可用的统计信息。

1、 

如果在被访问的表中，至少有一个表在数据字典中有可用的统计信息存在，则优化器使用基于成本的方法。

2、 

如果在被访问的表中，只有部分表在数据字典中有可用的统计信息，优化器仍然会使用基于成本的方法，但是优化器必须为无统计信息的表利用一些内部信息去尝试其他的统计，比如分配给这些表的数据块的数量等，这可能会导致产生不理想的执行计划。

3、 

如果在被访问的表中，没有一个表在数据字典中有统计信息，则优化器使用基于规则的方法。

ALL_ROWS

不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞吐量为优化目标。

FIRST_ROWS_n

不论是否有统计信息存在，优化器都使用基于成本的方法，并以最快的速度返回前n行数据集，n可以是1,10,100,1000。

FIRST_ROWS

优化器使用成本与试探法混合的方式，去寻找一个可以最快返回前面少数行的执行计划。

注：

CBO使用试探法产生的执行计划，其成本可能会比不使用试探法要大。

FIRST_ROWS可用于向后兼容和计划稳定性。

RULE

不论是否有统计信息存在，优化器都会使用基于规则的方法。

可以在SESSION中改变优化器模式：

ALTERSESSIONSETOPTIMIZER_MODE。

1、在初始化参数文件中加入如下语句，可以在实例级改变CBO优化目标：

OPTIMIZER_MODE=FIRST_ROWS_1

2、下面的语句可以改变当前SESSION的CBO优化目标：

ALTERSESSIONSETOPTIMIZER_MODE=FIRST_ROWS_1

可以改变CBO优化目标的Hints：

使用如下Hints可以单独为具体的SQL指定CBO优化目标，SQL语句中Hints能够覆盖OPTIMIZER_MODE初始化参数。

FIRST_ROWS（n），n为任意正整数。

FIRST_ROWS

ALL_ROWS

CHOOSE

RULE

了解了优化器模式后，我们需要了解Oracle访问数据表的方式：

Oracle采用两种访问表中记录的方式：

a.全表扫描

　全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块（databaseblock）的方式优化全表扫描.

Oracle何时会使用全表扫描

在以下情况中优化器会使用全表扫描：

1、无可用索引

如下面例子：

SELECTlast_name,first_name

FROMemployees

WHEREUPPER（last_name）='

TOM'

last_name字段有索引，但在查询中使用了函数，因此该查询不会使用索引。

如果想让这个查询走索引，则需要建立函数索引createindexind_upper_lastnameonlast_name（upper（last_name））。

特别要注意的是隐式转换，比如colx字段是varchar2型但存放数字：

wherecolx=123456，这时会发生隐式转换TO_NUMBER（colx），此时colx上的索引也会失效。

2、大量数据

如果优化器认为查询将会访问表中绝大多数的数据块，此时就算索引是可用的也会使用全表扫描。

3、小表

如果一个数据表的记录数较少，只需要一次I/O就能扫完，则使用全表扫描要比使用索引的成本低，此时会使用全表扫描。

如果有这样小表访问频率又高，通常把它固定在内存中为好altertabletable_namestorage（buffer_poolkeep）。

4、并行

如果在表一级设置了较高的并行度，如altertabletable_nameparallel（degree10），通常会使CBO错误的选择全表扫描。

通常不建议在表级的设置并行。

并行查询通常可以提高全表扫描的性能，建议在语句级用HINTS来实现并行，如/*+full（table_name）parallel（table_namedegree）*/。

b.通过ROWID访问表

你可以采用基于ROWID的访问方式情况,提高访问表的效率,,ROWID包含了表中记录的物理位置信息..ORACLE采用索引（INDEX）实现了数据和存放数据的物理位置（ROWID）之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高。

Oracle何时使用Rowid

并不是每个索引扫描都伴随着rowid的访问，如果索引中包含了被访问的所有字段，则不再需要通过rowid来访问表。

注意：

Rowid是Oracle表示数据存储的内部方法，它可能会由于版本的改变而改变。

不推荐通过在WHERE中指定rowid来访问数据，因为行迁移和行链接会导致rowid变化，exp/imp也会使rowid变化。

Oracle索引扫描方式：

∙索引唯一扫描（IndexUniqueScans）

∙索引范围扫描（IndexRangeScans）

∙索引降序范围扫描（IndexRangeScansDescending）

∙索引跳跃扫描（IndexSkipScans）

∙全