We have a build script for our schema with hundreds of call to scripts, which we run using "Execute as Script"
The build script is typically like this..
@@Script1.sql
@@Script2.sql
...
@@ScriptN.sql
Each of those scripts can themselves call other scripts / compile objects etc. Sometimes we have to run the build script multiple times if an error occurred during a run - we fix and re-run.
A recurring issue we see whilst running is TOAD erring with API Guard error....!Function 5308 (QP5_CHK GET_FIRST_MESSAGE) was called with a NULL handle, like this... TOAD_Error|389x176 and we always have to shut down TOAD as it becomes unstable, like starts giving syntax errors, where there is no syntax error. It occurs a lot.
OK, gotta ask this... are you running the latest version of Toad for Oracle? e.g. 14.x?
Reason for asking is that this was an occasional issue with some users on older combinations of Toad and/or Oracle. See for example the Support KB article here.
If you are running close to latest version, I would let Support know the details, or let the Dev team members respond in the forum here, whichever comes first...
Ah, regarding the link, you may have to log in to the Support site to see the page. However, it's basically an article that that was written more than a few years ago for a much earlier version of Toad than you have.
Hopefully we can get one of the Dev team to chime in here.
I don't know about that error (I think @avergison might know something), but if you can get 14.0, then you can get the 14.1 beta here. 14.1 should be much more stable than 14.0. All of those show-stopper type bugs that I'm aware of are fixed. You can scroll down on that page to see the list of fixed bugs.
I don't think I'm running out of memory, John, unless Toad has a heap usage limits. I have plenty of RAM and virtual memory available on my work machine. I will keep an eye on memory usage though, next time I run builds.
Unfortunately, due to company rules on their intellectual property, I can't send you the scripts.
It does seem to happen after running the build script several times (e.g. when correcting build errors and running again). Perhaps it's a memory leak in TOAD, but you probably use memory leak monitoring? I did wonder if it was the Navigator leaking, because that does populate with a large number of items and update.
It's sure okay that you can't send scripts, but instead can you tell something about the complexity of the set of scripts, for instance something as: at top the level we call 500 scripts, the call depth is 3 at most, we have super huge pl/sql packages in there, etc. We're sure not after content or data, we're after what exactly is happening in computing terms. The better idea we get the faster we can fix it.
In addition, about memory: the important figure here is the amount of memory the Toad process consumes during the script execution. Can I suggest to open Task Manager (or some equivalent tool you're familiar with), go to Details and find the Toad.exe row, and then keep an eye on one of the columns Memory or Commit Size, and tell us what the highest amount is, and the amount at time of crash? I assume you are running a 32 bits Toad?
The main build script invokes approximately 830-ish scripts.
The max call depth (scripts calling scripts calling scripts etc) is probably about 6-7 - although without spending an age treewalking all file dependencies, it's hard to know exactly.
The memory usage the last time this occurred (yesterday) was circa 250MB
Yes, I'm on 32 bit Toad (only because we have customers who use our apps using 32 bit Oracle Client, so we match the lowest common denominator).
One more thing, it doesn't always happen on occasional calls. It's intermittent, but if I run the BuildScript multiple times, it pretty much always happens.
There definitely seems to be a very serious memory leak issue when running lots of scripts. I started a new Toad session and whilst running the Build Script I watched Toad's memory foot print increase steadily during the build. In the end the memory usage went up by 60MB. That memory is never deallocated, even if no errors, after the build is finished, if I close down sessions, tabs, clear logs etc.
I suspect that might be related to this. Is your parser not freeing up memory resources?
Well it has been paid to do so. Just kidding. Well, we run a crazy set of automated tests which monitor memory usage as well, so important losses should not happen. But I did not say there could be scenarios which are not covered and which do cause memory loss.
I'll be talking with John about what exactly is being done in Toad context, to get a better picture.
@avergison Andre found a small leak on his side (in qp5.dll). I'm not sure where he is with that one, but he wasn't sure if this was really the one causing your problems.
I've been sidetracked by the code insight stuff. I'll take a look at this now and see if I can find anything.
Memory leaks or not, it would not be normal to get an out-of-memory condition when only 250 MB or so of memory is used. The virtual memory of a 32 bits process is 2 GB, so when and out-of-memory occurs then the Task Manager should show a memory working set or a commit size approaching that value, so let's say anything above 1500 MB.
Could I kindly ask you again to write down the value of commit size or working set of the Toad process whenever you would encounter that error again?
That would be of great help.
@avergison I have plenty of system memory, but I'm not just running TOAD - I have many apps open. I just don't expect to lose large chunks of memory never to be deallocated (unless I restart TOAD) every time I run our Build script. Especially when I have to run many builds (we have large enterprise schemas : over 100 schemas across many instances).
I just did a test of running just ONE build on one schema.
Before Build :
Memory (Active Private Working Set) : 109,567K
Commit size : 332,404K
After Build :
Memory (Active Private Working Set) : 238,984K
Commit size : 469,040K
Clearing everything, closing tabs, logs, dbms_output, schema, etc etc, no change to memory.
As a developer myself, I consider losing around 130-150MB of my working memory every time I do a build (which I might be doing many of), not an inconsiderate amount. This memory disappears into a black hole, even when I clear data / tabs / shut down the schema. It's also the instability aspect, like for example, seeing incorrect / non-sensical syntax errors when upgrading production systems. It's disconcerting, especially if my upgrade window is a small time slot.
Just kidding. Well, we run a crazy set of automated tests which monitor memory usage as well, so important losses should not happen. But I did not say there could be scenarios which are not covered and which do cause memory loss.