Is there a way to return more than 25 rows at a time (when pressing F9 or F5)? When I press either to run a query it only returns 25 records at a time when I press F9. When I press F5 (using rownum <= 1000), it gets records in increments of 25. Needless to say this is very time consuming when I want to look at more records. I don't want to use Ctrl+End to see all the records, I need to change the 25 record at a time default.
Thank you,
John
Fetch size has a big impact on performance, and Toad makes an attempt to optimize this. Toad measures the max row size before fetch. The bigger the row size, the smaller the number of rows retrieved per fetch. Smallish rows come in 500 per fetch, and number of rows per fetch decreases as row size increases. It's a balance of memory use and speed because before a fetch is performed, we have to allocate the maximum possible memory that these rows might use. If we have to allocate memory for a lot of very big rows, it's surprisingly easy to get an "out of memory" error because it has to be physical memory, not virtual memory. I guess it's probably not as big of a problem in 64 bit as it was in 32.
If you are getting rows 25 at a time, then either these are big rows (maybe a lot of varchar2(4000)'s, or you've changed the default. The setting is in Toad Options -> Oracle -> General. On the right, under "OCI Array Buffer Size". I really recommend leaving it on Automatic (so each SQL is tuned automatically) but if you want to override that, you can set it to manual and put in whatever value you want.
Thank you @JohnDorlon! That helps me to understand why it's doing it. There is a lot of CLOB data that's being retrieved.
John