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
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: