As a separate workaround, I was trying to force the queries to use hash
aggregation instead of sort aggregation. I was able to get one query to use the
HASH GROUP BY (which was also restored good performance) but was unable to get
another to bend to my will. This is why I was searching for hash aggregation
articles.
I did get results from the sort traces, in our non-production 19c environment.
I had not yet attempted to get them from our 12.2 environment, which is
production and is subject to change-control procedures. In the meantime I found
this solution.
I spent years as a data modeler; to see such columns defined as VARCHAR2(4000)
when they would never need to be such a size is…disappointing.
If I have time, I will try to construct a simple test case.
Mike
From: Jonathan Lewis [mailto:jlewisoracle@xxxxxxxxx]
Sent: Thursday, December 3, 2020 7:23 AM
To: Tefft, Michael J <Michael.J.Tefft@xxxxxxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Increased PGA requirements for SORTs in 19c?
CAUTION: This email originated from outside of Snap-on. Do not click on links
or open attachments unless you have validated the sender, even if it is a known
contact. Contact the sender by phone to validate the contents.
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@xxxxxxxxxx<mailto:Michael.J.Tefft@xxxxxxxxxx>> 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.
