Two SQL Developer Tricks: DBMS_XPLAN and Code Templates

thatjeffsmith SQL Developer 0 Comments

Tell Others About This Story:

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

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:

just remove the existing format and invoke the code template

Tell Others About This Story:

Leave a Reply

Your email address will not be published. Required fields are marked *