几个重要视图（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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/751371/viewspace-564933/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/751371/viewspace-564933/