CA UIM Oracle Probe - Blocking Sessions (lock_waits_4)

[NARONGKIJ TEJASAKULSIN]

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.

[David Michel]

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
------------------------------

[NARONGKIJ TEJASAKULSIN]

Hi David,

It's unchecked.

Also:

[David Michel]

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
------------------------------

[NARONGKIJ TEJASAKULSIN]

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'

[David Michel]

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
------------------------------

[NARONGKIJ TEJASAKULSIN]

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

[David Michel]

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
------------------------------

[NARONGKIJ TEJASAKULSIN]

The Oracle admin ran the query and it returned: no rows selected

I did enable the QoS and it showed:

[David Michel]

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
------------------------------

[NARONGKIJ TEJASAKULSIN]

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.

[David Michel]

Yes, at this point a support case is needed.

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