Re: Increased PGA requirements for SORTs in 19c?

That's very interesting,
Thanks for posting your conclusions.
Do you have a small model / test case that you could post for tother people to play around with ?

Since you mention hash aggregation does that mean you didn't actually get any results for the 10032 / 10033 traces which are for sorting only, not for hashing ?

Regards
Jonathan Lewis

On Thu, 3 Dec 2020 at 11:50, Tefft, Michael J <Michael.J.Tefft_at_snapon.com> wrote:

> Thanks to all for your advice.
>
>
>
> We have found a workaround for this issue.
>
>
>
> The query involves a view that uses a GROUP BY to 'pivot' a table's
> VARCHAR2(4000) column into over 20 copies of this column. The data in the
> column never has more than 33 characters. But the optimizer seems to be
> using the maximum (declared) column size to determine its memory needs -
> which has spilled over to TEMP demands as well.
>
>
>
> This seems to be a change in behavior from 12.2 to 19c. The disparity in
> memory allocation suggests that the previous version probably used the
> column statistics to plan the memory/temp requirement: we observed roughly
> a 100x increase in PGA+TEMP and the difference between 'declared' data size
> and statistics of the column data size is also roughly 100x.
>
>
>

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