DBMS_XPLAN and GATHER_PLAN_STATISTICS

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.

Permissions Required

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:
GRANT SELECT ON V_$SESSION TO MYUSER;
GRANT SELECT ON V_$SQL TO MYUSER;
GRANT SELECT ON V_$SQL_PLAN TO MYUSER;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO MYUSER;
Now your unprivileged user has enough privileges to run GATHER_PLAN_STATISTICS:

Gathering Execution Plan and Execution Statistics

The great thing about GATHER_PLAN_STATISTICS is that it show E-Rows (Estimates) vs A-Rows (Actual rows visited). This is great for hunting down statistics issues, either out of date Object Statistics, or exposing limitations of the CBO (think nested subqueries - a smal error deep down in the hierarchy ends up being a big error as incorrect estimates a fed up through the chain). Also the DBMS_XPLAN output shows the actual time spent on each step in the execution plan. This can also help you pinpoint issues that need some tuning attention. The big drawback is that your SQL Statement has to be capable of finishing. If your statement will not finish then GATHER_PLAN_STATISTICS has nothing to report on.
SQL> connect nonprivuser/pass@db
SQL> alter session set statistics_level=ALL;
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 |
---------------------------------------------------------------------------------------------
Output can also be specified as: ALLSTATS LAST +PEEKED_BINDS

Alternate Method to Supply Hint

Kerry Osbourne posted about this novel way of looking at GATHER_PLAN_STATISTICS hint. Basically Kerry points out if it is a hint then it can be applied via a SQL Profile without touching the existing statement.

Alternate Method for Producing Output

Some tools have some chatter on their database connections that might not enable you to easily or reliably get the 'LAST' execution through DBMS_XPLAN. Here is a way to add a comment when the statement is run and to use it to retrieve the SQL_ID.
SET TIMING ON;
SET SERVEROUTPUT OFF;
SET TERMOUT OFF;
SELECT /*+ gather_plan_statistics MY_CURSOR_1 */ * 
 FROM EMP;
SET TERMOUT ON;

SELECT PLANOUTPUT.*
 FROM gv$sql s,
    TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number,'ALLSTATS LAST')) PLANOUTPUT
 WHERE sql_text LIKE '%MY_CURSOR_1%';
Note that you will want to use unique identifiers as re-using MY_CURSOR_1 over and over will cause more and more SQL statements to match the WHERE clause above and will give you unpredictable results.