I can't get Oracle execution plan in prod database

user_9679725 0
Viewed 188

I'm connecting to a production database with a read-only separate account (no DBA privileges), and I can't get the execution plan for a query. When I try:

explain plan for
select * from my_table where id = 10;

select plan_table_output from
  table(dbms_xplan.display('plan_table',null,'typical'));

I get different plans for unrelated queries every time. I guess there are so many queries per second that the plan gets immediately replaced by other ones.

Then, I tried adding a "statement ID":

explain plan set statement_id = 'st1' for
select * from my_table where id = 10;

select plan_table_output from
  table(dbms_xplan.display('plan_table','st1','typical'));

but I get the error:

Error: cannot fetch plan for statement_id 'st1'

What's the easiest and consistent way of retrieving an execution plan in a highly concurrent db?

sql-execution-plan oracle database
1 Answers

This is late but I found this via an internet search on the error. First issue is the explain itself fails because is invalid. With a short query like the above you would see the error but on a longer query where you script the explains and query you might miss the error messages. Second, set serveroutput being on or your disabling it between the explain and results query will block getting the results. Hope this helps somebody.

user_76113460
Related