One more thing: IF only a reasonable number of tables, indexes, and other
objects (like out of line lobs) are asking for new extents (and you know which
ones those are), you can preemptively add extents to those objects so they
don’t ask for more space whilst the resize is in flight. I think the document
search to find the various alter commands is “allocate_extent_clause” which is
part of alter table, alter index, etc., for unique naming purposes rather than
a generalized command for any object. NOTE: this is completely different from
ALTER TABLESPACE resize, which reaches out to your “disk farm” to grab
additional operating system media space. This allocates additional space to the
subject object as extents. Further note: IF you have objects which routinely
grow in a predictable way, you can avoid busy “insert time” contention for
space without wasting much space. Most of my customers did this on a monthly or
quarterly basis during planned maintenance, so they had nary a dynamic extent
allocation of their “official” production objects except during maintenance.
I don’t grok why every professionally managed significant database operation
lacks this step. My best guess is they rarely notice any elapsed time
degradation and they do space capacity planning at a much larger grain.
What you need to plan for this is “extmon” and a trend spreadsheet day by day
that reports which things are growing and by how much. Since Oracle blew the
top off maximum extents and straight jacketed the next allocation
specifications to their idea of the right possible sizes, the capacity planning
side effect of knowing your growth pattern has mostly disappeared.
I wonder whether I missed a memo and Oracle slipped that tracking into the
dictionary in a reasonable way. “Extmon” relied on maintaining its own history
and change tables, but I have not updated it since circa Oracle 9 when folks
started indiscriminately sharing the open code instead of paying the nominal
fee.
Good luck.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mark W. Farnham
Sent: Monday, July 11, 2022 6:51 AM
To: oracle.developer35@xxxxxxxxx; 'Jonathan Lewis'
Cc: 'Oracle L'
Subject: RE: DML blocking
IF you’re nearly out of space in the tablespace Oracle may be attempting to
“glue” adjacent wrong size fragments into sizes the table or index being
extended is asking for.
So it *may* be enough to do one “smallish” addition, wait a while until you see
allocations from the new chunk, and then see if the big one will work.
Allocations started before the new space appears may continue to do a lot of
extra work and I’m uncertain when allocations in flight can see the new space.
The experiment to figure that out is difficult and involves race conditions, so
possibly an Oracle lurker can tell us.
Anyway, I think that is probably the basis for the recommendation to wait a
while between adding space “alters” – letting existing allocations complete.
Another approach is to make a new tablespace and move the least active (“old”
date partitioned table partitions, for example) large stuff to the new
tablespace, freeing the space of the things moved instead of adding to the
existing tablespace. I think the move itself only grabs any locks on the
existing tablespace space when it completes and has to add the now available
space to the tablespace free map.
Good luck.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Pap
Sent: Monday, July 11, 2022 6:27 AM
To: Jonathan Lewis
Cc: Oracle L
Subject: Re: DML blocking
Thank You Jonathan.
Yes, the workarounds in the doc are little extreme ones. So basically as you
suggested , the appropriate ones seems to be avoid the peak time for increasing
size we will definitely consider. Regarding other point doing it in smaller
chunks(say 100GB) and with some gap. So if i understand it correctly , doing it
in smaller chunks will make this blocking happen for smaller duration, but
talking about having some duration gap between two 'alter' statement, is there
any technical cause around this or its just to give some time to wait and watch
for any app blocking session?
On Mon, 11 Jul 2022, 3:33 pm Jonathan Lewis, <jlewisoracle@xxxxxxxxx> wrote:
You missed a line in the document:
"This is blocking other processes especially the session issuing the resize
command."
(my emphasis)
If an insert needs to add an extent to a table it has to acquire the
tablespace/file space management block(s) to mark the space as allocated. So
it's not surprising if it has to wait for the session (or Wnnn slaves) that are
extending the tablespace. The document gives a workaround; two other "damage
limitation" options are (1): don't resize a file by a HUGE amount in one step,
use smaller steps with an interval between each step. (2) resize the
tablespace/file only when there's likely to be very little competing activity,
Regards
Jonathan Lewis
On Mon, 11 Jul 2022 at 10:18, Pap <oracle.developer35@xxxxxxxxx> wrote:
Hi We have a customer database on version 19.11.We saw all of sudden most of
the session for a INSERT query got blocked and the wait event was showing as
"enq: TT - contention [mode=4]" . And the blocking session appeared to be a
session executing "ALTER database sql" as below. However , wanted to know if
its expected behavior to block application query doing DML on that same
tablespace object or we are hitting any bug here?
I see a doc "Resize Tablespace Hangs with TT Enqueue Contention (Doc ID
2595875.1)", but it says the ALTER statement itself will hang but doesnt tell
anything on, if it will imact any DML on the object residing on that
tablespace. It's a bigfile tablespace.
alter database datafile '+DATA_XXXX/XXXXX/DATAFILE/data.XXX.XXXXXX' resize
10000G
