This discussion is archived
13 Replies Latest reply: Jun 28, 2010 12:29 AM by 683218 RSS

Wait Class

683218 Newbie
Currently Being Moderated
Hi,

As per documents In general, the addition of wait classes helps direct the DBA more quickly toward the root cause of performance problems.
How could i trace the root cause of performence problems if it is related to wait class?

Thanks,
  • 1. Re: Wait Class
    Exadba Pro
    Currently Being Moderated
    V$EVENT_NAME
    Events Defined in the Database

    V$SESSION_WAIT
    Currently Waiting Sessions

    V$SESSION_EVENT
    Waited Events for Current Sessions

    V$SYSTEM_EVENT
    Waited Events for the Instance

    V$SESSION_WAIT_HISTORY (10g)
    Shows 10 most recent waits for session

    V$SYSTEM_WAIT_CLASS (10g)
    Shows instance-level waits by class


    Event 10046 TRACE

    1.Similar to setting SQL_TRACE = true;
    2.Referred to as Extended SQL Trace
    3.Trace Level setting controls the information reported
    4.Generated trace file can be processed using tkprof
    5.TIMED_STATISTICS = TRUE
    6.MAX_DUMP_FILE_SIZE = unlimited
  • 2. Re: Wait Class
    618702 Expert
    Currently Being Moderated
    Hi userpat,

    In addition to the above message, there are wait class events like concurrency, application, CPU, user I/O etc. For instance if the wait event class concurrency is high, you may need to check the shared pool hit ratio and your applications' logic because this means you have lots of latch (library cache lock) wait event on your database.

    So you will need to understand thos event classes along with the running application and your databases dynamic components.

    Ogan
  • 3. Re: Wait Class
    CharlesHooper Expert
    Currently Being Moderated
    userpat wrote:
    Hi,

    As per documents In general, the addition of wait classes helps direct the DBA more quickly toward the root cause of performance problems.
    How could i trace the root cause of performence problems if it is related to wait class?

    Thanks,
    I am not completely sure that I understand your question. The wait class gives you an approximate idea of where the performance problem will be found. You must then further investigate the wait events in that wait class. There are of course potential problems with starting at the wait class (some wait classes have 2 wait events, while others have many - that could throw off the search for the problem that is impacting performance the most), but at least it provides a starting point. To give you an idea of the wait events in each wait class, here is a SQL statement that was executed on Oracle Database 11.1.0.7:
    SQL> DESC V$EVENT_NAME
     
     Name                                      Null?    Type
     ----------------------------------------- -------- ------------
     EVENT#                                             NUMBER
     EVENT_ID                                           NUMBER
     NAME                                               VARCHAR2(64)
     PARAMETER1                                         VARCHAR2(64)
     PARAMETER2                                         VARCHAR2(64)
     PARAMETER3                                         VARCHAR2(64)
     WAIT_CLASS_ID                                      NUMBER
     WAIT_CLASS#                                        NUMBER
     WAIT_CLASS                                         VARCHAR2(64)
     
    SELECT
      SUBSTR(NAME,1,30) EVEMT_NAME,
      SUBSTR(WAIT_CLASS,1,20) WAIT_CLASS
    FROM
      V$EVENT_NAME
    ORDER BY
      SUBSTR(WAIT_CLASS,1,20),
      SUBSTR(NAME,1,30);
     
    EVEMT_NAME                     WAIT_CLASS
    ------------------------------ --------------------
    ASM COD rollback operation com Administrative
    ASM mount : wait for heartbeat Administrative
    Backup: sbtbackup              Administrative
    Backup: sbtbufinfo             Administrative
    Backup: sbtclose               Administrative
    Backup: sbtclose2              Administrative
    ...
    OLAP DML Sleep                 Application
    SQL*Net break/reset to client  Application
    SQL*Net break/reset to dblink  Application
    Streams capture: filter callba Application
    Streams: apply reader waiting  Application
    WCR: replay lock order         Application
    Wait for Table Lock            Application
    enq: KO - fast object checkpoi Application
    enq: PW - flush prewarm buffer Application
    enq: RC - Result Cache: Conten Application
    enq: RO - contention           Application
    enq: RO - fast object reuse    Application
    enq: TM - contention           Application
    enq: TX - row lock contention  Application
    enq: UL - contention           Application
    ASM PST query : wait for [PM][ Cluster
    gc assume                      Cluster
    gc block recovery request      Cluster
    ...
    enq: BB - 2PC across RAC insta Commit
    log file sync                  Commit
    Shared IO Pool Memory          Concurrency
    Streams apply: waiting for dep Concurrency
    buffer busy waits              Concurrency
    cursor: mutex S                Concurrency
    cursor: mutex X                Concurrency
    cursor: pin S wait on X        Concurrency
    ...
    Global transaction acquire ins Configuration
    Streams apply: waiting to comm Configuration
    checkpoint completed           Configuration
    enq: HW - contention           Configuration
    enq: SQ - contention           Configuration
    enq: SS - contention           Configuration
    enq: ST - contention           Configuration
    enq: TX - allocate ITL entry   Configuration
    free buffer waits              Configuration
    ...
    ASM background timer           Idle
    DIAG idle wait                 Idle
    EMON slave idle wait           Idle
    HS message to agent            Idle
    IORM Scheduler Slave Idle Wait Idle
    JOX Jit Process Sleep          Idle
    ...
    ARCH wait for flow-control     Network
    ARCH wait for net re-connect   Network
    ARCH wait for netserver detach Network
    ARCH wait for netserver init 1 Network
    ARCH wait for netserver init 2 Network
    ARCH wait for netserver start  Network
    ARCH wait on ATTACH            Network
    ARCH wait on DETACH            Network
    ARCH wait on SENDREQ           Network
    LGWR wait on ATTACH            Network
    LGWR wait on DETACH            Network
    LGWR wait on LNS               Network
    LGWR wait on SENDREQ           Network
    LNS wait on ATTACH             Network
    LNS wait on DETACH             Network
    LNS wait on LGWR               Network
    LNS wait on SENDREQ            Network
    SQL*Net message from dblink    Network
    SQL*Net message to client      Network
    SQL*Net message to dblink      Network
    SQL*Net more data from client  Network
    SQL*Net more data from dblink  Network
    ...
    AQ propagation connection      Other
    ARCH wait for archivelog lock  Other
    ARCH wait for process death 1  Other
    ARCH wait for process death 2  Other
    ARCH wait for process death 3  Other
    ARCH wait for process death 4  Other
    ARCH wait for process death 5  Other
    ARCH wait for process start 1  Other
    ...
    Streams AQ: enqueue blocked du Queueing
    Streams AQ: enqueue blocked on Queueing
    Streams capture: waiting for s Queueing
    Streams: flow control          Queueing
    Streams: resolve low memory co Queueing
    resmgr:I/O prioritization      Scheduler
    resmgr:become active           Scheduler
    resmgr:cpu quantum             Scheduler
    ARCH random i/o                System I/O
    ARCH sequential i/o            System I/O
    Archiver slave I/O             System I/O
    DBWR slave I/O                 System I/O
    LGWR random i/o                System I/O
    ...
    BFILE read                     User I/O
    DG Broker configuration file I User I/O
    Data file init write           User I/O
    Datapump dump file I/O         User I/O
    Log file init write            User I/O
    Shared IO Pool IO Completion   User I/O
    buffer read retry              User I/O
    cell multiblock physical read  User I/O
    cell single block physical rea User I/O
    cell smart file creation       User I/O
    cell smart index scan          User I/O
    cell smart table scan          User I/O
    cell statistics gather         User I/O
    db file parallel read          User I/O
    db file scattered read         User I/O
    db file sequential read        User I/O
    db file single write           User I/O
    ...
    So, if the User I/O wait class floats to the top of the wait classes between a known start time and end time, and the Commit wait class is at the bottom of the wait classes when comparing accumulated time, it probably would not make much sense to spend time investigating the wait events in the Commit class... until you realize that there is a single event in the Commit wait class that typically contributes wait time, while there are many in the User I/O wait class.

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 4. Re: Wait Class
    683218 Newbie
    Currently Being Moderated
    Hi,

    In addition to the above message, there are wait class events like concurrency, application, CPU, user I/O etc. For instance if the wait event class concurrency is high, you may need to check the shared pool hit ratio and your applications' logic because this means you have lots of latch (library cache lock) wait event on your database.
    ---From where i can get all information like concurrency, application, CPU, user I/O etc,Do i have to use "v$sql and v$sqlarea ".Can you go in more details.

    What i want to know from where i can start to explore it?

    Thanks,

    Edited by: userpat on Jun 13, 2010 8:03 AM
  • 5. Re: Wait Class
    618702 Expert
    Currently Being Moderated
    Please go for the views that userSA has written.

    Ogan
  • 6. Re: Wait Class
    683218 Newbie
    Currently Being Moderated
    Hi,

    SELECT sql_id, application_wait_time appl, concurrency_wait_time concurr,
    user_io_wait_time user_io
    FROM v$sqlarea where application_wait_time > 0

    Can you explain me what is application_wait_time and concurrency_wait_time.When i run it there are only 20 output which is greater than 0
    what does it means,

    Many thanks Ogan Ozdogan
  • 7. Re: Wait Class
    618702 Expert
    Currently Being Moderated
    Hi userpat,

    Please first check the link below;

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2129.htm

    application wait time and concurrency wait time parameters are the values in a time level to understand the amount of the wait for both application and concurrency wait events. As far as i know those are the microseconds that processes waited in the CPU. So the high it gets in microseconds, the impact on performance should be increased.

    Ogan
  • 8. Re: Wait Class
    683218 Newbie
    Currently Being Moderated
    Hi,

    What i monitored in v$session_wait there are two columns smon timer and pmon timer , Is it anything wrong Find output below of two production instance 9i and 10g.In both smon timer show "failed" in P2TEXT columns and pmon time are showing more in SECONDS_IN_WAIT on both of instances..Suggest me

    10g:
    SID     SEQ#     EVENT     P1TEXT     P1     P1RAW     P2TEXT     P2     P2RAW     P3TEXT     P3     P3RAW     WAIT_CLASS_ID     WAIT_CLASS#     WAIT_CLASS     WAIT_TIME     SECONDS_IN_WAIT     STATE

    2393     62948     smon timer     sleep time     300     000000000000012C     failed     0     00          0     00     2723168908     6     Idle     0     3     WAITING
    2400     158     pmon timer     duration     300     000000000000012C          0     00          0     00     2723168908     6     Idle     0     41040     WAITING

    9i:
    SID     SEQ#     EVENT     P1TEXT     P1     P1RAW     P2TEXT     P2     P2RAW     P3TEXT     P3     P3RAW     WAIT_TIME     SECONDS_IN_WAIT     STATE

    1     56815     pmon timer     duration     300     000000000000012C          0     00          0     00     0     162178     WAITING
    5     14033     smon timer     sleep time     300     000000000000012C     failed     0     00          0     00     0     41     WAITING

    Thanks,
  • 9. Re: Wait Class
    618702 Expert
    Currently Being Moderated
    Dear userpat,

    System Monitor Process (SMON)
    The system monitor process (SMON) performs recovery, if necessary, at instance startup. SMON is also responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents within dictionary managed tablespaces. If any terminated transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or file is brought back online. SMON checks regularly to see whether it is needed. Other processes can call SMON if they detect a need for it.

    With Real Application Clusters, the SMON process of one instance can perform instance recovery for a failed CPU or instance.

    Process Monitor Process (PMON)
    The process monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes.

    PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally). PMON also registers information about the instance and dispatcher processes with the network listener.

    Like SMON, PMON checks regularly to see whether it is needed and can be called if another process detects the need for it.

    It is normal to see them in waiting state.

    Ogan
  • 10. Re: Wait Class
    683218 Newbie
    Currently Being Moderated
    Thanks ogan,

    It is normal to see them in waiting state.
    ----Showing failed of smon timer is not issued correct?

    if there are a lot of "SQL*Net message to client" event that are showing in v$session_wait what
    action plan i have to perform if it is a issue?

    Regards,
  • 11. Re: Wait Class
    sb92075 Guru
    Currently Being Moderated
    Handle:      userpat
    Email:      sajidquadri@yahoo.com
    Status Level:      Newbie
    Registered:      Feb 4, 2009
    Total Posts:      230
    Total Questions:      42 (40 unresolved)
    :-(
    So many questions & so few answers
  • 12. Re: Wait Class
    683218 Newbie
    Currently Being Moderated
    I do not know what do you mean by you I did not get the solution for 40 question what i feel,Is there any rule that we have to follow.
  • 13. Re: Wait Class
    683218 Newbie
    Currently Being Moderated
    waiting your reply

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points