1、监控等待事件
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;
Lock wait occur when a session attempts to acquire a lock that is already held by another session. A session will be blocked until the blocking session releases the lock. Locks are designed to ensure data integrity by limiting simultaneous data access.
Multi-user database locking generally consists of two levels: exclusive locks and share locks. You want to watch out for exclusive locks (that is, TX) as they prohibit resource sharing. For example, the first transaction that exclusively locks a resource is the only one that can alter the resource (except for the DBA) until the exclusive lock is released. Share locks, unlike exclusive locks, allow a resource to be shared.
Deadlocking is commonly seen in multi-user systems. It typically occurs when all the hung users are waiting to access a table that another user has locked. This situation causes a deadlock, because each user (transaction) is waiting for resources to be freed by the other user (the blocker). Often, many developers attempt to update the same table and many users attempting to update or select from the same table.
Most locking issues are application-specific and can be addressed by tuning the concurrency logic in the application.
也可利用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
诊断：
If the number of physical block reads is significantly higher than the number of physical reads, this is an indication that the indexes on these tables may need to be reviewed, or there may be full table scans being performed on the tables within the tablespace. In general, if the number of block reads is equal to the number of reads, the tables in the tablespace were being accessed by a ROWID, requiring the database to read only one data block.
If one of the data files is getting a majority of the reads and writes, you may be able to improve performance by creating multiple data files on seperate disks or by striping the data file across multiple disks.
3、查询是否有长时间的操作
同时满足以下几个条件，操作信息才会出现在V$SESSION_LONGOPS中：
1）、操作是以下几种操作之一
# Table scan;
# Index Fast Full Scan;
# Hash join;
# Sort/Merge;
# Sort Output;
# Rollback;
# Gather Table's Index Statistics
2）、操作时间大于6秒
3）、读取的block数目大于一定量
如果是TABLE FULL SCAN，读取的block数目至少大于10000
如果是Index Fast Full Scan，读取的block数目至少大于1000
其他操作读取block的数目不明
实验：
create table tt as select * from all_objects;
commit;
Set timing on;
select * from tt order by 1,2,3,4;
用以下语句找出长时间操作的SQL语句：
select longops.sid,longops.elapsed_seconds,longops.opname,sql.sql_text from
v$session_longops longops , v$sql sql where longops.elapsed_seconds>6 and longo
ps.sql_id=sql.sql_id;
或者：
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
;
