Oracle Wait Interface (OWI)


 
Oracle wait interface is a group of views that you can use to monitor waiting events and sessions waiting them.

1-  first thing is thinking system wide.
--
-- System wide event waits
--
select * from v$system_event order by total_waits desc;
--
--
select * from v$event_name;

2-  and then session wide
-- Session wide events
--
-- you can see the SID and easily join with v$session
select * from v$session_event order by total_waits desc;

3-  time to examine session waits currently
--
-- session wide waits
--
-- you can obtain P1, P2 and P3 parameter meanings in v$event_name
select * from v$session_wait order by seconds_in_wait desc;

NOTES
N1- P1, P2 and P3 columns are important in some types of waits. For example:

If (event_name IN
             (‘db file sequential read’ , ‘db file scattered read’))
then
             P1 means file#
             P2 means block#
             P3 means blocks
End If;

--
-- lets find sessions waiting the files
--
 
select
t1.event,
t1.state,
t1.wait_time,
t1.seconds_in_wait,
t2.username,
t3.name
from
v$session_wait t1,
v$session t2,
v$datafile t3
where
t1.sid = t2.sid and
t1.p1 = t3.file# and
t1.event in ('db file sequential read','db file scattered read');

N2- There are two columns indicationg the waited time. “wait_time” and “second_in_wait”. You can consider these values by reading “state” column.

If (“state” = WAITING) then
         Consider “SECONDS_IN_WAIT”
Else If (“state” = WAITED_KNOWN_TIME) then
         Consider “WAIT_TIME”
Else If (“state” = WAITED_UNKNOWN_TIME) then
Alter system set timed_statistics=TRUE -- J
End If

4-  you can also examine system wide and session wide statistics
--
-- system statistics
--

select * from v$sysstat;
--
-- session statistics
--
-- you cannot find statistics name in v$sesstat
-- so we have to join with v$statname


select
    t1.sid,
    t3.username,
    t2.name,
    t1.value
from
    v$sesstat t1,
    v$statname t2,
    v$session t3
where
     t1.statistic# = t2.statistic# and
     t1.sid = t3.sid and
     t3.username is not null;

--
--
--
select * from v$statname;

List of views
v$system_event
          System wide wait events.
          Values are total waits until system startup.
          Wait values are reset every system restart.
v$event_name
          Properties of wait events.
v$session_event
           Session wide wait events.
           Has almost same properties with v$system_event.
           There is one mor column for SID.
v$session_wait
           Session wide waits, this view gives you the currents waits by user                   and resets every session kills itself.
v$latch
v$latchname
v$enqueue
v$sysstat
v$sesstat
v$filestat
v$tempstat
v$sgastat
v$pgastat
v$statname

parameters
      timed_statistics
      statistics_level

some of the important wait events (non idle wait events)
       buffer busy waits
       db file scattered read
       db file sequential read
       enqueue
       free buffer waits
       latch free
       log file parallel write
       log file sync

NOTES
           These kind of events points:
           Inefficient sql.
           Inefficient system architecture.
           Inproper instance confugiration.

Some of the idle events (can be ignored most of the times)
            NULL
            SQL*NET Message
            Rdbms ipc message
            Pmon timer
            Pipe get
            Smon timer

NOTES
             Not an indiaction of performance problem.

Some of the important statistics
bytes received via SQL*Net from client
bytes sent via SQL*Net to client
consistent gets               --consistent gets+db block gets = logical IO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: