Re: Unable to detect blocking session

Hi Sandra,

You say you only found the sql*plus session because it was listed as idle in gv$session, that sounds like a stab in the dark.

Do you still have the results and the query you did against gv$session, the only things interesting would be inst_id, sid, event,blocking_session, blocking_instance, status. From just those few columns you can see what is waiting, on what and who they are waiting for.

Not only will this help when the waits are due to a session blocking you, it will show you if there is some other event being waited on which isn’t necessarily another session holding an enqueue. Once you do this you will find that you don’t need to make guesses or work through a list of typical situations - it tells you exactly what is going on, you might need to google the event name but you will be in a 100x better position to fix the problem.

Additionally, if your application is properly instrumented via dbms_application_info, you can easily filter down to just the sessions you know are important to find out what they’re doing.

I certainly wouldn’t bother with the script you shared previously, especially as it didn’t help you in the exact circumstance it was designed for.

Hope that helps,
Andrew

On Wed, 21 Feb 2018 at 20:07, Sandra Becker <sbecker6925_at_gmail.com> wrote:

> 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#||',_at_'||b.inst_id||' :
> '||b.sql_id blocker, b.seconds_in_wait b_secs,
> w.username||' - '||w.sid||','||w.sess_serial#||',_at_'||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_at_dxc.com>
> 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
>>
>>
>> ------------------------------
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Sandra Becker <sbecker6925_at_gmail.com>
>> *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.
>
>

--
http://www.freelists.org/webpage/oracle-l