13 Replies Latest reply on Jun 28, 2010 7:29 AM by 683218

    Wait Class


      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?

        • 1. Re: Wait Class
          Events Defined in the Database

          Currently Waiting Sessions

          Waited Events for Current Sessions

          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
          6.MAX_DUMP_FILE_SIZE = unlimited
          • 2. Re: Wait Class
            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.

            • 3. Re: Wait Class
              Charles Hooper
              userpat wrote:

              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?

              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
              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)
                SUBSTR(NAME,1,30) EVEMT_NAME,
                SUBSTR(WAIT_CLASS,1,20) WAIT_CLASS
              ORDER BY
              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"
              IT Manager/Oracle DBA
              K&M Machine-Fabricating, Inc.
              • 4. Re: Wait Class

                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?


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

                  • 6. Re: Wait Class

                    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
                      Hi userpat,

                      Please first check the link below;


                      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.

                      • 8. Re: Wait Class

                        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

                        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

                        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

                        • 9. Re: Wait Class
                          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.

                          • 10. Re: Wait Class
                            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?

                            • 11. Re: Wait Class
                              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
                                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
                                  waiting your reply