2 Replies Latest reply on Jul 13, 2020 5:24 PM by Erik van Roon

    What is "Optimized/Unoptimized" in serveroutput?

    Erik van Roon

      Can anybody shed some light on what the effect is of the OPTIMIZED and UNOPTIMIZED options for SET SERVEROUTPUT (introduced in 18.2)???

       

      While looking at 20.2 I noticed something in the help for "set serveroutput" that I don't know and for which I can't find any other information.

       

      Until version 18.1.1 the help said:

      ERO@EVROCS>version
      Oracle SQLDeveloper Command-Line (SQLcl) version: 18.1.1.0
      ERO@EVROCS>help set serveroutput
      SET SERVEROUTPUT
        SET SERVEROUT[PUT] {ON|OFF}
                           [SIZE {n | UNLIMITED}]
                           [FOR[MAT]  {WRA[PPED] |
                           WOR[D_WRAPPED] |
                           TRU[NCATED]}]
      ERO@EVROCS>
      

       

      But from 18.2 upwards it says:

      (Notice OPTIMIZED | UNOPTIMIZED)

      ERO@EVROCS>version
      Oracle SQLDeveloper Command-Line (SQLcl) version: 18.2.0.0
      ERO@EVROCS>help set serveroutput
      SET SERVEROUTPUT
        SET SERVEROUT[PUT] { OPTIMIZED | UNOPTIMIZED | {ON|OFF}
                           [SIZE {n | UNLIMITED}]
                           [FOR[MAT]  {WRA[PPED] |
                           WOR[D_WRAPPED] |
                           TRU[NCATED]}]}
      ERO@EVROCS>
      

       

      I never heard of the optimized and unoptimized options and they are not known to sqlplus.

       

      The help does not explain what is does or how to use it.


      "Optimized" adds the "optimized" keyword to the current setting of serveroutput without changing the setting of the other options (SIZE/FORMAT)
      "Unoptimized" removes the "optimized" keyword from the current setting of serveroutput without changing the setting of the other options (SIZE/FORMAT)

      ERO@EVROCS>show serveroutput
      serveroutput ON SIZE UNLIMITED FORMAT WRAPPED
      
      ERO@EVROCS>set serveroutput optimized
      
      
      ERO@EVROCS>show serveroutput
      serveroutput ON SIZE UNLIMITED FORMAT WRAPPED OPTIMIZED
      ERO@EVROCS>set serveroutput unoptimized
      
      
      ERO@EVROCS>show serveroutput
      serveroutput ON SIZE UNLIMITED FORMAT WRAPPED
      
      ERO@EVROCS>
      

       

      Also, both optimized and unoptimized can be set while serveroutput is set to OFF, the setting will remain when setting serveroutput to ON

      ERO@EVROCS>show serveroutput
      serveroutput OFF
      ERO@EVROCS>set serveroutput optimized
      
      
      ERO@EVROCS>show serveroutput
      serveroutput OFF OPTIMIZED
      ERO@EVROCS>set serveroutput on
      
      
      ERO@EVROCS>show serveroutput
      serveroutput ON SIZE UNLIMITED FORMAT TRUNCATED OPTIMIZED
      ERO@EVROCS>
      

       

      When setting Optimized or Unoptimized the other options van not be used in the same statement, they can only be used with ON or OFF.

      Funny enough, when using Optimized or Unoptimized with one of the other options the errormessage claims that serveroutput must be set to ON or OFF
      Apparently the errormessage hasn't been updated for the new syntax.

       

      ERO@EVROCS>set serveroutput optimized size unlimited
      
      
      SP2-0265: serveroutput must be set ON or OFF
      ERO@EVROCS>
      

       

      The above leads me to the conclusion that the syntax in the help is not entirely correct

      I think it should be:

      ERO@EVROCS>help set serveroutput
      SET SERVEROUTPUT
        SET SERVEROUT[PUT] { OPTIMIZED | UNOPTIMIZED | { {ON|OFF}
                                                         [SIZE {n | UNLIMITED}]
                                                         [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]
                                                       }
                           }
      ERO@EVROCS>
      

       

      Also, since it appears to add an extra option to the existing options, it seems to me that it should have been an option like SIZE and FORMAT, and not something that replaces ON/OFF.
      As it is, if I want to set serveroutput on with size unlimited, format wrapped and optimized, it takes two SET statements, where it looks like it should be possible to do it in one.

      Still not a clue what optimized does, so this last remark might be nonsense....