Oracle seems to be very slow reporting large execution plans through explain plan. Is there any way to speed this up ?

The Oracle (tm) Users' Co-Operative FAQ

How to speed up reporting of large execution plans based on PLAN_TABLE?

Oracle supplies script to define PLAN_TABLE used by EXPLAIN PLAN command. The script usually resides in
$ORACLE_HOME/rdbms/admin/utlxplan.sql file in Unix or
#:\ORANT\RDBMSnn\ADMIN|utlxplan.sql file under MS Windows (# - is a letter specifying an assigned drive, nn specifies the Oracle version e.g. 81).

The data is inserted into PLAN_TABLE by issuing
EXPLAIN PLAN SET STATEMENT_ID = 'User_defined_id_string' FOR
User written SQL statement;

In order to retrieve the data another standard statement (or some derivation of it) is used:

   SELECT LPAD (' ', 2 * (level - 1)) || operation operation, options,
          object_owner || '.' || object_name object, DECODE (id, 0,'Cost = ' || position) pos
   FROM PLAN_TABLE
   START WITH id = 0 AND statement_id = 'User_defined_id_string'
   CONNECT BY PRIOR id = parent_id AND statement_id = 'User_defined_id_string';
复制

However quite often the execution of such statement requires large amount of time and computer resources, especially when explained statement is a complicated one (generates many rows in PLAN_TABLE) or when all application developers use single PLAN_TABLE (and nobody cares enough to delete an old data from it). The reason for such a behavior is simple enough, when we look at EXPLAIN PLAN of our SELECT statement:

SELECT STATEMENT OPTIMIZER=CHOOSE
   CONNECT BY
     TABLE ACCESS (FULL) OF PLAN_TABLE
     TABLE ACCESS (BY USER ROWID) OF PLAN_TABLE
     TABLE ACCESS (FULL) OF PLAN_TABLE
复制

As we see the explain shows that:
1. Full table scan is performed (to find a row with id = 0 and given statement_id)
2. For each child row another full table scan is executed (to get a row with the same statement_id and with a parent_id of current row).
In order to eliminate FTS and to enable the best possible access for each retrieved row I suggest defining 2 indexes:

CREATE INDEX PLAN_TABLE$STMTID_ID ON PLAN_TABLE (STATEMENT_ID, ID);
CREATE INDEX PLAN_TABLE$STMTID_PID ON PLAN_TABLE (STATEMENT_ID, PARENT_ID);
复制

(Of course you have to use TABLESPACE and STORAGE parameters as well)

Now the explain plan looks much better:

SELECT STATEMENT OPTIMIZER=CHOOSE
  CONNECT BY
    INDEX (RANGE SCAN) OF PLAN_TABLE$STMTID_ID
    TABLE ACCESS (BY USER ROWID) OF PLAN_TABLE
    TABLE ACCESS (BY INDEX ROWID) OF PLAN_TABLE
      INDEX (RANGE SCAN) OF PLAN_TABLE$STMTID_PID
复制

Selecting execution plan of 12 rows out of PLAN_TABLE with 7000 rows took (Oracle 8.1.6.0/Windows NT/P-III-350):
Without indexes: 0.501 sec
With both indexes defined: 0.010 sec - 50-fold improvement.
It's possible to use 1 index only instead of 2 (I advise to define it on STATEMENT_ID and PARENT_ID columns) to get significant performance improvements compared with situation where no index exists.

The same solution of defining an indexes on columns used in CONNECT BY PRIOR clause may be used to improve the performance of all hierarchical queries.

Further reading: N/A