Re: AW: Increased PGA requirements for SORTs in 19c?

Hi Mike,
Maybe this is due to automatic indexing . Sharing with us execution plans under 12.2 & 19.9 can help.

Thanks,
Cherif

Le sam. 21 nov. 2020 à 17:34, Mladen Gogala <gogala.mladen_at_gmail.com> a écrit :

> Yes, PGA usage is expanding, primarily because of hash joins. So called
> adaptive plans, which can be changed at runtime will switch a merge join or
> nested loops to hash join. Hash join is usually faster than a merge join.
> The unfortunate side effect is that it needs more memory. However, memory
> is cheap these days. My first 1MB memory expansion for my PC AT (Intel
> 80286) was priced around 100 DEM (50 EUR) in 1980's. Today, 1MB RAM is much
> cheaper than that. BTW, that card was purchased in Munich, Germany in the
> store on Schiller Strasse, which was then a paradise of electronic shops.
> You could buy Diton Soundspeakers, Dual turntables, the latest CRT TV sets
> and computers. Lots of computers. That is why I listed the price in DEM, a
> currency that no longer exists.
>
> BTW, Oracle 5.1.22 for DOS 3.3 required a special module to be loaded, so
> that SGA can be allocated from that 1MB card. PC AT itself was 16 bit, so
> 1MB was a maximum it could address. Oracle worked quite well, however
> without row level locking. That came around in version 6 which sometimes
> required whopping 4MB of SGA. The usual and recommended value for
> sort_area_size parameter was 64K. The times and databases have certainly
> changed.
> On 11/21/20 3:09 AM, Willy Klotz wrote:
>
> > The Oracle analyst said “Version to version there will be need a more
> memory on SGA and PGA memory, So this is expected. Please increase the
> PGA_AGGREGATE_TARGET and check most of the sort operations are in memory.”
>
> That’s the old and never changing bla from support. Every time I hear
> “this is expected” I know that they do not know what they are talking about
> ….
>
>
>
> > Has anyone seen an effect like this?
>
> In one word, YES.
>
> Give it more pga_aggregate_target, until direct path write temp and direct
> path read temp returns to acceptable values (you may have to increase
> pga_aggregate_limit also).
>
>
>
> Regards and good luck
>
> Wilhelm Klotz
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

-- 
Cordialement,
Cherif Ben Henda

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