Re: 10053 Trace || dbms_sqlidiag

>>> 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 ?

On Mon, 28 Mar 2022 at 09:35, Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Hi Jonathan,
>
> Thanks for responding !!
>
> I got your blog
> https://jonathanlewis.wordpress.com/2014/05/23/10053-trace/ which
> provided moredetails assosciated with it .
>
> However setting events at the system level can be expensive ? This is
> needs to be instantly on and off .
>
> Regards,
> krishna
>
>
>
> On Mon, 28 Mar 2022 at 13:08, Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>> I think what you're looking for isn't what you really need. The
>> dump_trace() command is something you use to force Oracle into
>> re-optimizing a query after the event and that won't guarantee that you'll
>> get the same result as the runtime problem. What you may need to do is use
>> a system-wide setting to dump the 10053 whenever that particular SQL_ID is
>> optimized. Something like this:
>>
>> alter system set events 'trace
>> [rdbms.SQL_Optimizer.*][SQL:ck5smk1nw5u0g]';
>>
>> Obviously you need to use the relevant SQL_ID.
>>
>> When you've got what you want you can disable the trace by adding an
>> "off":
>>
>> alter system set events 'trace [rdbms.SQL_Optimizer.*][SQL:ck5smk1nw5u0g]
>> off';
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>

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