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 18.104.22.168.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 22.214.171.124.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.