Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 31 views

    Anar Godjaev: Oracle Wait Interface (OWI)

    Uploaded by

    Mabu Dba
    The document discusses Oracle's wait interface (OWI) views that can be used to monitor wait events and sessions waiting on those events. It provides information on system-wide and session-specific wait views, how to examine current session waits, and how to view system and session statistics related to waits. Key views discussed include v$system_event, v$session_event, v$session_wait, and v$statname. The document also lists some important wait events and idle events to monitor.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd

    Anar Godjaev: Oracle Wait Interface (OWI)

    Uploaded by

    Mabu Dba
    31 views3 pages
    The document discusses Oracle's wait interface (OWI) views that can be used to monitor wait events and sessions waiting on those events. It provides information on system-wide and session-specific wait views, how to examine current session waits, and how to view system and session statistics related to waits. Key views discussed include v$system_event, v$session_event, v$session_wait, and v$statname. The document also lists some important wait events and idle events to monitor.

    Original Description:

    k

    Original Title

    wait-20interface-131101073759-phpapp02.docx

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses Oracle's wait interface (OWI) views that can be used to monitor wait events and sessions waiting on those events. It provides information on system-wide and session-specific wait views, how to examine current session waits, and how to view system and session statistics related to waits. Key views discussed include v$system_event, v$session_event, v$session_wait, and v$statname. The document also lists some important wait events and idle events to monitor.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    31 views3 pages

    Anar Godjaev: Oracle Wait Interface (OWI)

    Uploaded by

    Mabu Dba
    The document discusses Oracle's wait interface (OWI) views that can be used to monitor wait events and sessions waiting on those events. It provides information on system-wide and session-specific wait views, how to examine current session waits, and how to view system and session statistics related to waits. Key views discussed include v$system_event, v$session_event, v$session_wait, and v$statname. The document also lists some important wait events and idle events to monitor.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 3

    Anar Godjaev

    http://anargodjaev.wordpress.com/
    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,

    Anar Godjaev
    http://anargodjaev.wordpress.com/
    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 -- 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

    Anar Godjaev
    http://anargodjaev.wordpress.com/
    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

    You might also like