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

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


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