Examining 11.2.0.4, V$SESSION has four columns related to blocking that can
be useful for weeding out false positives.
(especially when processes are very briefly blocked by things like the DBWR and
LGWR processes.
This is a very common problem on one of my systems that very frequently
experiences blocking issues.)
COLUMN_NAME
------------------------------
BLOCKING_SESSION_STATUS
BLOCKING_SESSION
FINAL_BLOCKING_SESSION_STATUS
FINAL_BLOCKING_SESSION
(Those last two are helpful you have a cascading blocking).
The two *_STATUS columns can have values of UNKNOWN, NOT IN WAIT, NO HOLDER
and VALID. (and maybe others, those are just the ones I remember)
Matt Adams
"Doing linear scans over an associative array is like trying to club someone to
death with a loaded uzi."
* Larry Wall
From: Powell, Mark <mark.powell2@xxxxxxx>
Sent: Thursday, August 08, 2019 12:48 PM
To: ecandrietta@xxxxxxxxx; ORACLE-L <oracle-l@xxxxxxxxxxxxx>; Matt Adams
<madams@xxxxxxxxxx>
Subject: Re: Blocking_session is blocker or blocked
CAUTION: This email originated from outside of the organization. Do not click
links or open attachments unless you recognize the sender and know the content
is safe.
Ecandrietta, being that the OP's posted query uses V$SESSION and that 'VALID'
is not one of the five valid values of the STATUS column what column value are
you referring to?
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Matt Adams <madams@xxxxxxxxxx<mailto:madams@xxxxxxxxxx>>
Sent: Thursday, August 1, 2019 1:07:52 PM
To: ecandrietta@xxxxxxxxx<mailto:ecandrietta@xxxxxxxxx>
<ecandrietta@xxxxxxxxx<mailto:ecandrietta@xxxxxxxxx>>; ORACLE-L
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Subject: RE: Blocking_session is blocker or blocked
You may want to add the clause
and blocking_session_status = 'VALID'
I've seen cases where blocking_session is not null, but the
blocking_session_status != 'VALID'. I believe that both checks are needed to
avoid giving false positives.
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> On Behalf
Of Eriovaldo Andrietta
Sent: Thursday, August 01, 2019 11:06 AM
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Subject: Blocking_session is blocker or blocked
CAUTION: This email originated from outside of the organization. Do not click
links or open attachments unless you recognize the sender and know the content
is safe.
Hello,
In the sql below , the blocking_session is the BLOCKED or BLOCKER session ?
Select blocking_session, blocking_instance,wait_class, sid, serial#, inst_id,
seconds_in_wait, program, module, s.LOGON_TIME
From gv$session s
where blocking_session is not NULL
order by blocking_session;
Regards
Eriovaldo
Privacy Statement: The information contained in this transmission, including
any attachments, is for the sole use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review, use,
disclosure, or distribution is prohibited by Federal law. If you are not the
intended recipient of this message, you are notified that you may not disclose,
print, copy, or disseminate this information. If you have received this
transmission in error, please reply to the sender and delete or destroy the
message. Unauthorized interception of this transmission may be a violation of
criminal law.
DXC Technology Company - Headquarters: 1775 Tysons Boulevard, Tysons, Virginia
22102, USA.
DXC Technology Company -- This message is transmitted to you by or on behalf of
DXC Technology Company or one of its affiliates. It is intended exclusively for
the addressee. The substance of this message, along with any attachments, may
contain proprietary, confidential or privileged information or information that
is otherwise legally exempt from disclosure. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended recipient
of this message, you are not authorized to read, print, retain, copy or
disseminate any part of this message. If you have received this message in
error, please destroy and delete all copies and notify the sender by return
e-mail. Regardless of content, this e-mail shall not operate to bind DXC
Technology Company or any of its affiliates to any order or other contract
unless pursuant to explicit written agreement or government initiative
expressly permitting the use of e-mail for such purpose. --.
Privacy Statement: The information contained in this transmission, including
any attachments, is for the sole use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review, use,
disclosure, or distribution is prohibited by Federal law. If you are not the
intended recipient of this message, you are notified that you may not disclose,
print, copy, or disseminate this information. If you have received this
transmission in error, please reply to the sender and delete or destroy the
message. Unauthorized interception of this transmission may be a violation of
criminal law.
