MIME-Version: 1.0
References: <SN7PR14MB6470D41DF43F49D0BCB7CF5FE0A8A@SN7PR14MB6470.namprd14.prod.outlook.com>
 <CAGtsp8no7DNGXnJzsfBE-nGgt8UmVMzRPTLe8VFXxnEEuLWbig@mail.gmail.com>
 <SN7PR14MB647090B140548036B90FB20CE0AEA@SN7PR14MB6470.namprd14.prod.outlook.com>
 <CAGtsp8m=0qYLJp42hjNe_--YYb6YqNVysPjqjrcKhPC8WK7pCw@mail.gmail.com> <a608534c-8880-4098-ad2b-b54ffeeca6ff@gmail.com>
In-Reply-To: <a608534c-8880-4098-ad2b-b54ffeeca6ff@gmail.com>
From: Jonathan Lewis <jlewisoracle@gmail.com>
Date: Sat, 11 Nov 2023 00:08:31 +0000
Message-ID: <CAGtsp8mvK7Q+BGkZsLK5-Ju8-cEbwNbOm2YQMP+VLcqnzxGPkQ@mail.gmail.com>
Subject: Re: [EXTERNAL] Re: enq: TS - contention
To: oracle-l@freelists.org
Content-Type: multipart/alternative; boundary="0000000000006f53da0609d53d89"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jlewisoracle@gmail.com
--0000000000006f53da0609d53d89
Content-Type: text/plain; charset="UTF-8"

Mostly the source would probably be in Oracle Core. If not then in the
notes on my laptop of tests I've done in the past.

The meaning of the parameters is listed in v$event_name (though some of the
descriptions may be a little out of date)  Enabling event 10704 at various
levels used to produce interesting traces of locks but from 11g you can
execute (e.g.): *alter session set events 'trace[ksq] disk=high'* then do
some things that require tablespace space management to see what TS enqueue
information appears in the trace files.

The note you've referenced is very old and has various bits of information
that are out of date, and links to other notes that are out of date or
wrong, so I wouldn't put much faith in it - despite "update dates" being in
the recent past.

Regards
Jonathan Lewis


On Fri, 10 Nov 2023 at 21:15, Mladen Gogala <gogala.mladen@gmail.com> wrote:

> On 11/10/23 15:58, Jonathan Lewis wrote:
>
> P1 = convert to hex = 0x5453 0003:  the top half is equivalent to 'TS';
> the 0003 is the v$lock mode (which means it's not about creating or
> dropping segments which require mode 6
>
> P2 = 0x0003 0003 -> the top half says it's con_id 3 (which usually means
> the first PDB after the seed - see v$pdbs to check), the bottom half is the
> tablespace id (ts# from v$tablespaces in the PDB).
>
> p3 is (should be) the tablespace relative block number, which means it's
> block 2 you're waiting for.
>
> A block number that low would have to come from the one file allocated to
> a "bigfile" tablespace, so if the tablespace isn't bigfile then there's
> another interpretation of p2 and p3 that I don't know about.
>
> Block 2 of the file is the "File Space Header" - so the next step is to
> find out why a session got stuck waiting for it ... possibly something to
> do with the file being extended and a process losing track of what was
> going on and not clearing a flag - or maybe space allocation from the O/S
> just took a very long time.  I don't have any further ideas at the moment.
> It does give you a pointer to questions you could ask of Oracle Support.
>
>
> Regards
> Jonathan Lewis
>
> Jonathan, can you please provide source for this? I found the reference
> note, but it doesn't contain the information about P1/2/3. Can you please
> post the document id?
>
> Regards
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

--0000000000006f53da0609d53d89
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div di=
r=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><br><div>Mostl=
y the source would probably be in Oracle Core. If not then in the notes on =
my laptop of tests I&#39;ve done in the past.</div><div>=C2=A0</div><div>Th=
e meaning of the parameters is listed in v$event_name (though some of the d=
escriptions may be a little out of date)=C2=A0 Enabling event 10704 at vari=
ous levels used to produce interesting traces of locks but from 11g you can=
 execute (e.g.):=C2=A0<b><i>alter session set events &#39;trace[ksq] disk=
=3Dhigh&#39;</i></b> then do some things that require tablespace space mana=
gement to see what TS enqueue information appears in the trace files.</div>=
<div><br></div><div>The note you&#39;ve referenced is very old and has vari=
ous bits of information that are out of date, and links to other notes that=
 are out of date or wrong, so I wouldn&#39;t put much faith in it - despite=
 &quot;update dates&quot; being in the recent past.=C2=A0</div><div><br></d=
iv><div>Regards</div><div>Jonathan Lewis</div><div><br></div><div><br></div=
><div>=C2=A0</div></div></div></div></div></div></div></div></div><br><div =
class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, 10 Nov =
2023 at 21:15, Mladen Gogala &lt;<a href=3D"mailto:gogala.mladen@gmail.com"=
 target=3D"_blank">gogala.mladen@gmail.com</a>&gt; wrote:<br></div><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px=
 solid rgb(204,204,204);padding-left:1ex"><u></u>

 =20
   =20
 =20
  <div>
    <div>On 11/10/23 15:58, Jonathan Lewis
      wrote:<br>
    </div>
    <blockquote type=3D"cite">
      <div>P1 =3D convert to hex =3D 0x5453 0003:=C2=A0 the top half is
        equivalent to &#39;TS&#39;;=C2=A0 the 0003 is the v$lock mode (whic=
h means
        it&#39;s not about creating or dropping segments which require mode
        6</div>
      <div><br>
      </div>
      <div>P2 =3D 0x0003 0003 -&gt; the top half says it&#39;s con_id 3 (wh=
ich
        usually means the first PDB after the seed - see v$pdbs to
        check), the bottom half is the tablespace id (ts# from
        v$tablespaces in the PDB).</div>
      <div><br>
      </div>
      p3 is (should be) the tablespace relative block number, which
      means it&#39;s block 2 you&#39;re waiting for.
      <div><br>
      </div>
      <div>A block number that low would have to come from the one file
        allocated to a &quot;bigfile&quot; tablespace, so if the tablespace=
 isn&#39;t
        bigfile then there&#39;s another interpretation of p2 and p3 that I
        don&#39;t know about.</div>
      <div><br>
      </div>
      <div>Block 2 of the file is the &quot;File Space Header&quot; - so th=
e next
        step is to find out why a session got stuck waiting for it ...
        possibly something to do with the file being extended and a
        process losing track of what was going on and not clearing a
        flag - or maybe space allocation from the O/S just took a very
        long time.=C2=A0 I don&#39;t have any further ideas at the moment. =
It
        does give you a pointer to questions you could ask of Oracle
        Support.</div>
      <div><br>
      </div>
      <div><br>
      </div>
      <div>Regards</div>
      <div>Jonathan Lewis</div>
    </blockquote>
    <p>Jonathan, can you please provide source for this? I found the
      reference note, but it doesn&#39;t contain the information about
      P1/2/3. Can you please post the document id?</p>
    <p>Regards<br>
    </p>
    <pre cols=3D"72">--=20
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a href=3D"https://dbwhisperer.wordpress.com" target=3D"_blank">https://dbw=
hisperer.wordpress.com</a>
</pre>
  </div>

</blockquote></div>

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