Re: tablespace autoextend on next size not obeyed

Hello Jonathan,

Because all the tablespaces where we do heavy loading are bigfile I could answer yes.

_enable_space_preallocation is set to 3 (on both systems)

The configuration of primary/standby is absolutely identical, no differences in performance.

From what I see in ASH the resize operations in standby might impact PR00 process (wait reported being ASM file metadata operation / cell smart file creation). So instead of progressing with apply is being busy with the resizes (and those resizes should not be problematic if they will be just a few and not 15K with that very small increment)

I`ve opened an SR as well and the suggestion was to set _enable_space_preallocation to 0 (solution I would like to avoid because I don`t want the resizes to get reactive and have a big impact on primary workload).

THank you.

În joi, 17 iun. 2021 la 13:41, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

> Are these all bigfile tablespaces, do all the files have
> dba_data_files.increment_by set explicitly to 10g (i.e. 1,310,720 if you're
> using 8KB blocks).
> Is the parameter "_enable_extent_preallocation" set to 3 on production and
> standby.
> Do the two systems have significantly different numbers of CPUs - which
> could affect the number of Wnnn processes.
> Do you get any clues about the small allocations being handled by
> foreground processes while the large ones are handled by Wnnn?
>
> I can't explain why you're seeing the effect, but I could imagine that
> even a 10G (unit) allocation being shared out in units of 64MB across
> multiple Wnnn processes thanks to some algorithm that was checking
> concurrency, load, and CPU usage.
>
> Regards
> Jonathan Lewis
>
>
> On Thu, 17 Jun 2021 at 09:28, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
> wrote:
>
>> Hello listers,
>>
>> Does anyone have experienced the behavior of big file tablespace resize
>> operations in chunks of 64M with a very large number of resize operations?
>> If yes, how did you mitigate this behavior?
>>
>> I see some tablespaces being incremented with over 15k Resize operations
>> of 64M in size but sometimes times defined size by autoextend on next (10G)
>> is used.
>>
>> The problem is the huge number of resize operations are replied in the
>> standby database and looks to struggle to handle both redo apply and resize
>> operations creating significant apply lag .
>>
>> Thank you.
>>
>

--
http://www.freelists.org/webpage/oracle-l