在init.ora參數(shù)文件中設(shè)置TIMED_STATISTICS=TRUE 和在你的會(huì)話層設(shè)置ALTER SESSION SET STATISTICS=TRUE 。運(yùn)行svrmgrl 用 connect internal 注冊(cè)，在你的應(yīng)用系統(tǒng)正常活動(dòng)期間，運(yùn)行utlbstat.sql 開始統(tǒng)計(jì)系統(tǒng)活動(dòng)，達(dá)到一定的時(shí)間后，執(zhí)行utlestat.sql 停止統(tǒng)計(jì)。統(tǒng)計(jì)結(jié)果將產(chǎn)生在report.txt 文件中。(utlbstat.sql utlestat.sql 一般存放在$ORACLE_HOME/RDBMS/ADMIN 子目錄下)

優(yōu)化數(shù)據(jù)庫磁盤I/O

檢查系統(tǒng)的I/O問題

在UNIX系統(tǒng)中工具sar-d能檢查整個(gè)系統(tǒng)的iostat(IO statistics)，在NT系統(tǒng)上則使用性能監(jiān)視器(Performance Monitor).

反映Oracle文件I/O的進(jìn)程

使用V$FILESTAT確定Oracle數(shù)據(jù)文件I/O

SELECT NAME,PHYRDS,PHYWRTS FROM V$DATAFILE DF,V$FILESTAT FS WHERE DF.FILE#=FS.FILE# ;

使用分布I/O減少磁盤競爭

將數(shù)據(jù)文件和redo log文件分開

Striping 表數(shù)據(jù)

分開表和索引

減少與Oracle無關(guān)的磁盤I/O

避免動(dòng)態(tài)空間管理

在創(chuàng)建如表或回滾段的數(shù)據(jù)庫實(shí)體時(shí)，在數(shù)據(jù)庫中會(huì)為這些數(shù)據(jù)分配空間，該空間被稱為段。如果數(shù)據(jù)庫操作引起數(shù)據(jù)增加并超出了分配的表空間，oracle會(huì)擴(kuò)展該段，動(dòng)態(tài)擴(kuò)展會(huì)降低系統(tǒng)性能。

確定動(dòng)態(tài)擴(kuò)展

select name,value from v$sysstat where name=’recursive calls’ ;

分配分區(qū)

確定實(shí)體的最大大小;

選擇存儲(chǔ)參數(shù)值，使Oracle分配足夠大的分區(qū)，在創(chuàng)建實(shí)體時(shí)可以裝入所有數(shù)據(jù)

避免回滾段的動(dòng)態(tài)空間管理

回滾段大小由其存儲(chǔ)參數(shù)所決定，回滾段必須能保存所有交易的回滾入口;

使用set transaction 命令可以為回滾段賦予交易的合適的大小;

對(duì)長的查詢的修改數(shù)據(jù)，應(yīng)賦予大的回滾段，以保持所有的回滾入口;

對(duì)OLTP交易，由于頻繁交易，每個(gè)交易只修改小量的數(shù)據(jù)，因此賦予小的回滾段。

減少遷移和鏈接行

1. 使用ANALYZE 收集遷移和鏈接行的信息;

2. 查詢輸出表：chained_rows;

3. 如果有許多遷移和鏈接行，就需要消除遷移行，方法如下：

A. 創(chuàng)建與原表相同列的中間表，以保存遷移和鏈接行;

B. 從原表中刪除遷移和鏈接行;

C. 將中間表中的行插入到原表中;

D. 刪除中間表

4. 刪除第一步收集的信息;

5. 重新使用ANALYZE命令查詢輸出表

6. 在輸出表中出現(xiàn)的行都是鏈接行，只能通過增加數(shù)據(jù)塊的大小來清除。

調(diào)整排序

內(nèi)存中排序

使用動(dòng)態(tài)表V$SYSSTAT的信息反映排序

SELECT NAME , VALUE FROM V$SYSSTAT WHERE NAME IN (‘SORTS(MEMORY)’,’SORTS(DISK)’) ;

SORTS(MEMORY)-不需要使用I/O操作而完全在內(nèi)存完成的排序數(shù);

SORTS(DISK)-需要使用I/O操作與磁盤臨時(shí)段才能完成數(shù)據(jù)的排序數(shù)目。

增大SORT_AREA_SIZE以避免磁盤排序

使用NOSORT創(chuàng)建非排序的索引

CREATE INDEX INDEX_NAME ON TABLE TABLE_NAME(COLUMN_NAME) NOSORT ;

調(diào)整Checkpoints

一個(gè)checkpoint是oracle自動(dòng)執(zhí)行的一種操作，當(dāng)檢查點(diǎn)操作時(shí)，數(shù)據(jù)庫中的所有緩沖區(qū)會(huì)寫回磁盤，所有數(shù)據(jù)庫的控制文件被更新。Checkpoint頻繁發(fā)生會(huì)加快數(shù)據(jù)庫的恢復(fù)，但是增加了I/O次數(shù)，會(huì)降低系統(tǒng)的性能。

調(diào)整LGWR和DBWn I/O

調(diào)整LGWR I/O

每次I/O寫的大小依賴于LOG緩沖區(qū)的大小，該大小由LOG BUFFER 所設(shè)置，緩沖區(qū)太大會(huì)延遲寫操作，太小可能導(dǎo)致頻繁的小的I/O操作。如果I/O操作的平均大小很大，那么LOG文件就會(huì)成為瓶頸，可以使用STRIPE REDO LOG文件避免這個(gè)問題。

調(diào)整DBWN I/O

使用初始參數(shù)DB_WRITER_PROCESSES，可以創(chuàng)建多個(gè)數(shù)據(jù)庫寫進(jìn)程。

調(diào)整競爭

由多個(gè)進(jìn)程同時(shí)請(qǐng)求使用相同的資源時(shí)，就產(chǎn)生了競爭

確定競爭問題

視圖V$RESOURCE_LIMIT提供了一些系統(tǒng)資源的使用限制。

如果系統(tǒng)存在無反應(yīng)的現(xiàn)象，檢查V$SYSTEM_EVENT，檢查最大平均等待時(shí)間的事件;

如果存在過量的緩沖區(qū)等待，檢查V$WAITSTAT，確定哪個(gè)類型的塊有最多的等待次數(shù)和最長的等待時(shí)間，再查詢V$SESSION_WAIT得到每個(gè)緩沖區(qū)的等待時(shí)間。

減少回滾段的競爭

通過檢查V$WAITSTAT可以確定回滾段的競爭：

SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS IN 

(‘SYSTEM UODO HEADER’,’SYSTEM UODO BLOCK’,’UODO HEADER’,’UODO BLOCK’) ;

減少調(diào)度進(jìn)程的競爭

檢查調(diào)度進(jìn)程的busy率

SELECT NETWORK”PROTOCOL”, SUM(BUSY)/(SUM(BUSY)+SUM(IDLE)) “TOTAL BUSY RATE” 

FROM V$DISPATCHER GROUP BY NETWORK ;

如果指定協(xié)議的調(diào)度進(jìn)程忙的時(shí)間超過50%的有效工作時(shí)間，那么，增加調(diào)度進(jìn)程可以提高使用該協(xié)議連接到oracle的性能。

檢查調(diào)度進(jìn)程相應(yīng)隊(duì)列的等待時(shí)間

SELECT NETWORK “PROTOCOL” DECODE(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 ;

增加調(diào)度進(jìn)程：使用MTS_DISPATCHERS參數(shù)和ALTER_SYSTEM命令可以增加調(diào)度進(jìn)程

減少共享服務(wù)器進(jìn)程的競爭

共享服務(wù)器進(jìn)程競爭可以由不斷增加的請(qǐng)求等待時(shí)間所反映，使用如下查詢：

select decode(totalq,0,’No Requests’, wait/totalq||’hundredths of seconds’) 

“Average Wait Time Per Requests” from v$queue where type=’COMMON’ ;

使用如下查詢可以得到當(dāng)前運(yùn)行的共享服務(wù)進(jìn)程數(shù)：

select count(*) “Shared Server Processes” from V$shared_servers where status!=’QUIT’;

oracle能自動(dòng)增加共享服務(wù)進(jìn)程，但是MTS_MAX_SERVERS的值可以更改。

　　減少redo log緩沖區(qū)latches競爭

在LGWR進(jìn)程將redo入口從redo log緩沖區(qū)寫入redo log文件后，該入口就會(huì)被新入口覆蓋，供其他log的使用。

V$SYSSTAT中redo buffer allocation retries 反映用戶進(jìn)程等待redo log空間的次數(shù)：

Select name,value from v$sysstat where name=’redo buffer allocation retries’ ;

redo buffer allocation retries的值應(yīng)該接近0，如果該值持續(xù)增加，那么，說明進(jìn)程需要等待緩沖區(qū)的空間。增大參數(shù)LOG_BUFFER的值可以增大redo log的大小。

確定redo log緩沖區(qū)latches競爭

redo分配latch;

redo復(fù)制latches。

一次只能有一個(gè)用戶分配緩沖區(qū)中的空間，在分配了redo入口的空間后，用戶進(jìn)程將入口復(fù)制到緩沖區(qū)，其最大大小是由LOG_SMALL_ENTRY_MAX_SIZE指定。Redo復(fù)制latches的數(shù)目由參數(shù)LOG_SIMULTANEOUS_COPIES指定。

檢查redo log活動(dòng)

對(duì)redo log緩沖區(qū)的頻繁訪問可能導(dǎo)致redo log緩沖區(qū)latches競爭，降低系統(tǒng)性能。Oracle在動(dòng)態(tài)表V$LATCH中收集了所有LATCH的統(tǒng)計(jì)信息。

其中：表v$latch反映willing-to-wait 請(qǐng)求的列

gets-成功的willing-to-wait請(qǐng)求數(shù);

misses-初始不成功的willing-to-wait請(qǐng)求數(shù);

sleeps-請(qǐng)求不成功的等待時(shí)間;

表v$latch反映immediate請(qǐng)求的列：

immediate gets-成功的immediate請(qǐng)求數(shù)

immediate misses-不成功的immediate請(qǐng)求數(shù)

使用如下查詢：

select ln.name,gets,misses,immediate_gets,immediate_misses from v$latch l,

v$latchname ln where ln.name in (‘redo allocation ’,’redo copy’) and ln.latch#=l.latch# ;

可以計(jì)算出各類請(qǐng)求的等待率。

減少latch競爭

要減少redo allocation latch競爭，必須減少單個(gè)進(jìn)程占用latch的時(shí)間。要減少這個(gè)時(shí)間，可以減少該redo allocation latch的復(fù)制。減少LOG_SMALL_ENTRY_MAX_SIZE初始參數(shù)可以減少在redo allocation latch的redo入口的復(fù)制次數(shù)和大小。

減少redo copy Latches競爭可以用增加LOG_SIMULTANEOUS_COPIES的值來增加LATCH數(shù)，最多可以達(dá)到CPU的兩倍。

減少Free List競爭

確定Free List競爭，可以使用以下幾步：

1. 檢查V$WAITSTAT，確定DATA BLOCKS的競爭;

2. 檢查V$SYSTEM_EVENT,確定BUFFER BUSY WAITS，如果數(shù)值高，表明存在競爭;

3. 在這種情況下，檢查V$SESSION_WAIT查詢每個(gè)緩沖區(qū)的忙等待、FILE、BLOCK及ID;

4. 使用如下查詢得到實(shí)體和FREE LIST的名稱：

SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS 

WHERE FILE_ID=file AND BLOCK BETWEEN block_id AND block_id+blocks ;

5. 使用如下查詢找到FREE LIST:

SELECT SEGMENT_NAME,FREELISTS FROM DBA_SEGMENTS 

WHERE SEGMENT_NAME=segment AND SEGMENT_TYPE=type ;

增加Free List

要減少表的Free list的競爭， 可以使用freelists參數(shù)重新創(chuàng)建表，方法有：

1. 刪除舊表，重新創(chuàng)建表;

2. 通過從舊表選擇數(shù)據(jù)插入到新表，刪除舊表，重命名，完成表的重新創(chuàng)建;

3. 使用Export 和Import，export舊表，刪除舊表，import該表

建立和優(yōu)化數(shù)據(jù)庫文件的方針：

為表和索引創(chuàng)建不同的表空間。

將表和索引的表空間放在不同的磁盤上。

將REDO日志和回滾段放在不同的磁盤上。

將Oracle可執(zhí)行文件和數(shù)據(jù)庫文件放在單獨(dú)的磁盤上。

確定最常用的表、索引，以及他們的表空間，并放在單獨(dú)的磁盤上。

不要在Oracle數(shù)據(jù)磁盤上安裝其他第三方軟件。

監(jiān)控磁盤I/O的方法：

用以下查詢語句可以得到各表空間讀寫次數(shù)，phyrds+phywrts 即是磁盤I/O量。應(yīng)按前面講的方針調(diào)整數(shù)據(jù)文件的分布方式。

select name,phyrds,phywrts from v$datafile,

v$filestat where v$datafile.file# = v$filestat.file#

結(jié)果如下：

NAME PHYRDS PHYWRTS
-------------------------------------------------- ---------- ----------
/u/oracle/OraHome/oradata/pb/system01.dbf 2511 8
/u/oracle/OraHome/oradata/pb/tools01.dbf 5 2
/u/oracle/OraHome/oradata/pb/rbs01.dbf 18 20
/u/oracle/OraHome/oradata/pb/temp01.dbf 4 2
/u/oracle/OraHome/oradata/pb/users01.dbf 45 5
/u/oracle/OraHome/oradata/pb/indx01.dbf 4 2
/u/oracle/OraHome/oradata/pb/drsys01.dbf 4 2
7 rows selected.

【編輯推薦】

1. 優(yōu)化數(shù)據(jù)庫大幅度提高Oracle的性能
2. 淺談Oracle優(yōu)化排序的操作
3. Oracle設(shè)置系統(tǒng)參數(shù)進(jìn)行性能優(yōu)化