Re: DML blocking

Got it. Thank you so much for explaining the details behind adding a smaller chunk followed by a time gap.

On Mon, Jul 11, 2022 at 5:51 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> 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
> *To:* oracle.developer35_at_gmail.com; '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_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <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