Re: enq: TS - contention

I don't know all the options that might appear for the TS enqueue, but it is requested during the creation and dropping of many (probably all) types of segments. It's not restricted to temporary segments in the temporary tablespace.

The p1, p2, p3 values for the event are
  name || mode, (0x5453000n) --- n is the mode, values 0 - 6   con_id || ts#, (0xcccctttt) ,,, typical top 4 / bottom 4 hex characters, but the con_id might not be there   tablespace relative block address

If you can query v$lock id1 will be the (con_id || ts#) and id2 will be the block address.

For creating a segment Oracle gets the TS lock once in exclusive mode on the segment header block.
For dropping a segment Oracle gets the TS lock twice in exclusive mode, first for the segment header block, then for the first "data" block of the segment.

It seems unlikely that the SQL you've shown would lead to any type segment creation - possibly in a RAC system and with a large number of PDBs, object types and users some feature of the view definition might cause a SYS-recursive query to generate a very bad plan with a huge temporary segment - but that would be a little surprising.

Is there anything you know about procedures A and B that would lead to excessive numbers of segments being rapidly created and dropped, and possibly hitting some strange boundary conditions (e.g. lots being created - which take the TS enqueue - but the purge option not being specified on the drop - which would mean the TS enqueue wouldn't be taken, but a background might start dropping segments from the recyclebin when space pressure got too high). Another hypothetical problem could be excessive demand leading to automatic file extensions and an undetected deadlock.

I think you need to work out what the procedures are doing around the time of the fail, and see if that gives you any clues. Perhaps you could be guided by the SQL_IDs in the ASH data in the couple of minutes leading up to the wait.

Regards
Jonathan Lewis

On Wed, 8 Nov 2023 at 18:02, Hameed Amir <dmarc-noreply_at_freelists.org> wrote:

> Hi,
>
> The database version is 19.17.0.0, running on OEL8.
>
> In our Oracle E-Business Suite production database, a batch job (A) runs
> and spawns another job (B). Intermittently, job A runs longer and ends up
> getting terminated by the user. ASH data shows that the process running job
> A awaited the event "*enq: TS - contention*".
>
>
>
> Based on the limited information I have found, this event seems related to
> the TEMP tablespace. There is no SQL_ID associated with the session ID
> waiting on the event. There is a TOP_LEVEL_SQL_ID, which shows the
> following SQL statement:
>
>
>
> *select directory_name from all_directories where directory_path = :1*
>
>
>
> Has anyone run into this issue? Any feedback will be greatly appreciated.
>
>
>
> Thank you,
>
> Amir
>

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