12c Release 2, set feedback enhancement in SQL PLus

There’s a nice little touch to the “set feedback” command in SQL PLus in 12.2. There is a new “only” clause in the SET FEEDBACK command, so you can run queries but the resulting rows are not shown in the display. They are still queried, fetched and “returned”, but just rendered to the screen. Here’s an example


$ sqlplus hr/hr

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 14 22:59:15 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat Mar 11 2017 01:59:20 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

So that’s normal behaviour. Let’s now use the new ONLY option.


SQL> set feedback only 
SQL> select * from regions;

4 rows selected.

So why would you want that ? Well, sometimes you just want to run the query so that you can use a subsequent DBMS_XPLAN.DISPLAY_CURSOR call to see the true execution plan. Or perhaps, you just to want to examine some options with regard to the fetch performance. For example, here’s a demo of fetching from a large table called EMP


SQL> show arraysize
arraysize 10

SQL> set timing on
SQL> select * from emp;

1753088 rows selected.

Elapsed: 00:00:26.27

So that took 26 seconds, with an arraysize of 10. Let’s see if we can do better than that – we’ll bump up the arraysize to 200


SQL> set arraysize 200
SQL> select * from emp;

1753088 rows selected.

Elapsed: 00:00:04.65

Wow, thats pretty cool. Six times faster just by tweaking the batch size of the fetch. Surely then we can just keep bumping it up.


SQL> set arraysize 5000
SQL> select * from emp;

1753088 rows selected.

Elapsed: 00:00:04.43

Apparently not. There is a “sweet spot” for arraysize, and diminishing returns on performance when you go higher and higher (at the cost of consuming memory and resources on your client machine to drag all those rows down at once). But this post isn’t about arraysize, it’s merely a conduit for the nice cool feature SET FEEDBACK ONLY which lets us now test out such things without having to see all the rows presented back.

4 thoughts on “12c Release 2, set feedback enhancement in SQL PLus

  1. Handy, but it has always been possible to do this with SET AUTOTRACE TRACEONLY (albeit with that you also get the explain plan plus statistics).

  2. But autotrace is not available for everyone

    SQL> set autotrace traceonly stat
    SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
    SP2-0611: Error enabling STATISTICS report
    SQL> select * from scott.emp;

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    ———- ———- ——— ———- ——— ———- ———- ———-
    7369 SMITH CLERK 7902 17-DEC-80 800 20
    7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    7566 JONES MANAGER 7839 02-APR-81 2975 20
    7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
    7782 CLARK MANAGER 7839 09-JUN-81 2450 10
    7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
    7839 KING PRESIDENT 17-NOV-81 5000 10
    7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
    7876 ADAMS CLERK 7788 12-JAN-83 1100 20
    7900 JAMES CLERK 7698 03-DEC-81 950 30
    7902 FORD ANALYST 7566 03-DEC-81 3000 20
    7934 MILLER CLERK 7782 23-JAN-82 1300 10

    14 rows selected.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s