I have been developing some packages that run for extended periods of time. I usually run them as Oracle jobs and monitor progress with the application log. If I make a change to the package and try to recompile while the job is still running, TOAD hangs. The work around is to open another instance of TOAD and cancel the running job.
It would be nice if TOAD could check to see if an object is locked when attempting a recompile and return a warning rather than proceeding with the compilation. Another possibility is to cancel the compile if it doesn't complete within a configurable period of time. The longest compilation I have ever had is about 4 seconds, but then my packages usually consist of less than 2000 lines.
I am using TOAD for Oracle 14.1.120.923
Thanks
Well, to be fair...Toad isn't hanging. Toad is waiting for Oracle to finish what we asked it to do.
Oracle waits for the package to finish executing before it returns a result when we try to compile it. But I think you know all that.
I can investigate the possibility of checking for use before compiling. I think that should be an option if we add it. But you can always cancel manually if you are tired of waiting. If you don't have a popup window with a cancel button, then the cancel button is here:
Wow, quick response. I like.
I do know that TOAD is waiting on Oracle, which is why canceling the scheduled job works. However, when I hit the cancel button, TOAD turns gray and stops responding. Yesterday I exerted patience and waited 20 minutes to see if TOAD would respond. It did not until I cancelled the underlying job.
I just tried it again.
- I launched the scheduled job
- I pressed F9 in editor to recompile the package.
- Dialog popped with cancel option.
- I clicked cancel
- Briefly saw hour glass pointer, dialog box went away, cursor turned to a small bold spinning circle.
- I clicked on the editor and now it is once again hung.
- Opened second copy of TOAD, cancelled job, first copy of TOAD "unhung". (Is that a word?)
I would be happy to share my screen if that would be a help.
I also received this message at the bottom:
[Warning] Compilation (0: 0): PLW-06015: parameter PLSQL_DEBUG is deprecated; use PLSQL_OPTIMIZE_LEVEL = 1
PLW-06013: deprecated parameter PLSQL_DEBUG forces PLSQL_OPTIMIZE_LEVEL <= 1
20 minutes, not good. I guess some things are easier to cancel than others. It's good that it finally came back to life when you canceled the job though...that tells me that Toad and the Oracle were still connected to the server. I don't know why it wouldn't cancel though. I can try to set things up with a job using a package and see if I can reproduce it that way.
I'm not sure if this will help, but It looks like we set that PL_SQL debug setting if you're using JDWP debugging instead of DBMS Debugging (set here)
And you can set that PLSQL Optimize level here:
I can reproduce the Cancel-freeze as you describe. Here's what I did:
- Create scheduler job that runs a package (actually, I used a function, but it doesn't matter) that takes a while to run.
- Start running the job.
- In another copy of Toad, open the package in the editor and hit F9 to compile
- Compiling begins but doesn't finish.
- Hit cancel button. The timer stops but then Toad goes into "not responding" state
- In the first copy of Toad, stop the scheduler job.
- 2nd copy of Toad comes back to life after job is stopped.
I'm not sure what the solution is at this point.
1 Like
Well I feel better now that you can reproduce it. I will have to look up what JDWP debugging is. I have no idea.
If you have no idea, then you probably aren't using JDWP. It's a pain to set up. But maybe you have PLSQL_DEBUG set in your database parameters/init.ora.
Morning All,
Hope everyone is safe and well.
I think the problem here is the same as trying to cancel a normal SQL statement.
-
Toad is talking to Oracle quite happily and requests that the action in operation be cancelled.
-
The action is then flagged for cancelling.
-
However, the server process actually executing doesn't know that it's been cancelled until some communication take place between the server and user processes.
-
This could be the end of any of the PARSE, EXEC, FETCH phases of an SQL statement, returning of results and so on.
It's similar to executing a "kill session", sometimes it happens straight away, other times not.
HTH
Cheers,
Norm. [TeamT]
You can prevent the hang by turning off the "compile with debug" option.
Click this if it is in the "down" position, so that it goes into the "up" position as shown.
This is what's happening:
- You try to create or replace the package.
- It takes a long time - so you cancel
- Because the "compile with debug" option is enabled, Toad wants to compile the newly created proc with debug immediately after the "create or replace" finishes.
- If you cancel, we should skip step 3, but we don't. And the problem is that the "compile" SQL that we run has to wait for the the proc to not be in use before it will finish, just as the "create or replace" did. This SQL isn't cancellable, so it just sits there until it can finish. Which looks like a hang.
Unfortunately, development for version 14.2 is closed. This will be fixed in the first beta for the next version.
