Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Tuning SQL with Explain Plan

Oracle Database Tips by Donald BurlesonJuly 24, 2015

Finally, after all of these years and releases (1990 Version 6), Oracle Corporation has written two SQL scripts used to extract and interpret information from the table PLAN_TABLE after using the EXPLAIN PLAN command. The two scripts are named utlxpls.sql and utlx-plp.sql. On UNIX platforms, the scripts are found in $ORACLE_HOME/rdbms/admin.


After all this time of struggling with LPAD, CONNECT BY, and START WITH, it is now much easier to use Explain Plan. However, you must still create the table plan_table.


SQL> @f:\v8i\rdbms\admin\utlxplan

Table created.


Next, you are ready to run Explain Plan on queries.



  2  SELECT *

  3  FROM   EMP

  4  WHERE  SAL = 5000;



Now you can use one of the SQL scripts written by Oracle to extract meaningful information from the table plan_table. The following example uses utlx-pls.sql. Oracle would use a full table scan to retrieve all employees who earn $5000 per month. On a large table with only a few employees earning $5000 per month, you would want to create an index on the SAL column of the EMP table, and use the index to improve performance.


SQL> @f:\v8i\rdbms\admin\utlxpls

Plan Table


|Operation          |Name|Rows|Bytes|Cost|Pstart|Pstop|


|SELECT STATEMENT   |    |    |     |    |      |     |

| TABLE ACCESS FULL |EMP |    |     |    |      |     |



The example in Exhibit 9 shows the result of an Explain Plan on a Top N query. Notice the "STOPKEY" in the OPERATION column of the table plan_table. Oracle's new script is robust and easy to read. Thanks Oracle!






  4          FROM    EMP

  5          ORDER   BY SAL DESC)


Enter value for top: 3





Plan Table


|Operation                  |Name|Rows|Bytes|Cost|Pstart|Pstop|


|SELECT STATEMENT           |    |    |     |    |      |     |

|  COUNT STOPKEY            |    |    |     |    |      |     |

|    VIEW                   |    |    |     |    |      |     |

|      SORT ORDER BY STOPKEY|    |    |     |    |      |     |

|        TABLE ACCESS FULL  | EMP|    |     |    |      |     |


8 rows selected.

Exhibit 9.Result of an Explain Plan on a Top N Query

The Old Way

Previous to Oracle8i, you had to write a query similar to the following. For someone new to the Oracle world, it was always a traumatic experience trying to determine the functionality of LPAD, LEVEL, CONNECT BY, START WITH, and recursion.


SQL> get old_explain

  1  COL        "Query Plan" FORMATA40

  2  SELECT     LPAD(' ',2*LEVEL-1) || OPERATION || ' ' ||

  3  OPTIONS    || ' ' || OBJECT_NAME || ' ' ||

  4  OPTIMIZER  "Query Plan", COST

  5  FROM       PLAN_TABLE

  6  START      WITH ID= 0


SQL> @old_explain


Query Plan                                 COST

----------------------------------------- -----

SELECT STATEMENT CHOOSE                       3


    VIEW                                      3

      SORT ORDER BY STOPKEY                   3


Exporting Selected Rows from a Table

Thanks to the new export parameter, query, you can now export particular rows from a table. The example in Exhibit 10 shows an export of all employees in department number 10 from the EMP table.


C:\>exp help=yes

Keyword     Description (Default)      Keyword      Description (Default)

----------- -------------------------- ------------ -------------------------

USERID      username/password          FULL         export entire file (N)

BUFFER      size of data buffer        OWNER        list of owner usernames

FILE        output files (EXPDAT.DMP)  TABLES       list of table names

COMPRESS    import into one extent (Y) RECORDLENGTH length of IO record

GRANTS      export grants (Y)          INCTYPE      incremental export type

INDEXES     export indexes (Y)         RECORD       track incr. export (Y)

ROWSexport  data rows (Y)              PARFILE      parameter filename

CONSTRAINTS export constraints (Y)     CONSISTENT   cross-table consistency

LOG         log file of screen output  STATISTICS   analyze objects (ESTIMATE)

DIRECT      direct path (N)            TRIGGERS     export triggers (Y)

FEEDBACK    display progress every x rows (0)

FILESIZE    maximum size of each dump file

QUERY       select clause used to export a subset of a table


The following keywords only apply to transportable tablespaces

TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

TABLESPACES list of tablespaces to transport


Export terminated successfully without warnings.


C:\>exp query='where deptno=10' file=emp10.dmp tables=emp



Export: Release—Production on Sun Jan 23 09:55:22 2000


(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release—Production

With the Partitioning and Java options

PL/SQL Release—Production

Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character



About to export specified tables via Conventional Path ...

. . exporting tableEMP3 rows exported

Export terminated successfully without warnings.

Exhibit 10.All Employees in Department Number 10 from the EMP Table

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.