Sunday, January 7, 2018

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