DX Infrastructure Manager

Expand all | Collapse all

CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

Jump to Best Answer
  • 1.  CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

    Posted 16 days ago
    Hi All,

    I'm not getting any alerts from UIM "lock_waits_4" using Oracle probe v5.45 to monitor for Blocking Sessions in Oracle.
    The Oracle admin and I are testing and we can see in OEM that there are Sessions Blocked, but it's not showing up in the UIM Oracle probe.

    Can anyone give any insights on what we are missing.


  • 2.  RE: CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

    Posted 16 days ago
    If no alarms at all are being generated check out:
    Database probes (db2, oracle, sqlserver, sybase) alarms when thresholds are breached are not forwarded to the nas
    Article Id:5002
    https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=5002

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 3.  RE: CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

    Posted 15 days ago
    Hi David,

    It's unchecked.

    Also:



  • 4.  RE: CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

    Posted 15 days ago
    The referenced setting is at the Setup tab so it is unclear to me why the Status tab is being shown. 
    The question is on alarms for a specific check point lock waits. 
    What is not stated is if any alarms are being generated by the probe, knowing this will determine the direction to take for troubleshooting. 

    Try these queries:
    select * from NAS_TRANSACTION_SUMMARY where prid = 'oracle'
    select * from NAS_TRANSACTION_LOG where prid = 'oracle'

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 5.  RE: CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

    Posted 14 days ago
    Edited by Narongkij Tejasakulsin 14 days ago
    Hi David,

    The Setup tab:  (sorry, was showing the Status tab hoping it could be helpful).  Originally the Alarm severity filter was Informational, I just changed it to Clear prior to posting the image below.

    Ran both queries and both generated results.
    For: select * from NAS_TRANSACTION_SUMMARY where prid = 'oracle'

    For: select * from NAS_TRANSACTION_LOG where prid = 'oracle'





  • 6.  RE: CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

    Posted 14 days ago
    OK so clearly the probe is generating alarms and this bring the focus specific to lock_waits metric. 
    It may seem like a lot of time was spent for nothing but experience has shown it is best to get a good understanding and not make assumptions. 
    As per the metric page:
    QOS_ORACLE_lock_waits Seconds Measures number of time a user session was waiting for a lock in last interval.
    As per the probe gui hint editor:
    Indicates that more than one application needs an exclusive lock on the same object at the same time
    The default is alarm enabled, qos disabled, Condition: >=, Samples: 1
    Clear message: lock_waits_1
    However you mention lock_waits_4, so is that what's being used for the threshold, and with what value? 
     
    It may be necessary to setup a test and capture it in the log, with the probe setup for level 5. 
    This is best done if the condition can be created on demand, time noted, and then log reviewed for that time, and should be able to search for lock_waits or blocked_session. 



    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 7.  RE: CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

    Posted 13 days ago
    Yes, I chose the lock_waits_4 because the Oracle admin would like us to capture the Oracle "Blocking Session information".  This was introduced in v5.10 of the Oracle probe (UIM Oracle Probe document).

    My setup below, I also change the severity to Critical.  The threshold value is set to 1.  In the current interval value, I always see N/A, never a value.



    Below is the screen capture from the Oracle OEM console


    I did set the log level to 5 yesterday and did see the "lock_waits" and "blocking_session" messages.  Sample of the log file (I entered MASKED or x.x.x.x to obscure secured information):

    Sep 16 11:52:27:032 [0906] oracle: (RunQuery{Oracle Version}) 12
    Sep 16 11:52:27:032 [0906] oracle: (RunQuery)Query to run: select s.serial# as serial,s.sid,s.blocking_session,s.username,s.osuser,e.event,e.total_waits,e.time_waited/100 as total_time_waited from v$session_event e, v$session s where e.sid = s.sid and e.event like 'enq:%' and s.user# <> 0
    Sep 16 11:52:27:032 [0906] oracle: doQuery4 lock_waits starting
    Sep 16 11:52:27:032 [0906] oracle: doQuery4 qeury text select s.serial# as serial,s.sid,s.blocking_session,s.username,s.osuser,e.event,e.total_waits,e.time_waited/100 as total_time_waited from v$session_event e, v$session s where e.sid = s.sid and e.event like 'enq:%' and s.user# <> 0
    Sep 16 11:52:27:133 [0906] oracle: DefineByPos starting for SERIAL
    Sep 16 11:52:27:133 [0906] oracle: DefineByPos starting for SID
    Sep 16 11:52:27:133 [0906] oracle: DefineByPos starting for BLOCKING_SESSION
    Sep 16 11:52:27:133 [0906] oracle: DefineByPos starting for USERNAME
    Sep 16 11:52:27:133 [0906] oracle: DefineByPos starting for OSUSER
    Sep 16 11:52:27:133 [0906] oracle: DefineByPos starting for EVENT
    Sep 16 11:52:27:133 [0906] oracle: DefineByPos starting for TOTAL_WAITS
    Sep 16 11:52:27:133 [0906] oracle: DefineByPos starting for TOTAL_TIME_WAITED
    Sep 16 11:52:27:133 [0906] oracle: Profile: MASKED, Oracle status: OCI_NODATA
    Sep 16 11:52:27:133 [0906] oracle: Profile: MASKED/lock_waits: fwSendQos starting...mode 128, active=1
    Sep 16 11:52:27:133 [0906] oracle: Profile: MASKED/lock_waits,: fwSendQos QOS_ORACLE_lock_waits for target: MASKED
    Sep 16 11:52:27:133 [0906] oracle: (fwSendQos) - Checkpoint:lock_waits CI ID:4.1 CI Name:MASKED-lock_waits
    Sep 16 11:52:27:133 [0906] oracle: ciOpen - cache path: /CA/UIM/niscache
    Sep 16 11:52:27:134 [0906] oracle: RREPLY: status=OK(0) <-x.x.x.x/48001 h=37 d=28
    Sep 16 11:52:27:134 [0906] oracle: ciClose - [CFC990AE06916D93138D23B9E040FD557]
    Sep 16 11:52:27:134 [0906] oracle: sockClose:118036370:x.x.x.x/49913
    Sep 16 11:52:27:134 [0906] oracle: SREQUEST: _close ->x.x.x.x/48001


  • 8.  RE: CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

    Posted 13 days ago
    The condition is >=, threshold 1, and there was 1 blocked session, so yes it sure seems to me it should have generated an alarm. 
    Yet the log shows Oracle status: OCI_NODATA. which based on a search means there was less data than requested. 
    Really don't know enough about databases or this deep into the probe to say what's going on here. 

    You have the query there run that to see what it returns. 
    select s.serial# as serial,s.sid,s.blocking_session,s.username,s.osuser,e.event,e.total_waits,e.time_waited/100 as total_time_waited from v$session_event e, v$session s where e.sid = s.sid and e.event like 'enq:%' and s.user# <> 0




    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 9.  RE: CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

    Posted 12 days ago
    The Oracle admin ran the query and it returned: no rows selected

    I did enable the QoS and it showed:



  • 10.  RE: CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

    Posted 12 days ago
    The one thing worth noting is all the values are less that 1 which would be lower than the threshold. 
    Looks like a support case will be needed. 
    Leave the qos enabled so that the next time it happens you can show Support both the graph showing the breach and log showing no alarm generated.

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 11.  RE: CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

    Posted 9 days ago
    It is weird that its reading less than 1, I also changed the threshold value to 0.1 and 0.01, but nothing.  Opening a support case, there is also a memory leak with this version.  The probe will start at 10mb then ramp up to 100mb in about 24 hours.

    Thanks for the help on this.


  • 12.  RE: CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)
    Best Answer

    Posted 9 days ago
    Yes, at this point a support case is needed.

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------