Oracle - Generate Explain plan and plan statistics for SQL statement

Option 1: Display sql statement execution plan and sql execution statistics also

set pagesize 0
set linesize 5000
spool plan_q1.log
set auto trace on
@sql_statement.sql
spool off

Explain plan could lie explore at
Kerry Osborne's Blog - http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/

Option 2: Use DBMS_XPLAN, get explain plan from awr, using SQL_ID.
set pagesize 0
set linesize 5000
spool plan_q1.log
select * from table(dbms_xplan.display_awr('sql_id'));
spool off

Options for SQL statement:

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic +predicate +cost'));
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'typical -cost -bytes'));
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic +note'));
Select plan_table_output From table(dbms_xplan.display_cursor(null,null,'TYPICAL');
SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));
for adaptive
select * from table(dbms_xplan.display_cursor(format=>+adaptive));
for plan directives which list different no. of estimated and actual rows.
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM  <table_name> WHERE  <column_name> = '<value>';
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST');

Ref.:
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf

Option 3: Enterprise Manager

Display explain plan for SQL statement with query order.

Option 4: SQLT / SQLTXPLAIN

SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
If you have installed SQLT / SQLTXPLAIN, then you can generate explain plan with query order


SET ECHO OFF FEED OFF HEA OFF LIN 300 NEWP NONE TRIMS ON;
SPO sqlt_s46250_511925124_1_0.txt;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('SQLTXADMIN.SQLT$_PLAN_STATISTICS_V', '<sqlreport_id>', 'ADVANCED ALLSTATS', 'source = ''GV$SQL_PLAN'' AND inst_id = 1 AND child_number = 0'));
SPO OFF;