1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

About wait events in oracle.

Discussion in 'Server Administration and Options' started by Arju, Oct 18, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    One way of detecting performance problem in oracle is identifying wait events statistics in the AWR report. Wait event reports show that an event has to wait for another event to continue processing.

    The problem symptoms of wait events are latch contention, I/O contention, buffer contention etc.

    In the AWR report you can see how long an event is waited and also number of times an event was waited for.

    With help of various dynamic performance view you can measure the wait events also. Some listings are given below.

    1)V$ACTIVE_SESSION_HISTORY
    2)V$SESSION_WAIT
    3)V$SESSION
    4)V$SESSION_EVENT
    5)V$SESSION_WAIT_HISTORY
    6)V$SYSTEM_EVENT
    7)V$SYSTEM_WAIT_CLASS


    After investigate wait events and timing you further look for tuning.

    An example of this is you might get lots of buffer busy waits problem. This problem typically happens when lots of processes try to insert data into the same data block as before inserting another row in the same block it must wait for each other before insert. One case where this problem happens is try to insert index on the same block. This can be solved by using reverse key index. Or if the problem is due to row inserting then you might choose to partition the table.