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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_at_gmail.com> 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_at_gmail.com> 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    

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