Recently downloaded the trial version of TfO 12.10.0.30. Up to then I was using an older version (9.6 Commercial), which works rather good, though I hate the debugger for not showing variables in outer scopes or being able to evaluate expressions or accessing PL/SQL tables and other collections like “dbms_sql.varchar2_table” and the objects stored therein. Why can’t it evaluate something like “data_map_table(5).EXISTS(‘key’)” ? The debugger in 12.10.0.30 may be better than that in 9.6, that’s for sure, but with a memory footprint of 2.5 GB (see below) ? Anyway, the company had me evaluate the newest TfO version, which is 12.10.0.30.
So, here are the problems I had running TOAD 12.10.0.30 on Windows 7 Professional on x64, with Oracle 11.2 client against 11.2 server; saw no difference when running it on 11.2 client against 12c server)
Minor glitch: Open editor window and enter “CREATE OR REPLACE\nPROCEDURE MY_PROC AS\nBEGIN\n null;\nEND;\n/” (important: “\n” stands for line break!) Will not compile in TfO (PLS-00103: Found symbol “end-of-file” expecting one of …"); if you remove the line break between REPLACE and PROCEDURE, it compiles fine. This is not SQLPlus compatible behaviour, since SQLPlus would also compile fine WITH the line break. But as I said, a minor issue.
But this is not good: Working on a >300 KB package, i.e. loading the package source and enter the edit, compile, test-cycle, Toad.exe will soon have a Virtual Memory Size of 2.4 GB, sometimes, it was even 2.6 GB. If I had less than 16 gigs in my pc, it would have already gone under, but so I can work for about 15 minutes, but then Toad.exe will eventually “hang” after hitting F9 to compile the >300 KB package body after an edit. I have to “kill” the process, which consumes 0 % CPU due to Process Explorer, Task Manager will report Toad.exe as “not responding”, but the process is consuming absolutely no CPU time whatsoever., it seems to be in some kind of “wait state”.
The PL/SQL debugger will not show the values of NVARCHAR2-variables correctly (at least it doesn’t for me, just a blank text is displayed as value). I can see by debugging outputs that there IS a non-empty content in the variable, this is dangerous since you might think the variables have wrong content and look for non-existant errors in the code.
The second and third issues are significant and prevent my company from buying a new TOAD version. We could take the giant memory footprint, but this “hanging” think made us loose work on some occasions, not much, since you can access the safety copies of the editors in the User Files folder to reconstruct the changes which were lost by killing the process, but it’s ANNOYING. You must save edits each time before you hit F9 or F5, but sometimes you are too fast hitting F9 and then it’s too late to save.
Has anyone encountered similar problems or is there something which can I could do to get a more stable (and not so hungry) version ? Of course, I do not think, a new version would take only 380 KB memory like Toad 9.6, but 2.500 KB ist way too much,
Let me specifically address the issue with the PL/SQL Debugger. This is not a Toad for Oracle issue but rather a limitation of the Oracle DBMS_DEBUG API that Toad uses to perform debugging. This API was created before the addition of the NVARCHAR2 data type and has not been updated to provide support. Unfortunately, this is currently the only API we can use for debugging into Oracle. When debugging in Oracle, Toad has to ask the back end database for all of the debugging information (unlike a an IDE like Eclipse or Visual Studio where debug information is local). This creates severe limitations in what we can and can not show (this can be seen if you select Database -> Spool SQL while debugging). As an alternative, we have added an “auto debugger” feature (right click -> Debug -> Apply Auto Debugger) which will add DBMS_DEBUG.put_line statements in the code (easily removed with right click -> Debug -> Remove Auto Debugger) to see values. It’s probably not the answer you want to hear but it is a limitation that we have no control over since the back end API is owned and developed by Oracle.
Minor glitch: Open editor window and enter "CREATE OR REPLACE\nPROCEDURE...
In Options on the Execute/Compile page disable the "Treat blank line as statement terminator" option. This option has a couple of minor bugs including this one.
But this is not good: Working on a >300 KB package, i.e. loading the package source and enter the edit, compile, test-cycle...
What does your edit/test-cycle consist of? Also, are you connecting to huge schema like APPS on Oracle E-Business Suite and/or using Code Insight? If so try disabling the "Cache Code Insight results" option on the Editor|Code Assist page in Options. Do things improve? I created a 300KB procedure that has nothing more than a ton of select statements within. Editing, compiling, and executing it hung around 290 MB for me. I just have a single connection open with a single Editor and one tab for the procedure.
As far as the edit/test-cycle is concerned - I also have only one connection open, to a schema consisting of about 2500 objects (tables, views, packages, procedures... the whole bunch), so the schema IS rather big (about 1.5 GB);
I used only one editor with three tabs, package header, body and one for the test case. I use Code Insight frequently with cached results (will now try and switch it off). I use an instance of Toad 9.6 running in parallel on my 2nd monitor to check results.
Switching off the Code Insight cache helped a little, but not for long. After some test cycles with my 500 KB package the “Virtual Size” (VS, measured with Sysinternals “Process Explorer”) of Toad.exe sky-rockets to 2.3 GB, though I have only one connection and one editor with three tabs. With Toad 9.6 I worked on the same project, and the VS of this EXE never got over 450 MB. But 12.10 has far better Code Insight support, a more stable debugger and more improvements, so I really would prefer the new version.
I watched the VS of Toad 12.10 develop thru my work session, and it went like this:
Starting Toad 12.10, with no DB connection open, the VS is about 500 MB
After opening the connection, editor and loading the package source, the VS was about 700 MB, after doing some tests it slowly but surely climbed to 800 and little later to 1 GB.
With time the VS steadily increases until it reaches 2+ GB, even 2.5 GB. Then, it eventually would freeze. But I have to admit, that there were two Oracle instances (11.2 and 12c) running on my computer (16 GB RAM, pagefile is now 17 GB!), and a SQL Server 2012 instance, all three together consuming about 35 GB Memory (!). This really was too much, so I shut down the Oracle 12c and the SQL Server instance, which I do not need for now (these alone occupied 30 GB VS !), and the freezing of Toad got significally better, at least it felt this way.
After I rewrote the package and outsourced functional units into their own packages, I ended up with 4 packages (sizes 300, 130, 15 and 10 MB). At the moment, I see a VS of 1.6 GB, which is much, but not yet too much, I hope.
I feel that Code Insight is responsible for big parts of the VS, I tried and switched off all features I do not need. Especially global synonyms seem to play a role, but I’d like to have parameter support for SYS-packages like DBMS_LOB, UTL_FILE and some more (not many, there are 4 or 5, the rest just consumes memory). It would be fine, if you could specify the synonyms to load not in a global manner (all or nothing, though you can check “Do not load synonyms starting with “/””), but I’d like to configure Code Insight in this way: “Support only global synonyms starting with “DBMS_””, i.e. give a pattern for the global synonyms to support I think this could be an improvement in memory.
So it’s still a special situation, Toad 12.10 has features I like very much (to name a few. refactoring, better code assistance, support of conditional pl/sql compilation, there may be many more), but for the moment it seems to take about 4 times the memory of Toad 9.6 working on the same project. I still have three weeks for evaluation, maybe someone can point out a way to keep memory consistently under 2 GB. I’ll try and switch off global synonyms for code insight, the SYS-Package support is not that important.
I just caught that you’re looking at virtual memory and not the physical memory usage. Earlier my mem usage report was Toad’s working set. When I enable virtual size column in process explorer I do see higher usage, but ~700MB for my 300KB test package. It fluctuates a little here and there, but nothing crazy. What you are seeing is quite a big jump. If you can narrow down steps to something in particular and zip and send me your User Files folder to michaelstaszewskiquestcom I will run through them enabling advanced memory manager that will report on leaks.
Thanks again for looking into ,my problem, Michael !
So what columns of process explorer's memory items shold one look at ? Working Set, Peak Working Set ? As I reported, I divided the big package into 1 main package (240 KB) and 3 support packages (130 KB, 7 KB and 15 KB). First things seemed to get better (actually, Toad seems to be more stable now, but this may be due to the fact, that I saved >30 GB of virtual memory by shutting down two of my three database instances). But now - after some heavy editing and debugging Process Explorer again shows 2.15 GB Virtual Size, 1.7 GB Working Set (WS) and 1.8 GB Peak Working Set (PWS). Of course, I now have 9 tabs open (4 * 2 for the headers an bodies of the four packages, 1 for the test cases), seemingly the refactoring had no impact on memory usage, but up to now, Toad has not frozen again,.
I'll try and find the moment, when memory increases from 700 or 800 MB VS to more than 2 GB. Then I'll send to you my zipped user folder, so you can modify the settings to activate the advanced memory manager.
I don’t know what you should be looking at in Process Explorer. I pretty much have it set to default columns and have only looked at physical memory usage. I think the big observation is that Toad hangs after some time. If Toad degrades to the point that it is unusable then there’s a problem. Normally you’d see an Out of Memory exception thrown when Toad has consumed all memory available to it.
Not being a Toad user it’s difficult for me to interpret what heavy editing and debugging means in every case and I don’t have time to use Toad as if I were a user for long periods of time. I think it would be best if you could list exactly what editing and debugging means for you. What features in Toad are you using? For example, editing to one may mean straight text modification without any use of additional features like Code Insight, Object Palette, Schema Browser, describe window, etc. To another user editing could be the use of all of those features to look at table columns, PL/SQL parameters, etc. From your earlier description it looks like you’re using the Probe API debugger where breakpoints are set, you step through code, evaluate watches, etc. To some debugging means DBMS Output statements and multiple runs of the code looking at output between each.
You said that Toad 9.6 did not have this problem so I assume that all is the same with your code and database in your comparison. However, if you are on a different database now then I’d suggest leaving Toad in its hanging state and fire up a second instance of Toad to look at the database and see if there is a wait/lock or if the database is taxed. I don’t know really what to suggest here because I’m not sure what you’d be looking for. Perhaps someone with DBA knowledge can offer some insight. Also when it’s hanging have you left it alone for a bit to see what happens? Does it finish, throw an exception, just sit there? You can add DEBUG=1 to the [SETTINGS] section of Toad.ini and run Toad in this manner. When you get a hang there is a folder named QP5 Trace in your %APPDATA%\Dell\Toad for Oracle\12.10 folder. You can zip and send that to me offline to michaelstaszewskiquestcom.
OK, "heavy" editing and debugging means: Working on four new packages. add new code, modify existing code, execute and debug methods from test cases for some hours. By the way, I used Toad 9.6 and 12.10 on exactly the same task like I described, with the same DB connection, so I think, a comparison makes sense. And Toad 9.6 never goes over 800 MB Working Set (WS)
The situation now is:
I start with some hundred MB WS (IIRC both the values for Virtual Size (VS) and WS are mostly rather close to each other, I don't know whether this has any meaningor whether this is quite natural. From the DEBUG info: "Page File Usage (Virtual Memory): 380 MB, Working Set Usage (RAM): 387 MB"
Then, with time, the WS and VS values increase slowly until WS reaches 1.9 GB (VS is then 2.1 or 2.2). I set DEBUG=1 in Toad.ini, the QP5 Trace directory produced is more than 3 Gig, the zipped size is about 800 MB, and there's also a file named "qsetrace.log" in the "Toad 12.10 trial""User Files" folder, but I think this was created when I executed a script with SQL*Plus. I used the following switches in Toad.ini. section [SETTINGS]:
Debug=1
LogActivities=1
ScriptDebug=1
VCPDebug=1
I think these were a little bit too many Debug switches [:D], so I reverted to Debug=1 alone for the next session and hope, it will generate less data. Then, after some 5 or 6 hours, Toad froze again, it's always the same scenario: I hit F9 to test compile a package or package body I changed, and Toad would never return from that. it just sits there doing nothing, with a WS of 1.9 GB, a VS of >2 GB, consuming no CPU or RAM any more.
You can see the development thru time by "grepping" the string "WS" from the trace..txt files in the "QP5 Trace" folder. You will see the Working Set increasing slowly but surely until it reaches 1.9 GB. I'm afraid, Toad has no opportunity to log anything in this situation, since it seems not to consume any CPU ressources
During this trace file QP5 consumed -99944 KB of memory
During this trace file the application consumed 96532 KB of memory
04:16:03 Closing trace file: and reopening a new trace file, due to last call to QP5 more than 10 seconds ago
The Trace*.summary.txt file is too big to open it in an editor, so it's very tedious to handle. The file created as the very last one in the Trace folder, that is, the one with the newest time stamp, ends like this:
During this trace file QP5 consumed -99944 KB of memory
During this trace file the application consumed 96532 KB of memory
04:16:03 Closing trace file: and reopening a new trace file, due to last call to QP5 more than 10 seconds ago
[END OF FILE]
You can see a WS of 1965152 KB here and that "QP5 consumed -99944 KB of memory", which is quite funny. But I cannot see a hint describing that there was an exceptional situation.
So I will try and create another trace directory with just "Debug=1", so we can handle it's size, since I can't e-mail a 800 MB archive. Or are there single files or groupy of files from "QP5 Trace" you can use to analyse the situation ?
I don’t know which files from the QP5 trace are most meaningful. I’ll shoot a message to that team so that we can get those files from you and have them look them over. QP5 is the parser and may not be the problem here, but it’s a good place to start looking since the Editor parses a LOT and most importantly they produce excellent trace data so we should be able to narrow focus.
Slowly but surely, the evaluation period leans towards it’s end (6 days left). Before I close this thread, a little update about my experiences with Toad 12.10. Whether I can come to a decision about “Upgrade of not to upgrade from Toad 9.6 to 12.10” in this time, I don’t know. Is it possible to prolong the period, since my employer wants a clear vote about the advantages fo Toad 12.10 about Toad 9.6; By the way, Toad 9.6 is of course 32 bit - it must be since it came 9-10 years ago - and requires an extra 32 bit client to be installed on a 64 bit system, but that was no problem)
As far as Toad 12.10 itself is concerned, after I divided my ORACLE package project into 4 packages, I did receive hangs, but not as fast as with the 500 KB package (now 290 + 130 + 70 + some smaller packages). After a longer session (5-6 hours), it’s now working ok, Process Explorer shows a Working Set (size of the memory pages, that are ) of 850,000 K, and a Virtual Size (reserved, i.e allocated(?), virtual memory) of 1,200,000 K, more than 4 million page faults and 820,000 K “Private Bytes” (a.k.a “Commit Size” = Size of the pages that currently are guranteed to be mapped onto physical RAM). These are numbers we can live with (roughly 2 - 2.5 times the memory requirements of Toad 9,6).
My employer let me know, he would only invest in an upgrade, when the new version is significantly better than the old version). Of course I found some, but I’m not sure they are “significant” enough for him, so I’d like to add some impressive items to the list. I may have forgotten some or simply had no opportunity to use or test them)
List of reasons to upgrade TOAD 9.6 to TOAD 12.10:
TOAD 12.10 is 64 bit (no 32 bit ORACLE client needed),
better code assist,
refactoring functionality was added (but has not yet the depth I wish for, I’m used to the Java refactoring methods provided by Eclipse, if TOAD had these (and they worked), I had no doubt about upgrading!)
the PL/SQL debugger seems to be more stable and supports more variable types for Watch/Inspect (IIRC, collection elements can be watched, e.g. “collection_ref(32)” , but still - of course - no nvarchar2 or other UNICODE types’ values can be seen, and that’s a major problem, since many columns in our database ARE nvarchar2 or nclob, you would have to use “normal” varchar2 variables for reading from the DB and use “to_char(nvarchar2_var)” to populate the variables, for expressions like “to_char(nvarchar2_var)” are also not corrctly evaluated (value column in watch window show nothing, neither NULL nor a value, and IIRC, a message like “expression cannot be evaluated” is displayed
many other features like macro support, Data Pump support, administration functions have been added (I use those not too often, and haven’t had the time to walk thru it, so I may miss something here)
If anyone’s has items that I could add here, or does not agree with some item in the list, you are invited to do so.
I’d really like to upgrade and need to make clear to my employer, that we should do so. I admit, I myself am not 100 % sure about it, since the “hangs” were too annoying, at least in the time the package was sized 500 K or so. I did a trace of a session whichn “hung” at the end, but the folder “QP5 Trace” folder is more than 800,000 K in size, so I can only mail parts of it to Michael (Staszewski). More over, Michael thinks, that DEBUG=1 in the TOAD.ini (which if responsible for creating and filling the “QP5 Trace”) may not be suffiicient to locate a nenory leak, but I do not know yet how to procedd in this matter. By the way: After all, what I never could observe, was memory going DOWN instead of steadily increasing. I hope I’m wriong and would really like to prolong the eval period to analyze the matter.
I forgot to add one issue: From time to time (cannot exactly say, when), I hit the “Debug” button and EITHER a “Toad Error” box immediately shows up telling meabout a "Access Violation at “…FFFFF8”. OR: the box to enter the debugging code shows up, I click on “load from file”, select an xml file I saved from a former debug session, and again the error message for an “Access Violation” appears. I just met it again, after working without problems for some days. Luckily, there’s a workaround, you can copy/paste the debugging script into the window,
Contact the sales department. I can't imagine them not providing an extension.
List of reasons to upgrade TOAD 9.6 to TOAD 12.10
Your target Oracle database version may also be relevant. Check out this graphic and note the Oracle version that was out when a Toad version was released. Older Toad's should generally be OK with newer databases, but new database features will not be implemented and there's no guarantee that all existing Toad windows will function properly with newer Oracle versions.
I fired an email off to the team capable of digesting that trace log, but they may have missed it. If you still have it zip and send me the most recent files and I can send that to them. That may be enough for them to ask for additional data or to see if there's nothing astray in the parser (QP5). michaelstaszewskiquestcom
