Submitted by dmann on Tue, 11/24/2009 - 15:51
I have been making use of the GATHER_PLAN_STATISTICS hint more and more lately. In the interest of getting the most accurate data I usually log in as the user that normally executes the query. These are usually non-privileged accounts that just have access to their own objects.
In order to use DBMS_XPLAN.DISPLAY_CURSOR in a session that does not have DBA or SELECT CATALOG ROLE, grant the following permissions to them:
SQL> GRANT SELECT ON V_$SESSION TO &&MYUSER;
SQL> GRANT SELECT ON V_$SQL TO &&MYUSER;
Read more about DBMS_XPLAN.DISPLAY_CURSOR and "User has no SELECT privilege"