Re: [EXTERNAL] Re: enq: TS - contention

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_at_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
>
>

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