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] On ;
Behalf Of Jonathan Lewis
Sent: Friday, November 20, 2020 7:42 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.
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$>
