.
\n12c<\/i>R2 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.
\n<\/p>\n_EDITOR<\/h3>\n
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.<\/p>\n
The environment variables EDITOR and VISUAL are not set:<\/p>\n
\nSQL> host set | grep -E \"(^EDITOR|^VISUAL)\"\n \nSQL>\n<\/code><\/pre>\nbut the _EDITOR in sqlplus is set to ‘vi’:<\/p>\n
\nSQL> define\nDEFINE _DATE = \"11-NOV-16\" (CHAR)\nDEFINE _CONNECT_IDENTIFIER = \"CDB1\" (CHAR)\nDEFINE _USER = \"SYS\" (CHAR)\nDEFINE _PRIVILEGE = \"AS SYSDBA\" (CHAR)\nDEFINE _SQLPLUS_RELEASE = \"1202000100\" (CHAR)\nDEFINE _EDITOR = \"vi\" (CHAR)\nDEFINE _O_VERSION = \"Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production\" (CHAR)\nDEFINE _O_RELEASE = \"1202000100\" (CHAR)\nDEFINE 1 = \"sqlplus\" (CHAR)\nDEFINE _RC = \"1\" (CHAR)\n<\/code><\/pre>\nHere is the default. For sure, vi is better than ‘ed’. ‘ed’ was the line editor from the era of 2400 baud network.<\/p>\n
Command history<\/h3>\n
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.<\/p>\n
However, in 12cR2 a very basic history has been introduced to SQL*Plus.
\nYou have to enable it:<\/p>\n
\nSQL> show HISTORY\nhistory is OFF\nSQL> set HISTORY on\nSQL> show history\nhistory is ON and set to \"100\"\n<\/code><\/pre>\nso the default is 100 lines, but you can increase it:<\/p>\n
\nSQL> set HISTORY 999999\nSP2-0267: history option 999999 out of range (0 through 100000)\nSQL> set HISTORY 100000\n<\/code><\/pre>\nwhat can you do with it?<\/p>\n
\nSQL> help HISTORY\n \n HISTORY\n -------\n \n Stores, lists, executes, edits of the commands\n entered during the current SQL*Plus session.\n \n HIST[ORY] [N {RUN | EDIT | DEL[ETE]}] | [CLEAR]\n \n N is the entry number listed in the history list.\n Use this number to recall, edit or delete the command.\n \n Example:\n HIST 3 RUN - will run the 3rd entry from the list.\n \n HIST[ORY] without any option will list all entries in the list.\n<\/code><\/pre>\nHere are some examples:<\/p>\n
\nSQL> show history\nhistory is OFF\nSQL> set history on\nSQL> show history\nhistory is ON and set to \"100\"\nSQL> prompt 1\n1\nSQL> prompt 2\n2\nSQL> history\n 1 show history\n 2 prompt 1\n 3 prompt 2\n \nSQL> history list\n 1 show history\n 2 prompt 1\n 3 prompt 2\n \nSQL> history 2 run\n1\nSQL> history 2 edit\n \nSQL> history 2 delete\nSQL> history\n 1 show history\n 2 prompt 2\n 3 prompt 1\n \nSQL> history clear\nSQL> history\nSP2-1651: History list is empty.\n<\/code><\/pre>\nAs 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<\/p>\n
SET LOBPREF[ETCH], SET ROWPREF[ETCH], and SET STATEMENTC[ACHE].<\/h3>\n
Here are important performance improvements:<\/p>\n
\nSQL> show lobprefetch\nlobprefetch 0\nSQL> show rowprefetch\nrowprefetch 1\nSQL> show statementcache\nstatementcache is 0\n<\/code><\/pre>\nThose 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.<\/p>\n
I’ll probably blog about prefetch in a future blog, so for the moment, here is a quick demo of statement caching.<\/p>\n
By default, statement caching is off. I run 3 times the same query:<\/p>\n
\nselect current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%';\n \nCURRENT_TIMESTAMP NAME VALUE\n----------------------------------- ----------------------------------- ----------\n11-NOV-16 05.00.41.370333 PM +00:00 session cursor cache hits 15\n11-NOV-16 05.00.41.370333 PM +00:00 session cursor cache count 4\n11-NOV-16 05.00.41.370333 PM +00:00 parse time cpu 0\n11-NOV-16 05.00.41.370333 PM +00:00 parse time elapsed 0\n11-NOV-16 05.00.41.370333 PM +00:00 parse count (total) 6\n11-NOV-16 05.00.41.370333 PM +00:00 parse count (hard) 0\n11-NOV-16 05.00.41.370333 PM +00:00 parse count (failures) 0\n11-NOV-16 05.00.41.370333 PM +00:00 parse count (describe) 0\n \n8 rows selected.\n \nSQL> select current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%';\n \nCURRENT_TIMESTAMP NAME VALUE\n----------------------------------- ----------------------------------- ----------\n11-NOV-16 05.00.41.373429 PM +00:00 session cursor cache hits 22\n11-NOV-16 05.00.41.373429 PM +00:00 session cursor cache count 4\n11-NOV-16 05.00.41.373429 PM +00:00 parse time cpu 0\n11-NOV-16 05.00.41.373429 PM +00:00 parse time elapsed 0\n11-NOV-16 05.00.41.373429 PM +00:00 parse count (total) 7\n11-NOV-16 05.00.41.373429 PM +00:00 parse count (hard) 0\n11-NOV-16 05.00.41.373429 PM +00:00 parse count (failures) 0\n11-NOV-16 05.00.41.373429 PM +00:00 parse count (describe) 0\n \n8 rows selected.\n \nSQL> select current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%';\n \nCURRENT_TIMESTAMP NAME VALUE\n----------------------------------- ----------------------------------- ----------\n11-NOV-16 05.00.41.375993 PM +00:00 session cursor cache hits 29\n11-NOV-16 05.00.41.375993 PM +00:00 session cursor cache count 4\n11-NOV-16 05.00.41.375993 PM +00:00 parse time cpu 0\n11-NOV-16 05.00.41.375993 PM +00:00 parse time elapsed 0\n11-NOV-16 05.00.41.375993 PM +00:00 parse count (total) 8\n11-NOV-16 05.00.41.375993 PM +00:00 parse count (hard) 0\n11-NOV-16 05.00.41.375993 PM +00:00 parse count (failures) 0\n11-NOV-16 05.00.41.375993 PM +00:00 parse count (describe) 0\n \n8 rows selected.\n<\/code><\/pre>\nYou 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.<\/p>\n
Let’s set statement caching to one and run the query again 3 times:<\/p>\n
\nset statementcache 1\n \nSQL> select current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%';\n \nCURRENT_TIMESTAMP NAME VALUE\n----------------------------------- ----------------------------------- ----------\n11-NOV-16 05.00.41.378937 PM +00:00 session cursor cache hits 36\n11-NOV-16 05.00.41.378937 PM +00:00 session cursor cache count 4\n11-NOV-16 05.00.41.378937 PM +00:00 parse time cpu 0\n11-NOV-16 05.00.41.378937 PM +00:00 parse time elapsed 0\n11-NOV-16 05.00.41.378937 PM +00:00 parse count (total) 9\n11-NOV-16 05.00.41.378937 PM +00:00 parse count (hard) 0\n11-NOV-16 05.00.41.378937 PM +00:00 parse count (failures) 0\n11-NOV-16 05.00.41.378937 PM +00:00 parse count (describe) 0\n \n8 rows selected.\n \nSQL> select current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%';\n \nCURRENT_TIMESTAMP NAME VALUE\n----------------------------------- ----------------------------------- ----------\n11-NOV-16 05.00.41.381403 PM +00:00 session cursor cache hits 42\n11-NOV-16 05.00.41.381403 PM +00:00 session cursor cache count 4\n11-NOV-16 05.00.41.381403 PM +00:00 parse time cpu 0\n11-NOV-16 05.00.41.381403 PM +00:00 parse time elapsed 0\n11-NOV-16 05.00.41.381403 PM +00:00 parse count (total) 9\n11-NOV-16 05.00.41.381403 PM +00:00 parse count (hard) 0\n11-NOV-16 05.00.41.381403 PM +00:00 parse count (failures) 0\n11-NOV-16 05.00.41.381403 PM +00:00 parse count (describe) 0\n \n8 rows selected.\n \nSQL> select current_timestamp,name,value from v$mystat join v$statname using(statistic#) where name like 'parse %' or name like '%cursor cache%';\n \nCURRENT_TIMESTAMP NAME VALUE\n----------------------------------- ----------------------------------- ----------\n11-NOV-16 05.00.41.383844 PM +00:00 session cursor cache hits 48\n11-NOV-16 05.00.41.383844 PM +00:00 session cursor cache count 4\n11-NOV-16 05.00.41.383844 PM +00:00 parse time cpu 0\n11-NOV-16 05.00.41.383844 PM +00:00 parse time elapsed 0\n11-NOV-16 05.00.41.383844 PM +00:00 parse count (total) 9\n11-NOV-16 05.00.41.383844 PM +00:00 parse count (hard) 0\n11-NOV-16 05.00.41.383844 PM +00:00 parse count (failures) 0\n11-NOV-16 05.00.41.383844 PM +00:00 parse count (describe) 0\n \n8 rows selected.\n<\/code><\/pre>\nOne more parse call only. The cursor was cached at client side.<\/p>\n
How many statements can you cache?<\/p>\n
\nSQL> set statementcache 999999\nSP2-0267: statementcache option 999999 out of range (0 through 32767)\n<\/code><\/pre>\nfrom 1 to 32767. The value 0 disable statement caching.<\/p>\n
\nset statementcache 32767\n<\/code><\/pre>\nNot yet in 12.2 ?<\/h3>\n
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.<\/p>\n
sqlplus -F<\/h3>\n
Those performance settings can be set to default values with the ‘-F’ argument.
\nLet set which settings are different:<\/p>\n
\n[oracle@OPC122 ~]$ sqlplus -s \/ as sysdba <<< "store set a.txt replace"\nWrote file a.txt\n[oracle@OPC122 ~]$ sqlplus -s -F \/ as sysdba <<< "store set b.txt replace"\nWrote file b.txt\n[oracle@OPC122 ~]$ diff a.txt b.txt\n3c3\n set arraysize 100\n31c31\n set lobprefetch 16384\n46c46\n set rowprefetch 2\n59c59\n set statementcache 20\n<\/code><\/pre>\n