Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 79F2A10031424A for ; Thu, 8 Aug 2019 19:13:21 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 640722834A; Thu, 8 Aug 2019 13:13:20 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1565284400; bh=AEIlIRmOWIHvVSDELnFaG6o2dqJc2FeEcNn/J2vaA5s=; h=From:To:Subject:Date:References:In-Reply-To:Reply-To:List-Help: List-Unsubscribe:List-Id:List-Subscribe:List-Owner:List-post: List-Archive; b=b7O1yDWE1JYK4LOd5x+VSB6VKdc1MG7DCU1wv0zY1la+HBBNJPRqAAf6AbhP3XOyY SnmSUuUJysEogCt8clin/E8Efi/yZ5lS+ehsabE1sSIifZ/SCq6FW8wE1cC1b4R3oO kUfSoOu4W1fLxxm1VSX4X5QZNOsw+HSNlGExpFSE= X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id LeLqkq-03_p3; Thu, 8 Aug 2019 13:13:20 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A5C952821C; Thu, 8 Aug 2019 13:12:59 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1565284390; bh=AEIlIRmOWIHvVSDELnFaG6o2dqJc2FeEcNn/J2vaA5s=; h=From:To:Subject:Date:References:In-Reply-To:Reply-To:List-Help: List-Unsubscribe:List-Id:List-Subscribe:List-Owner:List-post: List-Archive; b=A2S/c8y56vehytKv82oRd52otDwlWdByMx2yibueCszQHs90NoJjl7e8Rrehmn1wf zYQ+WJbVmpL0MwMFXgTVb+FwdIpifK2yBWsrZHoi8Pbu1fE4ZAw4FVlcTf+xA3KhAZ n/zHTjTNq1aYnMJIFjjPBrSrQlS6xLQSxXfwVh0A= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 08 Aug 2019 13:11:37 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 922E92831E for ; Thu, 8 Aug 2019 13:11:37 -0400 (EDT) Authentication-Results: turing.freelists.org; dkim=fail reason="verification failed; unprotected key" header.d=equian.onmicrosoft.com header.i=@equian.onmicrosoft.com header.b=teN0MLut; dkim-adsp=none (unprotected policy); dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id cAP2YlGTdhXJ for ; Thu, 8 Aug 2019 13:11:37 -0400 (EDT) Received: from zixvpm02.icc.com (smtp.us-hsi.com [206.53.233.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 4E84F28318 for ; Thu, 8 Aug 2019 13:11:37 -0400 (EDT) Received: from 127.0.0.1 (ZixVPM [127.0.0.1]) by Outbound.equian.com (Proprietary) with SMTP id DFAD73320977 for ; Thu, 8 Aug 2019 13:11:36 -0400 (EDT) Received: from wpvi1hex01.equian.com (unknown [172.17.78.70]) (using TLSv1.2 with cipher AES256-SHA256 (256/256 bits)) (No client certificate requested) by zixvpm02.icc.com (Proprietary) with ESMTPS id 8F9C13320962; Thu, 8 Aug 2019 13:11:36 -0400 (EDT) Received: from wpvi1hex01.equian.com (172.17.78.70) by wpvi1hex01.equian.com (172.17.78.70) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.1415.2; Thu, 8 Aug 2019 13:11:36 -0400 Received: from NAM05-DM3-obe.outbound.protection.outlook.com (172.17.78.42) by wpvi1hex01.equian.com (172.17.78.70) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.1415.2 via Frontend Transport; Thu, 8 Aug 2019 13:11:36 -0400 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=Pxncf1AunkIwS324VNUOew01am6Th38dNJKZcJTpuhSbHBfLm0OPhkRToKh9J2NIo/v2pztj/y+Hu3/u6Hu0IQp1v3GZpLsjTYdTtXS32WCfPY4BaoMpruSNMN+PqAjC17Jwy8zJgUcZfMIE1yowKIUR0T0FSbPWgKHpHYyzrvBTaO/TUBSuC/wraHaZNh3OD6do4eVAg5o0G8we9QrrNNfhWRux9RRx1/wI7MPqE5FSCIG5XOpjgcWK0jn1T7pX2Vs4204jtQD9QD0LUm8HkH41WzFwErlFIZW0q9908qlhUMsQawYRmKaFsr3azXI5VjWpq8rASvSHWALDyxd2YQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=2gxT0R4dhiwjS9ZkpnNuXswCnQlynbipLt3CWYrdgCw=; b=GYcWzi5gNpKvRyPyFxLasEqFdNuPQBFWnG1RW9OVfi/Pl7Vg6iy+N7LJKFnyLWlt5iRj7O/49tKt+1Wdbi+QqsELqswCD5J/ezZYA7RAQ7OYL6B8m8NB6e3g3vMWxZ05DM4sDIzrmuqNrMMTkcPnYlMXOLPnjMaO4WrwObecqhuaS7zE7OqNQkQDdJOaujamQ9SRR4lmFV2T3ayxAhwCNd2MxGGPhS2ty7oQ4GzcGMl5OATjnPWGBwGkFDqwXbJwwQtZvO0xI0bPnlGNNS1Z/hbS3RYtjYGsmqyQ8HGU7kdjfnI3AA4PN5Pc1/ieIDv3VgfnVKArXR2P0zVM3pcwsA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=equian.com; dmarc=pass action=none header.from=equian.com; dkim=pass header.d=equian.com; arc=none Received: from DM6PR10MB4187.namprd10.prod.outlook.com (10.141.184.15) by DM6PR10MB4011.namprd10.prod.outlook.com (10.141.167.141) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.2157.13; Thu, 8 Aug 2019 17:11:35 +0000 Received: from DM6PR10MB4187.namprd10.prod.outlook.com ([fe80::a8e6:162e:3293:73af]) by DM6PR10MB4187.namprd10.prod.outlook.com ([fe80::a8e6:162e:3293:73af%2]) with mapi id 15.20.2157.015; Thu, 8 Aug 2019 17:11:34 +0000 From: Matt Adams To: "Powell, Mark" , "ecandrietta@gmail.com" , ORACLE-L Subject: RE: Blocking_session is blocker or blocked Thread-Topic: Blocking_session is blocker or blocked Date: Thu, 8 Aug 2019 17:11:33 +0000 Message-ID: References: , In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=madams@equian.com; x-originating-ip: [209.43.100.54] x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: 04bdd45a-5b16-4e70-cde0-08d71c2376e7 x-microsoft-antispam: BCL:0;PCL:0;RULEID:(2390118)(7020095)(4652040)(8989299)(4534185)(4627221)(201703031133081)(201702281549075)(8990200)(5600148)(711020)(4605104)(1401327)(2017052603328)(7193020);SRVR:DM6PR10MB4011; x-ms-traffictypediagnostic: DM6PR10MB4011: x-ms-exchange-purlcount: 2 x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:2958; x-forefront-prvs: 012349AD1C x-forefront-antispam-report: SFV:NSPM;SFS:(10019020)(346002)(376002)(136003)(366004)(396003)(39850400004)(189003)(199004)(11346002)(6436002)(2420400007)(256004)(446003)(186003)(6116002)(790700001)(53936002)(15650500001)(6306002)(52536014)(3846002)(6506007)(53546011)(478600001)(110136005)(7736002)(2501003)(7110500001)(8676002)(66066001)(316002)(2906002)(229853002)(71200400001)(86362001)(25786009)(99286004)(66446008)(71190400001)(102836004)(74316002)(5660300002)(81156014)(81166006)(76116006)(55016002)(8936002)(66476007)(26005)(66556008)(64756008)(66946007)(476003)(33656002)(76176011)(5024004)(14444005)(14454004)(6246003)(9686003)(236005)(7696005)(486006)(54896002);DIR:OUT;SFP:1102;SCL:1;SRVR:DM6PR10MB4011;H:DM6PR10MB4187.namprd10.prod.outlook.com;FPR:;SPF:None;LANG:en;PTR:InfoNoRecords;MX:1;A:1; received-spf: None (protection.outlook.com: equian.com does not designate permitted sender hosts) x-ms-exchange-senderadcheck: 1 x-microsoft-antispam-message-info: L5nGrFLvAVEzT1gaHIIAcALTPxQpfSf0jcHH+pv4lktVW+pcJhat1r70Q7Mh4HjH2W3zyyR8uKeTr9KG3rkMHVoEPe0KrRMREtl50RUKS02hGB+LnOImMdyHZYhqxou7MBH7HaYFqIl/iKKi04ALjDWPGZK91n1uSVAE09u6zI2lnac+gcY854kc7gvNRx/rX7ZeLk/amPsna3Pv7tJtQ2JCRxx+E3gbaVZx0WFhICgcM9cQr23gCsU6UDTX7DXT1RPXZipOTu4ZPIoV/DpB9J1DMXXhQUVW6rq8lu1h2GOFrRJp26dpMrqcdYEIjgtio5WdSLW1kshSjMDamw/QRGnJnAJZigJbCHiRPCh4MkPNbN0tGwuu6YnTYkCG6U1gn3vGIe4IbdSpkNVb3GyV6o3f1ub7h1IG7Ju2VSb4Ht4= x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_DM6PR10MB41879B38F7ACE846EF42B2AFA5D70DM6PR10MB4187namp_" MIME-Version: 1.0 X-MS-Exchange-CrossTenant-Network-Message-Id: 04bdd45a-5b16-4e70-cde0-08d71c2376e7 X-MS-Exchange-CrossTenant-originalarrivaltime: 08 Aug 2019 17:11:33.9049 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 80e8d8ee-c783-427f-b0fc-f8dc3a1938f2 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: wv1pB2naWVorewxWR7iHJF06/JH+Oy6q/Sfree43nzjPa8SmD4VEzEaLIcqXCrpJYZ2GThZmJLsgrfp4FIzpoQ== X-MS-Exchange-Transport-CrossTenantHeadersStamped: DM6PR10MB4011 X-OriginatorOrg: equian.com X-VPM-HOST: zixvpm02.icc.com X-VPM-GROUP-ID: c7de17ec-9b80-48a5-97a8-3e98aca035bf X-VPM-MSG-ID: bcb6ca2e-5985-490d-b613-d6ae760846d9 X-VPM-ENC-REGIME: Plaintext X-VPM-CERT-FLAG: 0 X-VPM-IS-HYBRID: 0 X-archive-position: 74641 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: madams@equian.com Precedence: normal Reply-To: madams@equian.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --_000_DM6PR10MB41879B38F7ACE846EF42B2AFA5D70DM6PR10MB4187namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Examining 11.2.0.4, V=24SESSION 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 =22Doing linear scans over an associative array is like trying to club = someone to death with a loaded uzi.=22 * Larry Wall From: Powell, Mark Sent: Thursday, August 08, 2019 12:48 PM To: ecandrietta=40gmail.com; ORACLE-L ; Matt = Adams 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=24SESSION 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=40freelists.org = > = on behalf of Matt Adams > Sent: Thursday, August 1, 2019 1:07:52 PM To: ecandrietta=40gmail.com = >; ORACLE-L = > Subject: RE: Blocking_session is blocker or blocked You may want to add the clause and blocking_session_status =3D 'VALID' I've seen cases where blocking_session is not null, but the = blocking_session_status =21=3D 'VALID'. I believe that both checks are = needed to avoid giving false positives. From: = oracle-l-bounce=40freelists.org = > = On Behalf Of Eriovaldo Andrietta Sent: Thursday, August 01, 2019 11:06 AM To: ORACLE-L > 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=23, = inst_id, seconds_in_wait, program, module, s.LOGON_TIME From=20gv=24session 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.= --_000_DM6PR10MB41879B38F7ACE846EF42B2AFA5D70DM6PR10MB4187namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <=21--=5Bif =21mso=5D><=21=5Bendif=5D--><=21--=5Bif gte mso 9=5D> <=21=5Bendif=5D--><=21--=5Bif gte mso 9=5D> <=21=5Bendif=5D-->

Examining = 11.2.0.4,   V=24SESSION  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.)

 <= /span>

COLUMN_NAME

------------------= ------------

BLOCKING_SESSION_S= TATUS

BLOCKING_SESSION

FINAL_BLOCKING_SES= SION_STATUS

FINAL_BLOCKING_SES= SION

 <= /span>

(Those last two = are helpful you have a cascading blocking).

 <= /span>

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)

 <= /span>

 <= /span>

Matt = Adams

&=238220;Doing = linear scans over an associative array is like trying to club someone to = death with a loaded uzi.&=238221;

  • Larry = Wall

<= o:p> 

<= o:p> 

<= o:p> 

F= rom: = Powell, Mark <mark.powell2=40dxc.com>
Sent: Thursday, August 08, 2019 12:48 PM
To: ecandrietta=40gmail.com; ORACLE-L = <oracle-l=40freelists.org>; Matt Adams <madams=40equian.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.

&= nbsp;

Ecandr= ietta, being that the OP's posted query uses V=24SESSION and that 'VALID' = is not one of the five valid values of the STATUS column what column = value are you referring to?

&= nbsp;

&= nbsp;

Mark Powell<= /o:p>

Database Administration<= /o:p>

(313) 592-5148<= /o:p>

&= nbsp;

&= nbsp;

From: oracle-l-bounce=40freel= ists.org <oracle-l-bounce=40freel= ists.org> on behalf of Matt Adams <madams=40equian.com>
Sent: Thursday, August 1, 2019 1:07:52 PM
To: ecandrietta=40gmail.com = <ecandrietta=40gmail.com>= ; ORACLE-L <oracle-l=40freelists.org&g= t;
Subject: RE: Blocking_session is blocker or blocked =

 

You may want to add the clause  =

 

and blocking_session_status =3D = 'VALID'

 

I&=238217;ve seen cases where blocking_session = is not null, but the blocking_session_status =21=3D = &=238216;VALID&=238217;.   I believe that both checks are needed = to avoid giving false positives.

 

 

 

From: oracle-l-bounce=40freelists.org <oracle-l-bounce=40freel= ists.org> On Behalf Of Eriovaldo Andrietta
Sent: Thursday, August 01, 2019 11:06 AM
To: ORACLE-L <oracle-l=40freelists.org&g= t;
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=23, inst_id,
seconds_in_wait, program, module, s.LOGON_TIME
From=20gv=24session 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. --_000_DM6PR10MB41879B38F7ACE846EF42B2AFA5D70DM6PR10MB4187namp_-- -- http://www.freelists.org/webpage/oracle-l