We recently (18.2) added a new way to generate execution plans in SQL Developer – we generate a call to DBMS_XPLAN for the SQL ID at your cursor position.

Put your cursor on the query, click on the drop-down arrow on the Explain Plan button.

DBMS_XPLAN is a package, and one of it’s procedures is ‘DISPLAY_CURSOR’:

This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).

The 3rd parameter tells the package just how to display the plan, or what information you want included.

There’s about a million (made up number) combinations available here.

We default to


But maybe you don’t want that format. Let’s say that you have 3 or 4 formats you use frequently.

Program them into your SQL Templates!

So when you want to call DBMS_XPLAN, you already have your most frequently used formats available. I’ve put mine in using:


All I have to do is type xpl and hit ctrl+spacebar, and presto-change-O!

Note that I’ve included the quotes, as you can’t invoke the code template inside a quoted string.

Don’t remember how to use Code Templates? — A MUST KNOW TRICK!!! —

Here’s a quick demo:

just remove the existing format and invoke the code template

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment