Someone told me something a few weeks ago that didn’t make sense to me, and it still doesn’t.
Maybe I’m not seeing something obvious or maybe this is a legacy left over from the days before PLAN_TABLE became a synonym for SYS.PLAN_TABLE$ and SYS.PLAN_TABLE$ existed as a global temporary table? As ever, your comments on what follows are most welcome.
The discussion I was involved in regarded GoldenGate and the impact long running transactions were having. The comment that jumped out at me was that running EXPLAIN PLAN left a transaction open. “We’ll that implies that PLAN_TABLE$ is a global temporary table with on commit delete rows.” was my response. This doesn’t necessarily follow, but it was the first reason I could think of why Oracle would not code EXPLAIN PLAN to automatically commit… But, that raised the question (in my head) of, “Why don’t they just define PLAN_TABLE$ with ON COMMIT PRESERVE ROWS and perform a commit as part of EXPLAIN PLAN”. The discussion quickly moved on, but this seemed something worth spending a few minutes confirming…
First things first, I thought I’d better check the “duration” of PLAN_TABLE$
SYSTEM@orcl> select duration from all_tables where owner = 'SYS' and table_name = 'PLAN_TABLE$'; DURATION -------------------- SYS$SESSION SYSTEM@orcl>
Well, that wasn’t what I was expecting. PLAN_TABLE$ records are persistent for the duration of a session, so why doesn’t EXPLAIN PLAN automatically commit? The best answer I can come up with is that it doesn’t because it just doesn’t. Pathetic, I know.
I can’t imagine that someone would start inspecting execution plans, via EXPLAIN PLAN, midway through a transaction, but hey, they might. However, if we have to learn that DDL, gathering statistics, etc perform implicit commits then why not just add EXPLAIN PLAN to that list?
Anyway, here’s a quick demo to stop you feeling you need to verify this for yourself.
Session 1 – Connect to a new session, verify PLAN_TABLE is empty and run EXPLAIN PLAN
20:59:13 MNASH@orcl> conn mnash
Enter password:
Connected.
20:59:29 MNASH@orcl> select count(*) from plan_table;
COUNT(*)
----------
0
20:59:37 MNASH@orcl> explain plan for select * from t;
Explained.
20:59:50 MNASH@orcl>
Session 2 – Query v$session and v$transaction to verify that there is a transaction open
20:58:57 SYSTEM@orcl> l 1 select username, 2 sid, 3 sql_id, 4 event, 5 state, 6 seconds_in_wait 7 from v$session s 8 , v$transaction t 9* where s.taddr = t.addr 20:59:58 SYSTEM@orcl> / USERNAME SID SQL_ID EVENT STATE SECONDS_IN_WAIT ---------- ----- ------------- ----------------------------- --------- --------------- MNASH 17 6j5vng1ckm1p2 SQL*Net message from client WAITING 11 21:00:01 SYSTEM@orcl>
Session 1 – Use DBMS_XPLAN.DISPLAY to view the plan
20:59:50 MNASH@orcl> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1161K| 107M| 4697 (1)| 00:00:57 | | 1 | TABLE ACCESS FULL| T | 1161K| 107M| 4697 (1)| 00:00:57 | -------------------------------------------------------------------------- 8 rows selected. 21:00:16 MNASH@orcl>
Session 2 – Query v$session and v$transaction to verify that there is a transaction open
21:00:01 SYSTEM@orcl> / USERNAME SID SQL_ID EVENT STATE SECONDS_IN_WAIT ---------- ----- ------------- ----------------------------- --------- --------------- MNASH 17 SQL*Net message from client WAITING 6 21:00:22 SYSTEM@orcl>
Session 1 – Commit
21:00:16 MNASH@orcl> commit; Commit complete. 21:00:33 MNASH@orcl>
Session 2 – Query v$session and v$transaction to verify that the transaction is closed
21:00:22 SYSTEM@orcl> / no rows selected 21:00:40 SYSTEM@orcl>
If the above interested you then have a read of the sections containing “commit” in the EXPLAIN PLAN documentation.
If you are doing explain plan on queries which themselves involve temporary tables (esp with dynamic sampling) you definately don’t want a COMMIT prior to the explain, which is what you get with DDL.
But also remember you don’t HAVE to use the system PLAN_TABLE so you can EXPLAIN PLAN INTO an ‘ON COMMIT DELETE’ temporary table.
I suspect the reason it doesn’t commit though, is that it doesn’t need to. A DDL (or stats gathering) affects other sessions. They can’t easily leave those open without causing blocking problems
Thanks. All good comments.
The real issue in our case is end users running an explain plan and then carrying on with something else (potentially for hours) not realising that they have an open transaction… I can’t blame them for not knowing that they would have an open transaction as I didn’t know either until this investigation.
One of the suggested solutions to the “long running transactions” issue was to use a profile with idle_time for non-application accounts… I find myself back in the land of user management and access control
This pops up with Toad users ALL the time
>>The real issue in our case is end users running an explain plan and then carrying on with something else