Oracle效能 - 常用查詢診斷及調整指令碼[不斷更新] (final)
Oracle效能 - 常用查詢診斷及調整指令碼[不斷更新] (final)
一, 現象:系統執行緩慢,負載較高
1. 實時查詢長時間操作的SQL(或者透過OEM圖形介面檢視)
使用檢視 V$SESSION_LONGOPS,查詢執行超過6秒鐘的操作,這些操作也包括很多備份
恢復功能,統計資訊收集,查詢操作,不同版本可能有更多的操作加入 。
Oracle OEM中的長時間操作就是讀取這個檢視。要監控query execution progress,
必須滿足以下前提條件:
1) Oracle最佳化器使用 CBO;
2) 資料物件已經收集了統計資訊;
3) 初始化引數 TIMED_STATISTICS = true 或是 SQL_TRACE = true ;
如果是Oracle 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;
2. 10g或以上版本透過檢視SYS.wrh$_active_session_history查詢歷史等待事件及SQL
查詢等待事件及TX鎖表
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;
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'
-- AND event = '' ;
SELECT * FROM v$event_name
1.獲取最大的磁碟讀的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 < 20;
2. 透過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為top檢視到的os段的process id .
緊急處理時候可以透過kill session方式或直接kill os process來結束程式。
Alter system kill session 'SID,SERIAL#' ;
Kill -9 SPID (unix,linux)
orakill sid(oracle_instance_name) spid (windows)
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 ;
知道有問題的SPID情況下檢視正在執行的SQL .
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
3. 查詢Oracle庫中的Lock (或透過Toad中session browser或OEM參看lock情況)
檢視資料庫中的鎖(LOCK),找出程式及SQL
SELECT se.inst_id, se.SID, se.serial#,lk.SID,
se.username,se.OSUser,se.Machine,se.program,
DECODE (lk.TYPE,
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
lk.TYPE)
lock_type,
DECODE (lk.lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.lmode))
mode_held,
DECODE (lk.request,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.request))
mode_requested,
TO_CHAR (lk.id1) lock_id1,
TO_CHAR (lk.id2) lock_id2,
ob.owner,
ob.object_type,
ob.object_name,
DECODE (lk.Block,
0,
'No',
1,
'Yes',
2,
'Global')
block,
se.lockwait,
sq.sql_fulltext
FROM GV$lock lk, dba_objects ob, GV$session se, GV$sql sq
WHERE lk.TYPE IN ('TM', 'UL')
AND lk.SID = se.SID
AND lk.id1 = ob.object_id(+)
AND (lk.inst_id = se.inst_id)
AND sq.address=se.sql_address ;
軟硬解析數 :
select a.value as total_Parse, b.value as hard_parse,
a.value - b.value as soft_parse
from v$mystat a , v$mystat b,v$statname aa , v$statname bb
where aa.name = 'parse count (total)'
and bb.name = 'parse count (hard)'
and aa.statistic# = a.statistic#
and bb.statistic# = b.statistic# ;
查詢壞塊:
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)
查詢當前執行的程式:
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 () ;
select a.serial#, a.sid,b.spid from v$session a,v$process b
where a.paddr=b.addr and b.spid in ('1245','2985','5884');
Alter system kill session 'SID,SERIAL#' ;
Kill -9 SPID (unix,linux)
orakill sid(oracle_instance_name) spid (windows)
4. 透過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小時的延遲,所以其取樣資訊並不能用於診斷資料
庫的當前狀態,但可以用來作為一段時期內資料庫效能調整的參考。
5. 查詢物理讀寫嚴重的SQL及查詢哪個SID最消耗資源
檢視佔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 ;
6. 查詢物件統計資訊是否不是最新的。
select * from dba_tables
where wner='DFMS' and table_name='TEST' and LAST_ANALYZED >= sysdate-1
and status='VALID';
select * from dba_indexes where LAST_ANALYZED
where wner='DFMS' and index_name='IDX_TEST' and LAST_ANALYZED >= sysdate-1;
select * from dba_tab_columns where LAST_ANALYZED
where wner='DFMS' and table_name='TEST' and LAST_ANALYZED >= sysdate-1
and COLUMN_NAME='XXXXX' ;
7. 繫結變數窺視(Peeking)問題
9i, 10g 繫結變數窺視使得執行計劃出現變化 。11g有改善。如果出現此類
問題導致的效能問題, 需要升級到11g, 或者加入hint進行強制改變執行計劃。
8. 壞塊導致系統效能 (當然一般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)
8. Oracle9i, 10g buffer cache及Library Cache的命中率及其他
9i 命中率 :
// oracle9i data buffer hit ratio .
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 命中率 :
// oracle10g data buffer hit ratio .
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)');
9. redo log都處於active狀態。
select * from v$log ; 檢視redo log是否都出於active狀態,以及大小及組數
是否需要調大 。
10. 碎片程度定期檢視
檢視碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
分析index的碎片程度:
SQL> analyze index dfms.IDX1_WIP_D_WO_DETAIL_COMID validate structure ;
SQL> select name, del_lf_rows, lf_rows, round((del_lf_rows/(lf_rows+0.0000000001))*100)
2 "Frag Percent" from index_stats where name = 'IDX1_WIP_D_WO_DETAIL_COMID' ;
11. OS及網路檢視命令
top, sar, vmstat, iostat, ping , tnsping, route, traceroute,
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-700610/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle效能問題 - 常用查詢診斷及調整指令碼[不斷更新] (final)Oracle指令碼
- Oracle效能異常查詢及調整指令碼-不斷更新(old versionl)Oracle指令碼
- 查詢Tuxedo積壓的Oracle診斷指令碼UXOracle指令碼
- Oracle效能問題檢查 - 常用查詢指令碼(final)Oracle指令碼
- Oracle Wait Interface效能診斷與調整實踐指南OracleAI
- 某公司oracle 效能調優診斷案例Oracle
- 【RAC】Oracle Clusterware 診斷收集指令碼Oracle指令碼
- oracle維護管理指令(不斷更新)Oracle
- OWI效能診斷與調整實踐指南(1~4)
- oracle 效能診斷工具Oracle
- TiDB 查詢優化及調優系列(三)慢查詢診斷監控及排查TiDB優化
- MySQL效能診斷與調優MySql
- RAC故障診斷指令碼指令碼
- Oracle效能診斷藝術Oracle
- oracle效能優化(二)-調整查詢Oracle優化
- MySQL故障診斷常用方法手冊(含指令碼、案例)MySql指令碼
- Oracle效能診斷一例Oracle
- Oracle DBA常用Linux,Unix命令(不斷更新中)OracleLinux
- 【SQL】長事務診斷指令碼SQL指令碼
- Oracle效能診斷檢視總結Oracle
- 一次ORACLE IO效能診斷案例Oracle
- Oracle SQLT 診斷SQL語句效能(3)OracleSQL
- Oracle SQLT 診斷SQL語句效能(2)OracleSQL
- Oracle SQLT 診斷SQL語句效能(1)OracleSQL
- Oracle效能問題診斷一例Oracle
- ORACLE之常用FAQ:效能調整Oracle
- Oracle系統執行慢及資源消耗多問題診斷 [final]Oracle
- ORACLE診斷案例Oracle
- Oracle故障診斷Oracle
- ORACLE診斷事件Oracle事件
- 整車EOL診斷系統
- 整車EOL 診斷系統
- Java jvm 診斷調優JavaJVM
- 案例 - EBS SQL效能診斷SQL
- 等待事件效能診斷方法事件
- Oracle效能優化視訊學習筆記-診斷和調優工具Oracle優化筆記
- Oracle診斷事件列表Oracle事件
- ORACLE診斷事件(zt)Oracle事件