When a SQL statement is executed, it's only fetching 500 rows. How can I increase this?
When a SELECT query is executed, Toad retrieves the rows from the Oracle server. Toad retrieves the rows in blocks. The number of rows retrieved in each block is the number of rows you specify with the OCI Array Buffer Size option. The default is 500.
To change this option - View | Toad Options | Oracle | General - you'll see it bottom right
You can set the buffer up to a value of 9999. The 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 some memory is allocated that will not be released until the cursor is freed. Overall, this amount of allocated memory is generally unnoticeable.