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