Try this … Any way … in OEM12 or EMCC 13c .. has session block metric that you
can set …. Then … you can let corrective action wait for 15 minutes or
something .. then kill
------------------------------ Find lock and kill ------------------------------
select vs.username lock_user,
vs.osuser lock_os_user,
vh.sid || ' <-- he is locking' locking_sid ,
vs.status lock_status,
vs.module lock_module,
vs.program program_holding,
jrh.job_name lock_job,
vsw.username wait_user,
vsw.osuser wait_os_user,
vw.sid ||' <-- he is waiting' waiter_sid,
vsw.program program_waiting,
jrw.job_name wait_job,
'alter system kill session ' || ''''|| vh.sid || ',' || vs.serial# ||
',@' ||vs.inst_id || ''';' kill_the_lock_command,
'alter system kill session ' || ''''|| vw.sid || ',' || vsw.serial# ||
',@' ||vsw.inst_id || ''';' kill_the_wait_command
from gv$lock vh,
gv$lock vw,
gv$session vs,
gv$session vsw,
dba_scheduler_running_jobs jrh,
dba_scheduler_running_jobs jrw
where (vh.id1, vh.id2) in (select id1, id2 from gv$lock where request = 0
intersect
select id1, id2 from gv$lock where lmode = 0)
and vh.id1 = vw.id1
and vh.id2 = vw.id2
and vh.request = 0
and vw.lmode = 0
and vh.sid = vs.sid
and vw.sid = vsw.sid
and vh.sid = jrh.session_id(+)
and vw.sid = jrw.session_id(+);
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Sandra Becker
Sent: Wednesday, February 21, 2018 3:07 PM
To: Powell, Mark <mark.powell2@xxxxxxx>
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Unable to detect blocking session
***This email is from an external source. Only open links and attachments from
a Trusted Sender.***
New information: we didn't actually find the blocker using v$lock (or
gv$lock). Someone finally looked at gv$session and saw that a sqlplus session
had been idle for over an hour during the timeframe the application was
experiencing issues. We had a script, below, that we were using to locate the
root blocker since we have an application issue we're also trying to resolve.
That script did NOT show the sqlplus session that appeared to be the blocker.
We think that session was the blocker because as soon as the user exited the
session, everything cleared almost immediately. We're trying to figure out a
script that will find the root block, including sessions like this user's that
ran an update statement, but didn't actually update any rows and he didn't do a
commit. If someone has any suggestions, we would definitely like to hear them.
ROOT BLOCKER SCRIPT
SELECT
DISTINCT
b.username||' -
'||b.blocker_sid||','||b.blocker_sess_serial#||',@'||b.inst_id||' : '||b.sql_id
blocker, b.seconds_in_wait b_secs,
w.username||' - '||w.sid||','||w.sess_serial#||',@'||w.inst_id||' :
'||w.sql_id waiter, w.seconds_in_wait w_secs
FROM (SELECT
bb.blocker_sid,
bb.blocker_sess_serial#,
sb.inst_id,
sb.username,
sb.sql_id,
sb.seconds_in_wait
FROM gv$session_blockers bb
JOIN gv$session sb
ON bb.blocker_sid = sb.sid
AND bb.blocker_sess_serial# = sb.serial#
) b
JOIN (SELECT
bw.blocker_sid,
bw.sid,
bw.sess_serial#,
sw.inst_id,
sw.username,
sw. sql_id,
sw.seconds_in_wait
FROM gv$session_blockers bw
JOIN gv$session sw
ON bw.sid = sw.sid
AND bw.inst_id = sw.inst_id
AND bw.sess_serial# = sw.serial#
) w
ON b.blocker_sid = w.blocker_sid
ORDER BY w.seconds_in_wait DESC
/
On Wed, Feb 21, 2018 at 12:14 PM, Powell, Mark
<mark.powell2@xxxxxxx<mailto:mark.powell2@xxxxxxx>> wrote:
Sandy, to add to what Rick's reply, the "SQL*Net message from client" indicates
to me the user made an update and failed to commit it. I think Rick provided
valid possibilities for EM but for why you could not directly query the
information I think we would need to see what queries you used to try to find
the blocker though from your final remarks you did use GV$LOCK and GV$SESSION
plus sys.dbms_lock_allocated to find the blocker. Could the earlier attempt
have been using the V$ version and so missed the blocker since it was on
another instance? The sys.dbms_lock_allocated table would not be necessary to
find the blocking session though it would identify which User Lock (UL) was
involved if a UL was involved.
Mark Powell
Database Administration
(313) 592-5148<tel:(313)%20592-5148>
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Sandra Becker <sbecker6925@xxxxxxxxx<mailto:sbecker6925@xxxxxxxxx>>
Sent: Wednesday, February 21, 2018 10:32:14 AM
To: oracle-l
Subject: Unable to detect blocking session
Oracle EE 12.1.0.2 on RHEL 5.11
We had a situation in our production environment yesterday where a user had a
sqlplus session had an uncommitted "zero row" update on a table. This kept the
actual application from processing orders using that same table. The sqlplus
session was initiated from SQL*Plus Release 11.1.0.6.0. The wait event on the
application session was holding a user lock, which was apparently blocked, with
a wait event of "SQL*Net message from client". Once the user's sqlplus
session was exited, all application sessions resumed normal functions without
any intervention. The user who issues the update is a tier1 support person, so
we can't lock out their access for such activites to prevent future occurrences.
What we are trying to understand is why we were unable to see that the user's
sqlplus session was blocking either through EM or through queries looking at
gv$session and gv$session_blockers. We found the application locks by joining
gv$lock, gv$session and sys.dbms_lock_allocated. Any ideas why we could see
the blocking or suggestions on what we can look at so we don't miss it again?
--
Sandy B.
--
Sandy B.
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
