Unix環境下的Oracle調優原則

lyf625發表於2010-03-09
Oracle

首先通過作業系統的一些工具檢查系統的狀態，比如CPU、記憶體、交換、磁碟的利用率，根據經驗或與系統正常時的狀態相比對，有時系統表面上看起來看空閒這也可能不是一個正常的狀態，因為cpu可能正等待IO的完成。除此之外我們還應觀注那些佔用系統資源(cpu、記憶體)的程式。 

$ sar -u 1 10


HP-UX bilut42 B.11.11 U 9000/800 10/31/06


09:50:02 %usr %sys %wio %idle
09:50:03 4 1 30 65
09:50:04 7 1 27 65
09:50:05 2 0 25 73
09:50:06 1 1 21 77
09:50:07 1 0 19 80
09:50:08 8 1 18 73
09:50:09 12 1 22 65
09:50:10 9 1 22 68
09:50:11 8 0 21 71
09:50:12 9 1 20 70


Average 6 1 23 71

其中%usr是使用者程式使用cpu的百分比，%sys是系統程式使用cpu的百分比，%wio 是等待IO完成使用cpu的百分比， %idle是空閒的百分比。如果%wio列值很大，說明IO存在問題，需要進行優化，一般情況下認為%wio大於35IO就存在問題。 如果%idle值很低，說明cpu已經滿負荷。

當你的系統存在IO的問題，可以從以下幾個方面解決:

1 聯絡相應的作業系統的技術支援對這方面進行優化，比如hp-ux在劃定卷組時的條帶化等方面。

2 查詢Oracle中不合理的sql語句，對其進行優化

3 對Oracle中訪問量頻繁的表除合理建索引外，再就是把這些表分表空間存放以免訪問上產生熱點，再有就是對錶合理分割槽。

關注一下記憶體

常用的工具便是vmstat，對於hp-unix來說可以用glance,Aix來說可以用topas,當你發現vmstat中pi列非零，memory中的free列的值很小，glance,topas中記憶體的利用率多於80%時，這時說明你的記憶體方面應該調節一下了，方法大體有以下幾項。

1 劃給Oracle使用的記憶體不要超過系統記憶體的1/2,一般保在系統記憶體的40%為益。

2 為系統增加記憶體

3 如果你的連線特別多，可以使用MTS的方式

4 打全補丁，防止記憶體漏洞

如何找到點用系用資源特別大的Oracle的session及其執行的語句。

Hp-unix可以用glance,top

IBM AIX可以用topas

些外可以使用ps的命令。

通過這些程式我們可以找到點用系統資源特別大的這些程式的程式號，我們就可以通過以下的sql語句發現這個pid正在執行哪個sql，這個sql最好在pl/sql developer,toad等軟體中執行，不需要進行格式化, 把<>中的spid換成你的spid就可以了。

使用top檢視執行時間最長state為run程式的pid，使用如下的語句檢視pid在做什麼 

SELECT a.sql_text
FROM v$sqltext a,v$session b
WHERE a.hash_value = b.sql_hash_value AND b.SID='&sid'
ORDER BY piece ASC

檢視到如果是有全表掃描的語句就要進行優化。

查詢前十條效能差的sql. 

SELECT * FROM
IXDBA.NET社群論壇
(
SELECT PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC
)
WHERE ROWNUM<10 ;
迅速發現Oracle Server的效能問題的成因，我們可以求助於v$session_wait這個檢視，看系統的這些session在等什麼，使用了多少的IO。以下是我提供的參考指令碼:

指令碼說明:檢視佔io較大的正在執行的session 

SELECT se.sid, se.serial#, pr.SPID, se.username, se.status,
se.terminal, se.program, se.MODULE, se.sql_address,
st.event,st.p1text,si.physical_reads,si.block_changes
FROM v$session se, v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR
AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC

對檢索出的結果的幾點說明:

1、我是按每個正在等待的session已經發生的物理讀排的序，因為它與實際的IO相關。

2、你可以看一下這些等待的程式都在忙什麼，語句是否合理? 

Select sql_address from v$session where sid=;
Select * from v$sqltext where address=;

執行以上兩個語句便可以得到這個session的語句。

你也以用alter system kill session 'sid,serial#';把這個session殺掉。

3、應觀注一下event這列，這是我們調優的關鍵一列，下面對常出現的event做以簡要的說明:

a、buffer busy waits，free buffer waits這兩個引數所標識是dbwr是否夠用的問題，與IO很大相關的，當v$session_wait中的free buffer wait的條目很小或沒有的時侯，說明你的系統的dbwr程式決對夠用，不用調整;free buffer wait的條目很多，你的系統感覺起來一定很慢，這時說明你的dbwr已經不夠用了，它產生的wio已經成為你的資料庫效能的瓶頸，這時的解決辦法如下:

a.1增加寫程式，同時要調整db_block_lru_latches引數

示例:修改或新增如下兩個引數 

db_writer_processes=4
db_block_lru_latches=8

a.2開非同步IO，IBM這方面簡單得多，hp則麻煩一些，可以與Hp工程師聯絡。

b、db file sequential read，指的是順序讀，即全表掃描，這也是我們應該儘量減少的部分，解決方法就是使用索引、sql調優，同時可以增大db_file_multiblock_read_count這個引數。

c、db file scattered read,這個引數指的是通過索引來讀取，同樣可以通過增加db_file_multiblock_read_count這個引數來提高效能。

d、latch free,與栓相關的了，需要專門調節。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13024285/viewspace-629019/，如需轉載，請註明出處，否則將追究法律責任。

相關文章