Oracle运行环境的优化

一.内存结构的调整

内存参数的调整主要是指Oracle数据库的系统全局区（SGA）的调整。SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。

1．共享池

      共享池由两部分构成：共享SQL区和数据字典缓冲区。共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。

（1）数据库管理员通过执行下述语句，来查看共享SQL区的使用率。

select （sum（pins-reloads））/sum（pins） "Lib Cache" from v$librarycache;

         共享SQL区的使用率应该在90％以上，否则需要增加共享池的大小。

（2）数据库管理员可以执行下述语句，查看数据字典缓冲区的使用率。

select （sum（-getmisses-usage-fixed））/sum（gets） "Row Cache" from v$rowcache;

 

          数据字典缓冲区的使用率也应该在90％以上，否则需要增加共享池的大小。

2．数据缓冲区

      数据库管理员可以通过下述语句，来查看数据库数据缓冲区的使用情况。

SELECT name，  FROM v$sysstat WHERE name IN （'db block gets','consistentgets','physical reads'）;

      根据查询出来的结果可以计算出数据缓冲区的使用命中率：

数据缓冲区的使用命中率＝1 –( physical reads/(db block gets + consistent gets))

这个命中率应该在90％以上，否则需要增加数据缓冲区的大小。

3．日志缓冲区

      数据库管理员可以通过执行下述语句，查看日志缓冲区的使用情况。

select name,value from v$sysstat where name in ('redo entries','redo log space requests');

根据查询出的结果可以计算出日志缓冲区的申请失败率：

申请失败率＝requests/entries

申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区。

二、物理I/O的调整 

（1）在磁盘上建立数据文件前首先运行磁盘碎片整理程序

          为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就很容易避免数据文件产生碎片。

（2）不要使用磁盘压缩

          Oracle数据文件不支持磁盘压缩。

（3）不要使用磁盘加密

 

          加密象磁盘压缩一样增加了一个处理层，降低磁盘读写速度。如果担心自己的数据可能泄密，可以使用dbms_obfuscation包和label security选择性地加密数据的敏感部分。

5）使用RAID

     RAID的使用应注意：

①选择硬件RAID超过软件RAID；

②日志文件不要放在RAID 5卷上，因为RAID 5读性能高而写性能差；

③把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上。

（6）分离页面交换文件到多个磁盘物理卷

 

          跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益，确保所有页面文件的大小之和至少是物理内存的两倍。

三、CPU的优化调整

1．查看CPU的使用情况

      使用操作命令可以看到CPU的使用情况，一般UNIX操作系统的服务器，可以使用sar –u命令查看CPU的使用率；NT操作系统的服务器，可以使用NT的性能管理器来查看CPU的使用率。

 

     出现CPU资源不足的情况是很多的：SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足。

2．查看SQL语句的解析情况

（1）数据库管理员可以执行下述语句来查看SQL语句的解析情况：

SELECT * FROM V$SYSSTAT WHERE NAME IN ('parse_time_cpu','parse_time_elapsed','parse_count_ hard');

这里：

①parse_time_cpu：是系统服务时间。

②parse_time_elapsed：是响应时间。

而用户等待时间为：

waite_time = parse_time_elapsed – parse_time_cpu

由此可以得到用户SQL语句平均解析等待时间：

 

用户SQL语句平均解析等待时间＝waite_time/parse_count

（2）数据库管理员还可以通过下述语句，查看低效率的SQL语句：

SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT FROM V$SQLAREA;

 

优化这些低效率的SQL语句也有助于提高CPU的利用率。

3．查看Oracle数据库的冲突情况

 

      数据库管理员可以通过v$system_event数据字典中的“latch free”统计项查看Oracle数据库的冲突情况，如果没有冲突的话，latch free查询出来没有结果。如果冲突太大的话，数据库管理员可以降低spin_count参数值，来消除高的CPU使用率。

4．CPU的优化调整方法

      一些优化CPU使用和配置的具体方法有：

（1）取消屏幕保护。

（2）把系统配置为应用服务器。

（3）监视系统中消耗中断的硬件。

（4）保持最小的安全审计记录。

（5）在专用服务器上运行Oracle。

（6）禁止非必须的服务。

四、网络配置的优化 

        网络配置是性能调整的一项很重要的内容，而且很容易隐藏性能瓶颈。

（1）配置网卡使用最快速度和有效模式

（2）删除不需要的网络协议

（3）优化网络协议绑定顺序

（4）为Oracle禁止或优化文件共享

五、Oracle碎片整理

1．碎片是如何产生的

2．碎片对系统的影响

（1）导致系统性能减弱

 

（2）浪费大量的表空间

3．自由范围的碎片计算

      用fsfi——free space fragmentation index（自由空间碎片索引）值来直观体现：

 

fsfi=100*sqrt(max(extent)/sum(extents))*1/sqrt(sqrt(count(extents)))

4．自由范围的碎片整理

      可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1，如：

alter tablespace temp default storage(pctincrease 1);

     这样smon便会将自由范围自动合并，达到碎片整理的目的。

     也可以采用如下语句，通过手工合并自由范围来达到碎片整理的目的。

 

alter tablespace temp coalesce;

5．段的碎片整理

     段由范围组成，在有些情况下，有必要对段的碎片进行整理。要查看段的有关信息，可查看数据字典DBA_segments，范围的信息可查看数据字典DBA_extents。如果段的碎片过多， 将其数据压缩到一个范围的最简单方法便是用正确的存储参数将这个段重建，然后将旧表中的数据插入到新表，同时删除旧表。这个过程可以用import/export（输入/输出）工具来完成。

      export（）命令有一个（压缩）标志，这个标志在读表时会引发export确定该表所分配的物理空间量，它会向输出转储文件写入一个新的初始化存储参数，等于全部所分配空间。若这个表关闭， 则使用import（）工具重新生成。这样，它的数据会放入一个新的、较大的初始段中。例如：

exp user/password file=exp.dmp compress=y grants=y indexes=y tables=(table1,table2);

若输出成功，则从库中删除已输出的表，然后从输出转储文件中输入表：

imp user/password file=exp.dmp commit=y buffer=64000 full=y;

这种方法可用于整个数据库。

 

另外，应该定期shutdown database，从而清理momery碎片。

六、Oracle系统参数的调整

1．Shared Pool and Library Cache Performance Tuning（共享池和Library Cache）

共享池调整的技巧主要有：

（1）刷共享池

刷（ Flush）共享池可以使小块的内存合并为大块的内存。当共享池的碎片过多时，能够暂时恢复性能。刷共享池可以使用语句：

alter system flush shared_pool;

 

（2）绑定变量

2．Buffer Cache Performance Tuning（数据库缓存调整）

从缓存调整的角度看，应力求避免以下的问题：

（1）“缓存的最近最少使用（LRN）链”（cache buffers LRU chain）的加锁竞争；

（2）“平均写队列”（Average Write Queue）长度过大；

（3）过多时间花在等待“写完毕等待上”（write complete waits）；

 

（4）过多时间花在等待“缓冲释放等待”上（free buffer waits）。

3．Latch Contention（加锁或插销竞争）

     插销加锁是SGA中保护共享数据结构的低层的串行化机制。插销latch是一类可以非常快的获得和释放的锁。插销锁的实现是依赖于操作系统的，尤其在关于一个进程是否会等待一个锁，和等多久方面。

有如下的锁（插销）需要调整：

（1）Redo Copy/Allocation Latch：重写日志的复制/分配插销

（2）Shared Pool Latch：共享池的插销

 

（3）Library Cache Latch：Library Cache插销

4．Redo Log Buffer Performance Tuning（重写日志缓冲的调整）

      LGWR 将重写日志缓冲中的重写项写到重写日志文件中。一旦LGWR将这些项复制到重写日志文件中，用户进程就可以重写这些项。统计项目“redo log space requests”反映了用户进程等待重写日志缓冲中空间的时间的数字。

（1）设置重写日志大小的提示：

“redo log space requests”的值应该接近0。

 

（2）设定合适的重写日志的大小，建议每15-30分钟进行一次重写日志的切换。

5．Query Performance Tuning（查询效率的调整）

      如果查询运行得很慢，请考虑以下这些方面：

（1）希望这个查询运行的有多快以及有理由这样要求吗？

（2）优化模式OPTIMIZER_MODE 设为何值？

（3）查询涉及的索引都是有效的吗？

（4）在数据库中有没有其他的长时间运行的查询（大查询）。

（5）表和索引上有统计信息吗？

（6）统计信息是被计算出来的还是被估计出来的？

 

对于查询的性能调整有两个主要的调试工具：TKPROF和AUTOTRACE。

6．Temporary Tablespace Performance Tuning（临时表空间的调整）

      临时表空间的调整的技巧如下：

       如果即使在稳定的状态下也存在很多的排序扩展锁（Sort Extent Pool latch）的竞争，应该通过修改临时表空间的DEFAULT STORAGE 子句的NEXT值来增大扩展块的大小。如果存在很多的排序扩展锁（Sort Extent Pool latch）的竞争并且这种等待是由于过多的并发的排序造成的，应该增大SORT_AREA_SIZE参数的大小，以使更多的排序能保存在内存中。

 

建议让扩展块的大小和SORT_AREA_SIZE参数相同。