How to Save 7 Keystrokes with dbms_xplan

An almost unknown simplification in the usage of dbms_xplan is possible since Oracle 12.2. Sometimes, there are small details that makes our job (a little bit) easier.

In one of his presentations at the Kscope19 conference, Kim Berg Hansen mentioned an interesting detail about the syntax for table functions. The call of a table function in SQL was simplified in Oracle 12.2. Instead of “SELECT … FROM TABLE(<function_name>)“ we can now use the syntax “SELECT … FROM <function_name>“. I did not know that, and to be honest, I don’t like it so much because it is harder to see from the SQL statement that a PL/SQL function is called in the FROM clause.

I don’t use table functions very often, except for one particular case which I use almost every day: the functions provided by dbms_xplan to display execution plans. For example, the execution plan for the last executed statement in the current session can be displayed with the following call:

 
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, ‘iostats -bytes last’));
 

In Oracle 12.2 and later, a simplified syntax can be used instead, although I did not find it anywhere in the documentation. But it works and returns exactly the same result:

 
SELECT * FROM dbms_xplan.display_cursor(NULL, NULL, ‘iostats -bytes last’);
 

That’s nice and helps to save 7 keystrokes per call. But it’s definitely not the big feature I was waiting for since years. Usually, I work with SQL Developer on my MacBook, where I configured several code snippets for the common used dbms_xplan calls. Or I use an alias dc (for “display cursor”) that I defined in my local SQLcl login script:


alias dc=select * from table(dbms_xplan.display_cursor(null, null, ‘iostats -bytes last’));

 

 
But sometimes I have to use SQL*Plus or any other tools in environments of my customers. In these cases, I’m now able to display the execution plans with less keystrokes. Let’s see whether this reduces my time to solve performance problems in the future…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s