Re: 10053 Trace || dbms_sqlidiag

Hello Krishna,
the overhead of checking for a (global or session based) event is neglectable but if you have 100+ sessions doing a hard parse at roughly the same time you generate a lot of traces.

However Oracle's "new" kernel debug, diagnostics & tracing infrastructure is very flexible and you can narrow down by filtering in a very granular fashion (see ORADEBUG DOC EVENT FILTER). Here is just a slightly more complex setup of such an event (of course you would need to replace sql_trace[] with trace[]): https://www.freelists.org/post/oracle-l/sql-trace-filtering,8

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Krishnaprasad Yadav <chrishna0007_at_gmail.com> hat am 28.03.2022 18:31 geschrieben:
>
>
> Hi Jonathan ,
>
> just thinking that 100+ session waiting for single query , so during that time if alter system is executed does there will be additional overhead for system
>
> Regards,
> krishna
>
>
> On Mon, 28 Mar 2022 at 14:46, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:
> >
> > >>> What you may need to do is use a system-wide setting to dump the 10053 whenever that particular SQL_ID is optimized.
> >
> > How often are you expecting to optimize (HARD parse) the statement ?

--
http://www.freelists.org/webpage/oracle-l