Re: 10053 Trace || dbms_sqlidiag

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