Re: ARRAYSIZE??? --> Glogin.sql

From: NeilC <neilc-olops_at_nospambtinternet.com>
Date: Wed, 22 Dec 1999 15:15:30 +0000
Message-ID: <3860EB12.2A927076@nospambtinternet.com>

Hi, from Oracles metalink site ....

SQL*Plus: 'BUFFER OVERFLOW' Explained

Abstract:

In SQL*Plus, 'buffer overflow' is an error that occurs while running a SELECT statement. This bulletin explains this error message. A typical scenario might be like this:

SQL> SELECT * FROM dept;
buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA. How the Overflow Occurs:

The value of the MAXDATA (see Glossary) must be "at least" the value of the buffer. If the MAXDATA value is lower than the buffer size, buffer overflow results because the buffer at that point is not large enough to hold the requested arraysize of data. Hence, the system returns with the message of increasing the MAXDATA or decreasing the ARRAYSIZE (which in turn decreases the buffer size given the other parameters remain constant).

To set MAXDATA, use the SET MAXDATA command in SQL*Plus.

Calculating the Buffer Size:

If more than one column is selected, the formula (as detailed in the Glossary) needs to be applied to each of the columns, and then all values must be added up to determine the final buffer size. For numeric columns, the figure derived using the formula increases by 40%. This is derived by experimentation with one numeric column and one row of data and by noting the difference between the value actually returned by the formula and the value of MAXDATA at which the "buffer overflow" error message disappears.

There may be space wasted because of calls to the align() function to align pointers along word boundaries. This is dynamic and cannot be predetermined. As a result, there may be a discrepancy between the value at which the "buffer overflow" message actually disappears and the value returned by the formula.

Examples:

In the following two examples, the ARRAYSIZE is set to 20.

Example 1: Using the demo DEPT table

SQL> DESC dept;

   Name                            Null?    Type
   ------------------------------- -------- ----
   DEPTNO                          NOT NULL NUMBER(2)
   DNAME                                    CHAR(14)
   LOC                                      CHAR(13)

  Calculations:
  For deptno: n=20, d=10, h=6, t=4, and the value according to the formula is 477.
  For dname: n=20, d=14, h=5, t=4, and the value is 559.   For loc: n=20, d=13, h=3, t=4, and the value is 534.

Add these up: 477 + 559 + 534 = 1570

  SQL> SET MAXDATA 1569;
  SQL> SELECT * FROM dept;
  buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.
  SQL> SET MAXDATA 1570;
  SQL> SELECT * FROM dept;

DEPTNO DNAME LOC

-------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Example 2

SQL> DESC test;

   Name                            Null?    Type
   ------------------------------- -------- ----
   COL1                                     NUMBER(7)
   COL2                                     CHAR(10)
   COL3                                     DATE

  For COL1: n=20, d=10, h=4, t=4, and the value is 473.

For COL2: n=20, d=10, h=4, t=4, and the value is 473. For COL3: n=20, d= 9, h=4, t=4, and the value is 452.

Add these up: 473 + 473 + 452 = 1398

  SQL> SET MAXDATA 1399;
  SQL> SELECT * FROM test;
  buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.
  SQL> SET MAXDATA 1400;
  SQL> SELECT * FROM test;

COL1 COL2 COL3

---------- --------- 10 indra 24-JUN-92

If you are selecting columns from different tables by joining them on a Primary/Foreign key, use the tablename for the column of which it is the primary key.

Glossary:

BUFFER

      is the memory space in bytes that SQL*Plus presents to the kernel.
      This memory space is utilized by the kernel to send in the rows
      requested by the user through the SELECT statement.

  ARRAYSIZE
      is the number of rows that SQL*Plus will fetch from the
      database table at one time and put in the buffer.  Valid values
      are 1 to 5000.  A large value increases the efficiency of
      queries and subqueries that fetch many rows because a large number
      of rows are being filled into the buffer space.  This gain in
      efficiency is hardly realized for the arraysize of over 100.
      Please note that ARRAYSIZE has no effect on the SQL*Plus
operations
      other than increasing the efficiency of the query, and it is not
      related to the number of rows that the SQL SELECT actually brings
      back.

  MAXDATA
      sets the maximum total row width that the SQL*Plus can process.
      The default and the maximum values of MAXDATA varies from
      operating system to operating system.  It is 500 through 60000
      in VMS; it is 500 through 32767 for UNIX systems.  The total
      row width depends on the arraysize.

  BUFFER SIZE FORMULA
      The formula, for the size of the BUFFER (Memory Usage), when
reduced, 
      appears as follows:

      Memory usage = 7n + n*d + d + 2h + t + 111 in bytes

      where:
              n = ARRAYSIZE (number of rows)
              d = column width in the output of SQL*Plus (the display
length)
              h = column heading length
              t = table name length

      These computations are for 32-bit platforms only.

      'd', the display width, can best be determined by counting the
      number of dashes that makes the underline of the column headings.
      Note that this length has nothing to do with the column
specification
      given at the time of the creation of the table.  If "COLUMN ..
FORMAT"
      command is used to format a column, that length should be taken
into
      account.
   
      'h', the column heading length, is the number of characters in the
      column heading.  For ENAME, it is 5; for DEPTNO, it is 6.

      't' is the table name length.
       For DEPT, it is 4; for EMP, it is 3.

HTH NEILC Jaqueline Berghout wrote:

> 
> Thank you very much Reiner.
> 
> It works!!! (it is so simple.... but I couldn't find it out myself)
> 
> Do you knwo when I have to set a ARRAYSIZE into 500 or 5?
> I mean, why and when???
> 
> And what has MAXDATA to do with it. Is there an relation between ARRAYSIZE
> and MAXDATA?
> 
> Thank you
> Jaqueline

Received on Wed Dec 22 1999 - 09:15:30 CST