Oracle常用调优手段

Oracle数据库应用系统的调优主要包括十个方面：
(1)、优化数据库内存；
(2)、在Oracle共享池中固定应用程序代码；
(3)、优化数据存储；
(4)、优化数据排序的技术；
(5)、优化SQL语句；
(6)、优化回退段；
(7)、优化索引；
(8)、优化磁盘I/O；
(9)、定期生成数据库对象的状态统计信息；
(10)、优化操作系统环境。
其实质就是降低CPU负载、改善I/O性能。

 

1、化磁盘I/O
数据库的作用就是实现对数据的管理和查询，所以必然存在对数据的大量读写操作，其I/O问题也往往是导致Oracle数据库性能问题的重要原因。
1.1、Oracle中I/O的产生
1.2、优化OS存储
一、在UNIT环境下，采用裸设备（Raw Device）作为Oracle数据文件的存储设备比采文件系统（File System）存储Oracle数据文件具有较高的读写效率。
二、采用异步IO（Asynchronous IO）方式。在异步IO模式下，进程发出IO请求后无需等待IO完成，可以去处理其它事情；IO请求被放入一个队列中，一旦IO完成，系统会发出信号通知进程。异步IO可以使需要大量写的Oracle进程（如DBWn进程）将IO请求队列化，以充分利用硬件的IO带宽，从而使它们能最大程度实现并行处理。确认操作系统已经设置支持AIO后，还需要设置Oracle初始化参数“DISK_ASYNCH_IO”为“true”以支持异步IO。
三、磁盘负载均衡及条带化（Striping）。条带化技术就是将数据分成很多小部分并把他们分别存储到不同磁盘上的不同区域中去。这就能使多个进程同时访问数据的多个不同部分而不会造成磁盘冲突。很多操作系统、磁盘设备供应商、各种第三方软件都能做到条带化。通过条带化，DBA可以很轻松的做到IO负载均衡而无需去手工配置。
1.3、优化IO配置
一、利用LVM（Logical Volume Manager逻辑卷管理器）软件合理的配置条带的宽度和深度。
二、采用分离文件策略，避免磁盘热点。尽管我们在硬件和操作系统层面通过磁盘条带化实现了磁盘负载均衡，但我们依然不能避免某些数据文件成为“热点文件”。
分离表、索引和临时表空间的存储，即为应用系统建立单独的数据表空间、索引表空间、临时表空间。
分离重做日志(Redo Log)文件。如果Redo Log文件的IO吞吐率高，则应将Redo Log存储在单独磁盘上，在有充裕磁盘的的情况下，应将Redo Log文件条带化分布到多个磁盘上去；
分离归档日志文件(Archive Log)。当ORACLE数据库运行在归档模式下时，归档进程(ARCn )必然会产生大量的磁盘读写。所以应将归档日志文件条带化分布到多个磁盘上单独存储。
1.4、优化ORACLE I/O相关的参数设置
db_file_multiblock_read_count：用于控制一个完全连续扫描中一次 I/O 操作所读取的数据块的最大值。默认值8
db_writer_processes：数据库“写进程”的初始数量。
disk_asynch_io：用于控制数据文件、 控制文件和日志文件的 I/O 是否异步 。只有在平台支持磁盘异步 I/O的情况下才能更改该参数。
log_archive_max_processes：指定归档模式下ARCH 进程的数量。

 

2.优化数据存储

2.1、表空间优化
SYSTEM表空间用于存放ORACLE系统的有关信息，一般的用户建立的对象（object）不应放在SYSTEM表空间中，另外还需要注意增加SYSTEM表空间合适的大小，保证有100M左右的空闲空间。
对于ORACLE的数据库应用系统，都应该为应用系统建立独立的ORACLE用户(scheme)、数据表空间、索引表空间、临时表空间。应将表空间及数据文件一次性设置成合适的大小，避免数据文件自动增长，造成数据段块/段的不连续，影响系统性能。
定期检查数据库表空间的使用情况，防止表空间碎块过多，影响系统性能。通过查询视图dba_extents可获取表空间详细的使用情况。
整理表空间碎片，回收数据表段的可用空间。
合并表空间碎片
alter tablespace tablespacename coalesce;
回收数据表段的空闲空间
alter table tablename deallocate unused;

2.2、合理配置回滚段大小
在Oracle 9i以后，系统采用了透明的本地化的管理方式。默认情况下，回滚段表空间是比较小的，往往不能满足实际应用中处理大型的事务的需要，就需要为专门的大型应用或事务建立大的回滚段。

2.3、临时表空间设计规划
临时表空间主要用于查询操作中的distinct 、union 、order by以及create index操作及存储临时表数据等。Oracle缺省表空间为Temp，其大小为1MB，对于一个真正的中、大型应用系统是远远不够的，因此需做如下工作：
增加Temp表空间到合适的大小，一般为300M-500M左右。
创建用户时应为其选择专用的临时表空间。
应该为大的应用系统建立专门的大的临时表空间，用于进行系统的月报、季报、年报统计存储临时表数据等。

2.4、将数据文件和日志文件存储于不同磁盘上
数据文件的写入是通过DBWR后台进程实现，日志文件的写入是通过LGWR后台进程实现，由于日志文件是连续的写入，因此无并发处理现象。而数据文件的写入相对是随机的，为避免在同一时间内DBWR和LGWR的冲突，应将日志文件和数据文件放在不同的硬盘上。
另外，在ARCHIVELOG模式时，也可能产生日志文件写磁盘与日志归档间的冲突，这种冲突的避免只能通过将日志文件分配到多个磁盘才能解决。

 

3、优化操作系统环境

3.1、配置操作系统合适的信号量
Oracle在某些Unix操作系统环境下运行需要合适的操作系统信号量。应该根据Oracle版本发行的要求进行设置，譬如在SOLARIS环境下，需要以root 登录并根据Oracle安装手册的参数要求修改/etc目录的system文件。

3.2、配置合适大小的交换区
在UNIX操作系统环境下，交换区是Oracle的一项基本的要求。可以根据Oracle的发行要求来确定。建议交换区的大小是该服务器内存的2倍以上。

3.3、配置操作系统启用异步I/O
目前大多数操作系统都支持异步I/O，但如果让Oracle运行在异步I/O模式下，就必须做相应的配置。譬如在HP－UNIX下，需要把“/dev /async”授权给操作系统的oracle用户,并且要修改Oracle参数disk_asynch_io＝true。

3.4、将Oracle的SGA锁定在物理内存中。
几乎所有的操作系统都支持虚拟内存，所以即使我们使用的内存小于物理内存，也不能避免操作系统将SGA交换到虚拟内存（SWAP），通过相应配置将Oracle SGA锁定在物理内存避免被交换到虚拟内存中，可以减少页面的换入和换出，从而提高性能。
HP-UNIX下的配置方法：
#/etc/setprivgrp dba MLOCK
调整ORACLE参数：lock_sga＝TRUE
重启数据库
AIX 5L（AIX 4.3.3）下的配置方法：
$/usr/sbin/vmo -r -o v_pinshm=1（或vmtune -s 1）
调整ORACLE参数：lock_sga＝TRUE
重启数据库

3.5、控制内存交换操作(Paging)
大量的内存交换操作会极大地影响系统的性能，尤其是在当数据库文件创建在文件系统上时影响更大。在这种情况下经常访问的数据，即在SGA中存在也同样在文件的缓存中存在。这种相同的数据在内存中缓存两次的情况，会降低内存的使用效率，从而使内存频繁进行交换操作，造成系统的I/O瓶颈，降低整个系统的性能。 通过将ORACLE数据文件建在裸设备上和调整系统文件缓存，可以有效降低内存交换操作。
在AIX上，可以通过vmtune命令中的MINPERM（缺省值20）和MAXPERM（缺省值80）参数来调整系统文件缓存，用以控制内存交换操作。一般将MINPERM和MAXPERM分别设为5%和20%甚至更小，可使内存更多地被用于Oracle的SGA而不是系统的文件缓存。
＃vmtune -p 5 -P 20
在HP-UINX 10.X以前的版本中，分配过大的文件系统缓存，也会导致Oracle的SGA被交换到虚拟内存中去。在10.X以后的版本中文件系统缓存动态分配。不合理的设置dbc_min_pct和dbc_max_pct也会导致无法解释的偶然或间歇性停顿出现。

 

4.优化数据库内存

4.1、Oracle内存结构

4.2、优化SGA(System Global Area )
对于Oracle内存的调整设置，要根据实际情况酌情考虑，基本的原则是：
数据缓冲区(data buffer)：用于存放从数据文件读入的数据块，可以尽可能的大；
共享池(shared_pool_size)：用于保存数据字典及当前执行的SQL语句和存储过程，要适度；
日志缓冲区(log_buffer)：用于缓存用户更新的数据，不需太大。
shared_pool_size：要大小要适度，通常设为500M左右，不应超过700M。
log_buffer ：通常设为512K到1M。 
large_pool_size ：如果不设置MTS（Multi-Threaded Server），该部分内存只会在 RMAN（恢复管理） 、OPQ（并行查询） 中使用到，通常设置为是16M-64M 。
java_pool_size : 如果在数据库里不使用java，通常设置为16M。 
data buffer ：在做了上面的设置后，凡可以提供给Oracle的内存，都应该给data buffer = (db_block_size * db_block_buffers) 。 
SGA=data buffer+shared_pool_size+log_buffer+java_pool_size+large_pool_size

4.3、优化PGA (Program Global Area )
sort_area_size：默认64K,通常设置为128K到512K 
hash_area_size:不做配置，是根据2*sort_area_size计算得到 
这两个参数在非MTS下都是属于PGA(Program Global Area)，不属于SGA。它是为每个session单独分配的，所以服务器上内存开销情况通常要求：OS内存+SGA+session* (sort_area_size+hash_area_size+2M)<总物理RAM。