My TOAD locks up and will not close without task manager assistance after selecting over 150,000 rows of data in the editor window. The lock up is independent of recent TOAD version 12.7, 12.8 and 12.9 all do this. My workstation is near total physical memory space, 16 GB; 24,453 MB of virtual memory.
Is this just our machine configuration of are others experiencing this bug, too?
Larry Wickliffe
Performance and Tuning Services Lead, Performance Management Branch
Are you doing the selection using F5 (run as a script) or F9?
How big are the rows of data? If using F5, Toad has to allocate memory for the total amount of potential data per row. For example, 150k rows of number data can be quite small whereas 150k rows where each row has 100 VARCHAR2(4000) columns can be enormous.
Honestly, you’re going to run out memory trying to run a script that cranks out that many lines of results. If you need that result in the script output like that, you’re better off using the SPOOL command to spool it directly to a file. Also, you may want to set the LINESIZE larger since it looks like your lines may be wrapping (therefore increasing the number of lines). You can also disable the grid output to free up some memory.
Unlike SQLPlus, Toad script execution retains the entire content of the script output - SQLPlus only retains the last X lines of execution. Because of this, when retrieving a very large dataset like this, you can run out of memory which can cause portions of the output to not be available. Your best bet is to use the SPOOL command which will output it to an external file which you can then view.
For a single select try using F9. There are some known issues (AVs, not hanging though) with large script output. Perhaps your hang is related? It looks like you have one real select and two timing selects. I'd use F9 for single select along with an accurate timing method as outlined in your other thread if you're tuning. It'll use less memory and execute faster as well with F9 compared to execute script with the grids and output enabled.
