v$lock: Oracle Rac gv$lock Script

Here’s my #2 most used script that can detect and drill down into v$lock / gv$lock to detect Oracle’s Blocking LOCKs across an entire Oracle RAC Cluster – locks.sql. Here’s how to use it:

wget https://s3.amazonaws.com/mve-shared/locks.sql
sqlplus / as sysdba
@locks.sql

NOTE: if you don’t have wget on your system try curl instead:

curl -o locks.sql https://s3.amazonaws.com/mve-shared/locks.sql

And here’s an example of locks fully decoded across multiple instances:

-- In this example (see "blocked sessions from GV$LOCK" section) we have a case of:
--       SID-3084 on INST_ID=4 is blocking two SIDs on INST_ID=6
--
-- NOTE: "blocked objects from GV$LOCK and SYS.OBJ$" section 
--       is just an FYI of all current locks
--

17:31:45 VMOGILEVSKIY@LPROD3=> @locks.sql

blocked objects from GV$LOCK and SYS.OBJ$

   INST_ID        SID      LMODE MIN_BLOCKED BLOCKED_OBJ
---------- ---------- ---------- ----------- -----------------------------------
         3       3961          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       3866          3           0 APPUSER_OWNER.DBJOBREQUESTS
         5       3887          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       3484          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       3161          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       2998          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       2979          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       2752          3           1 APPUSER_OWNER.DBJOBREQUESTS
         3       2618          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       2610          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       2456          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       2368          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       2243          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       2134          3           0 APPUSER_OWNER.DBJOBREQUESTS
         3       2132          3           0 APPUSER_OWNER.DBJOBREQUESTS
         6       3854          3           0 APPUSER_OWNER.DBJOBREQUESTS
         6       3507          3           0 APPUSER_OWNER.DBJOBREQUESTS
         6       3417          3           0 APPUSER_OWNER.DBJOBREQUESTS
         6       3303          3           0 APPUSER_OWNER.DBJOBREQUESTS
         6       3222          3           1 APPUSER_OWNER.DBJOBREQUESTS
         6       3135          3           0 APPUSER_OWNER.DBJOBREQUESTS
         6       2804          3           0 APPUSER_OWNER.DBJOBREQUESTS
         6       2786          3           0 APPUSER_OWNER.DBJOBREQUESTS
         4       3818          3           0 APPUSER_OWNER.DBJOBREQUESTS
         4       2869          3           0 APPUSER_OWNER.DBJOBREQUESTS

25 rows selected.

Elapsed: 00:00:00.03
blocked sessions from GV$LOCK

   INST_ID BLOCKER_SID    INST_ID BLOCKED_SID MIN_BLOCKED    REQUEST
---------- ----------- ---------- ----------- ----------- ----------
         4        3084          6        3135           0          6
         4        3084          6        3485           0          6

2 rows selected.

Elapsed: 00:00:00.02
blocked session details from GV$SESSION and GV$SQLTEXT

Instance........ :          6
Sid ............ :       3135
Serial ......... :      30604
Username ....... : APP1USER_NAME
SQL Id ......... : null
Prev SQL Id .... : gm424t8fyx3w6
Displayed SQL Id : gm424t8fyx3w6
Client Info .... : null
Machine ........ : dbt4.dc1.mydomain.com
OSuser ......... : dbt
Process ........ : 1234
Action ......... : JDBC Thin Client
SQL_TEXT
----------------------------------------------------------------------
select this_.WorkRequestId as WorkRequ1_1_0_, this_.CreateTime a
s CreateTime1_0_, this_.Event_Type as Event3_1_0_, this_.Status
as Status1_0_, this_.UserId as UserId1_0_ from DBJOBREQUESTS thi
s_ where this_.WorkRequestId = :1  and this_.Status=:2  for upda
te

Instance........ :          6
Sid ............ :       3485
Serial ......... :      45149
Username ....... : APP1USER_NAME
SQL Id ......... : null
Prev SQL Id .... : gm424t8fyx3w6
Displayed SQL Id : gm424t8fyx3w6
Client Info .... : null
Machine ........ : dbt5.dc1.mydomain.com
OSuser ......... : dbt
Process ........ : 1234
Action ......... : JDBC Thin Client
SQL_TEXT
----------------------------------------------------------------------
select this_.WorkRequestId as WorkRequ1_1_0_, this_.CreateTime a
s CreateTime1_0_, this_.Event_Type as Event3_1_0_, this_.Status
as Status1_0_, this_.UserId as UserId1_0_ from DBJOBREQUESTS thi
s_ where this_.WorkRequestId = :1  and this_.Status=:2  for upda
te

10 rows selected.

Elapsed: 00:00:09.33
blocker session details from GV$SESSION and GV$SQLTEXT (current or previous SQL)

Instance........ :          4
Sid ............ :       3084
Serial ......... :       8911
Username ....... : APP1USER_NAME
SQL Id ......... : null
Prev SQL Id .... : 629vx81ykvhpp
Displayed SQL Id : 629vx81ykvhpp
Client Info .... : null
Machine ........ : dbt1.dc1.mydomain.com
OSuser ......... : dbt
Process ........ : 1234
Action ......... : JDBC Thin Client
SQL_TEXT
----------------------------------------------------------------------
update DBT_LOCK set FINISHED=:1 , VERSION=:2  where USER_ID=:3
and VERSION=:4

2 rows selected.

Elapsed: 00:00:10.13

This script presented few performance challenges because a self-join query against gv$lock joined with sys.obj$ to get a list of blocked objects is very expensive in a cluster environment, in fact it’s expensive even in a single instance environment. We also have to join gv$session with a result of self-join query against gv$lock in order to get the SQL_TEXT of the sessions doing blocking and being blocked – that’s extremely slow as well.

To solve the above performance challenges I created two tables and indexed them appropriately:

GV$ Table COPY Table Indexed Columns
gv$lock gv_lock_mon type,block
gv$session gv_session_mon inst_id,sid

 

Once that was done it was a simple matter of replacing GV$ Table name with COPY Table name on the key joins and performance shot up through the roof. In fact, it was so lightweight that I created a custom event in my monitoring system and started to trap occurrences of these DB blocks for historical purposes so that when a developer came to our team and asked us if there were any DB locks/blocks 3 hours ago we could simply review our alerts and answer that question with authority providing exact details on the race condition that caused these blocks. This was much more helpful then the generic alert email we’d get from OEM stating that session XYZ is blocking this many sessions on instances 1,4 and 5 for example.

My question to you is how do you monitor Oracle LOCKs? Is OEM alerting sufficient for your needs? Do you think a solution such as the one I outlined above would be beneficial to your team? I am considering adding the Oracle LOCK Monitoring feature to the Oracle Event Monitoring Framework I am developing. If you think it’s a good idea then let me know by joining the EvenTorEx Mailing List and I’ll notify you on the progress and when the private beta becomes available.

EvenTorEx Mailing List

December 9, 2015

Posted In: Operations, Scripts

Tags: , , ,

raccheck 2.2 beta released

raccheck 2.2 beta released
v. 2.2.0 Beta

Support for Single Instance Configurations (i.e., no longer limited to RAC configurations)
High Availability (HA) Best Practices
New checks and bug fixes

This new version was prodiced with collaboration with ORACLE HA team in Support to add their Top 20 checks for common problems related to backup and recovery and Data Guard. These new checks augment the MAA Scorecard. These new checks can be obtained with the following syntax:

./raccheck -r – includes the standard health checks as well as HA checks.
./raccheck -c hacheck -o -v – abbreviates the report output to ONLY the HA related checks

raccheck can be downloaded from RACcheck – RAC Configuration Audit Tool (Doc ID 1268927.1)

CREDIT: Bob Caldwell

November 2, 2012

Posted In: Data Guard, Installs, RAC, RMAN, Scripts

Tags: , , ,