2010-03-28

Visualizing plans grants

A while ago I post about to use plan visualization by Tanel Poder. That has been a tool worth using. Thou that should be a tool also to other developers than DBAs. A-Rows and A-Time are such valuable information about the execution bottlenecs inside SQL execution.

How to get it working without dba rights.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

User has no SELECT privilege on V$SESSION


It is not possible to grant select rights straight to v$ views as Coscan has writen.


grant select on v_$session to rafu;


And a try


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL


Giving a grant


grant select on v_$sql_plan_statistics_all to rafu;


And after that no success. The same output.
11.2 documentation says that to use DBMS_XPLAN.DISPLAY_CURSOR the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views.


grant select on v_$sql_plan to rafu;

grant select on v_$sql to rafu;


And we are there. User rafu may use DBMS_XPLAN.DISPLAY_CURSOR with sql_id=>null parameter. Also grant alter session to avoid the need to use gather_plan_statistics hint might be recommended. That is how you enable ALTER SESSION SET statistics_level = ALL;


grant alter session to rafu;


Also giving these grants through a role to developer users is worth considering.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.