一、发现哪些session导致瓶颈或等在什么event上

在Oracle10g上，通过v$session视图就能找到瓶颈session,而Oracle9i中，除了v$session 还需要查询v$session_wait视图，具体如下：

在oracle 9i

select event, count(*) sessions from v$session_wait

where state='WAITING'

and Event not like '%SQL*Net%'

and USERNAME is not null

group by event

order by 2 desc;

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.Username is not null

and SW.Sid = S.Sid

and SW.Event not like '%SQL*Net%'

order by SW.Wait_Time Desc;

在oracle 10g

select event, count(*) sessions from v$session

where state='WAITING'

and event not like '%SQL*Net%'

and USERNAME is not null

group by event

order by 2 desc;

select s.SID,s.USERNAME,s.EVENT,s.WAIT_TIME,s.SECONDS_IN_WAIT,s.LAST_CALL_ET,s.STATE,

s.BLOCKING_SESSION,s.BLOCKING_SESSION_STATUS

from v$session

where s.USERNAME is not null

and s.STATUS = 'ACTIVE'

and s.EVENT not like '%SQL*Net%'

order by s.SECONDS_IN_WAIT;

知道有问题session后，需要进一步查出导致等待的对象或SQL,在此针对不同evet需要采取不同的处理方法，对db file scattered read和db file sequential read类event，可以采取如下查询：

--确定导致等待对象：

select Sid, Event, P1text, P1, P2text, P2, P3text, P3,SQL_ADDRESS,SQL_HASH_VALUE

from V$SESSION

where event not like '%SQL%'

and Event not like '%rdbms%';

and Sid between &sid1 and &sid2

select Owner, Segment_Name, Segment_Type, Tablespace_Name

from DBA_EXTENTS

where File_Id = &FileId_In

and &BlockId_In between Block_Id and Block_Id + Blocks - 1;

--确定导致等待的SQL:

select sql_id,child_number,sql_text,sql_fulltext from v$sql

where hash_value=&hv

and address=&addr

--找出该SQL相关的执行计划：

调用dbms_xplan包，查看该语句执行时的执行计划：

select * from table(dbms_xplan.display_cursor('sql_id'));

SQL> select * from table(dbms_xplan.display_cursor('9dy3zmd40w004'));

根据SQL执行计划，优化SQL，即对该SQL相关索引进行优化

如下为全表扫描和索引扫描的英文解释，共参考：
db file scattered readThe db file scattered read event is posted by the session when it issues an I/O request to read multiple data blocks. The blocks read from the datafiles are scattered into the buffer cache. These blocks need not remain contiguous in the buffer cache. The event typically occurs during full table scans or index fast full scans. The initialization parameter, DB_FILE_MULTIBLOCK_READ_COUNT determines the maximum number of data blocks to read.
Waiting on datafile I/O completion is normal in any Oracle database. The presence of this wait event does not necessarily indicate a performance problem. However, if the time spent waiting for multiblock reads is significant compared to other waits, you must investigate the reason for it.
Wait Parameters
Wait parameters for db file scattered read are described here:
• P1 File number to read the blocks from
• P2 Starting block number to begin reading
• P3 Number of blocks to read
Wait Time
No timeouts. The session waits until all of the I/Os are completed to read specified number of blocks.

db file sequential readThe db file sequential read wait event occurs when the process waits for an I/O completion for a sequential read. The name is a bit misleading, suggesting a multiblock operation, but this is a single block read operation. The event gets posted when reading from an index, rollback or undo segments, table access by rowid, rebuilding control files, dumping datafile headers, or the datafile headers.
Waiting on datafile I/O completion is normal in any Oracle Database. The presence of this wait event does not necessarily indicate a performance problem. However, if the time spent waiting for single block reads is significant compared to other waits, you must investigate the reason for it.
Wait Parameters
No timeouts. Wait parameters for db file sequential read are described here:
• P1 File number to read the data block from
• P2 Starting block number to read