Explin Plan Blocked in Read-Only mode

[Jim Gust]

I am working on a performance issue that we see in production but not in the lower environments. I tried to look at the EXPLAIN PLAN in prod for a particular CTE but ran into a "Not allowed in read-only mode" error.

PLD Read-Only Explain Error:

Connecting the same way to prod, I'm able to generate it using SQL*Plus:

I'm also able to create it in PLD if I turn off read-only:

I'll turn off read-only since security already has me blocked on DML and DDL statements.

 

[Marco Kalter]

The explain plain function will update the plan_table, so it is indeed not allowed in read-only mode. We'll see if we can allow this in the next release.

 

[Jim Gust]

I just assumed that read-only excluded system tables. It turns out that the 'explain plan' is a statement that populates a plan table that might be in your schema or as a public synonym for sys.plan_table. It would still be nice to be able to do it on a read-only connection. I understand if the answer is "no chance, Jim" =)