Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
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.
Nice !
Nice
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).
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.
Can’t able to find the scott user in version 12.2.0.1.0 of the oracle 12c release 2
You need to install the examples. If you just want to SCOTT only, go to asktom.oracle.com, click on Resources and search for “SCOTT” in the presentations area. I’ve uploaded a script to create that schema and the sample tables