幾個重要檢視（V$SYSTEM_EVENT V$SESSION_EVENT V$SESSION_WAIT）

V$SYSTEM_EVENT 是oracle所有事件的鳥覽圖,它不包含session級的資訊,但是它概括了從上次啟動以來的所有的wait事件. Event 這是事件的名稱.比如某些普通而最有意義的事件:enqueue, buffer busy waits, latch free, db file scattered read, db file sequential read, and free buffer waits. Total_Waits 這是從instance啟動以來所給事件的等待次數. Total_Timeouts 這提供了所有等待超時的總次數 Time_Waited 這是全部等待時間(本次instance啟動後所給定事件的阿全部等待時間),單位1/100秒. Average_Wait 這是平均等待時間,Average_Wait = (time_waited/total_waits),單位1/100秒 drop table BEGIN_SYS_EVENT; drop table END_SYS_EVENT; /* Create Begin Table at Time T1 */ create table BEGIN_SYS_EVENT as select * from V$SYSTEM_EVENT; /* Wait n seconds or n minutes */ /* Create End Table at Time T2 */ create table END_SYS_EVENT as select * from V$SYSTEM_EVENT; /* View delta numbers for wait events between Begin (T1) and End (T2) */ select T1.Event, (T2.Total_Waits—T1.Total_Waits) "Delta Waits", (T2.Total_Timeouts—T1.Total_Timeouts) "Delta Timeouts", (T2.Time_Waited—T1.Time_Waited) "Delta Time Waited", (T2.Average_Wait—T1.Average_Wait) "Delta Average Wait" from BEGIN_SYS_EVENT T1, END_SYS_EVENT T2 where T1.Event = T2.Event; 這就能挑選出你所關心的部分,比如IO事件:db file scattered read, db file sequential read, or free buffer waits . 比如記憶體事件: buffer busy waits V$SESSION_EVENT 這個檢視提供了核V$SYSTEM_EVENT同樣的資訊,但是是session級的.它也提供了Sid,這可以和v$session聯絡檢視各個session正在做什麼. select S.Username, S.Program, S.Status, SE.Event, SE.Total_Waits, SE.Total_Timeouts, 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_WAIT 這個檢視提供了對每個事件來說最低階的資訊.它是session級的.不像某些檢視顯示總體的資訊,這個檢視在真實時間裡的session級的等待資訊.你每次查詢它都會有不同的結果. 比如一個session透過dbfile sequential read正在等待an index scan,它就提供了正在發生的檔案號和塊號. SID 這個是session的id Seq# 這是一個內部的和這個session相關的等待sequence號,這個值決定了正在等待給定事件所決定的號 Event 事件的名字,比如: enqueue, buffer busy waits, latch free, db file scattered read, db file sequential read, and free buffer waits. 檢視這些重複的事件,不用關心PMON Timer, RDBMS timer等空閒事件,這通常表明instance正在等待某些時事件. P[1-3] 這3列告訴我們真實的給定事件的意義. Here it is folks! This is the treasure we have been digging around for. These three columns contain the detail information that really tells us what a given wait event means. The values in these columns are the logical relationships (foreign keys) to other V$ views. This is also where you have to really pay attention, because the interpretation of a value here is wait-event dependent. 比如等待的時db file scattered read ,P1就包括檔案號,P2包括正等待程式的塊號,P3包括從P2指定的塊號開始讀的塊的數量.透過使用P1來查詢V$FILESTAT or DBA_DATA_FILES,透過P2查詢DBA_EXTENTS or SYS.UET$,你就能決定這個session正在等待的物件.如果有幾個程式等待同樣的檔案或者在同樣檔案系統的檔案,就檢視IP分佈來修改這問題. 如果是Latch Free,P2就是latch的號,指向v$latch. State 被給的一個重要的指示器,它提供了接下來2列的細節(wait_time and seconds_in_wait),不能全面的理解這個state,wait_time and seconds_in_wait的價值就很低.這裡有4種等待事件(不包括Texas): .WAITING 這個session當前正在等待事件.並不很強烈 .WAITED UNKNOWN TIME 如果TIMED_STATISTICS被設為false,這個就是真的 .WAITED SHORT TIME 這個值意味著等待的無關緊要的時間,這個不用擔心,除非他們發生的非常頻繁 .WAITED KNOWN TIME 如果一個程式獲得了它所等待的資源,state列將改為WAITED KNOWN TIME Wait_time 這個值依靠STATE ,單位為秒: If STATE in ('WAITING','WAITED UNKNOWN TIME','WAITED SHORT TIME') then WAIT_TIME = Irrelevant; End If; If STATE = 'WAITED KNOWN TIME' then WAIT_TIME = Actual wait time, in seconds; End If; 如果你有WAITED_SHORT_TIME ,這就沒有什麼問題,除非一遍一遍的重複出現. 如果當前是WAITING ,你就真的不知道WAIT_TIME最後是什麼,這個值並沒有什麼用,這時就看SECONDS_IN_WAIT 如果你有WAITED UNKNOWN TIME ,這時因為TIMED_STATISTICS沒有設為true ,因此並不相關. 但是有一件事顯示系統繁忙,這個session正在等待多個資源和開始等待另外的資源,這個等待事件的STATUS將改為WAITING,WAIT_TIME 又等於Irrelevant了 Seconds_in_wait If STATE in ('WAITED UNKNOWN TIME','WAITED_SHORT TIME','WAITED KNOWN TIME') then SECONDS_IN_WAIT = Irrelevant; End If; If STATE = 'WAITING' then SECONDS_IN_WAIT = Actual Wait Time in seconds; End If; 該檢視的 P1RAW,P2RAW,P3RAW 列對應 P1,P2,P3 的十六進位制值；P1TEXT,P2TEXT,P3TEXT 列對應 P1,P2,P3 列的解釋。 SQL> 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'); 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.Username is not null and SE.Sid = S.Sid and S.Status = 'ACTIVE' and SE.Event not like '%SQL*Net%'; 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; select Sid, Event, P1text, P1, P2text, P2, P3text, P3 from V$SESSION_WAIT where Sid between 28 and 52 and Event not like '%SQL%' and Event not like '%rdbms%'; 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; CREATE OR REPLACE FUNCTION GetSQLTxt (HashAddr_In IN V$SQLTEXT.Hash_Value%TYPE ,Addr_In IN V$SQLTEXT.Address%TYPE) RETURN VARCHAR2 IS Temp_SQLTxt varchar2(32767); CURSOR SQLPiece_Cur IS select Piece, Sql_Text from V$SQLTEXT where Hash_Value = HashAddr_In and Address = Addr_In order by Piece; BEGIN FOR SQLPiece_Rec IN SQLPiece_Cur LOOP Temp_SQLTxt := Temp_SQLTxt || SQLPiece_Rec.Sql_Text; END LOOP; RETURN Temp_SQLTxt; END GetSQLTxt; / select Sid, GetSQLtxt(Sql_Hash_Value, Sql_Address) from V$SESSION where Sid = &Sid_In;