Two SQL Developer Tricks: DBMS_XPLAN and Code Templates

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.

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.

We default to

format=>'ALLSTATS LAST'

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:

• XPLAN1 – ‘ALLSTATS LAST ALL +OUTLINE’
• XPLAN2 – ‘ALL +OUTLINE’
• XPLAN3 – ‘TYPICAL ALLSTATS LAST ALIAS’

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: