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.

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

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569