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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: 21 December 2017 18:43
To: tim.evdbt@xxxxxxxxx; 'Oracle-L Freelists' <oracle-l@xxxxxxxxxxxxx>
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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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 11.2.0.4 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.conflict on HW Contention (50 minutes per hour) on a number of tables.
Liz
Elizabeth Reen
CPB Database Group Manager
718.248.9930 (Office)
Service Now Group: CPB-ORACLE-DB-SUPPORT
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
decision to use APIs designed for interactive programs, so we have a slow
This is a data conversion running in parallel. The problem stems from a
row by row conversion which we are attempting to speed up by running it in
parallel. I can't change this decision - I tried!.
this would no longer be an option.
I suspect if the table were created at the correct size to start with
which doesn't allow the initial extent to be specified per table. You
Unfortunately the tables are created by an application (PeopleSoft)
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).
dbms_metadata.get_ddl to get the definitions, alter them, then drop and
I am thinking I need to try to do something clever with
recreate the tables.
There are hundreds of them, so I don't really want to insert loads of rows
Can you think of another (easier) way to increase the size of a tables?
then delete them.
Thanks
PaulH
--
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_
webpage_oracle-2Dl&d=DwIFAg&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8V
LeJtKLVJGefQxustAZ9UxecV7xpc&m=53bPIOlcEiirXwZKnhoPO-sihX9NkhnkSLoC3Ev
iWLc&s=kAU6Moq2m9R40NEC4i08Bqx5q80F-hO8IusUK_eCzyk&e=
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
