How to explain plan on views when you don’t have access on base tables

One of my friends asked, how can developers see the explain plan on views without having access to the base tables.

After a small search in Oracle Forums I found the solution of John Spencer.

Basically he creates a procedure to run explain plan and gives the execute access to that procedure.

This is the procedure

CREATE OR REPLACE PROCEDURE explain_it (p_plan_id IN VARCHAR2,
p_sql IN VARCHAR2,
p_plan OUT my_types.expl_cur) AS
l_sqlstr VARCHAR2(32767);
BEGIN
l_sqlstr := 'SELECT LPAD('' '',2*(LEVEL-1))||OPERATION||'' ''||OPTIONS||'' ''||OBJECT_NAME||'' ''||';
l_sqlstr := l_sqlstr||'DECODE(ID,0,''COST = ''||POSITION) "QUERY PLAN" FROM plan_table ';
l_sqlstr := l_sqlstr||'START WITH ID = 0 AND STATEMENT_ID = :b1 ';
l_sqlstr := l_sqlstr||'CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = :b2';

EXECUTE IMMEDIATE 'EXPLAIN PLAN SET statement_id = '''||p_plan_id||''' FOR '||p_sql;

OPEN p_plan FOR l_sqlstr USING p_plan_id, p_plan_id;
END;
/

or to use with dbms_xplan.display

CREATE OR REPLACE PROCEDURE explain_it (
p_sql IN VARCHAR2,
p_plan OUT sys_refcursor) AS
l_sqlstr VARCHAR2(32767);
BEGIN
l_sqlstr := 'select * from table (dbms_xplan.display)';
EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR '||p_sql;
OPEN p_plan FOR l_sqlstr ;
END;
/

and this is how it works

SQL> create user read_only identified by read_only
  2  ;

User created.

SQL> grant create session to read_only;

Grant succeeded.

SQL> create table exp_test as select * from user_objects where rownum<1000;

Table created.

SQL> create view v_exp_test as select * from exp_test;

View created.

SQL> grant select on v_exp_test to read_only;

Grant succeeded.

SQL> grant execute on explain_it to read_only;

Grant succeeded.

----connect with read_only

SQL> connect read_only/read_only
Connected.

SQL> select count(*) from hr.v_exp_test;

  COUNT(*)
----------
       156

SQL> explain plan for select count(*) from hr.v_exp_test;
explain plan for select count(*) from hr.v_exp_test
                                         *
ERROR at line 1:
ORA-01039: insufficient privileges on underlying objects of the view

---TRY WITH NEW PROCEDURE

SQL> var my_cur refcursor;
SQL> EXEC hr.explain_it('SELECT * FROM hr.v_exp_test',:my_cur);

PL/SQL procedure successfully completed.

SQL> print my_cur;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
-------------------------------------------------
Plan hash value: 3488715207

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   156 | 29640 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EXP_TEST |   156 | 29640 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

12 rows selected.