Tuesday, July 11, 2017

SQLcl 17.2

New Versioning Scheme

Starting with this release the numbering scheme is changed.  All releases will now be the YEAR<period>Quarter<period>build numbers.

So the new SQLcl is 17.2.0.184.0917.  

Breaking that down. 
  • 17   - Year
  • 2     - Quarter
  • 0     -  Patch number
  • 184 - Day in Julian
  • 0917 - hour and minute the build was done.

New Features

Securing Literals  which was introduced here : http://krisrice.blogspot.com/2015/09/sqlcl-more-secure-now-with-rest.html so this is not new.  What is new is controls over when it's done.  It was set so that SQLcl did secure all literals for anything that was issued. Now there's a control for when/how deep to check.

The default is any anonymous block less than 10 lines will be scrubbed automatically.  This will catch the majority of uses.  To ratchet up what is checked  "set secureliterals ON" will secure every block completely.  There is a performance impact to this if there are very large block such as in the APEX installation which has some blocks over 1k in size.

The opposite is there also to disable this feature: set secureliterals OFF


Here's an example of what happens. The 'abcxyz' is removed and turned into a bind :SqlDevBind1ZInit1


SQL> declare
  2    l_local varchar2(20);
  3  begin
  4    l_local := 'abcxyz';
  5    dbms_output.put_line(l_local || chr(10));
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select sql_text from v$sql where sql_text like '%abcxyz%';
SQL_TEXT      
                                                                                                                                                                                                                                  
DECLARE 
SqlDevBind1Z_1 VARCHAR2(32767):=:SqlDevBind1ZInit1;  
BEGIN 
   declare   
       l_local varchar2(20); 
   begin   
       l_local := 'abcxyz';   
       dbms_output.put_line(l_local || chr(TO_NUMBER( SqlDevBind1Z_1))); 
   end;  
 :AUXSQLDBIND1:=SqlDevBind1Z_1;  
END;



New Performance


So I spent the better part of 2 week in the NetBeans profiler and the outcome is well worth the time.  ALL these numbers are on my laptop so milage will vary.  APEX is probably one of the largest / complicated set of sql / plsql scripts to install into a database so I used that as my baseline. The SQLcl version I started from took 19m27.352s to install APEX.  For comparison, I ran the same install with SQL*PLUS that took almost 10 full minutes less at 9m59.789s.  SOOOO clearly there was an issue here.

The key thing is knowing WHAT your application should be doing and how it should be doing it.  There were a number of things that SQLcl was being overly aggressive  about such as securing literals  which was introduced here http://krisrice.blogspot.com/2015/09/sqlcl-more-secure-now-with-rest.html Then there were calls that were repetitive and could simple be removed.  Then the next boost was from being more lax on dbms_output.  SQLcl was checking for things like DDL that clearly can't have output so no need to check.

The end result is that turned secure literals off and it now takes on my machine 7m17.635s




Posted by at