無工具情況下Oracle應急診斷思路（一）

一、發現哪些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;

[@more@]

在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