oracle优化

（1）获取缓冲区活动情况

select sum(value)"Redo Buffer Waits" from v$sysstat where name='redo log space wait time';

（2）计算日志缓冲区的申请失败率

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

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

F:\oracle\product\10.2.0\db_1\srvm\admin\Init.ora文件中的LOG_BUFFER
该参数指明分配给SGA中的日志缓冲区的字节数，该参数值较大时，可减少日志I／O的次数。对于繁忙的系统不宜采用大于或等于64K的值。缺省值-般为数据库块的4倍

(3) 共享池

整个共享池的大小由参数SHARED_POOL_SIZE确定

（1）      确定库高速缓存的性能

SELECT SUM(pins),SUM(reloads) FROM V$LIBRARYCACHE;

PINS列给出SQL语句、PL／SQL块及被访问对象定义的总次数；RELOADS给出SQL语句或PL／SQL块的隐式分析或对象定义重装载时在库程序缓冲区中发生的错误。

（2）       查看reloads和pins的比率

SELECT (SUM(reloads)/SUM(pins))*100"Library Cache Ratio"FROM V$LIBRARYCACHE;

用户必须保证reloads和pins的比率尽可能低，其值应低于1%,若RELOADS／PINS＞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的参数值而达到满意的优化比率。

（4）       优化数据字典缓冲区
数据字典缓冲区在功能上

SELECT (SUM(getmisses)/SUM(gets))*100 "DaTa Dictionary Cache Ratio"FROM V$ROWCHACHE

对于贫繁访问的数据字典缓冲区，GETMISSES与GETS之比要小于10％到15%。若大于此百分数时，则应考虑增加数据字典缓冲区的容量，即要增加SHARED_POOL_SIZE或者DB_BLOCK_BUFFERS初始化参数的值

（5）       使用多线索服务器时共享池的优化

获取缓冲区活动情况

select name ,value from v$sysstat where name in('db block gets','consistent gets','physical reads');

其中，“db block gets”和“consistent gets”的值是请求数据缓冲区中读的总次数。“physical reads”为请求数据时引起从盘中读文件的次数。

缓冲区命中率

从缓冲区读的可能性的高低称为缓冲区命中率。它可用如下公式计算：

Hot Ratio＝1-(physical reads／(db block gets＋consistent gets)

缓冲区命中率越高，其速度就越快。如果命中率低于60％或70％时。则应增加缓冲区(即DB_BLOCK_BUFFERS)，以改进性能。根据公式可以计算出本例中的Hot Ratio=1-(1963/(3437+30500)=92％。 如果缓冲区的命中率很高，希望在保持良好性能下适当减少缓冲区，这时可减少DB_BLOCK_BUFFERS的值，其最小值为4。

（6）       I/O优化的

磁盘的活动情况

SELECT  Name,phyrds,phywrts FROM  V$DATAFILE df,V$FILESTAT fs WHERE df.file#=fs.file#;

◆     phyrds：记录从盘上读每个数据库文件的次数。
◆ phywrts：记录往盘上写数据库文件的次数。

 

（7）       优化DBWR进程争用

（8）       数据库写进程(DBWR)、日志写进程(LGWR)与归档进程(ARCH)三种后台处理进程可以空闲存取磁盘上的数据库文件。这三个进程之间常因同时读写争用而导致I/O争用，而DBWR进程还有自我争用发生的可能。通过对DBWR赋值可以有效的解决DBWR的自我争用问题。

（9）       通常在操作系统中为每个实例创建多个DBWR进程，并通过初始参数DB_WRITERS来确定DBWR的个数。建议将赋值在n与2n之间，这里的n是指磁盘的个数。如果赋值不能很好的解决DBWR的内部争用，可以通过异步I/O来减少DBWR的内部争用。由于I/O进程的执行都是并行的，所以异步的I/O仅需一个DBWR即可解决问题。

（10）   表数据存放策略

将大表的数据划分成若干小部分，并将这些部分存储在不同磁盘的不同数据文件中。用create tablespace创建表空间，在datafile子句中指定数据文件，每个文件应在不同的盘上。如我们需要在三个磁盘上存放数据文件，可使用如下代码：

create tablespace trip

datafile 'file_on_disk_1' size 500k,

'file_on_disk_2' size 500k,

'file_on_disk_3' size 500k;

指定表空间和表大小

用“create table”创建表，在tablespace子句中指定表空间，在storage子句中指定表的大小，如以下SQL语句：

create table striptab (

……

Tablespace strip 1500k

Storage (initial 495k next 495k minextent 5 pctincreate 0);

（11）   清除其它的磁盘I/O

减少在已有表中的迁移和链接行的具体步骤如下：

     (1)用analyze命令收集信息：

analyze table *** list chained rows;

(2)查询输出表：

select * from chained_rows where table_name='test_chain';

(3)消除迁移行：

创造与已有表有相同列的中间表来持有迁移行和链接行：

create table int_***

as seelct * from *** where rowid in (select head_rowid from chained_rows where table_name='***');

从已有表中删除迁移行和链接行：

delete from *** where rowid in (select head_rowid from chained_rows where table_name='***');

把中间表中的行插入已有表中：

insert into *** select 8 from int_***;

撤消中间表：

drop table int_***;

(4)从输出表中删除第1步收集的信息：

delete from chained_rows where table_name='***';

(5)再次使用analyze命令，并查询输出表；

(6)在输出表中出现的任何行是链接行，可增加数据块长度。

（12）   可采用以下三种方法来处理在动态空间管理时所出现的回滚段：
(1)对于长查询或长事务应当分配一个较大的回滚段，以提高性能。
(2)在同一个应用中同时运行的多个副本，不能共用同一个回滚段，以免出现回滚段争用。
(3)对于频繁更改，但更改数据量较小的并发联机事务处理，可采用较小的回滚段以加快缓冲区中数据的存取。

（13）   来检测是否存在对回滚段的争用

SELECT class,count FROM V$WAITSTAT WHERE class IN('system undo header','system undo block','undo header','undo block');

其中参数含义如下：
◆ system undo header：对含有SYSTEM回滚段标题块的缓冲区的等待次数。
◆ system undo block：对含有SYSTEM回滚段非标题块的缓冲区的等待次数。
◆ undo header：对含有非SYSTEM回滚段标题块的缓冲区的等待次数。
◆ undo block：对含有非SYSTEM回滚段非标题块的缓冲区的等待次数。
如果任何等待次数大于总请求数的1%，则应创建更多的回滚段来减少竞争，可以周期性地

检查这些统计数字，并将它与总的请求数据的次数作比较

总的请求数据次数可用如下语句求出：

SELECT SUM(value) FROM V$SYSSTAT WHERE name IN('db block gets','consistent gets');

减少回滚段争用的办法是为其建立适当数量的回滚段。

(14) 减少调度进程的争用

对该进程的争用主要表现在调度进程占用率高及等待响应时间的增长方面

SELECT network "protocol",SUM(busy)/(SUM(busy)+SUM(idle)) "Total Rate" FROM V$DISPATCHER GROUP BY network;

其中V$DISPATCHER表中的idle和busy列的含义如下：
◆ idle：表示1%秒单位时间内调度进程的空闲时间。
◆ busy：表示1%秒单位时间内调度进程的占用时间。
DEC net分发进程接近0.5%时间是忙的，TCP分发进程接近3%时间是忙的。若占用率超过50%，则应该用增加调度进程的网络协议来改善性能。若要检查等待响应时间的增长情况，可通过查询动态性能表V$QUEUE(需有SELECT ANY TABLE特权)来检查。例如：

SQL>SELECT network "protocol",DECODEE(SUM(totalq),0,'No Responses'),SUM(wait)/SUM(totalq)||'hundredths of seconds,"Average Wait Time Per Response"’ FROM V$QUEUE q,V$DISPATCHER d WHERE q．type='DISPATCHER' AND q.paddr=d.paddr GROUP BY network;

其中表V$QUEUE中的wait和totaq列的含义如下：
◆ wait：它表示1%秒单位时间内所有已在队列中等待响应的时间。
◆ totalq：表示在队列中的总的响应次数。
可通过以下办法来增加调度进程：
(1)用SQL * DBA的“Configure Multi-Threaded Dipatchers”对话框。
(2)用具有MTS_DISPATCHER参数的ALTER SYSTEM命令。
调度进程的总数由初始化参数MTS_MAX-DIPATCHER限制，在增加调度进程之前需要增加该参数，缺省值为5，最大值依赖操作系统。

(15) 减少共享服务器进程的争用

首先需确定是否有争用。请求队列中请求等待时间的增长反映对共享服务器进程的争用。

SELECT  DECODE(totalq,0,'No Requests',wait/totalq||'hundreadths of seconds') "Average Wait Time Per Requests"

FROM V$QUEUE t

WHERE t.type='COMMON';

◆     wait：队列中1%秒单位时间内所有请求的等待时间。
◆ totalq：是队列中请求的总数。
用如下的查询可以确定有多少个服务器进程在运行：

SELECT COUNT(*) "Shared Server Proccess"

FROM v$shared_servers

WHERE status!='QUIT';

增加共享服务器进程是由Oracle自动完成的。所以您不必显式地增加服务器进程。但是，当服务器进程的数量增加到参数MTS_MAX_SERVER所限制的数量时，如果还需增加，就必须修改MTS_MAX_SERVERS的值。此参数的缺省值为20，最大值依赖于操作系统。

无论是由Oracle自动增加服务器进程或是显示地增加共享服务器进程都可采用如下办法来改善性能：

(1)设置合适的MTS_MAX_SERVERS参数；

(2)用SQL * DBA的“Configure MuIti-Threaded Servers”对话框；

(3)用具有MTS_SERVERS参数的ALTER SYSTEM命令运行时执行：

alter system.mts_dispatchers

(16) 减少日志缓冲区空间的争用

是否争用日志缓冲区空间

SELECT name, value

FROM V$SYSSTAT

WHERE name='redo log space requests';

日志空间的请求值应接近于0，否则需增加初始化参数LOG_BUFFER的值，以增加空间、减少争用。

(17) 减少日志缓冲区闩锁的争用

当多个用户同时频繁地访问日志缓冲区时，就可能引起对日志缓冲区闩锁的争用，从而降低性能。Oracle将所有闩锁的活动记录在动态性能表V$LATCH中。有SELECT ANY TABLE特权的用户可以查询它，以了解是否有争用发生。查询语句如下：

SELECT name,gets,misses,iddediate_gets,iddediate_miss

FROM V$LATCH l, V$LATCHMANE ln

WHERE ln.nome IN('redo allocation','redo copv') AND ln.latch #=l.latch#;

◆     willing_to_wait：对闩锁的请求种类之一。
◆ iddediate：对闩锁的请求种类之二。
◆ gets：表示对闩锁的willing_to_wait请求成功的次数。
◆ misses：表示对闩锁的willing_to_wait请求不成功的次数。
◆ iddediate_gets：表示对每个闩锁立即请求成功的次数。
◆ iddediate_miss：表示对每个闩锁立即请求不成功的次数。

要减少日志分配闩锁的争用就需使单个进程持有闩锁的时间最短，要减少此时间又应减少在日志闩锁上的拷贝，即减少参数LOG_SMALL_ENTRY_MAX_SIZE的值。
要减少日志拷贝闩锁的争用，一是观察其争用情况，增加闩锁，即增加LOG_SIMULTANEOUS_COPIES的值；二是减少持有闩锁的时间，即在获得日志拷贝闩锁之前，就迫使Oracle用户进程事先建立日志项。事先建立的所有日志项的大小都要小于参数LOG_ENTRY_PREBUILD_THRESHOLD，要事先建立日志项就要增加参数LOG_ENTRY_PREBUILD_THRESSHOLD的值。

(18) 优化排序

排序是一项花销很大的操作，而且对性能的影响程度也较大，因此使大部分排序在内存中完成，而不是在磁盘上进行，这是至关重要的。

识别排序量的大小，就是要确定内存中排序的量和磁盘上排序的量，可用如下语句查询：

SQL> select name ,value from V$SYSSTAT where upper(name) in ('sorts(memory)','sorts(disk)');

其中“sorts(memory)”选项表示不需要磁盘I/O，选项“sorts(disk)” 表示需要磁盘I/O。如果用户认为在磁盘上的排序意义较大，可以增加init.ora文件SORT_AREA_SIZE参数的设置值。

(19) 查看SQL语句的解析情况

数据库管理员可以执行下述语句来查看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语句平均解析等待时间＝waite time / parse count。这个平均等待时间应该接近于0，如果平均解析等待时间过长，数据库管理员可以通过下述语句来发现是什么SQL语句解析效率比较低。

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA

ORDER BY PARSE_CALLS;

程序员可以优化这些语句，或者增加Oracle参数SESSION_CACHED_CURSORS的值。

alter system set open_cursors=1500 scope=both;

alter system set session_cached_cursors=100 scope=spfile;

数据库管理员还可以通过下述语句查看低效率的SQL语句，优化这些语句也有助于提高CPU的利用率。
SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;