）設立合理的性能優化目標
）測量並記錄當前性能
）確定當前Oracle性能瓶頸（Oracle等待什麼哪些SQL語句是該等待事件的成分）
）把等待事件記入跟蹤文件
）確定當前的OS瓶頸
）優化所需的成分（應用程序數據庫I/O爭用OS等）
）跟蹤並實施更改控制過程
）測量並記錄當前性能
）重復步驟到直到滿足優化目標
．設立合理的性能優化目標
重點關於設立目標的最重要的一點是它們必須是可量化和可達到的
方法目標必須是當前性能和所需性能的的陳述形式的語句只需填寫下列語句中的空格即可
花費了 （時/分/秒）但要求它在 （時/分/秒）內執行
使用了 （資源量）但它不能使用超過
． 測量並記錄當前性能
重點
（）需要在峰值活動時間獲得當前系統性能快照
（）關鍵是要在出現性能問題的時間段內采集信息
（）必須在合理的時間段上采集一般在峰值期間照幾個為期分鐘的快照
方法執行STATSPACK
建立性能快照表空間
sqlplus sys as sysdba
create tablespace perfstat datafile /u/oradata/dbnms/perfstatdbf size M extent management local;
安裝STATSPACK
@$ORACLE_HOME/rdbms/admin/spcreatesql;
獲取性能數據可以生成多個快照
sqlplus perfstat
execute statspacksnap;
生成性能快照的報表
sqlplus perfstat
select min(snap_id) snapid_min max(snap_id) snapid_max from stats$snapshot;
@$ORACLE_HOME/rdbms/admin/spreport;
該報告中有關於性能的重要信息如前位的等待事件cache大小各種內存結構的命中率每秒及每事務邏輯物理讀寫數據塊數性能最差的sql語句等
． 確定當前Oracle性能瓶頸
重點從Oracle 等待接口v$system_eventv$session_event和v$session_wait中獲得等待事件進而找出影響性能的對象和sql語句
方法
首先利用v$system_event視圖執行下面的查詢查看數據庫中某些常見的等待事件
select * from v$system_event
where event in (buffer busy waits
db file sequential read
db file scattered read
enqueue
free buffer waits
latch free
log file parallel write
log file sync);
接著利用下面對v$session_event和v$session視圖進行的查詢研究具有對上面顯示的內容有貢獻的等待事件的會話
select sesidsusernameseeventsetotal_waitssetime_waitedseaverage_wait
from v$session sv$session_event se
where ssid = sesid
and seevent not like SQL*Net%
and sstatus = ACTIVE
and susername is not null;
使用下面查詢找到與所連接的會話有關的當前等待事件這些信息是動態的為了查看一個會話的等待最多的事件是什麼需要多次執行此查詢
select swsidsusernamesweventswwait_timeswstateswseconds_in_wait SEC_IN_WAIT
from v$session sv$session_wait sw
where ssid = swsid
and swevent not like SQL*Net%
and susername is not null
order by swwait_time desc;
查詢會話等待事件的詳細信息
select sideventptextpptextpptextp
from v$session_wait
where sid between & and &
and event not like %SQL%
and event not like %rdbms%;
利用PP的信息找出等待事件的相關的段
select ownersegment_namesegment_typetablespace_name
from dba_extents
where file_id = &fileid_in
and &blockid_in between block_id and block_id + blocks ;
獲得操作該段的sql語句
select sid getsqltxt(sql_hash_valuesql_address)
from v$session
where sid = &sid_in;
getsqltxt函數
create or replace
function GetSQLtxt (hashaddr_in in v$sqltexthash_value%type
addr_in in v$sqltextaddress%type)
return varchar
is
temp_sqltxt varchar();
cursor sqlpiece_cur
is
select piecesql_text
from v$sqltext
where hash_value = hashaddr_in
and address = addr_in
order by piece;
begin
for sqlpiece_rec in sqlpiece_cur
loop
temp_sqltxt := temp_sqltxt || sqlpiece_recsql_text;
end loop;
return temp_sqltxt;
end GetSQLtxt;
至此已經找到影響性能的對象和sql語句可以有針對性地優化
． 把等待事件記入跟蹤文件
重點如果在跟蹤系統上的等待事件時由於某種原因遇到了麻煩則可以將這些等待事件記入一個跟蹤文件
方法
對於當前會話
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set events trace name context forever level ;
執行應用程序然後在USER_DUMP_DEST指出的目錄中找到跟蹤文件
查看文件中以詞WAIT開始的所有行
對於其它的會話
確定會話的進程ID（SPID）下面的查詢識別出名稱以A開始的所有用戶的會話進程ID
select SUsername PSpid from V$SESSION S V$PROCESS P
where SPADDR = PADDR and SUsername like A%;
以 sysdba 進入sqlplus執行
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
oradebug setospid <SPID>
oradebug unlimit
oradebug event trace name context forever level X /* Where X = () */
跟蹤某個時間間隔得會話應用程序
在USER_DUMP_DEST 的值指出的目錄中利用SPID查看跟蹤文件
查看文件中以詞WAIT開始的所有行
． 確定當前OS瓶頸
（）Windows NT上的監控
使用控制面板〉管理工具〉性能即可
（）UNIX上的監控
使用通用性的工具包括sariostatcpustatmpstatnetstattoposview等
） CPU使用情況
sar u
%sys和%wio的數值應該小於百分之到
） 設備使用情況
sar d
在%busy超過%時最佳設備利用率開始降低在具有足夠磁盤高速緩存的系統上認為avserv為毫秒的值非常高
） 虛擬內存使用情況
vmstat S
執行隊列（r）應該明確的平均小於（*CPU數目）
．優化所需的成分（應用程序數據庫I/O爭用OS等）
．跟蹤並實施更改控制過程
．測量並記錄當前性能
．重復步驟到直到滿足優化目標
From:http://tw.wingwit.com/Article/program/Oracle/201311/18162.html
