EXPLAIN PLAN

The Oracle and PL/SQL EXPLAIN PLAN statement parses a query and records the “plan” that Oracle devises to execute it.

The general format is:

EXPLAIN PLAN [SET STATEMENT_ID = 'text'] FOR sql-sentence;
EXPLAIN PLAN [SET STATEMENT_ID = 'text'] INTO [schema.]table@dblink FOR sql-sentence;

Example 1:

DELETE PLAN_TABLE;
EXPLAIN PLAN FOR SELECT * FROM INVOICE WHERE NUM = 5;

result:

SELECT SUBSTR (LPAD(' ', LEVEL-1) || OPERATION || ' (' || OPTIONS || ')',1,30 ) "OPS", OBJECT_NAME "OBJECT"
FROM PLAN_TABLE
START WITH ID = 0
CONNECT BY PRIOR ID=PARENT_ID;