You identified poorly performing SQL statements that use bind variables. You want to examine the
execution plans for the most recently executed statements for further diagnosis. Which method
would you recommend to view the execution plans?
A.
SQL*Plus Autotrace
B.
the dbms.xplan package
C.
EXPLAIN PLAN for SQL statements
D.
the SQL trace utility to generate trace files and read the formatted output using the tkprof utility
The tip given by the question is “the most recently executed…”.
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xplan.htm
You can easily get the sql_id for a statement that it is still in the library cache and then investigate the plan.
SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE ‘%TOTO%’;
SQL_ID CHILD_NUMBER
———- —————————–
gwp663cqh5qbf 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR((‘gwp663cqh5qbf’,0));
The other answers seems to present a different approach and may not get the plan of the “most recently executed” sqls.
SQL*Plus might not be so useful because its created after a successful DML statement.
SQL trace utility will not show execution plans, just the statements.
Rectifying, SQL Trace Utility will show execution plans and the statements.
dbms_xplan
B