DBMS_XPLAN.DISPLAY_CURSOR

How to Use DBMS_XPLAN.DISPLAY_CURSOR to examine execution plans

As you all know we can get to know the execution plan of the optimizer in several different ways. In this post, we are going to examine, DBMS_XPLAN.DISPLAY_CURSOR which shows the actual/running execution plan of a SQL query.

DBMS_XPLAN is a package inside SYS schema. DISPLAY_CURSOR is a function inside the package.

Let us see how we can use the above to benefit us in fixing problematic and long running queries.

SELECT * FROM table(dbms_xplan.display_cursor()); 

The SQL Statement executed above will pick the SQL that has been last executed in the current session. It returns the SQL_ID, the child Number, the Plan Hash Value, the Execution Plan.
The * in the execution plan as indicated will have the predicate information below the execution plan.

Sample DBMS_XPLAN.DISPLAY_CURSOR output

The Rows column & the Time Column in the above plan are only the estimated Values for each operation as indicated in the left. To get the "Actual" values you need to add a hint to your SQL statement while you execute it.

The hint is called -  /* + GATHER_PLAN_STATISTICS */

The above hint will record the execution statistics and therefore we will get exact values and therefore we can easily identify the longest running step in the execution plan. Now after adding the hint and executing the SQL, you need to supply a parameter while the display_cursor function is invoked. The parameter is "format=>'ALLSTATS LAST' ", it is highlighted below.


SELECT * FROM table(dbms_xplan.display_cursor(sqlid=>'sd213csdfsf', format=> 'ALLSTATS LAST')); 

Execution Plan with Gather Hint and Format Options

You can now see "A-Rows and E-Rows" attributing to Actual and Expected rows. The exection plan also shows the Actual time taken per operation. You can see the memory being used actually under the 0mem and 1Mem columns. However the COST & BYTES column are missing. If you want the cost and bytes information, you may execute the query like below.

SELECT * FROM table(dbms_xplan.display_cursor(sqlid=>'sd213csdfsf', format=> 'ALLSTATS LAST + cost +bytes' )); 

If you are in a postion to add the above hint to the SQL query the only way you can be sure if the execution plan stats are close to actual stats is by executing the select on the intended tables with the where caluse predicates, the information can be obtained from the predicate information as listed in the dbms_xplan output.


Another imporant thing, we can get from dbms_xplan is the OUTLINE. It is actually a set of hints which can be used to re-produce the plan. When you have a really large execution plan, it will be difficult to sort out the JOIN orders or the operations done by the optimizer because figuring out the Identation is really difficult. With OUTLINE information you may also get other key things the optimizer does to the query before proceeding for the execution. ( these operations on the query are called query [optimizer] transformations) along with JOIN ORDERS.

You may get the outline by using the dbms_xplan in another neat way.


SELECT * FROM table(dbms_xplan.display_cursor(sqlid=>'sd213csdfsf', format=> 'ALLSTATS LAST + outline' )); 

Outline Data along with the execution plan and predicate information of a SQL query

LEADING hint mentioned in the outline proposes the JOIN ORDER. All of the methods used in the execution plan are HASH JOINS and the query transformation is the SWAP_JOIN_INPUTS.

If there are SQL statements which use BIND variables instead of Literals you may want to see the literal values used for the specific execution. And we can get that if we supply the right value for the format parameter.

SELECT * FROM table(dbms_xplan.display_cursor(sqlid=>'sd213csdfsf', format=> 'ALLSTATS LAST + PEEKED_BINDS + cost +bytes' )); 

You may get the outputs in the execution plan like below. In the example shown below, the first bind variable value is given as "TABLE" and the second bind variable value is given as "SSB"

Displaying Bind Variables with DBMS_XPLAN

I hope the discussion above with regards to XPLAN will be useful in getting the actual rows and actual times and getting a peek into the query transformations and finally troubleshooting whats happening with your query.

See you next time.

Regards
Sai Marlakunta