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.
That is just *weird*. Exactly the question I was asked yesterday!
Comment by Doug Burns — June 30, 2009 @ 12:37 pm
Thank you for visiting Doug
Looks like Our friends have smilar minds 🙂
What was your solution to the question ?
Comment by coskan — June 30, 2009 @ 12:45 pm
Well it didn’t meet the requirement correctly the way this solution did, but I suggested DBMS_XPLAN.DISPLAY_CURSOR if they were able to run the query and look at the plan afterwards.
Comment by Doug Burns — June 30, 2009 @ 12:53 pm
Good one.
Comment by Asif Momen — June 30, 2009 @ 3:36 pm