3 Replies Latest reply: Nov 25, 2012 5:18 PM by user3266490 RSS

    wait event

    user3266490
      Hi,

      DB: 11.2.0.1

      We getting below wait event in db.what i have to do

      I68-SQL> select * from (
      2 select event, total_waits, total_timeouts, time_waited
      3 from v$system_event
      4 where event not like 'SQL*Net%'
      5 and event not in ('pmon timer','rdbms ipc message','dispatcher timer','smon timer')
      6 order by time_waited desc )
      7 where rownum < 6;

      EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
      ------------------------------ ----------- -------------- -----------
      db file sequential read 925803300 0 877516576
      library cache lock 291405693 0 584484549
      library cache: mutex X 838924416 0 538332419
      PX Idle Wait 2580331 2543090 507523714
      latch: cache buffers chains 237030965 0 170232983

      Any suggestions
      Thanks
        • 1. Re: wait event
          sb92075
          user3266490 wrote:
          Hi,

          DB: 11.2.0.1

          We getting below wait event in db.what i have to do
          It appears that you suffer from Compulsive Tuning Disorder.
          Have a cup of tea & ignore this result set.

          You need to learn to mark your questions as answered

          Handle:     user3266490
          Status Level:     Newbie (25)
          Registered:     Nov 26, 2008
          Total Posts:     1,362
          Total Questions:     799 (652 unresolved)
          • 2. Re: wait event
            user3266490
            Hi,
            It appears that you suffer from Compulsive Tuning Disorder
            what is the meaning?
            Could you tell why the above result should i ignore?

            Thanks & Regards,
            VN

            Edited by: user3266490 on Nov 26, 2012 4:47 AM
            • 3. Re: wait event
              Nikolay Savvinov
              Hi

              >
              We getting below wait event in db.what i have to do
              EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
              ------------------------------ ----------- -------------- -----------
              db file sequential read 925803300 0 877516576
              library cache lock 291405693 0 584484549
              library cache: mutex X 838924416 0 538332419
              1) v$ views show accumulated stats since instance startup, which means that the numbers you posted don't give us the full picture. Has your instance been running for an hour, a day, a week, a month, a year? What is the DB time to elapsed time ratio (average number of active sessions)? Without additional context, wait event dumps are often rather misleading than helpful

              2) since we don't have DB time or wall clock time, let's use the total wait time for the biggest wait event to set the scale. library cache lock and library cache: mutex X events combined are responsible for more wait time than db file sequential read, which suggests that you do have a problem

              3) these waits indicate high level of library cache contention which often happens when you do too much of the following:
              - hard parsing
              - soft parsing
              - cursor invalidation (e.g. by performing DDL on live schema or gathering stats)
              - resize shared pool

              4) the best course of action would be to collect some meaningful diagnostic data, like an AWR report for a busy hour (if you have the Diagnostic Pack License; if you don't, use statspack) and post following sections:
              - header (report time, num CPUs, platform and other info)
              - load profile
              - top timed events
              - time model statistics

              5) be sure to use
               tags when posting anything, it really doesn't look good when someone makes his/her 1,363th post without properly formatting SQL and it's output
              
              Best regards,
                Nikolay