Most people already know about the very cool GATHER_PLAN_STATISTICS hint.  If not, you can see an example here

But here’s a common cause of confusion when using it in SQL Plus:

SQL> select /*+ gather_plan_statistics*/ count(p) from t where x > sysdate - 30;


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

SQL_ID  9babjv8yq8ru3, child number 0


NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

So what has happened ? Why didn’t I get my nicely formatted output ?

Well, if you have “SERVEROUTPUT” set to “on” in SQL Plus, then when your SQL statement has completed, SQL Plus makes an additional call to the database to pick up any data in the DBMS_OUTPUT buffer.

Hence when you ask for statistics on the last call made to the database, it is not the one you were expecting.

Moral of the story: Turn off serveroutput before using GATHER_PLAN_STATISTICS in SQL Plus.

3 thoughts on “Common GATHER_PLAN_STATISTIC confusion

  1. The most of our production databases has audit “ALL STATEMENTS” enabled for all non-application schemas.
    That’s a reason why I wrap “gather_plan_statistics” queries in PL/SQL block.
    I.e. in order to retrieve Row-source Execution Statistics I execute:

      -- gather_plan_statistics query
      for test_rec (
        select /*+ gather_plan_statistics*/..
      end loop;
      -- dbms_xplan query
      for plan_rec in (
        select plan_table_output pto from table(dbms_xplan.display_cursor( format=> 'allstats last')))
        dbms_output.put_line( plan_rec.pto);
      end loop;

    This solution is independent of SERVEROUT settings.

  2. Hi Connor,

    You are correct. The root cause of this issue is SQL*Plus making an additional call when serverout is on. Since this additional call “bumps” out sql_id of the SQL statement that you executed, dbms_xplan.display_cursor won’t work irrespective of GATHER_PLAN_STATISTIC hint.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s