This post was originally published on this site
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’));
