Working with DBMS_XPLAN to display the EXPLAIN PLAN of a query

The Oracle-provided PL/SQL package DBMS_XPLAN has extensive functionality to get explain plan information for a given query. There are many functions within the DBMS_XPLAN package. The DISPLAY function can be used to quickly get the execution plan for a query, and also to customize the information that is presented to meet your specific needs. The following is an example that invokes the basic display functionality:

The DBMS_XPLAN.DISPLAY procedure is very flexible in configuring how you would like to see output. If you wanted to see only the most basic execution plan output, using the foregoing query, you could configure the DBMS_XPLAN.DISPLAY function to get that output:

How It Works

The DBMS_XPLAN.DISPLAY function has a lot of built-in functionality to provide customized output based on your needs. The function provides four basic levels of output detail:

• BASIC
• TYPICAL (default)
• SERIAL
• ALL

If you simply want the default output format, there is no need to pass in any special format options:

SELECT * FROM table(dbms_xplan.display);

If you want to get all available output for a query, use the ALL level of detail format output option:

SELECT * FROM table(dbms_xplan.display(null,null,'ALL'));