Toad for Oracle Data Grid – What is the OCI Buffer Array Size option for in the View – Options – Oracle – General section?

After a SELECT query is executed, we must retrieve the rows from the Oracle server to your PC. We do not retrieve the rows all at once, nor do we retrieve them one at a time (unless there is a LONG or LOB column involved). We retrieve the rows in blocks. The number of rows retrieved in each block is the number of rows you specify with "OCI Array Buffer Size".

TOAD defaults to 25 because SQL*Plus defaults to 25. In my opinion, an optimal setting is more like 500 or 1000. Here is why: If your dataset has 1,000 rows, and your OCI Array Buffer size is set to 25, then TOAD has to make 40 (that's 1000 / 25) round trips across the network to retrieve all of the rows. So you can immediately see why 500 or 1000 is better.

The only disadvantage to a higher setting of OCI Array Buffer Size is that TOAD must allocate memory to hold that many rows prior to each fetch. If that many rows are actually fetched, there is no loss. On the other hand, if not that many rows are retrieved, then we allocated some memory that is not going to be released until the cursor is freed. Luckily, this is a trivial amout of memory, in the grand scheme of things.