RE: Expanding a table
Date: Fri, 22 Dec 2017 13:09:07 +0000
Message-ID: <AM5PR0701MB1810015561C185BDB5CC5060E6020_at_AM5PR0701MB1810.eurprd07.prod.outlook.com>
> -----Original Message-----
> From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]
> On Behalf Of Mark W. Farnham
> Sent: 21 December 2017 18:43
> To: tim.evdbt_at_gmail.com; 'Oracle-L Freelists' <oracle-l_at_freelists.org>
> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> 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_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org] 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=
> >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
��i��0���zX���+��n��{�+i�^ Received on Fri Dec 22 2017 - 14:09:07 CET