The event 10053, which is used to dump cost-based optimizer (CBO) computations may include a plan.
To capture an Optimizer trace (10053) for a SQL statement you need to issue an alter session command to switch on a 10053 trace for that entire session, and then issue the SQL statement you wanted to capture the trace for. Once the statement completed you need to exit the session to disable the trace. Then you can look into SER_DUMP_DEST directory for the trace file.
To the following and check the trace for execution details....
SQL> alter session set events '10053 trace name context forever, level 1';
.
.
SQL> select * from emp;
.
.
SQL> exit;
To trace specific SQLID , do this.....
atler session set events 'trace[rdbms.SQL_Optimizer.*][sql:interested-sqlid]';
To capture an Optimizer trace (10053) for a SQL statement you need to issue an alter session command to switch on a 10053 trace for that entire session, and then issue the SQL statement you wanted to capture the trace for. Once the statement completed you need to exit the session to disable the trace. Then you can look into SER_DUMP_DEST directory for the trace file.
To the following and check the trace for execution details....
SQL> alter session set events '10053 trace name context forever, level 1';
.
.
SQL> select * from emp;
.
.
SQL> exit;
To trace specific SQLID , do this.....
atler session set events 'trace[rdbms.SQL_Optimizer.*][sql:interested-sqlid]';
Reference Link: Check Maria Colgan blog post for more details...
https://blogs.oracle.com/optimizer/entry/how_do_i_capture_a
No comments:
Post a Comment