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.
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 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
--
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
