Oracle Database 12.2 New Features – SQL* Plus Enhancements

I want to start a series of posts about Oracle Database 12.2 new features this new year 2018.
There’s no better start of this series than to start with SQL*Plus.
SQL*Plus is probably one of the most utilized tools by DBA’s (sqlcl is gaining steam) and here I show some really cool new features.

SQL*Plus History

With this command HIST[ORY] and if turned on; you can run, edit, delete or list previously used SQL or PL/SQL commands for the current session.
  
 $ sqlplus sys as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 5 12:28:58 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> show history
history is OFF
SQL> set history on
SQL> show history
history is ON and set to “100”
SQL> set history 1000
SQL> show history
history is ON and set to “1000”
SQL> show user
USER is “SYS”
SQL> desc dual
 Name                                                      Null?    Type
 —————————————– ——– —————————-
 DUMMY                                                                 VARCHAR2(1)
SQL> select * from dual;
D
X
SQL> hist
  1  show history
  2  show user
  3  desc dual
  4  select * from dual;
SQL> hist 4 run
D
X
SQL>

SQL*Plus SET MARKUP CSV

This setting is going to present the output is CVS format. 
SQL> set markup csv on
SQL> select * from emp;
“EMPNO”,”ENAME”,”JOB”,”MGR”,”HIREDATE”,”SAL”,”COMM”,”DEPTNO”
7839,”KING”,”PRESIDENT”,,”17-NOV-81″,5000,,10
7698,”BLAKE”,”MANAGER”,7839,”01-MAY-81″,2850,,30
7782,”CLARK”,”MANAGER”,7839,”09-JUN-81″,2450,,10
7566,”JONES”,”MANAGER”,7839,”02-APR-81″,2975,,20
7788,”SCOTT”,”ANALYST”,7566,”19-APR-87″,3000,,20
7902,”FORD”,”ANALYST”,7566,”03-DEC-81″,3000,,20
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
7654,”MARTIN”,”SALESMAN”,7698,”28-SEP-81″,1250,1400,30
7844,”TURNER”,”SALESMAN”,7698,”08-SEP-81″,1500,0,30
7876,”ADAMS”,”CLERK”,7788,”23-MAY-87″,1100,,20
7900,”JAMES”,”CLERK”,7698,”03-DEC-81″,950,,30
7934,”MILLER”,”CLERK”,7782,”23-JAN-82″,1300,,10
14 rows selected.

SQL*Plus SET FEEDBACK ONLY

This option will display the number of rows selected without displaying the data. Useful to measure fetch time.
SQL> set feedback only
SQL> set timing on
SQL> select * from emp;
14 rows selected.
Elapsed: 00:00:00.01

SQL*Plus Performance Settings


SET ROWPREFECTH (default 1 | 2G max)
This setting pre-fetches rows in a result set. It can reduce the number of round trips between OCI execute calls.
I had an interesting question from a colleague about this setting. What is the difference between this ROWPREFECTH and ARRAYSIZE?
I’m still struggling to find the differences. Here are both definitions extracted from Oracle’s documentation:
SET ARRAYSIZE
Sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000.
The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. In recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect. Overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance.

SET ROWPREFETCH {1 | n}

Sets the number of rows that SQL*Plus will prefetch from the database at one time.
The default value is 1.
What I can see from here is that ARRAYSIZE takes place during the fetch step and probably ROWPREFECTH is just before the fetch step?
I even tried to identify this by using the autotrace option and by tracing a test session, but unfortunately I was not able to find any differences. Maybe the amount of data queried wasn’t large enough.
$ sqlplus scott/****
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 5 13:18:41 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Fri Jan 05 2018 13:15:53 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> set autotrace on
SQL> select * from emp;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
————————————————————————–
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
————————————————————————–
Note
—–
   – dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1537  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed
SQL> set rowprefetch 10
SQL> select * from emp;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
————————————————————————–
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
————————————————————————–
Note
—–
   – dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1534  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed
SQL> set rowprefetch 5
SQL> select * from emp;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
————————————————————————–
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
————————————————————————–
Note
—–
   – dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1534  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed
SQL> set arraysize 5
SQL> select * from emp;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
————————————————————————–
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
————————————————————————–
Note
—–
   – dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1534  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed
SQL> set rowprefetch 1
SQL> select * from emp;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
————————————————————————–
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
————————————————————————–
Note
—–
   – dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
           10  consistent gets
            0  physical reads
            0  redo size
       1919  bytes sent via SQL*Net to client
          630  bytes received via SQL*Net from client
            4  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed
As you can see the only visible changes are the SQL*Net roundtrips to/from client of the ARRAYSIZE setting.
SET LOBPREFETCH (default 0 bytes | 2G max)
Similar to ROWPREFECTH but for LOB data.

SET STATEMENTCACHE (default 0 | 32767 max)
This setting will cache similar SQL statements for the current session reducing the amount of necessary parses.
Thanks,

Alfredo