RE: Increased PGA requirements for SORTs in 19c?
Date: Fri, 20 Nov 2020 15:37:34 +0000
Message-ID: <8d6efca7f4d240118c3e3d4ed23e7b34_at_snapon.com>
I have used
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Friday, November 20, 2020 7:42 AM
To: 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.
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
On Fri, 20 Nov 2020 at 12:29, Sayan Malakshinov <xt.and.r_at_gmail.com<mailto:xt.and.r_at_gmail.com>> 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,
alter system set events 'sql_trace[sql: 42v4z1dh3255x] wait=true,bind=true';
Jonathan Lewis
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$>
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 20 2020 - 16:37:34 CET