10 Replies Latest reply: Feb 22, 2010 3:00 PM by user12009428 RSS

    set serveroutput on size question?

    user12009428
      Hi,
      Can anybody tell me what is the purpose of the parameter SIZE of SET SERVEROUTPUT sql*plus command?
      I have read documentation, but I am not sure about the meaning of this.
      SIZE sets the number of bytes of the output that can be buffered within the Oracle Database server

      Thanks.
        • 1. Re: set serveroutput on size question?
          Boneist
          It's a method of telling the system how much space is required for the buffer. Think of it like declaring a varchar2 - you have to specify how long the variable can be, so that the system can allocate the memory.
          • 2. Re: set serveroutput on size question?
            Hoek
            Docs also say:
            "Resources are not pre-allocated when SERVEROUTPUT is set. As there is no performance penalty, *use UNLIMITED unless you want to conserve physical memory*."
            I've never used (or needed to use) SIZE...
            • 3. Re: set serveroutput on size question?
              user12009428
              Thanks, but I must ask you in detail:

              1. Where is the physical location of the buffer (on my client or database server), because this sentence SIZE sets the number of bytes of the output that can be buffered within the Oracle Database server totaly confused me
              2. What is inside of this buffer?

              Please sorry,
              and thanks.
              • 4. Re: set serveroutput on size question?
                Boneist
                user12009428 wrote:
                1. Where is the physical location of the buffer (on my client or database server), because this sentence SIZE sets the number of bytes of the output that can be buffered within the Oracle Database server totaly confused me
                Does it matter? I suspect it'll be on the database server, given that the server won't necessarily have access to your client.
                2. What is inside of this buffer?
                It's a means of sending messages from PL/SQL to other programs - you'd use the dbms_output package (specficially put_line and (if you were doing it yourself, rather than a front end like SQL*Plus) occasionally get_line) to put and retrieve messages.

                Typically, it's a method for doing a quick debug of a program - that's all it's useful for, IMO!

                eg. to show you the difference between having a buffer vs not having a buffer in SQL*Plus:
                SQL> set serveroutput on
                SQL> begin
                  2    dbms_output.put_line('This is a test!');
                  3  end;
                  4  /
                This is a test!
                
                PL/SQL procedure successfully completed.
                
                SQL> set serveroutput off
                SQL> begin
                  2    dbms_output.put_line('This is a test!');
                  3  end;
                  4  /
                
                PL/SQL procedure successfully completed.
                
                SQL> 
                Edited by: Boneist on 18-Feb-2010 13:59

                Edited by: Boneist on 18-Feb-2010 14:01
                ETA2: As Hoek has pointed out, the memory may (will?) not be pre-allocated until it's needed. Therefore if you specify a size on your buffer, that'll put a limit on how much data you can put into the buffer.

                Since most programs will only read messages from the buffer after the procedure has finished running, this can cause problems if you've put lots of messages into the buffer - you'll run out of space.

                Hence Hoek's suggestion (which I second!) to not use the size option at all!
                • 5. Re: set serveroutput on size question?
                  389403
                  Just look for description of DBMS_OUTPUT package, Oracle every time comments all functions and procedures, except of internal.
                  It is from 10g:

                  procedure enable (buffer_size in integer default 20000);
                  pragma restrict_references(enable,WNDS,RNDS);
                  -- Enable calls to put, put_line, new_line, get_line and get_lines.
                  -- Calls to these procedures are noops if the package has
                  -- not been enabled. Set default amount of information to buffer.
                  -- Cleanup data buffered from any dead sessions. Multiple calls to
                  -- enable are allowed.
                  -- Input parameters:
                  -- buffer_size
                  -- Amount of information, in bytes, to buffer. Varchar2, number and
                  -- date items are stored in their internal representation. The
                  -- information is stored in the SGA. An error is raised if the
                  -- buffer size is exceeded. If there are multiple calls to enable,
                  -- then the buffer_size is generally the largest of the values
                  -- specified, and will always be >= than the smallest value
                  -- specified. Currently a more accurate determination is not
                  -- possible. The maximum size is 1,000,000, the minimum is 2000.
                  • 6. Re: set serveroutput on size question?
                    Hoek
                    1. Database.
                    2. Well, it's for controlling the 'serveroutput', for example dbms_output.put_line in your PL/SQL code:
                    SQL> set serveroutput off
                    SQL> exec dbms_output.put_line('Hello World!');
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> set serveroutput on
                    SQL> exec dbms_output.put_line('Hello World!');
                    Hello World!
                    
                    PL/SQL procedure successfully completed.
                    • 7. Re: set serveroutput on size question?
                      William Robertson
                      The DBMS_OUTPUT buffer is just a PL/SQL collection within the DBMS_OUTPUT package body. Therefore it will be held in your session's PGA memory, unless you are using the shared server option in which case IIRC PL/SQL variables go in the UGA.

                      DBMS_OUTPUT.PUT_LINE appends the text to the collection variable. DBMS_OUTPUT.GET_LINES fetches the collection contents and clears it down afterwards. SQL*Plus and other tools call DBMS_OUTPUT.GET_LINES after each statement completes (if SERVEROUTPUT is ON, or the equivalent in TOAD etc) and print out the results on the screen.
                      • 8. Re: set serveroutput on size question?
                        user12009428
                        Thanks to all,

                        If I have understand you clearly:
                        when we put (set serveroutput on) we are indirectly starting dbms_output.enable command,
                        which initialize memory/buffer (which then can grows up as we fill it with put_lines, put commands) in PGA of our session (in which part of PGA it is located?).
                        Tools like sqlplus,Toad ... automatically using DBMS_OUTPUT.GET_LINES (when serveroutput is on) method so the content of buffer is always showed to us, and cleared after each sql statement.

                        I have little played with DBMS_OUTPUT package.

                        1) I wish to find the limit of the dbms_output.put_line command. I have created the test1.sql script.

                        test1.sql

                        begin
                        dbms_output.enable(NULL);
                        end;
                        /
                        begin
                        dbms_output.put_line(rpad('*',32767,'*')); -- I have count 1724 of this line
                        dbms_output.put_line('*'); - the line 1725
                        end;
                        /

                        After I have executed it I get the message:

                        ERROR at line 201:
                        ORA-06550: line 1725, column 37:
                        PLS-00123: program too large (Diana nodes)

                        Then I eliminate line (dbms_output.put_line('*');) from my test2.sql script and I get the output:

                        PL/SQL procedure successfully completed.

                        I find that I can insert 1724 lines * 32KB=55168KB=53.875MB in one turn.

                        2) After that I wished to know can I totaly collapse my system with dbms_output.

                        I have started 5 sessions repeatly executing test2.sql script. My PGA become extra large and my system totaly crashed.
                        I get the message:

                        begin
                        ***
                        ERROR at line 1:
                        ORA-04030: out of process memory when trying to allocate 40984 bytes (koh-kghu
                        sessi,pl/sql vc2)

                        Thanks.
                        • 9. Re: set serveroutput on size question?
                          William Robertson
                          Good summary.
                          in which part of PGA it is located?
                          The part that holds your private PL/SQL variables.
                          we are indirectly starting dbms_output.enable command, which initialize memory/buffer which initialize memory/buffer
                          Yes in principle. However unless they've changed it a lot since Oracle 7 (when the supplied packages were not wrapped) the "buffer" is an associative array so there is no separate initialisation step, and dbms_output.enable just sets a Boolean variable in the package body.
                          • 10. Re: set serveroutput on size question?
                            user12009428
                            Thanks William!