3 Replies Latest reply: May 16, 2010 11:59 PM by SatishKandi RSS

    Top Waited Objects

    ahb72
      In an attempt to write a query that retrieves top waited-for objects, I tried using the following SELECT statement. The problem is the statement takes too long to finish.
      Is there a way to retrieve top waited Object in a reasonable response time?
      Oracle 11g R1
      Windows 2003 SP2
      -- Top Waited Objects
      SELECT o.object_name, o.object_type, a.event,  
       SUM(a.wait_time + a.time_waited) total_wait_time
      FROM v$active_session_history a, dba_objects o
      WHERE (a.wait_time + a.time_waited) > 0
        AND a.current_obj# is not NULL
        AND a.sample_time between sysdate - 30/2880 and sysdate
        AND a.current_obj# = o.object_id
      GROUP BY o.object_name, o.object_type, a.event
      ORDER BY total_wait_time DESC;
        • 1. Re: Top Waited Objects
          SatishKandi
          I have used this query earlier - see if this helps you.

          SELECT (SELECT o.object_name
          FROM dba_objects o
          WHERE o.object_id = current_obj#) object_name,
          (SELECT o.object_type
          FROM dba_objects o
          WHERE o.object_id = current_obj#) object_type,
          a.event, SUM (a.wait_time +
          a.time_waited) total_wait_time
          FROM v$active_session_history a
          WHERE (a.wait_time +
          a.time_waited) > 0
          AND a.current_obj# IS NOT NULL
          AND a.sample_time BETWEEN SYSDATE -
          15 / 1440 AND SYSDATE
          GROUP BY a.event, current_obj#
          ORDER BY total_wait_time DESC;
          • 2. Re: Top Waited Objects
            sb92075
            SELECT (SELECT o.object_name
                    FROM   dba_objects o
                    WHERE  o.object_id = current_obj#) object_name,
                   (SELECT o.object_type
                    FROM   dba_objects o
                    WHERE  o.object_id = current_obj#) object_type,
                   a.event,
                   SUM (a.wait_time + a.time_waited)   total_wait_time
            FROM   v$active_session_history a
            WHERE  ( a.wait_time + a.time_waited ) > 0
                   AND a.current_obj# IS NOT NULL
                   AND a.sample_time BETWEEN SYSDATE - 15 / 1440 AND SYSDATE
            GROUP  BY a.event,
                      current_obj#
            ORDER  BY total_wait_time DESC;  
            formatted code is easier to read
            • 3. Re: Top Waited Objects
              SatishKandi
              :-)

              Will keep that in mind in future. Thanks.