Oracle效能問題 - 常用查詢診斷及調整指令碼[不斷更新] (final)
一， 現象：系統執行緩慢，負載較高
0. SID, PID及SQL相關檢視
-- 透過OS PID得到SID及透過SID得到OS PID :
SELECT pro.spid, sess.SID, sess.* from v$session sess, v$process pro
where sess.PADDR = pro.ADDR and pro.spid=26457 ;
SELECT pro.spid, sess.SID, sess.* from v$session sess, v$process pro
where sess.PADDR = pro.ADDR and sess.sid=1077 ;
-- 根據OS PID查出sid,serial# (kill session用得到)
select s.sid, s.serial# from v$session s, v$process p
where s.paddr=p.addr and p.spid='138482'
比如sid，serial#為482，56767
-- 結束session及OS中的process .
Alter system kill session 'SID,SERIAL#' ;
Kill -9 SPID (用於unix,linux)
orakill SID(oracle例項名稱) spid (windows)
orakill引數取得方式：
select instance_name from v$instance;
select s.sid, spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr;
-- 透過Oracle SID得到問題程式及不完整SQL
SELECT sid, serial#, osuser, username, status,
machine, terminal, program, type, sql_text
from v$session a, v$sql b
where a.sql_address =b.address and SID=&SID ;
-- 透過SID, v$sqltext得到完整的SQL:
select /*+ ORDERED */ sql_text
from v$sqltext a
where (a.hash_value,a.ADDRESS) in
( select decode(sql_hash_value,0,PREV_HASH_VALUE,sql_hash_value),
decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_ADDRESS)
from v$session b
where b.sid=&SID)
order by piece asc
或者
SELECT sql_text FROM v$sqltext a
WHERE a.hash_value =
(SELECT sql_hash_value
FROM v$session b
WHERE b.SID ='&sid')
ORDER BY piece ASC
-- 透過OS PID找到完整SQL :
select /*+ ORDERED */ sql_text
from v$sqltext a
where (a.hash_value,a.ADDRESS) in
( select decode(sql_hash_value,0,PREV_HASH_VALUE,sql_hash_value),
decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_ADDRESS)
from v$session b
where b.paddr = (select addr
from v$process c
where c.spid = '&spid')
) order by piece asc
或者
SELECT sess.*, sqlarea.SQL_TEXT
FROM V$session sess,V$process pro,V$sqlarea sqlarea
WHERE pro.SPID=&pid
AND sess.PADDR = pro.ADDR
AND sess.SQL_ADDRESS = sqlarea.ADDRESS
1. 實時查詢長時間操作的SQL(或者透過OEM圖形介面檢視)
使用檢視 V$SESSION_LONGOPS，查詢執行超過6秒或某些操作block數達到一定數量的操作，
這些操作也包括很多備份恢復功能，統計資訊收集，查詢操作，不同版本可能有更多的操
作加入 。
必須滿足以下前提條件:
1) Oracle最佳化器使用 CBO;
2) 資料物件已經收集了統計資訊;
3) 初始化引數 TIMED_STATISTICS = true 或是 SQL_TRACE = true ;
如果是RAC, 可以使用以下指令碼：
SELECT a.opname, a.SID, a.serial#,
a.sql_id, a.start_time, a.time_remaining, a.elapsed_seconds,
b.sql_fulltext, a.MESSAGE
FROM gv$session_longops a, gv$sql b
WHERE a.start_time > SYSDATE - 0.1
AND a.time_remaining > 0 AND a.sql_id = b.sql_id
ORDER BY a.start_time DESC;
如果是單機, 可以使用以下指令碼：
SELECT a.opname,a.SID, a.serial#, a.sql_id, a.start_time,
a.time_remaining, a.elapsed_seconds, b.sql_fulltext, a.MESSAGE,
b.module, b.executions
FROM v$session_longops a, v$sql b
WHERE a.start_time > SYSDATE - 0.1 AND a.time_remaining > 0
AND a.sql_id = b.sql_id
ORDER BY a.start_time DESC;
檢視ASH檢視進行診斷：
select * from v$active_session_history
where sample_time>=to_date('2011-08-16 03:08:16','yyyy-mm-dd hh24:mi:ss')
and session_type='FOREGROUND';
SELECT * FROM V$SESSION_WAIT_CLASS WHERE SID=1986 ;
select to_char(sample_time,'yyyymmdd hh24:mi:ss.ffff') sample_time,session_id,sql_id,
session_state,event,wait_class,wait_time,time_waited
from v$active_session_history
where session_id=1986 and sample_time>=to_date('2011-08-16 03:08:16','yyyy-mm-dd hh24:mi:ss')
order by sample_time;
select session_state,event,wait_class,sum(wait_time),sum(time_waited)
from v$active_session_history
where session_id=1986 and sample_time>=to_date('2011-08-16 03:08:16','yyyy-mm-dd hh24:mi:ss')
group by session_state,event,wait_class;
select session_state,event,sum(wait_time),sum(time_waited)
from v$active_session_history
where session_id=1986
group by session_state,event;
2. 10g或以上版本透過檢視SYS.wrh$_active_session_history查詢歷史等待事件
及SQL。 查詢等待事件及TX鎖表 :
select count(sid), event from v$session_wait group by event ;
SELECT sid, serial#, osuser, username, status, sql_text
from v$session a, v$sql b
where a.sql_address =b.address AND a.schemaname<>'SYS'
and a.status='ACTIVE' order by username
注意: 因為awr表是預設每1小時執行一遍, 所以查詢的不是實時資訊.
SELECT session_id, sql_id, program, machine,blocking_session,
blocking_session_serial#
FROM SYS.wrh$_active_session_history a, v$event_name b
WHERE sample_time BETWEEN
TO_DATE ('2011/06/24 07:20', 'yyyy/mm/dd hh24:mi')
AND TO_DATE ('2011/06/24 07:40', 'yyyy/mm/dd hh24:mi')
AND a.event_id = b.event_id
ORDER BY SQL_ID ;
SELECT session_id, sql_id, program, blocking_session,
blocking_session_serial#
FROM SYS.wrh$_active_session_history a, v$event_name b
WHERE sample_time BETWEEN TO_DATE ('2011/01/24 21:50', 'yyyy/mm/dd hh24:mi')
AND TO_DATE ('2011/01/24 22:35', 'yyyy/mm/dd hh24:mi')
AND a.event_id = b.event_id
AND b.NAME = 'enq: TX - row lock contention';
SELECT session_id, sql_id, program, blocking_session,blocking_session_serial#
FROM SYS.dba_hist_active_sess_history
WHERE sample_time BETWEEN TO_DATE ('2011/04/14 06:00', 'yyyy/mm/dd hh24:mi')
AND TO_DATE ('2011/04/14 08:30', 'yyyy/mm/dd hh24:mi')
AND session_type = 'FOREGROUND' ;
3. 如果發現I/O WAIT嚴重，查詢最大的磁碟讀的SQL語句
查詢物理讀寫嚴重的SQL(每次執行)
SELECT *
FROM (SELECT sql_text, module, address ,
disk_reads / DECODE (executions, 0, 1, executions) AS tt
FROM v$sqlarea a
ORDER BY tt DESC)
WHERE ROWNUM <= 20
select * from v$sqltext where address = '0000000692664EC8' order by piece ;
-------------------------------------------------------------------------------
SELECT PARSING_USER_ID EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
ROUND(DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS),1)
DISKREADS_PER_EXEC ,
ADDRESS,
MODULE,
sql_text
FROM v$sqlarea
ORDER BY DISKREADS_PER_EXEC DESC ;
select * from v$sqltext where address = '0000000692664EC8' order by piece ;
-----------------------------------------------------------------------------------------------
SELECT c.*
FROM (SELECT UPPER (b.username) username,
a.disk_reads disk_reads,
a.executions executions,
a.disk_reads / DECODE (a.executions, 0, 1, a.executions)
reads_per_exec,
a.address,
a.sql_id,
a.sql_text || CHR (10) || CHR (10) sql,
A.MODULE,
a.last_load_time last_time
FROM sys.gv_$sql a, dba_users b
WHERE a.parsing_user_id = b.user_id
AND a.disk_reads > 1000
AND b.username NOT IN ('SYS', 'SYSTEM')) c
WHERE c.reads_per_exec >= 1000
ORDER BY c.reads_per_exec DESC;
select * from v$sqltext where address = '0000000692664EC8' order by piece ;
4. 透過top看到明顯異常的PID, 透過Linux PID及SID查詢相關SQL及程式：
select a.sid,a.serial#, a.program,b.spid from v$session a,v$process b
where a.paddr=b.addr and b.spid in ('1245','2985','5884');
知道有問題的SPID情況下檢視正在執行的SQL .
select a.username,a.machine,a.program,b.spid,c.sql_text
from v$session a,v$process b,v$sqlarea c
where a.paddr=b.addr and c.hash_value=a.sql_hash_value
and c.address=a.sql_address and b.spid=12984 ;
SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,
c.piece,c.sql_text from v$session a,v$process b,v$sqltext c
WHERE b.spid='14150' AND b.addr=a.paddr AND a.sql_address=c.address(+)
order BY c.piece
spid為top檢視到的os段的process id .
緊急處理時候可以透過kill session方式或直接kill os process來結束程式。
查詢當前執行的程式：
select a.username,a.machine,a.program,b.spid,a.serial#,a.sid from v$session a,v$process b
where a.paddr=b.addr and b.spid in () ;
select a.username,a.machine,a.program,b.spid,a.serial#,a.sid from v$session a,v$process b
where a.paddr=b.addr and a.sid in () ;
select a.username,a.machine,a.program,b.spid,a.serial#,a.sid from v$session a,v$process b
where a.paddr=b.addr and a.machine in () ;
結束session及OS中的process .
Alter system kill session 'SID,SERIAL#' ;
Kill -9 SPID (用於unix,linux)
orakill sid(oracle_instance_name) spid (windows)
5. 查詢Oracle庫中的Lock (或透過Toad中session browser或OEM檢視lock情況)
檢視資料庫中的鎖(LOCK),找出程式及SQL
SELECT /*+ rule */
s.username,
DECODE (l.TYPE,
'TM', 'TABLE LOCK',
'TX', 'ROW LOCK',
NULL
) lock_level, o.owner, o.object_name, o.object_type, s.SID,
s.serial#, s.terminal, s.machine, s.program, s.osuser
FROM v$session s, v$lock l, dba_objects o
WHERE l.SID = s.SID AND l.id1 = o.object_id(+) AND s.username IS NOT NULL;
SELECT /*+ rule */
s.username,
DECODE (l.TYPE,
'TM', 'TABLE LOCK',
'TX', 'ROW LOCK',
NULL
) lock_level, o.owner, o.object_name, o.object_type, s.SID,
s.serial#, s.terminal, s.machine, s.program, s.osuser, q.sql_text
FROM v$session s, v$lock l, dba_objects o, v$open_cursor c, v$sql q
WHERE l.SID = s.SID
AND l.id1 = o.object_id(+)
AND s.username IS NOT NULL
AND s.sql_address = c.address
AND s.sql_hash_value = c.hash_value
AND q.address = s.sql_address
AND q.hash_value = s.sql_hash_value;
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
誰被鎖住?
Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name
from v$session a, audit_actions b
where a.command=b.action
AND LOCKWAIT IS NOT NULL;
誰在鎖表?
Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name
from v$session a, audit_actions b
where a.command=b.action
AND STATUS='ACTIVE';
6. 查詢物理讀寫嚴重的SQL及查詢哪個SID最消耗資源(前提：timed_statistics=true )
檢視佔I/O較大的正在執行的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 ;
查詢物理讀寫嚴重的SQL
SELECT *
FROM (SELECT sql_text, module,
disk_reads / DECODE (executions, 0, 1, executions) AS tt
FROM v$sqlarea a
ORDER BY tt DESC)
WHERE ROWNUM <= 20
查詢哪個SID最消耗資源
select s.sid,s.value "CPU Used"
from v$sesstat s,v$statname n
where s.statistic#=n.statistic# and n.name='CPU used by this session'
and s.value>0
order by 2 desc;
查詢前十條效能差的SQL
SELECT * FROM
(select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
FROM v$sqlarea order BY disk_reads DESC )
where ROWNUM<10 ;
7. 透過AWR查詢效能問題(預設有1小時延遲，不能查詢當前時間的session)
SQL> conn / AS SYSDBA
SQL> @/u01/product/oracle/rdbms/admin/awrrpt.sql
輸入 report_type 的值:
輸入 num_days 的值: 2 --- 現在到過去兩天時間內的snap id (可以檢視到).
輸入 begin_snap 的值: 2147 --- 輸入的開始及結束的snap id 對應您要查詢的出現問題的時間段。
輸入 end_snap 的值: 2182
輸入 report_name 的值:
Report written to awrrpt_1_2177_2182.html
SQL> exit
下載awrrpt_1_2177_2182.html並開啟檢視。
ASH儲存了系統最新的處於等待的會話記錄，可以用來診斷資料庫的當前狀態；
而AWR中的資訊最長可能有1小時的延遲，所以其取樣資訊並不能用於診斷資料
庫的當前狀態，但可以用來作為一段時期內資料庫效能調整的參考。
8. 查詢物件統計資訊是否不是最新的。
select * from dba_tables
where wner='CHANNEL' and table_name='AC_BA_INVOICE'
and LAST_ANALYZED >= sysdate-40
and status='VALID';
select * from dba_indexes where
where wner='DFMS' and index_name='IDX_TEST'
and LAST_ANALYZED >= sysdate-20 ;
select * from dba_tab_columns where
where wner='DFMS' and table_name='TEST'
and LAST_ANALYZED >= sysdate-1
and COLUMN_NAME='XXXXX' ;
如果統計資訊有誤或者沒有最新的統計資訊，我們可以使用如下
語句手動統計。
exec dbms_stats.gather_schema_stats(ownname => 'DFMS',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade=>TRUE, degree => 8 ) ;
9. redo log都處於active狀態。
select * from v$log ;
檢視redo log是否都處於active狀態，以及大小及組數是否需要調大 。
10. 繫結變數窺視(Peeking)問題
9i, 10g 繫結變數窺視使得執行計劃出現變化 。11g有改善。如果出現此類
問題導致的效能問題， 需要升級到11g, 或者加入hint進行強制改變執行計劃。
11. 壞塊導致系統效能 (當然一般alert log中都有error, 查詢壞塊)
SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
where file_id = 10
AND 51896 between block_id and (block_id + blocks - 1)
12. 9i,10g buffer cache及Library Cache的命中率及其他
9i buffer cache 命中率 :
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 40 and b.statistic# = 41 and c.statistic# = 42;
10g buffer cache 命中率 :
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 47 and b.statistic# = 50 and c.statistic# = 54;
監控SGA Library Cache的命中率，應該小於1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
監控記憶體和硬碟的排序比率，最好使它小於 .10，增加PGA
SELECT name, value FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-700801/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle效能 - 常用查詢診斷及調整指令碼[不斷更新] (final)Oracle指令碼
- Oracle效能異常查詢及調整指令碼-不斷更新(old versionl)Oracle指令碼
- Oracle效能問題檢查 - 常用查詢指令碼(final)Oracle指令碼
- 查詢Tuxedo積壓的Oracle診斷指令碼UXOracle指令碼
- Oracle效能問題診斷一例Oracle
- Oracle Wait Interface效能診斷與調整實踐指南OracleAI
- Oracle系統執行慢及資源消耗多問題診斷 [final]Oracle
- 某公司oracle 效能調優診斷案例Oracle
- 【RAC】Oracle Clusterware 診斷收集指令碼Oracle指令碼
- 在Oracle10g中診斷效能問題Oracle
- 使用 DBMS_SQLDIAG診斷各種查詢問題SQL
- oracle維護管理指令（不斷更新）Oracle
- oracle 效能診斷工具Oracle
- OWI效能診斷與調整實踐指南（1~4）
- TiDB 查詢優化及調優系列（三）慢查詢診斷監控及排查TiDB優化
- 斷號查詢問題
- MySQL效能診斷與調優MySql
- Oracle學習遇到的問題收集及解決 - 不斷更新Oracle
- RAC故障診斷指令碼指令碼
- Oracle效能診斷藝術Oracle
- SQL問題診斷SQL
- oracle效能優化（二）-調整查詢Oracle優化
- Oracle Stream實戰(10)—問題診斷Oracle
- Oracle效能診斷一例Oracle
- MySQL故障診斷常用方法手冊（含指令碼、案例）MySql指令碼
- 使用awr來診斷資料庫效能問題資料庫
- J2EE效能問題的診斷示例
- 診斷Oracle資料庫Hanging問題Oracle資料庫
- Oracle DBA常用Linux,Unix命令(不斷更新中)OracleLinux
- 【SQL】長事務診斷指令碼SQL指令碼
- Oracle效能診斷檢視總結Oracle
- GreysJava線上問題診斷工具Java
- 問題診斷和PLSQL方面SQL
- ThinkPHP3.2.3 常見問題（不斷更新）PHP
- 一次ORACLE IO效能診斷案例Oracle
- Oracle SQLT 診斷SQL語句效能(3)OracleSQL
- Oracle SQLT 診斷SQL語句效能(2)OracleSQL
- Oracle SQLT 診斷SQL語句效能(1)OracleSQL