|Author's name: Michael Bialik
Author's Email: firstname.lastname@example.org
|Date written: 12-Dec-2001
Oracle version(s): 7.3.4, 8.0.6, 8.1.6, 8.1.7
|Oracle seems to be very slow reporting large execution plans through explain plan. Is there any way to speed this up ?|
Back to index of questions
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
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 18.104.22.168/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
Back to top
Back to index of questions