Toad World® Forums

Locks up on large data sets

Good Morning TOAD team,

  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

Wickliffe.Larry@dol.gov: 202-693-3485

l.wickliffe@computer.org: 703-508-4345

A couple of questions…

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.

Good Morning Gregory,

  The rows are about 50 number fields and a couple Varchar fields each. .  I'm running simple scripts (F5) :

select to_char(SYSTIMESTAMP,‘HH24:MI:SSxFF’) as “Start” from dual;

– code to tune goes here ;

select to_char(SYSTIMESTAMP,‘HH24:MI:SSxFF’) as “End” from dual;

Larry Wickliffe

Performance and Tuning Services Lead, Performance Management Branch

Wickliffe.Larry@dol.gov: 202-693-3485

l.wickliffe@computer.org: 703-508-4345

Additionally, the output tab runs out of lines at 22,458, 654 lines before the script ends. see attached.

Larry

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.

On 11/30/2015 02:05 PM, wickliffe.larry wrote:

RE: Locks up on large data sets

Reply by wickliffe.larry
Additionally, the output tab runs out of lines at 22,458, 654 lines before the script ends. see attached.

Larry

Attachments:

Rows in output run out.jpg

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.