RE: DML blocking
Date: Mon, 11 Jul 2022 08:20:49 -0400
Message-ID: <05ce01d89520$a89ad0c0$f9d07240$_at_rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Monday, July 11, 2022 6:51 AM
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
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
To: oracle.developer35_at_gmail.com; 'Jonathan Lewis'
Cc: 'Oracle L'
Subject: RE: DML blocking
To: Jonathan Lewis
Cc: Oracle L
Subject: Re: DML blocking
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jul 11 2022 - 14:20:49 CEST