Explain Plan leaves a transaction uncommitted

Toad for Oracle
1

Morning All,

Toad 13.0.0.80 64bit
Windows 7 64bit.

I’m seeing a funny in the transactions window when I have executed an EXPLAIN PLAN (CTRL-E) in a session.

I’ve got Toad configured to:

  • Execute queries in threads;
  • Use the main Toad session for Explain Plan;
  • Use the SYS.PLAN_TABLE$ table for explain plan.

The funny is that whenever I explain a plan, I get a transaction that uses 1 block of UNDO and doesn’t appear to be able to be committed. It’s holding the transaction open on the above plan table.

The table is created as follows (by Oracle, not by me):

CREATE GLOBAL TEMPORARY TABLE SYS.PLAN_TABLE$
(
...
...
)
ON COMMIT PRESERVE ROWS
NOCACHE;

So a commit should preserve the data for subsequent re-reading.

As I’m using the main Toad session for my explain plans, I tried running a COMMIT in that to see if the transaction would end, but it didn’t.

It’s not a major problem, and it might be my settings, but I thought I’d mention it for those times when the Toad Developers have nothing to do! :joy::joy::joy:

To reproduce, just set up Toad as above, and CTRL-E in the editor. Now check with the session browser, the undo usage for your Toad session and the transactions dialogue thingy.

Cheers,
Norm. [TeamT]