Re: Expanding a table

From: Tim Gorman <>
Date: Fri, 22 Dec 2017 09:38:49 -0700
Message-ID: <>


I don't want to discourage you from testing the ALTER TABLE|INDEX ... ALLOCATE EXTENT command, but I believe that extent allocation is not what triggers the "eng: HW - contention" event.  I believe that is due to none other than the act of moving the high-water mark within extents, but it should still be tested, since it would be so easy to test in situ.

Also, please do not give up so quickly on the possibility of changing tablespace and table attributes?  Very few applications issue requirements on tablespace attributes, and the fact that this one is using the default setting lends credence to that.  Likewise for table attributes like FREELISTS.

So even though you might not encounter resistance to the suggestion of creating new tablespaces with SEGMENT SPACE MANAGEMENT MANUAL only for the involved tables and associated indexes, nor for also increasing the table/index attribute for FREELISTS, it would not be difficult for you to test some scenarios ahead of making such recommendations.  Anybody likely to raise an objection (internally or from the vendor) is less likely to do so in the face of factual evidence.  Conjecture tends to stimulate stifling debate, but factual evidence tends to win the day, or at least raise the bar on debate.

It would not be difficult to create a SQL*Plus script consisting of an anonymous PL/SQL block inserting rows one at a time, and then creating a shell script to concurrently execute that SQL*Plus script multiple times from multiple simultaneous sessions, to simulate your problem scenario.

You could baseline a test by running with concurrency of 8, 16, 24, or 32 simultaneous sessions against your test table with FREELISTS 1 in a tablespace with SEGMENT SPACE MANAGEMENT AUTO.  Then, repeat the same tests against the test table with FREELISTS set roughly equivalent to the degree of parallelism in a tablespace with SEGMENT SPACE MANAGEMENT MANUAL, and see what happens?

Of course, in addition to simply recording elapsed times, it could be useful to snapshot AWR before and after, and if you can initiate SQL tracing as well, it could come in handy for possible diagnosis too.

If you need templates or samples for such SQL*Plus or shell scripts, I and others on this list would be glad to contribute?

Another consideration is that you might have material for a great blog post or a users group conference presentation or newsletter article?  This is the kind of stuff colleagues want to hear about, rather than purported "experts" blathering.

Please let us know what you think?

Hope this helps?



On 12/22/17 06:09, Paul Houghton wrote:
> Thanks Tim, you prompted me to read up on storage management. We have locally managed tablespaces with automatic segment management, so there isn't much we can tune. No freelists. I suppose we are removing many of the benefits of ASSM by hammering the same tables. They start off empty and are inserted into. I am hoping that allocating an extent of the expected size for the table and its indexes I will reduce the time taken to move the HWM. No space has to be found in the tablespace or allocated from the filesystem, so the pointer can just be changed which will hopefully be pretty quick. Yes, I told them to remove indexes not needed for the process and add them in again afterwards! We are next running a test in the new year, so I will see if it helped then.
> You make good points Mark, but it is too late in the project to make the changes you suggest. This is a peoplesoft system, and the APIs are delivered by Oracle which would have made some of the suggestions difficult. I believe the data conversion runs fast enough at present, we are just trying to increase the contingency by reducing run times.
> Thanks again for your help.
> PaulH
>> -----Original Message-----
>> From: []
>> On Behalf Of Mark W. Farnham
>> Sent: 21 December 2017 18:43
>> To:; 'Oracle-L Freelists' <>
>> Subject: RE: Expanding a table
>> Tim covered what you asked for really well. (And pre-saged the possibility
>> of bumping up the number of Freelists and freelist groups if your
>> management is "manual" (which means Freelists and is no less automatic in
>> operation than AUTO) as opposed to AUTO (meaning a bitmap pattern
>> allocation of using free blocks.)
>> Now for something completely different: You mentioned this is a data
>> conversion using APIs. IF these are APIs your team built, and if you're
>> running many copies of this API in simultaneously in serial (a valid use
>> of the English word "in parallel") as opposed to trying to get parallelism
>> out of row by row processing run with a parallel degree in Oracle,
>> THEN if you run each copy of this job into its own scratchpad table in
>> serial and when they are full you copy append the rows into the true
>> destination one after the other you *SHOULD* avoid all this contention
>> completely.
>> IF even changing the destination table is a challenge to re-code (as
>> opposed to what might reasonably be a challenge to re-design row by row to
>> be batch), then you can use multiple schema (ie. users in Oracle) to build
>> the appropriate scratchpad fragments and simply grant read to the "real"
>> application schema to read them for the append mode inserts.
>> IF all that is suitable, then a cherry on top is if ordering the select
>> for the batch insert might have a useful effect grouping data that is read
>> together together in blocks. In configurable off the shelf stuff like
>> Peoplesoft this is often some internal id for a person or part or general
>> ledger combination. Your mileage may vary.
>> This is NOT what you asked for, but I believe it may be a useful solution
>> to your problem, especially if you limit the engineering to the big things
>> that need to be migrated and the machine over exercise itself for the
>> other things.
>> One more thing: If even this much change is a problem, if you have many
>> tables to migrate, you could also avoid the problem by migrating sets of
>> tables (each serial) in parallel. To do that you may need to order the
>> loading of tables so that any foreign key required reference checking
>> happens exists when needed or disable constraints.
>> mwf
>> -----Original Message-----
>> From: []
>> On Behalf Of Tim Gorman
>> Sent: Thursday, December 21, 2017 1:01 PM
>> To: Oracle-L Freelists
>> Subject: Re: Expanding a table
>> Elizabeth,
>> That thought occurred to me as well, and it is worth testing, but moving
>> the high-water mark within extents (i.e. "enq: HW - contention") is not
>> the same as adding extents.
>> Paul,
>> I'm curious about the tablespace's setting for SEGMENT SPACE MANAGEMENT
>> AUTO|MANUAL and the table's FREELISTS settings? Do you have any
>> latitude in how these are set? If so, I'm pretty sure we can ameliorate
>> this issue?
>> If we can't figure out a solution by fiddling with tablespace space
>> management settings and table freelist settings, then perhaps we might
>> consider going off-roading for a bit...
>> May the gods (and Oracle Support) forgive me for saying so, but there is
>> the undocumented parameter named "_bump_highwater_mark_count" which
>> specifies how many blocks should be allocated per freelist on advancing
>> HWM, at least there was as late as 11gR2. Historically, the default value
>> here has always been "5", and looking in I see a default value of
>> "0" (which probably simply indicates "default value" rather than "do not
>> advance HWM"). Theoretically, increasing this count might decrease the
>> number of occurrences of HWM advancing, which would perhaps reducing the
>> number of waits? Anyway, I'll leave that idea laying in the gutter by the
>> roadside...
>> Hope this helps...
>> Thanks!
>> -Tim
>> On 12/21/17 10:09, Reen, Elizabeth (Redacted sender elizabeth.reen for
>> DMARC) wrote:
>>> You can change the next extent size with an alter table command.
>>> Liz
>>> Elizabeth Reen
>>> CPB Database Group Manager
>>> 718.248.9930 (Office)
>>> Service Now Group: CPB-ORACLE-DB-SUPPORT
>>> -----Original Message-----
>>> From:
>>> [] On Behalf Of Paul Houghton
>>> Sent: Thursday, December 21, 2017 7:06 AM
>>> To: Oracle-L Freelists
>>> Subject: Expanding a table
>>> We are finding ourselves in a situation where a number of sessions
>> conflict on HW Contention (50 minutes per hour) on a number of tables.
>>> This is a data conversion running in parallel. The problem stems from a
>> decision to use APIs designed for interactive programs, so we have a slow
>> row by row conversion which we are attempting to speed up by running it in
>> parallel. I can't change this decision - I tried!.
>>> I suspect if the table were created at the correct size to start with
>> this would no longer be an option.
>>> Unfortunately the tables are created by an application (PeopleSoft)
>> which doesn't allow the initial extent to be specified per table. You
>> can't change the initial extent once the table has been created, even if
>> the segment hasn't been created (We have deferred segment creation).
>>> I am thinking I need to try to do something clever with
>> dbms_metadata.get_ddl to get the definitions, alter them, then drop and
>> recreate the tables.
>>> Can you think of another (easier) way to increase the size of a tables?
>> There are hundreds of them, so I don't really want to insert loads of rows
>> then delete them.
>>> Thanks
>>> PaulH
>>> --
>>> webpage_oracle-2Dl&d=DwIFAg&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8V
>>> LeJtKLVJGefQxustAZ9UxecV7xpc&m=53bPIOlcEiirXwZKnhoPO-sihX9NkhnkSLoC3Ev
>>> iWLc&s=kAU6Moq2m9R40NEC4i08Bqx5q80F-hO8IusUK_eCzyk&e=
>>> --
>> --
>> --

Received on Fri Dec 22 2017 - 17:38:49 CET

Original text of this message