Re: Increased PGA requirements for SORTs in 19c?

Michael,

Thanks for the comments, possibly the non-appearance of the "hash group by" relates to something in
https://jonathanlewis.wordpress.com/2018/07/14/quiz-night-33/

A thought I've had about multiple count(distinct) in a select query is that there may be cases where you could benefit by rewriting the query first as a union all of queries that each handle one of the count(distincts) - since each would then be subject to the and then aggregate across the union all.

Regards
Jonathan Lewis

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

> 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_at_gmail.com]
> *Sent:* Thursday, December 3, 2020 7:23 AM
> *To:* Tefft, Michael J <Michael.J.Tefft_at_snapon.com>
> *Cc:* ORACLE-L <oracle-l_at_freelists.org>
> *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_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