一， 现象：系统运行缓慢，负载较高
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)');