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

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 10 Nov 2023 23:11:02 -0500
Message-ID: <6979781d-80d3-408a-8bbc-c48e021ebd80_at_gmail.com>

On 11/10/23 19:08, Jonathan Lewis wrote:
> 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.

V$EVENT_NAME descriptions are notoriously unclear and incomplete. Here is what they show for the enq: TS congestion:

SQL> select name,parameter1,parameter2,parameter3    2  from v$event_name
   3* where name like '%enq: TS - contention%';

NAME                    PARAMETER1    PARAMETER2 PARAMETER3

_______________________ _____________ ________________ _____________

enq: TS - contention    name|mode     tablespace ID dba

Tablespace ID is clearly not 196611 as Amir has stated.  Converted to hex, P2 is 0x30003:

SQL> select to_char(196611,'XXXXX') from dual;

TO_CHAR(196611,'XXXXX')

---

  30003

Now, if I understand you correctly, the tablespace should be:

SQL> select mod(196611,65536) from dual;

    MOD(196611,65536)

---

                    3

So, in my database, that would be TEMP tablespace:

SQL> select name,included_in_database_backup from v$tablespace where ts#=3;

NAME    INCLUDED_IN_DATABASE_BACKUP

_______ ______________________________

TEMP    NO Again, in my database that would be in the tenant called "ORCLPDB1":

SQL> select to_char(196611/65536,'9999') from dual;

TO_CHAR(196611/65536,'9999')

---

     3

Elapsed: 00:00:00.022
SQL> show pdbs

    CON_ID CON_NAME    OPEN MODE     RESTRICTED _________ ___________ _____________ _____________          3 ORCLPDB1    READ WRITE    NO SQL> Now, how can I figure out by myself how is the data encoded? I got result by splitting the P2 argument on the 16 bit boundary. Most of the machines of today are using 64 bits processors . That means that a single machine word spans 4 sixteen bits groups. How can I figure out that the value is encoded using 16 bits values? I cannot find that in the documentation.  Long time ago, in the times of 8i, such "details" were available in the documentation. Every available event was documented and the parameters were well described in the Anjo Kolk's famous paper. Now, the events are renamed, some are documented but the many are not. I must say that I find the documentation quality of the recent Oracle versions rather disappointing, to put it mildly.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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

Received on Sat Nov 11 2023 - 05:11:02 CET