Starting with Oracle 9.2 the typical method for generating explain plans were:
1. explain plan for <statement> 2. select * from table(dbms_xplan.display) Oracle 10g: 1. autotrace uses dbms_xplan 2. set autotrace on 3. select * from table(dbms_xplan.display_cursor()) Oracle 11g: Introduced DBMS_SQLTUNE.REPORT_SQL_MONITOR Inherent issues when using explain plan for – all bind variables are treated as VARCHAR2 and you may not see the actual (real) execution plan Oracle used. An alternative method is to query the view V$SQL_PLAN_STATISTICS or use DBMS_XPLAN.DISPLAY_CURSOR. Common Terminology Definitions Access Path – physical data storage access to read data from tables, indexes, external tables and database links. Only access paths can access physical data and return the row sources. Row Source – virtual stream of data via access paths or from child row sources Filter Predicate – row source property – rows can be discarded based on defined conditions. Row sources can be cascading, non-cascading or semi-cascading.Join – rows are filtered and merged based on rows that match from child row sources. Matching is determined by join predicates. Join operators can only operate on two inputs while other operations can combine the result of more than two row sources. Oracle Kernel functions are responsible for execution plan steps.
