sql - GATHER_PLAN_STATISTICS does does not generate basic plan statistics

Question

all,

I am learning to tune query now, when I ran the following:

select /*+ gather_plan_statistics */ * from emp;

select * from table(dbms_xplan.display(FORMAT=>'ALLSTATS LAST'));

The result always says:

• Warning: basic plan statistics not available. These are only collected when:
  • hint 'gather_plan_statistics' is used for the statement or
  • parameter 'statistics_level' is set to 'ALL', at session or system level

I tried the alter session set statistics_level = ALL; too in sqlplus, but that did not change anything in the result.

Could anyone please let me know what I might have missed?

Thanks so much.

Answer 1

If all you ran were the two statements in your question:

select /*+ gather_plan_statistics */ * from emp;

select * from table(dbms_xplan.display(FORMAT=>'ALLSTATS LAST'));

Then I think your problem is your use of DBMS_XPLAN.DISPLAY. The way you are using it, you are printing the plan of the last statement you explained, not the last statement you executed. And "explain" will not execute the query, so it will not benefit from a gather_plan_statistics hint.

This works for me in 12c:

select /*+ gather_plan_statistics */ count(*) from dba_objects;

SELECT *
FROM   TABLE (DBMS_XPLAN.display_cursor (null, null, 'ALLSTATS LAST'));

i.e., display_cursor instead of just display.