Re: Blocking_session is blocker or blocked

Matt,

I will pay attention to then FINAL_ columns when lock occurs again.

Thanks.
Eriovaldo

Em qui, 8 de ago de 2019 às 14:11, Matt Adams <madams_at_equian.com> escreveu:

> 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_at_dxc.com>
> *Sent:* Thursday, August 08, 2019 12:48 PM
> *To:* ecandrietta_at_gmail.com; ORACLE-L <oracle-l_at_freelists.org>; Matt
> Adams <madams_at_equian.com>
> *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_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Matt Adams <madams_at_equian.com>
> *Sent:* Thursday, August 1, 2019 1:07:52 PM
> *To:* ecandrietta_at_gmail.com <ecandrietta_at_gmail.com>; ORACLE-L <
> oracle-l_at_freelists.org>
> *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_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Eriovaldo Andrietta
> *Sent:* Thursday, August 01, 2019 11:06 AM
> *To:* ORACLE-L <oracle-l_at_freelists.org>
> *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.

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