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.


  1. As with just about everything produced by 3rd rate Oracle devs, this doesn’t work worth a shit.

    • I can’t help you if you don’t give me more info.

      And please don’t insult our developers.

      You took the time to leave this comment, so I believe you actually want help.

      What happens when you try and what version of SQLDev are you on?

      This on 22.2.1 – I just reproduced what I demonstrated in 2018 with our software from 2022

Write A Comment