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.

About these ads

3 thoughts on “Explain Plan and PLAN_TABLE$

  1. 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 :-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s