Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ravi.

Asked: November 10, 2015 - 10:45 pm UTC

Last updated: July 27, 2018 - 1:13 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hello,

Is there a way I could run explain plan on Oracle Live SQL? Apparently, it gives an error that PLAN_TABLE doesn't exist. I ran utlxplan.sql in my session but still could not execute statement

SELECT * FROM TABLE(DBMS_XPLAN.display)

Which gives an error

ERROR: an uncaught error in function display has happened; please contact Oracle support
Please provide also a DMP file of the used plan table PLAN_TABLE
ORA-00904: DBMS_XPLAN_TYPE_TABLE: invalid identifier

Is this by design that we are blocked for doing explain plan?

Thanks!



and Connor said...

That is a *current* restriction. To provide isolation for all users, some facilities are currently blocked (eg dbms_xplan also lets you get the plan for anyone's cursor).

But you can still do explain into a PLAN_TABLE and use the old style query to get a plan output, ie along the lines of

SELECT ...
  FROM PLAN_TABLE
CONNECT BY prior id = parent_id
        AND prior statement_id = statement_id
  START WITH id = 0
        AND statement_id = 'XXX'
  ORDER BY id;


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks!

ravi B, November 11, 2015 - 1:30 am UTC

Thanks!

Will be available?

Duke Ganote, November 27, 2017 - 3:59 pm UTC

It was a pleasant surprise to log into liveSQL today and get the explain plan (below); any chance we can get actuals?

explain plan for select * from scott.emp;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Chris Saxon
November 27, 2017 - 5:09 pm UTC

You already can. See this script:

https://livesql.oracle.com/apex/livesql/file/content_FAQU2DYYI7N1RYDB5CR2Y98DD.html

lack privileges to run dbms_xplan

Jim Dickson, July 26, 2018 - 10:05 am UTC

Chris

In current version of Live Oracle SQL, when using you test code, I get following error:

PLAN_TABLE_OUTPUT
User has no SELECT privilege on V$SQL

Can you please confirm you get the same - or is there a way to workaround this issue?

jim
Connor McDonald
July 27, 2018 - 1:13 am UTC

Check out my blog post on the topic here

https://connor-mcdonald.com/2018/02/20/execution-plans-on-livesql/

This explains the why, and has a workaround.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.