Common GATHER_PLAN_STATISTIC confusion

January 29, 2016

1 minute

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;

  COUNT(P)
----------
         0

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
--------------------
SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

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 responses to “Common GATHER_PLAN_STATISTIC confusion”

Mikhail Velikikh

February 1, 2016 at 3:29 pm
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:
```
begin
  -- gather_plan_statistics query
  for test_rec (
    select /*+ gather_plan_statistics*/..
  )
  loop
    null;
  end loop;

  -- dbms_xplan query
  for plan_rec in (
    select plan_table_output pto from table(dbms_xplan.display_cursor( format=> 'allstats last')))
  loop
    dbms_output.put_line( plan_rec.pto);
  end loop;
end;
/
```
This solution is independent of SERVEROUT settings.
Reply
Jay Mehta

February 10, 2016 at 6:46 am

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.

Thanks.

Reply
Dwight Beres

April 12, 2016 at 11:15 am

Timely suggestions ! BTW , if someone needs a USCIS I-9 , my wife edited a template form here https://goo.gl/8S4Jfw.

Reply