ba6.us - Dave's Database Related Stuff

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;
SQL> GRANT SELECT ON V_$SQL_PLAN TO &&MYUSER;
SQL> GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO &&MYUSER;

Now your unprivileged user has enough privileges to run GATHER_PLAN_STATISTICS:

SQL> connect nonprivuser/pass@db
SQL> select /*+ GATHER_PLAN_STATISTICS */ * from dual;
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


PLAN_TABLE_OUTPUT

SQL_ID  9br385ua0jjfq, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from dual
 
Plan hash value: 3543395131
 
---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS FULL| DUAL |      1 |      1 |      1 |00:00:00.02 |       3 |      2 |
---------------------------------------------------------------------------------------------