Mark,
You're braver than me - that's exactly the sort of thing but I didn't want
to try typing it in without having a database in front of me to check
syntax, spelling, and punctuation.
Regards
Jonathan Lewis
On Sat, 5 Dec 2020 at 13:59, Mark W. Farnham <mwf@xxxxxxxx> wrote:
something like
select max(a),max(b)
from
(select count(distinct a),0 “b” from tab
union all
select 0 “a”, count(distinct b) from tab)
so each select can use the fastest method to get the distinct count of its
column and the union all only has the number of columns required as its
number of rows to unionize. Then each independent distinct would need only
whatever temporary memory its plan might require (possibly nearly nothing)
saving its single row result set also in nearly nothing for which the union
all to operate on. You might need to cast the literal to match the type
thrown by count.
direct from fingers, didn’t test syntax, just couldn’t work out your
missing word or words after “the” so checking if I understood your meaning.
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jonathan Lewis
*Sent:* Thursday, December 03, 2020 7:49 AM
*To:* Tefft, Michael J
*Cc:* ORACLE-L
*Subject:* 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@xxxxxxxxxx>
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@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>
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.
