Re: Increased PGA requirements for SORTs in 19c?

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_at_snapon.com> 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_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
>
> Jonathan Lewis
>
>
>
>
>
> On Fri, 20 Nov 2020 at 12:29, Sayan Malakshinov <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,
>
> 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