This is a standard EBS interim table. Depending on the vintage of its creation
it might be in a specially designated tablespace.
The tablespace name of this particular table, a list of your tablespace names,
and a distinct list of the tablespaces for table names containing %INTERIM% may
be helpful.
From time to time scheduled timing of jobs at large volumes may create
conditions where one large batch process is grabbing big chunks of a tablespace
in contention with additional processes in the same tablespace.
Sometimes in the past the freelist or new space bitmap for such tables has
become unworkably tangled with many empty blocks near the front of the table
essentially unusable.
Many of the *interim* tables should from time to time be empty, and it is often
productive to very carefully (meaning when you are certain no one else can be
inserting into them and the particular table is in fact empty) truncate.
Checking for the “empty front” condition may also be useful: Just select some
non-indexed not null column where rownum < 2 from the table after checking how
many blocks you have fetched in your session and again after the one row has
been returned. If a surprisingly large number of blocks has been fetched to
obtain the first row, you may have a problem.
Sometimes antediluvian batches that were aborted may be “disconnected” (my
word) from the usual batch completion processes. In that case (or if batch
creations and completions are simply staggered inconveniently for cleanup
purposes) you may need to arrange to copy out all the data (probably using
batch id and one or two other columns that will likely scream their names at
you for ordering), do the truncate, and then copy the data back in. Having
residual “disconnected” batches at the front of such tables will complicate
testing for “empty front” in the future, so it is usually worthwhile to
investigate whether that data is truly obsolete, and if so, copy it out into a
dated version of the table that can be dropped eventually in accordance with
your data retention policy, and from which you can retrieve it if you made a
mistake assessing it as obsolete.
IF it should be the case that many “interim” tables for concurrent manager jobs
(batch jobs) that are run at the same time are in the same tablespace, you can
evaluate moving some of them somewhere else. Unless you are having disk i/o
throughput problems, they don’t really need to be on different volume stripes.
They just need to be a different tablespace to avoid the space contention.
Good luck,
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Hameed Amir ("amir.hameed")
Sent: Friday, November 10, 2023 1:47 PM
To: Jonathan Lewis
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: [EXTERNAL] Re: enq: TS - contention
Hi Jonathan,
Thank you for the explanation. I looked through DBA_HIST views for information
on job A for periods when it finished successfully, and I found the following
statement in all those periods:
DELETE FROM AR_AUTOREM_INTERIM WHERE BATCH_ID = :B1
It is a standard Oracle EBS table. It is partitioned and has no index on it.
Can you please show me how to convert P2 into TS name below?
Event = enq: TS – contention
P1 = 1414725635
P2= 196611
P3= 2
Thank you,
Amir
From: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
Sent: Thursday, November 9, 2023 6:13 PM
To: Hameed, Amir <amir.hameed@xxxxxxxxxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
Subject: [EXTERNAL] Re: enq: TS - contention
CAUTION: External source
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@xxxxxxxxxxxxx> 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
