Oracle效能最佳化基本方法詳解

Oracle效能最佳化基本方法包括一下幾個步驟，包括：

　　1）設立合理的Oracle效能最佳化目標。

　　2）測量並記錄當前的Oracle效能。

　　3）確定當前Oracle效能瓶頸（Oracle等待什麼、哪些SQL語句是該等待事件的成分）。

　　4）把等待事件記入追蹤檔案。

　　5）確定當前的OS瓶頸。

　　6）最佳化所需的成分（應用程式、資料庫教程、I/O、爭用、OS等）。

　　7）跟蹤並實施更改控制過程。

　　8）測量並記錄當前效能

　　9）重複步驟3到7，直到滿足最佳化目標

　　下面來一一詳述。

　　1.設立合理的Oracle效能最佳化目標

　　重點：關於設立目標的最重要的一點是它們必須是可量化和可達到的。

　　方法：目標必須是當前效能和所需效能的的陳述形式的語句。

　　2.測量並記錄當前Oracle效能重點：

　　1）需要在峰值啟用時間獲得當前系統效能快照

　　2）關鍵是要在出現效能問題的時間段內採集資訊

　　3）必須在合理的時間段上採集，一般在峰值期間照幾個為期15分鐘的快照

　　3.確定當前Oracle效能瓶頸重點：從Oracle 等待介面v$system_event、v$session_event和v$session_wait中獲得等待事件，進而找出影響效能的對象和sql語句。方法如下：

　　1)首先，利用v$system_event視圖執行下面的查詢查看資料庫中某些常見的等待事件：

　　1.select * from v$system_event

　　2.

　　3.where event in ('buffer busy waits',

　　4.

　　5.'db file sequential read',

　　6.

　　7.'db file scattered read',

　　8.

　　9.'enqueue',

　　10.

　　11.'free buffer waits',

　　12.

　　13.'latch free',

　　14.

　　15.'log file parallel write',

　　16.

　　17.'log file sync');

　　18.

　　2)接著，利用下面對v$session_event和v$session視圖進行的查詢，研究具有對上面顯示的內容有貢獻的等待事件的會話：

　　1.select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait

　　2.

　　3.from v$session s,v$session_event se

　　4.

　　5.where s.sid = se.sid

　　6.

　　7.and se.event not like 'SQL*Net%'

　　8.

　　9.and s.status = 'ACTIVE'

　　10.

　　11.and s.username is not null;

　　12.3)使用下面查詢找到與所串連的會話有關的當前等待事件。這些資訊是動態，為了查看一個會話的等待最多的事件是什麼，需要多次執行此查詢。

　　1.select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait SEC_IN_WAIT

　　2.

　　3.from v$session s,v$session_wait sw

　　4.

　　5.where s.sid = sw.sid

　　6.

　　7.and sw.event not like 'SQL*Net%'

　　8.

　　9.and s.username is not null

　　10.

　　11.order by sw.wait_time desc;

　　12.

　　4)查詢會話等待事件的詳細資料

　　1.select sid,event,p1text,p1,p2text,p2,p3text,p3

　　2.

　　3.from v$session_wait

　　4.

　　5.where sid between &1 and &2

　　6.

　　7.and event not like '%SQL%'

　　8.

　　9.and event not like '%rdbms%';

　　10.

　　5)利用P1、P2的資訊，找出等待事件的相關的段

　　1.select owner,segment_name,segment_type,tablespace_name

　　2.

　　3.from dba_extents

　　4.

　　5.where file_id = &fileid_in

　　6.

　　7.and &blockid_in between block_id and block_id + blocks - 1;8.

　　6)獲得操作該段的sql語句：

　　1.select sid, getsqltxt(sql_hash_value,sql_address)

　　2.

　　3.from v$session

　　4.

　　5.where sid = &sid_in;

　　6.

　　7)getsqltxt函數

　　8)至此已經找到影響效能的對象和sql語句，可以有針對性地最佳化

　　4.把等待事件記入追蹤檔案

　　重點：如果在跟蹤系統上的等待事件時，由於某種原因遇到了麻煩，則可以將這些等待事件記入一個追蹤檔案。方法如下：

　　1)對於當前會話：

　　1.alter session set timed_statistics=true;

　　2.

　　3.alter session set max_dump_file_size=unlimited;

　　4.

　　5.alter session set events '10046 trace name context forever, level 12';

　　6.

　　2)執行應用程式，然後在USER_DUMP_DEST指出的目錄中找到追蹤檔案。

　　3)查看檔案中以詞WAIT開始的所有行。

　　4)對於其它的會話

　　5)確定會話的進程ID（SPID）。下面的查詢識別出名稱以A開始的所有使用者的會話進程ID：

　　1.select S.Username, P.Spid from V$SESSION S, V$PROCESS P

　　2.

　　3.where S.PADDR = P.ADDR and S.Username like 'A%';

　　4.

　　6)以sysdba進入sqlplus執行

　　1.alter session set timed_statistics=true;

　　2.

　　3.alter session set max_dump_file_size=unlimited;

　　4.

　　5.oradebug setospid

　　6.

　　7.oradebug unlimit

　　8.

　　9.oradebug event 10046 trace name context forever, level X /* Where X = (1,4,8,12) */

　　10.

　　7)跟蹤某個時間間隔得會話應用程式。

　　8)在USER_DUMP_DEST 的值指出的目錄中利用SPID查看追蹤檔案

　　9)查看檔案中以詞WAIT開始的所有行。

　　5.確定當前OS瓶頸1）Windows NT上的監控

　　使用控制台-〉管理工具-〉效能即可

　　2）UNIX上的監控

　　使用通用性的工具，包括sar、iostat、cpustat、mps教程tat、netstat、top、osview等。

　　6．Oracle效能最佳化所需的成分（應用程式、資料庫、I/O、爭用、OS等）。

　　7．跟蹤並實施更改控制過程。

　　8．測量並記錄當前Oracle效能

　　9．重複步驟3到7，直到滿足最佳化目標