By Franck Pachot
.
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.
_EDITOR
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.
Command history
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 100000
what 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
SET LOBPREF[ETCH], SET ROWPREF[ETCH], and SET STATEMENTC[ACHE].
Here are important performance improvements:
SQL> show lobprefetch
lobprefetch 0
SQL> show rowprefetch
rowprefetch 1
SQL> show statementcache
statementcache is 0
Those 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
Not yet in 12.2 ?
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.
sqlplus -F
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 20
Those 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.
VARIABLE
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
-
X
You can now simply:
SQL> variable text char='X'
SQL> select * from DUAL where DUMMY=:text;
D
-
X
SQLcl the SQLDeveloper command line
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.149s
2 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
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)