--、监控等待事件
select event,
sum(decode(wait_time, 0, 0, 1)) prev,
sum(decode(wait_time, 0, 1, 0)) curr,
count(*)
from v$session_wait
group by event
order by 4;
--利用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',
'enq: TX - row lock contention');
--接着,利用下面对v$session_event和v$session视图进行的查询,研究具有对上面显示的内容有贡献的等待事件的会话:
select se.sid,
s.username,
se.event,
se.total_waits,
se.time_waited,
se.average_wait
from v$session s, v$session_event se
where s.sid = se.sid
and se.event not like 'SQL*Net%'
and s.status = 'ACTIVE'
and s.username is not null;
--还可以组合v$session和v$session_wait视图进行查询:
select sw.sid,
s.username,
sw.event,
sw.wait_time,
sw.state,
sw.seconds_in_wait SEC_IN_WAIT
from v$session s, v$session_wait sw
where s.sid = sw.sid
and sw.event not like 'SQL*Net%'
and s.username is not null
order by sw.wait_time desc;
--查询具体会话等待事件的详细信息
select sid, event, p1text, p1, p2text, p2, p3text, p3
from v$session_wait
where sid between &1 and &2
and event not like '%SQL%'
and event not like '%rdbms%';
--在查出会话执行了什么SQL语句发生等待事件:
select s1.sid, s1.event, s2.sql_text
from v$session s1, v$sql s2
where s1.sid = &sid_in
and s1.event in ('enq: TX - row lock contention')
and s1.SQL_ID = s2.sql_id;
--2、监控表空间的I/O比例:
select df.tablespace_name name,
df.file_name "file",
f.phyrds pyr,
f.phyblkrd pbr,
f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
--3、查询是否有长时间的操作
--用以下语句找出长时间操作的SQL语句:
select longops.sid,
longops.elapsed_seconds,
longops.opname,
s.sql_text,
longops.MESSAGE
from v$session_longops longops, v$sql s
where longops.elapsed_seconds > 6
and longops.sql_address = s.address;
--或者:
SELECT SE.SID,
OPNAME,
TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
ELAPSED_SECONDS ELAPSED,
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME;
--调整PGA优化排序:
--首先查看Oracle的v$pga_target_advice:
SELECT ROUND(pga_target_for_estimate / 1024 / 1024) AS target_mb,
estd_pga_cache_hit_percentage AS hit_ratio,
estd_overalloc_count
FROM v$pga_target_advice
ORDER BY target_mb;
--然后调整PGA
alter system set pga_aggregate_target = M;
/*在OLTP系统中,典型PGA内存设置应该是总内存的较小部分(例如20%),剩下80%分配给SGA。
OLTP:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
在DSS系统中,由于会运行一些很大的查询,典型的PGA内存最多分配70%的内存。
DSS:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50% */
--Oracle的排序操作:
Select * from v$sysstat where name like '%sort%';
--Sort(disk):要求IO去临时表空间的排序数目
--Sort(memory):完全在memory中完成的排序数目
--Sort(rows):被排序的行数合计
--Sort(disk)/ Sort(memory)<5%, 如果超过5%,增加sort_area_size的值(调整PGA)。
select (442/17649049)*100 from dual;
--查cursor: pin S wait on X类等待时间有用。
select a.sid,
b.sid l_sid,
a.osuser,
b.osuser l_user,
a.event,
(select sql_text
from v$sqlarea a1
where a1.sql_id = a.sql_id
and a.prev_hash_value <> 0
or a1.sql_id = a.prev_sql_id
and a.prev_hash_value = 0) c_sql,
(select sql_text
from v$sqlarea a1
where (a1.sql_id = b.sql_id and b.prev_hash_value <> 0 or
a1.sql_id = b.prev_sql_id and b.prev_hash_value = 0)) l_sql,
c.pname c_pname,
d.pname l_pname,
a.p1text,
a.p1,
a.p1raw,
a.p2text,
a.p2,
a.p2raw,
a.p3text,
a.p3,
a.p3raw
from v$session a, v$session b, v$process c, v$process d
where a.wait_class# <> 6
and a.blocking_session = b.sid(+)
and a.paddr = c.addr
and b.paddr = d.addr(+);
select * from x$kgllk a where a.kgllkreq > 0;
select * from x$kglpn a where a.kglpnreq > 0;
select * from x$kglob a where a.kglhdpmd > 0;