Re: 10053 Trace || dbms_sqlidiag

I can't say how Oracle will have coded for this, but presumably there are a couple of lines near the start of each hard parse that say something like: Is 10053 trace enabled by system for this SQL_ID if 10053 trace enabled by session for this SQL_ID and that overhead will be there whether you have enabled the 10053 trace or not

So the only effect of enabling 10053 trace at the system level for a specific statement is the workload of actually dumping the 10053 when the statement is hard parsed and you need only worry if that statement is hard parsed extremely frequently.

Regards
Jonathan Lewis

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

> 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