Oracle的性能优化

Oracle性能优化

Oracle 性能优化就是通过合理安排资源、调整系统参数使Oracle 运行更快、更节省资源。Oracle性能优化包括查询速度优化、更新速度优化、Oracle服务器优化等。

1.优化简介

优化 Oracle数据库是数据库管理员和数据库开发人员的必备技能。Oracle 优化，一方面是找出系统的瓶颈，提高 Oracle 数据库整体的性能；另一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。

Oracle数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，提高系统的反应速度。例如，通过优化文件系统，提高磁盘I/O的读写速度；通过优化操作系统调度策略，提高Oracle在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。

从内存中直接读取数据的速度远远大于从磁盘中读取数据。影响内存读取速度的因素有两个，包括内存的大小和内存的分配、使用和管理方法。Oracle提供了自动内存管理机制，所以用户只需要手动分配内存即可。

Oracle中的内存主要包括两部分：系统全局区和进程全局区。它们既可以在数据库启动时进行加载，也可以在数据库使用中进行设置。

1.1修改系统全局区

系统全局区，简称为SGA，是System Global Area的缩写。SGA是共享的内存机构，主要存储的是数据库的公用信息，SGA也被称为共享全局区。SGA主要包括共享池、缓冲区、大型池、Java池和日志缓冲区。

 

查看当前数据库的SGA状态，命令如下：

SQL> show parameter sga;

 

需要注意的结果有两个：sga_max_size和sga_target。sga_max_size是为SGA分配的最大内存，sga target指定的是数据库可管理的最大内存。如果sga target值为0,表示关闭共享内存区。

 

在Oracle中，管理员还可以通过视图 v$sgastat来查看SGA的具体分配情况。命令如下：

SQL> SELECT * FROM v$sgastat;

 

客户端接收到查询语句后，首先进行语法分析，然后是语义分析，最后才是执行步骤。在执行步骤以前的操作就是SQL语句的预处理，这些预处理都是在共享池中进行缓存，缓存的标示是根据SQL语句所形成的Hash 值。服务器收到SQL语句是根据Hash 值在共享池中查找是否已经有预处理的SQL语句，如果存在，则直接进行数据库操作，否则将进行语法分析。对于共享池来说，存在命中率的概念，也就是直接从共享池中获取执行计划的成功率。成功率越高，代表数据库的性能越高。因此，共享池命中率是影响SQL语句的重要指标。

 

修改SGA内存大小，命令如下：

SQL> alter system set sga_max_size=2000M scope=spfile;

scope=spfile表示设置作用到数据库启动文件中，一旦数据库重启，则该参数将立即重启。

 

修改参数sga_target为2000MB，代码如下：

SQL> alter system set sga_target =2000M scope=spfile;

 

数据库重启后，SGA的大小已经被成功修改了。

1.2修改进程全局区

进程全局区简称为PGA。每个客户端连接到Oracle服务器都由服务器分配一定内存来保持连接，并将在该内存中实现用户私有操作。所有用户连接的内存集合就是Oracle数据库的PGA。

 

查看PGA的状态，命令如下：

show parameter pga;

 

参数pga_aggregate_target可以指定PGA内存的最大值。当pga_aggregate_target值大于0时，Oracle将自动管理PGA内存。

 

修改PGA的大小，命令如下：

SQL>alter system set pga_aggregate_target=500M scope=both;

scope=both表示同时修改当前环境与启动文件spfile。

 

2.优化查询

查询是数据库中最频繁的操作，提高查询速度可以有效地提高 Oracle 数据库的性能。

2.1分析查询语句的执行计划

如果想要分析SQL语句的性能，可以查看该语句的执行计划，从而分析每一步执行是否存在问题。

查看执行计划的方法有以下两种：

2.1.1通过设置 AUTOTRACE查看执行计划

设置AUTOTRACE的具体含义如下：

（1）SET AUTOTRACE OFF：此为默认值，即关闭AUTOTRACE。

（2）SET AUTOTRACE ON EXPLAIN：只显示执行计划。

（3）SET AUTOTRACE ON STATISTICS：只显示执行的统计信息。

（4）SET AUTOTRACE ON：包含（2）、（3）两项的内容。

（5）SET AUTOTRACE TRACEONLY：与（4）相似，但不显示语句的执行结果。

 

通过设置AUTOTRACE查看执行计划，命令如下：

SQL> set autotrace on;

 

在查询结果中，Id表示一个序号，但不是执行的先后顺序，执行的先后根据缩进来判断；Operation表示当前操作的内容；Rows表示当前操作的 Cardinality,Oracle 估计当前操作的返回结果集；Cost（%CPU）表示Oracle计算出来的一个数值，用于说明SQL执行的代价；Time表示 Oracle 估计当前操作的时间。

2.1.2使用EXPLAIN PLAN FOR语句查看执行计划

使用EXPLAIN PLAN FOR语句可以查看执行计划。具体语法格式如下：

EXPLAIN PLAN FOR SQL语句；

 

通过设置EXPLAINPLAN FOR语句查看执行计划，命令如下：

EXPLAIN PLAN FOR SELECT * FROM dual;

2.2索引对查询速度的影响

Oracle中提高性能的一个最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此，索引对查询的速度有着至关重要的影响。使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。

如果查询时没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。

合理的索引可以提高查询的速度，但不是索引越多越好。在执行插入语句的时候，Oracle要为新插入的记录建立索引。所以，过多的索引会导致插入操作变慢。原则上是只有查询用的字段才建立索引。

2.3使用索引查询

索引可以提高查询的速度。但并不是使用带有索引的字段查询时，索引都会起作用。

使用索引有几种特殊情况，在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用，下面重点介绍这几种特殊情况。

2.3.1使用LIKE关键字的查询语句

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。

2.3.2使用多列索引的查询语句

Oracle可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中第1个字段时，索引才会被使用。

2.3.3使用 OR关键字的查询语句

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

2.4优化子查询

Oracle 支持子查询，使用子查询可以进行 SELECT 语句的嵌套查询，即一个 SELECT 查询的结果作为另一个SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，Oracle需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

在Oracle中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能会更好。连接之所以更有效率，是因为Oracle不需要在内存中创建临时表来完成查询工作。

3.优化数据库结构

一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果。合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

3.1将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而使查询速度变慢。

3.2增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

3.3增加冗余字段

设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加入冗余字段可以提高查询速度。

表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。

冗余字段会导致一些问题。比如，冗余字段的值在一个表中被修改了，就要想办法在其他表中更新该字段，否则就会使原本一致的数据变得不一致。分解表、增加中间表和增加冗余字段都浪费了一定的磁盘空间。从数据库性能来看，为了提高查询速度而增加少量的冗余大部分时候是可以接受的。是否通过增加冗余来提高数据库性能，这要根据实际需求综合分析。

3.4优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况，可以分别进行优化。

对于MyISAM引擎的表，常见的优化方法如下：

3.4.1禁用索引

对于非空表，插入记录时，Oracle会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。禁用索引的语句如下：

ALTER index index_name unusable;（index name是禁用索引的名称。）

 

重新开启索引的语句如下：

ALTER index index_name usable;

 

3.4.2禁用唯一性检查

插入数据时，Oracle会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启。禁用唯一性检查的语句如下：

ALTER TABLE table_nameDISABLE CONSTRAINT constraint_name;

table_name 是表的名称，constraint_name是唯一性约束的名称。

 

开启唯一性检查的语句如下：

ALTER TABLE table_nameENABLE CONSTRAINT constraint_name;

3.4.3使用批量插入

插入多条记录时，可以使用一条INSERT语句插入一条记录，也可以使用一条INSERT语句插入多条记录。插入一条记录的INSERT语句情形如下：

INSERT INTO 表 VALUES('值1','值2','值3','值4');INSERT INTO 表 VALUES('值5','值6','值7','值8')INSERT INTO 表 VALUES('值9','值10','值11','值12')

使用一条INSERT语句插入多条记录的情形如下：

 

INSERT INTO 表 VALUESSELECT '值1','值2','值3','值4' from dualUnion allSELECT '值5','值6','值7','值8' from dualUnion allSELECT '值9','值10','值11','值12' from dual;

第2种情形的插入速度要比第1种情形快。

 

4.优化Oracle服务器

优化 Oracle服务器主要从两个方面来优化，一方面是对硬件进行优化；另一方面是对Oracle服务的参数进行优化。这部分的内容需要较全面的知识，一般只有专业的数据库管理员才能进行这一类的优化。对于可以定制参数的操作系统，也可以针对Oracle进行操作系统优化。

4.1优化服务器硬件

服务器的硬件性能直接决定着 Oracle 数据库的性能。硬件的性能瓶颈，直接决定 Oracle数据库的运行速度和效率。针对性能瓶颈，提高硬件配置，可以提高Oracle数据库的查询、更新的速度。优化服务器硬件的方法如下：

4.1.1配置较大的内存

足够大的内存，是提高Oracle数据库性能的方法之一。内存的速度比磁盘I/O快得多，可以通过增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘I/O。

4.1.2配置高速磁盘系统

减少读盘的等待时间，提高响应速度。

4.1.3合理分布磁盘I/O

把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。

4.1.4配置多处理器

Oracle是多线程的数据库，多处理器可同时执行多个线程。

4.2优化Oracle的参数

通过优化Oracle的参数可以提高资源利用率，从而达到提高Oracle服务器性能的目的。为了访问数据库中的数据，Oracle数据库为所有用户提供一组后台进程，并且有一些存储结构专门用来存储最近的有关对数据库访问的数据。这些存储区域可以通过减少对数据库文件的I/0次数来改善数据库性能。

数据库实例就是用来访问数据库文件集的一个存储结构以及后台进程的集合。它使一个单独的数据库可以被多个实例访问。决定实例的组成以及大小的参数存储在文件init.ora中。这个文件在实例启动时候需要装载，也可以在运行中被装载。

 

通常需要设置的参数如下：

4.2.1DB_BLOCK_BUFFERS

该参数决定了数据库缓冲区的大小，这部分内存的作用主要是在内存中缓存从数据库中读取的数据块，数据库缓冲区越大，为用户已经在内存中的共享数据提供的内存就越大，这样可以减少所需要的磁盘物理读写次数。

4.2.2shared_pool_size

参数 shared_pool size的作用是缓存已经被解析过的SQL语句，使其能被重用使用，而不用再解析。SQL语句的解析非常消耗CPU的资源，如果一条SQL语句已经存在，则进行的仅是效解析，这将大大提高数据库的运行效率。当然，这部分内存也并非越大越好，如果分配的内存太大，Oracle 数据库为了维护共享结构，将付出更大的管理开销。这个参数的设置建议在150~500MB之间。如果系统内存为1GB，该值可设为150~200MB；如果为2GB，该值设为250~300MB；每增加1GB内存，该值增加100MB；但该值最大不应超过500MB。

4.2.3Sort_area_size

该参数是当查询需要排序的时候，Oracle将使用这部分内存做排序，当内存不足时，使用临时表空间做排序。这个参数是针对会话（session）设置的，不是针对整个数据库。即如果应用有170个数据库连接，假设这些连接都做排序操作，则Oracle会分配8×170等于1360MB内存做排序，而这些内存是在Oracle的SGA区之外分配的，即如果SGA区分配了1.6GB内存，Oracle还需要额外的1.3GB内存做排序。 建议该值设置不超过3MB，当物理内存为1GB时，该值宜设为1MB或更低（如512KB）；2GB时可设为2MB但不论物理内存多大，该值也不应超过3MB。

4.2.4sort_area_retained_size

这个参数的含义是当排序完成后至少为session继续保留的排序内存的最小值，该值最大可设为等于sort_area_size。这样设置的好处是可以提高系统性能，因为下次再做排序操作时不需要再临时申请内存，缺点是如果 sort_ara_size 设得过大并且 session 数很多时，将导致系统内存不足。建议该值设为sort_area_size的10%～20%，或者不设置（默认为0）。

4.2.5Log_buffer

Log_buffer是重做日志缓冲区，对数据库的任何修改都按顺序被记录在该缓冲，然后由进程将它写入磁盘。当用户提交后、有1/3重做日志缓冲区未被写入磁盘、有大于1MB重做日志缓冲区未被写入磁盘。建议不论物理内存多大，该值统一设为1MB。

4.2.6SESSION_CACHED_CURSOR

该参数指定要高速缓存的会话游标的数量。对同一SQL语句进行多次语法分析后，它的会话游标将被移到该会话的游标高速缓存中。这样可以缩短语法分析的时间，因为游标被高速缓存，无须被重新打开。设置该参数有助于提高系统的运行效率，建议无论在任何平台都应被设为50。

4.2.7re_page_sga

该参数表示将把所有SGA装载到内存中，以便使该实例迅速达到最佳性能状态。这将增加例程启动和用户登录的时间，但在内存充足的系统上能减少缺页故障的出现。建议在2GB以上（含2G）内存的系统都将该值设为true。

4.2.8ML_LOCKS

该参数表示所有用户获取的锁的最大数量。对每个表执行DML操作均需要一个DML锁。例如，如果3个用户修改2个表，就要求该值为6。该值过小可能会引起死锁问题。建议该参数不应该低于600。

4.2.9DB_FILE_MULTIBLOCK_READ_COUNT

该参数主要同全表扫描有关。当Oracle在请求大量连续数据块的时候，该参数控制块的读入速率。该参数能对系统性能产生较大的影响，建议把DB_FILE_MULTIBLOCK_READ_COUNT 设为8。

4.2.10OPEN_CURSORS

指定一个会话一次可以打开的游标的最大数量，并且限制游标高速缓存的大小，以避免用户再次执行语句时重新进行语法分析。请将该值设置得足够高，这样才能防止应用程序耗尽打开的游标。此值建议设置为250～300。

合理地配置这些参数可以提高Oracle服务器的性能。配置完参数以后，需要重新启动Oracle服务才会生效。