Formatted Explain Plan

There are Some steps to get neat and clear explain plan .

In 10.2 and above, you can pull execution plans from the library cache if the SQL has already been executed (in addition to the standard explain plan option from earlier releases).

Last Executed SQL

To get the plan of the last executed SQL issue the following:

SQL> set linesize 150

SQL> set pagesize 2000

SQL> select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));

SQL_ID and child number of the SQL are known

In this case use dbms_xplan.display_cursor as follows:

SQL> set linesize 150

SQL> set pagesize 2000

SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));

gather_plan_statistics hint

The gather_plan_statistics hint with some additional options may also provide run time statistics:e.g:

SQL> select /*+ gather_plan_statistics */ col1, col2 etc.....

SQL> set linesize 150

SQL> set pagesize 2000

SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD,'ALL IOSTATS LAST'))

Plan from Memory

Bselect * from table(dbms_xplan.display_cursor('&sql_id'));

select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));

For SQL ID, Child Cursor :

select * from table(dbms_xplan.display_cursor('&sql_id', &child_number, 'ALL'));

For SQL Text :

select t.*

from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'ALL')) t

where s.sql_text like '%&querystring%';

for more sql plan detail please visit. How to Obtain a Formatted Explain Plan - Recommended Methods (Doc ID 235530.1)