Re: 10053 Trace || dbms_sqlidiag

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