X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

Ever Evolving SQL*Plus 12.2.0.1 Adds New Performance Features

Christopher Jones
Senior Principal Product Manager

This is a guest post by Luan Nim, Senior Development Manager at Oracle.

SQL*Plus 12.2.0.1 has introduced a number of features to improve the performance and ease of use in general. These features can be enabled with SET commands, or via the command line.

New Oracle SQL*Plus 12.2.0.1 features include:

  • SET MARKUP CSV

    This option lets you generate output in CSV format. It also lets you choose the delimiter character to use and enable quotes ON or OFF around data. The benefit of using CSV format is that it is fast. This option improves the performance for querying large amount of data where formatted output is not needed.

    Syntax:

    SET MARKUP CSV ON [DELIMI[TER] character] [QUOTE {ON|OFF}]

    Example:

    SQL> set markup csv on
    SQL> select * from 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,"19-APR-87",3000,,20
    7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10
    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
    7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20
    7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10
    
    14 rows selected.

    This option is also available from command line with the "-m csv" argument.

    $ sqlplus –m “csv on” scott/tiger @emp.sql
    SQL*Plus: Release 12.2.0.2.0 Development on Wed Jul 5 23:12:14 2017
    
    Copyright (c) 1982, 2017, Oracle.  All rights reserved.
    
    Last Successful login time: Wed Jul 05 2017 23:11:46 -07:00 
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.2.0 - 64bit
    Development
    
    "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,"19-APR-87",3000,,20
    7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10
    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
    7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20
    7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10
    
    14 rows selected.
  • SET FEEDBACK ONLY

    The new ONLY option to SET FEEDBACK is to display the number of rows selected without displaying data. This is useful for users who are interested in measuring the time taken to fetch data from the database, without actually displaying that data.

    Example:

    SQL> set feedback only
    SQL> select * from emp;
    
    14 rows selected.
  • SET STATEMENTCACHE

    This option is to cache executed statements in the current session. The benefit of this setting is that it reduces unnecessary parsing time for the same query. Therefore it improves performance when repeatedly executing a query in a session.

    Example:

    SQL> set statementcache 20
    SQL> select * from emp;
    SQL> select * from emp;
  • SET LOBPREFETCH

    This option is to improve access of smaller LOBs where LOB data is prefetched and cached. The benefit of this setting is to reduce the number of network round trips to the server, allowing LOB data to be fetched in one round trip when LOB data is within the LOBPREFETCH size defined.

    Example:

    SQL> set lobprefetch 2000
    SQL> select * from lob_tab;
  • SET ROWPREFETCH

    This option is to minimize server round trips in a query. The data is prefetched in a result set rows when executing a query. The number of rows to prefetch can be set using this SET ROWPREFETCH

    This option can reduce round trips by allowing Oracle to transfer query results on return from its internal OCI execute call, removing the need for the subsequent internal OCI fetch call to make another round trip to the DB.

    Example:

    SQL> set rowprefetch 20
    SQL> Select * from emp;

    If, for example, you expect only a single row returned, set ROWPREFETCH to 2, which allows Oracle to get the row efficiently and to confirm no other rows need fetching.

  • Command line –FAST option.

    This command line option improves performance in general. When this option is used, it changes the following SET options to new values:

    • ARRAYSZE 100

    • LOBPREFETCH 16384

    • PAGESIZE 50000

    • ROWPREFETCH 2

    • STATEMENTCACHE 20

    Once logged in, these setting can also be changed manually.

    Syntax:

    $ sqlplus –f @emp.sql

I hope the new features described above are helpful to you. For more information, please refer to the SQL*Plus Users Guide and Reference.

If you have questions about SQL, PL/SQL or SQL*Plus, post them in the appropriate OTN space.

Join the discussion

Comments ( 1 )
  • Saurabh Pandey Thursday, March 21, 2019
    good
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.