Having been a user of Toad for over 10 years, as both a DBA and developer, I am now in the habit of having autocommit turned on (Just makes me automatically more careful in what I am doing, I suppose) - but no doubt there will be arguments on both sides of that particular question. However, since Toad offers this functionality, I have just upgraded from 10 to 11.6 and it seems that this feature now does not actually commit after every statement (like it used to). In the Oracle options -> transactions page, as usual, I have selected “Execute in threads”, “Execute scripts in toad sessions” and “commit after every statement” as I have done since Toad 6. I have also, on the same options page, selected the “Threaded query session” for Explain plan, and the area for “When closing connections …” is greyed out as I would expect having selected the “Commit after every statement” option.
However, if I execute an “Insert into …” query in a thread, I would expect that to be committed when executed. But in order to “see” this committed data in another session, I find i am now needing to physically commit the insert statement in its session before the result is visible to other sessions. It seems that this feature has stopped working. I am just wondering if anyone else (if there is anyone that actually uses this feature intentionally) has come across this behaviour in 11.6?
From original >>>
needing to physically commit the insert statement in
its session before the result is visible to other sessions.
Welcome to the world of threading and session management. By definition from Oracle, what you do in one session should not be visible until after a commit.
So if you execute via threads and those threads use different sessions, then that’s exactly how it’s supposed to work. You cannot say what happens in Toad while it’s running should always be visible to me – not when you add threads and have threads running
in other sessions.
What you might be really asking for – is there a way to run threads in the same sessions as the main Toad session? Look under options (or do options search)
for thread ….
I just tested Toad 10.6 and Toad 12.0 using your option setup and am seeing the same behavior in both.
My test procedure...
Open two connections to same SCHEMA@DB.
Open an Editor for each.
In Session1 execute (F9) an insert statement.
In Session2 execute (F9) a SELECT from that table. The new row is visible.
In Session1 execute as script (F5) an insert statement.
In Session2 execute (F9) a SELECT from that table. The new row is not visible.
Close both sessions.
Open new session.
Execute (F9) a SELECT from that table. The new row from step 5 is visible so a commit did occur at some point, but probably not immediately after the script
execute.
Are you executing a statement (F9) or executing as script (F5)?
Bert, I understand the concept of threaded queries, and commits per session. The whole point of using threads is to allow me to have separate sessions as I operate on multiple schemas at any one time as multiple users and it allows me to see and validate changes made in one session from all other sessions, when I expect those changes to be seen. When I enable autocommit, if I execute a statement, under the covers Toad has always added in a commit in that thread when this feature is enabled. I have never encountered a problem prior to this release where the autocommit does not work as advertised, and have always set these options the same way for many previous versions. Additional options for Toad are the “Run query in thread” in the query builder (which I have probably used a couple of times only) - in my case it is enabled, and the “Apply commit/rollback to all tabs (threaded queries)” option for the Editor behaviour, which I would never have enabled unless I needed a correlated commit across sessions/threads (and in my case is not enabled).
Michael, thanks for the test session - you may have found the source of the issue - the autocommit certainly does not appear to work as advertised/expected when running a script. I had a script of a series of insert statements which I executed, and expected to see each committed as it executed in another session. the data only appeared when I ran an explicit commit, or used the commit button on the editor window. It would seem that the autocommit actually commits when an execute command is issued (F9), but will only autocommit the results of a script execution (F5) with a commit statement, or when closing the thread/session by closing the connection. I am convinced that this was not the behaviour in 6.5, 7.2, 7.6, 8.6, 9.2 or any flavour of 10, but do not have any of my prior versions on which to check. It is just that these are the versions I can recall using over the years, and have never run into this behaviour before with any of them. If you say that your 10.6 version commits an execute but not an execute as a script, I cannot argue against that. According to the Toad Help, and all the docs that I have ever found, the Autocommit will issue a commit “… every time a statement is run …” - it does not differentiate between running a statement using F5 or F9 - should it make a difference?
F9 <> F5 whether in online help or any other source. Run Statement means F9 by definition. Executing as script f5 has always been a different beast. But it
may be that f5 used to and needs to support auto commit – so maybe a bug. But the developer who works on that (Greg) is out on vacation – so this discussion will need to wait until he returns …
Bert Scalzo
Engr Tech Principal Engineer Dell | Information Management office
+1 469 888 5302
Quest Software is now part of Dell
I am convinced that this was not the behaviour in 6.5, 7.2, 7.6, 8.6, 9.2 or any flavour of 10
The oldest I have is 10.6 and 10.6 works the same as 11.6 and Toad 12, but the behavior does seem a little odd. The execute as script dude is out of the office for several
more days, but I'll forward this onto him in case there's some technical reason why it's not behaving the same as F9.
should it make a difference?
I'd think it should not, but I'm not the expert in the script execution department.
This is slightly off topic, but generally speaking F9 is better for single statement execution. When I think of a script I think of a series of 2..N statements. Several options
and features apply to F9 only. Compiling with debug is F9 only. Auto Trace is F9 only. Optimizing compiler value, substitution variable prompting, SQL Recall and Query Viewer (tab and window) are all F9 only. F9 uses much less memory when your result set it
large. Results are fetched in pages or blocks. The next page is loaded when you scroll to the bottom of the grid. Execute as script fetches all rows and creates a grid to display them as well as prints all of the data in plain text assuming you don't have
the server output turned off. That's not to say that executing with F5 doesn't have a place in single statement execution especially if you want the SQL*Plus style text output, but F9 is the better tool for the job in most cases.
Sure I agree with all your comments regarding F5/F9, and whilst I use F9 mainly, there are times when I might have created a series of insert statements (for example off a spreadsheet or similar) which I then run as F5. Similarly, if I create an anonymous PL/SQL block in the editor to call a procedure, I will use F5, but then of course this will commit on completion anyway. I guess it is probably this element that appears to behave differently with autocommit, unlike the F9 which does appear to commit instantly when enabled. It will be interesting to see what Greg says on the subject.
If you execute a script (F5) in the existing Toad session and don’t do an explicit commit, it won’t do a commit (honor the the Options -> Oracle -> Transactions -> When closing connections) until you close the session. This is the same behavior that SQL*Plus exhibits (and what we try to emulate with the script engine). If you want it to commit and be seen, you can either execute scripts in a seperate session by unchecking Options -> Oracle -> Transactions -> Execute scripts in Toad session. This will create a new session to execute the script in and close the session when the script execute is complete. It will then honor the Options -> Oracle -> Transactions -> When closing connections setting for committing.
The script engine has never used the Options -> Oracle -> Transactions -> Commit every statement. At least not since the 9.0 rewrite around 7 years ago.