RE: Increased PGA requirements for SORTs in 19c?
Date: Thu, 3 Dec 2020 12:43:40 +0000
Message-ID: <38175_1606999438_5FC8DD8E_38175_2337_1_a302b459a1564e2e8b33f8d149f5bfc0_at_vontobel.com>
“But the optimizer seems to be using the maximum (declared) column size to determine its memory needs “ That sounds completely strange for Oracle optimizer. Here’s a model that proves that’s not the case (tested on 19c): https://nenadnoveljic.com/blog/suboptimal-sort-calculation/. Maybe you hit some special case? But it shouldn’t be the normal behavior. Otherwise, this could cause havoc in execution plans. Best regards,
Nenad
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Tefft, Michael J Sent: Donnerstag, 3. Dezember 2020 13:37 To: Jonathan Lewis <jlewisoracle_at_gmail.com> Cc: ORACLE-L <oracle-l_at_freelists.org> Subject: RE: Increased PGA requirements for SORTs in 19c?
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<mailto:Michael.J.Tefft_at_snapon.com>> Cc: ORACLE-L <oracle-l_at_freelists.org<mailto: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<mailto: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.
Please consider the environment before printing this e-mail. Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">p { font-family: Arial;font-size:9pt }</style>
</head>
<body>
<p>
<br>Important Notice</br>
<br />
This message is intended only for the individual named. It may contain confidential or privileged information. If you are not the named addressee you should in particular not disseminate, distribute, modify or copy this e-mail. Please notify the sender immediately by e-mail, if you have received this message by mistake and delete it from your system.<br />
Without prejudice to any contractual agreements between you and us which shall prevail in any case, we take it as your authorization to correspond with you by e-mail if you send us messages by e-mail. However, we reserve the right not to execute orders and instructions transmitted by e-mail at any time and without further explanation.<br />
E-mail transmission may not be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also processing of incoming e-mails cannot be guaranteed. All liability of Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively referred to as "Vontobel Group") for any damages resulting from e-mail use is excluded. You are advised that urgent and time sensitive messages should not be sent by e-mail and if verification is required please request a printed version.</br>
Please note that all e-mail communications to and from the Vontobel Group are subject to electronic storage and review by Vontobel Group. Unless stated to the contrary and without prejudice to any contractual agreements between you and Vontobel Group which shall prevail in any case, e-mail-communication is for informational purposes only and is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction.<br />
The legal basis for the processing of your personal data is the legitimate interest to develop a commercial relationship with you, as well as your consent to forward you commercial communications. You can exercise, at any time and under the terms established under current regulation, your rights. If you prefer not to receive any further communications, please contact your client relationship manager if you are a client of Vontobel Group or notify the sender.
Please note for an exact reference to the affected group entity the corporate e-mail signature.
For further information about data privacy at Vontobel Group please consult <a href="https://www.vontobel.com">www.vontobel.com</a>.<br />
</p>
</body>
</html>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 03 2020 - 13:43:40 CET