1 Reply Latest reply: Feb 1, 2013 2:13 AM by Bawer RSS

    How to monitor the resource used by the query

    SagiGal
      Hi All,

      I have a locking monitoring script as below, which is currently run at daytime.
      We now planned to cron the script to run by 24/7. But we have concern since this is a old legacy system.
      Please advise if I going to turn on the script to run by 24/7, which item I need to monitor on?(eg: temp space, rollback segments etc)
      Hope to gather some statistic whether it is safe to run the script at 24/7..

      Please note that the database version is 8.1.6.3.0

      SELECT /*+ RULE */
      ss.username||'('||ss.sid||','||ss.serial#||')' "WAITER",
      ss.status, ss.osuser, pr.spid, dbo.object_name,
      sw.state, sw.SECONDS_IN_WAIT/60 mins, NVL(SUBSTR(ss.program,-10),ss.program) program, NVL(SUBSTR(ss.module,-20),ss.module) module
      FROM v$session ss, v$process pr, dba_objects dbo, v$session_wait sw
      WHERE ss.row_wait_obj# = dbo.object_id (+) --rows in ss that no matching rows in dbo, Oracle returns NULL
      AND pr.addr = ss.paddr
      AND sw.sid = ss.sid
      AND sw.event ='enqueue'
      AND ss.lockwait IS NOT NULL
      AND ss.sid IN
      (SELECT sid FROM v$lock WHERE TYPE='TX' AND id1 IN
      (SELECT id1 FROM v$lock WHERE sid IN
      (SELECT sid FROM v$session WHERE lockwait IS NOT Null)
      )
      )
      AND sw.seconds_in_wait > 180
      ORDER BY mins DESC;

      Edited by: SagiGal on 31-Jan-2013 23:05
        • 1. Re: How to monitor the resource used by the query
          Bawer
          you can use v$lock to identify locks directly:
          select *
                    FROM v$lock aa, v$lock b, v$session blocker ,v$session waiter 
                    WHERE aa.block = 1
                    AND b.request > 0
                    AND aa.id1 = b.id1
                    AND aa.id2 = b.id2
                    AND aa.sid = blocker.sid
                    AND waiter.sid = b.sid 
          SagiGal wrote:Please advise if I going to turn on the script to run by 24/7, which item I need to monitor on?(eg: temp space, rollback segments etc)
          Nothing. you can run this query every minute! It queries the system views, which are organized by oracle continuously.