Explain Plan and PLAN_TABLE$

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.