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

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;

SQL> hist

1 show history

2 show user

3 desc dual

4 select * from dual;

SQL> hist 4 run

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

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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

Labels: 12.2 features, Database

Alfredo Krieg's DBA blog - Oracle ACE

Sunday, January 7, 2018

Oracle Database 12.2 New Features - SQL* Plus Enhancements

SQL*Plus History

SQL*Plus SET MARKUP CSV

SQL*Plus SET FEEDBACK ONLY

SQL*Plus Performance Settings

SET ROWPREFETCH {1 | n}

About Me

Links

Previous Posts

Archives