RE: Expanding a table

        Maybe creating bigger extents will mitigate the issue. If the next extent is 64k, then I can see contention here. If it is sized to be large then something else is going on here. Paul did not say what the sizes were.

Liz

-----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=
>
>
> --
> https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_
> webpage_oracle-2Dl&d=DwIDaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8V
> LeJtKLVJGefQxustAZ9UxecV7xpc&m=_HnNno-OlaKP_UEXbAksglRt2akqgEEgaZEG5KI
> E9b4&s=T62XC8d-uhD3Su2qJm538DbV_DHOLFtAuxsVe3qZDdw&e=
>
>
>

--
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwIDaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=_HnNno-OlaKP_UEXbAksglRt2akqgEEgaZEG5KIE9b4&s=T62XC8d-uhD3Su2qJm538DbV_DHOLFtAuxsVe3qZDdw&e=

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Fri Dec 22 2017 - 21:10:09 CET