Re: 10053 Trace || dbms_sqlidiag

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
>
>
> On Mon, 28 Mar 2022 at 07:41, Krishnaprasad Yadav <chrishna0007_at_gmail.com>
> wrote:
>
>> Hi Experts ,
>>
>> Please let us know what value we need for p_component to use for
>> generating 10053 when we use the dbms_sqldiag package.
>>
>> begin
>> dbms_sqldiag.dump_trace (
>> p_sql_id => '…' ,
>> p_child_number => … ,
>> p_component => 'Optimizer', -- or 'Compiler' ====?
>> p_file_id => 'CBO_TRACE'
>> );
>> end;
>> /
>>
>> During the issue time a query plan is changed and causes a huge pile in
>> the database , which P_component is preferred to get 10053 trace to get
>> cause of plan change
>>
>> Regards,
>> krishna
>>
>

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