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.
Our workaround: We changed the definition of the table's VARCHAR2(4000) column
to VARCHAR2(100) and now performance (response time, PGA usage, TEMP usage) is
acceptable.
By the way, this is the article that got me thinking in this direction:
https://oracle-randolf.blogspot.com/2011/01/hash-aggregation.html
in particular, these observations:
Ø The fundamental defect is that it is not able to dynamically resize to a
larger workarea size when using automatic PGA management and therefore remains
more or less at its initial expected size based on the estimates at
optimization time.
Ø This effectively means that the efficiency of the hash aggregation operation
when using automatic PGA management is heavily dependant on the cardinality
estimates at optimization time - in case of estimates in the right ballpark,
the memory used at execution time will correspond to the actual requirements at
runtime, but in case of bad estimates, the operation potentially uses far less
memory than available and unnecessarily spills to disk.
My thinking was: perhaps Oracle changed/fixed the optimizer to be less
optimistic in estimating memory needs - using actual column sizes rather than
statistics, which caused the increased memory demands. (Note that such a change
would not necessarily be intentional...)
Thanks again. The suggestions here were helpful in diagnosing this issue and
gave me some new additions for my toolbox.
Mike Tefft
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Friday, November 20, 2020 11:19 AM
To: 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.
I haven't tested it for a long time but the following might work:
SQL> alter system set events 'sort_end[sql:42v4z1dh3255x]';
System altered.
SQL> alter system set events 'sort_run[sql:42v4z1dh3255x]';
System altered.
sort_end is the 10032
sort_run is the 10033
Regards
Jonathan Lewis
On Fri, 20 Nov 2020 at 15:37, Tefft, Michael J
<Michael.J.Tefft@xxxxxxxxxx<mailto:Michael.J.Tefft@xxxxxxxxxx>> wrote:
I have used
alter system set events 'sql_trace[sql: 42v4z1dh3255x] wait=true,bind=true';
to capture the 10046 trace from executions ‘in the wild’. I have also seen a
similar ability to capture 10053 trace:
alter system set events 'trace[sql_optimizer.*] [sql:42v4z1dh3255x]';
So… Is there a similar method to capture 10032 and 10033 traces for a specific
sql_id?
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On
Behalf Of Jonathan Lewis
Sent: Friday, November 20, 2020 7:42 AM
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx<mailto: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.
Add 10033 to that for reporting spills to disc, otherwise the 10032 will only
show you the final memory used during the last merge, and that could be very
deceptive.
Regards
Jonathan Lewis
On Fri, 20 Nov 2020 at 12:29, Sayan Malakshinov
<xt.and.r@xxxxxxxxx<mailto:xt.and.r@xxxxxxxxx>> wrote:
Hi Mike,
That's not enough info for deep detailed analysis. Could you trace your query
with enabled trace events 10046 and 10032 (sort statistics) and provide raw
trace files, please? Or at least RTSM reports. You can also analyze
v$sql_workarea (v$sql_workarea_active)
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE Associate
http://orasql.org<https://urldefense.com/v3/__http:/orasql.org__;!!Lf_9VycLqA!zwd7B5IqtJ_MSod_hzjPC27jGlS8p4TTs2chnyl3M0nqd7izdiTJyd8-sa8nRJ6XzYCLqg$>
