This was first published on https://blog.dbi-services.com/12cr2-has-new-sqlplus-features (2016-11-11)
Republishing here for new followers. The content is related to the the versions available at the publication date
12cR2 is there. What’s new in SQL*Plus? For sure, you can’t expect lot of things from it. The new command line is the SQL*Developer sqlcl which aims to be 100% compatible with SQL*Plus with lot of more features. However, a few little things came here: default editor, command line history and easy row/LOB prefetch and statement caching.
Yes, it seems that the default editor is ‘vi’ instead of ‘ed’, finally. This is a great improvement. Of course, you can set the VISUAL environment variable in your system. But when you come to another environment (which consultants do), this default will save lot of “define _editor=vi” keystroke.
The environment variables EDITOR and VISUAL are not set:
SQL> host set | grep -E "(^EDITOR|^VISUAL)" SQL>but the _EDITOR in sqlplus is set to ‘vi':
SQL> define DEFINE _DATE = "11-NOV-16" (CHAR) DEFINE _CONNECT_IDENTIFIER = "CDB1" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR) DEFINE _EDITOR = "vi" (CHAR) DEFINE _O_VERSION = "Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production" (CHAR) DEFINE _O_RELEASE = "1202000100" (CHAR) DEFINE 1 = "sqlplus" (CHAR) DEFINE _RC = "1" (CHAR)
Here is the default. For sure, vi is better than ‘ed’. ‘ed’ was the line editor from the era of 2400 baud network.
Yes. In 2016 SQL*Plus has a command line history. Do you need it? Probably not. If you are on Windows, you can navigate with arrow-up and arrow-down in any command line program. If you are on Linux, you have probably installed rlwrap. And finally, if you want to do something friendly on command line, you probably use sqlcl.
However, in 12cR2 a very basic history has been introduced to SQL*Plus. You have to enable it:
SQL> show HISTORY history is OFF SQL> set HISTORY on SQL> show history history is ON and set to "100"so the default is 100 lines, but you can increase it:
SQL> set HISTORY 999999 SP2-0267: history option 999999 out of range (0 through 100000) SQL> set HISTORY 100000what can you do with it?
SQL> help HISTORY HISTORY ------- Stores, lists, executes, edits of the commands entered during the current SQL*Plus session. HIST[ORY] [N {RUN | EDIT | DEL[ETE]}] | [CLEAR] N is the entry number listed in the history list. Use this number to recall, edit or delete the command. Example: HIST 3 RUN - will run the 3rd entry from the list. HIST[ORY] without any option will list all entries in the list.Here are some examples:
SQL> show history history is OFF SQL> set history on SQL> show history history is ON and set to "100" SQL> prompt 1 1 SQL> prompt 2 2 SQL> history 1 show history 2 prompt 1 3 prompt 2 SQL> history list 1 show history 2 prompt 1 3 prompt 2 SQL> history 2 run 1 SQL> history 2 edit SQL> history 2 delete SQL> history 1 show history 2 prompt 2 3 prompt 1 SQL> history clear SQL> history SP2-1651: History list is empty.
As you see, it’s not the most user friendly. But for the basic DBA task that you do on a server you may find it safer than up-arrow. Imagine that a ‘shutdown immediate’ is in the history. Do you want to take the risk to run it because of some network latency and you run the line above the one you wanted? Or do you prefer to be sure tho have read the command befor
Here are important performance improvements:
SQL> show lobprefetch lobprefetch 0 SQL> show rowprefetch rowprefetch 1 SQL> show statementcache statementcache is 0Those are things that you can to on OCI or JDBC and that you can easily do now in SQL*Plus: prefetch rows and LOBs to avoid fetch roundtrips and use statement caching to avoid parse calls.
I’ll probably blog about prefetch in a future blog, so for the moment, here is a quick demo of statement caching.
By default, statement caching is off. I run 3 times the same query:
select current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%'; CURRENT_TIMESTAMP NAME VALUE ----------------------------------- ----------------------------------- ---------- 11-NOV-16 05.00.41.370333 PM +00:00 session cursor cache hits 15 11-NOV-16 05.00.41.370333 PM +00:00 session cursor cache count 4 11-NOV-16 05.00.41.370333 PM +00:00 parse time cpu 0 11-NOV-16 05.00.41.370333 PM +00:00 parse time elapsed 0 11-NOV-16 05.00.41.370333 PM +00:00 parse count (total) 6 11-NOV-16 05.00.41.370333 PM +00:00 parse count (hard) 0 11-NOV-16 05.00.41.370333 PM +00:00 parse count (failures) 0 11-NOV-16 05.00.41.370333 PM +00:00 parse count (describe) 0 8 rows selected. SQL> select current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%'; CURRENT_TIMESTAMP NAME VALUE ----------------------------------- ----------------------------------- ---------- 11-NOV-16 05.00.41.373429 PM +00:00 session cursor cache hits 22 11-NOV-16 05.00.41.373429 PM +00:00 session cursor cache count 4 11-NOV-16 05.00.41.373429 PM +00:00 parse time cpu 0 11-NOV-16 05.00.41.373429 PM +00:00 parse time elapsed 0 11-NOV-16 05.00.41.373429 PM +00:00 parse count (total) 7 11-NOV-16 05.00.41.373429 PM +00:00 parse count (hard) 0 11-NOV-16 05.00.41.373429 PM +00:00 parse count (failures) 0 11-NOV-16 05.00.41.373429 PM +00:00 parse count (describe) 0 8 rows selected. SQL> select current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%'; CURRENT_TIMESTAMP NAME VALUE ----------------------------------- ----------------------------------- ---------- 11-NOV-16 05.00.41.375993 PM +00:00 session cursor cache hits 29 11-NOV-16 05.00.41.375993 PM +00:00 session cursor cache count 4 11-NOV-16 05.00.41.375993 PM +00:00 parse time cpu 0 11-NOV-16 05.00.41.375993 PM +00:00 parse time elapsed 0 11-NOV-16 05.00.41.375993 PM +00:00 parse count (total) 8 11-NOV-16 05.00.41.375993 PM +00:00 parse count (hard) 0 11-NOV-16 05.00.41.375993 PM +00:00 parse count (failures) 0 11-NOV-16 05.00.41.375993 PM +00:00 parse count (describe) 0 8 rows selected.You can see that each one had its parse call. Of course, it’s not a hard parse because cursor is shared. It’s not even a soft parse thanks to session cursor cache. But it’s still a parse call.
Let’s set statement caching to one and run the query again 3 times:
set statementcache 1 SQL> select current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%'; CURRENT_TIMESTAMP NAME VALUE ----------------------------------- ----------------------------------- ---------- 11-NOV-16 05.00.41.378937 PM +00:00 session cursor cache hits 36 11-NOV-16 05.00.41.378937 PM +00:00 session cursor cache count 4 11-NOV-16 05.00.41.378937 PM +00:00 parse time cpu 0 11-NOV-16 05.00.41.378937 PM +00:00 parse time elapsed 0 11-NOV-16 05.00.41.378937 PM +00:00 parse count (total) 9 11-NOV-16 05.00.41.378937 PM +00:00 parse count (hard) 0 11-NOV-16 05.00.41.378937 PM +00:00 parse count (failures) 0 11-NOV-16 05.00.41.378937 PM +00:00 parse count (describe) 0 8 rows selected. SQL> select current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%'; CURRENT_TIMESTAMP NAME VALUE ----------------------------------- ----------------------------------- ---------- 11-NOV-16 05.00.41.381403 PM +00:00 session cursor cache hits 42 11-NOV-16 05.00.41.381403 PM +00:00 session cursor cache count 4 11-NOV-16 05.00.41.381403 PM +00:00 parse time cpu 0 11-NOV-16 05.00.41.381403 PM +00:00 parse time elapsed 0 11-NOV-16 05.00.41.381403 PM +00:00 parse count (total) 9 11-NOV-16 05.00.41.381403 PM +00:00 parse count (hard) 0 11-NOV-16 05.00.41.381403 PM +00:00 parse count (failures) 0 11-NOV-16 05.00.41.381403 PM +00:00 parse count (describe) 0 8 rows selected. SQL> select current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%'; CURRENT_TIMESTAMP NAME VALUE ----------------------------------- ----------------------------------- ---------- 11-NOV-16 05.00.41.383844 PM +00:00 session cursor cache hits 48 11-NOV-16 05.00.41.383844 PM +00:00 session cursor cache count 4 11-NOV-16 05.00.41.383844 PM +00:00 parse time cpu 0 11-NOV-16 05.00.41.383844 PM +00:00 parse time elapsed 0 11-NOV-16 05.00.41.383844 PM +00:00 parse count (total) 9 11-NOV-16 05.00.41.383844 PM +00:00 parse count (hard) 0 11-NOV-16 05.00.41.383844 PM +00:00 parse count (failures) 0 11-NOV-16 05.00.41.383844 PM +00:00 parse count (describe) 0 8 rows selected.One more parse call only. The cursor was cached at client side.
How many statements can you cache?
SQL> set statementcache 999999 SP2-0267: statementcache option 999999 out of range (0 through 32767)from 1 to 32767. The value 0 disable statement caching.
set statementcache 32767
If you did not upgrade yet to 12.2 you have a way to use statement caching. You can set it in oraaccess.xml which can enable those optimizations for all OCI clients.
Those performance settings can be set to default values with the ‘-F’ argument. Let set which settings are different:
[oracle@OPC122 ~]$ sqlplus -s / as sysdba <<< "store set a.txt replace" Wrote file a.txt [oracle@OPC122 ~]$ sqlplus -s -F / as sysdba <<< "store set b.txt replace" Wrote file b.txt [oracle@OPC122 ~]$ diff a.txt b.txt 3c3 set arraysize 100 31c31 set lobprefetch 16384 46c46 set rowprefetch 2 59c59 set statementcache 20Those settings avoid roundtrips and unnecessary work. Documentation says that PAGESIZE set to higher value but I don’t see it here and anyway, it’s about formatting output and not about performance.
You may use SQL*Plus to test queries with bind variables. Here is what you do before 12.2:
SQL> variable text char SQL> exec :text:='X' PL/SQL procedure successfully completed. SQL> select * from DUAL where DUMMY=:text; D - XYou can now simply:
SQL> variable text char='X' SQL> select * from DUAL where DUMMY=:text; D - X
Since 11g SQLDeveloper is shipped in ORACLE_HOME and in 12.2 it includes SQLcl, the SQLDeveloper command line that is fully compatible with SQL*Plus scripts. The version we have on the DBCS lacks the executable flag and the right JAVA_HOME:
[oracle@SE222 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/sqldeveloper/sqlcl/bin/sql / as sysdba -bash: /u01/app/oracle/product/12.2.0/dbhome_1/sqldeveloper/sqlcl/bin/sql: Permission denied [oracle@SE222 ~]$ [oracle@SE222 ~]$ bash /u01/app/oracle/product/12.2.0/dbhome_1/sqldeveloper/sqlcl/bin/sql / as sysdba SQLcl: Release 12.2.0.1.0 RC on Fri Nov 11 21:16:48 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. USER = URL = jdbc:oracle:oci8:@ Error Message = No suitable driver found for jdbc:oracle:oci8:@ USER = URL = jdbc:oracle:thin:@127.0.0.1:1521:CDB2 Error Message = No suitable driver found for jdbc:oracle:thin:@127.0.0.1:1521:CDB2 USER = URL = jdbc:oracle:thin:@localhost:1521/orcl Error Message = No suitable driver found for jdbc:oracle:thin:@localhost:1521/orcl Username? (RETRYING) ('/ as sysdba'?)
I’ve defined the following alias:
alias sql='JAVA_HOME=$ORACLE_HOME/jdk bash $ORACLE_HOME/sqldeveloper/sqlcl/bin/sql'and I’m ready to run it:
[oracle@SE222 ~]$ sql / as sysdba SQLcl: Release 12.2.0.1.0 RC on Fri Nov 11 21:20:15 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production SQL>
I like SQLcl except one thing – it’s in java and is long to start:
[oracle@SE222 ~]$ time sql /nolog real 0m2.184s user 0m3.054s sys 0m0.149s2 seconds is long when you run it frequently. Compare with sqlplus:
[oracle@SE222 ~]$ time sqlplus /nolog real 0m0.015s user 0m0.008s sys 0m0.006s