Re: [EXTERNAL] Re: enq: TS - contention
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 10 Nov 2023 23:11:02 -0500
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
Other related posts:
