The SQLBLOCKMAX option controls the maximum number of records retrieved from an Oracle Database instance at one time. This option provides a means of fine-tuning the performance of data fetches.
An INTEGER that identifies the number of records you want fetched at one time. While you can set SQLBLOCKMAX to any INTEGER, no appreciable change in performance results in setting it over 100. The default is 10 records.
Only cursors opened after SQLBLOCKMAX is reset use the new block size.
When a program typically opens a cursor, reads one record, and closes the cursor, set SQLBLOCKMAX to 1. Otherwise, the SQL FETCH statement retrieves 10 records and discards 9 of them. The same is true for other routine fetches of less than 10 records.
When your program is fetching small records, you can increase SQLBLOCKMAX to reduce the number of blocks required for the fetch. Oracle OLAP fetches the data into a 64K buffer. The block size in bytes is the number of records multiplied by the size of the records. When the block size exceeds the 64K limit imposed by the buffer, Oracle OLAP automatically reduces the number of records fetched. See Example 5-98, "Defining a Cursor with SQLBLOCKMAX".
Example 5-98 Defining a Cursor with SQLBLOCKMAX
The following program fragment defines a cursor for fetching 50-byte records from a database. The new block size easily fits into Oracle OLAP's 64K buffer (50 bytes * 100 = 50k block size).
SQLBLOCKMAX = 100 SQL DECLARE CURSOR c1 FOR SELECT * FROM mydata SQL OPEN c1