When “display all results in grid” is checked, the query runs in a bi-directional mode. We use this mode for grids, so you can scroll forwards and backwards. The disadvantage is that records have to be held in memory to allow for the bidirectional scrolling. When that option is unchecked, we run the query again, but in a uni-directional mode. This means that as soon as we scroll off of a record, we know that we will never need to return to it, so we can release that memory. So if your data won’t easily fit into memory, it’s always best to have this option unchecked.
it sounds like when you get to the 500 or 1500 row mark, that’s when we have to do a fetch (the number of rows per fetch depends on the width of your rows). Sometimes, especially in views or complex queries, you’ll see a pause like that between fetches while Oracle has to do some work to retrieve the next set of rows. 10 minutes is pretty long though - I don’t know if the delay is Oracle processing time, or if it’s a slow fetch of data across the network or what.
Exporting to text files is the fastest - in this format, there is the least amount of processing of the data as it is placed in the file. But it sounds like your problem is somewhere on the Oracle side. On the queries that export slowly, do you see the same lag as you scroll in the grid past the 500 or 1500 row mark?