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

What I leared from the answers so far:

When a query is parsed, the optimizer estimates how many rows are produced during each step of the query plan. Sometimes it is neccessary to check how good the prediction was. If the estimates are off by more than a order of magnitude, this might lead to the wrong plan being used.

To compare estimated and actual numbers, the following steps are necessary:

1. You need read access to V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL. These privileges are included in the SELECT_CATALOG role. (source)

2. Switch on statistics gathering, either by

   ALTER SESSION SET STATISTICS_LEVEL = ALL;

   or by using the hint /*+ gather_plan_statistics */ in the query.

   There seems to be a certain performance overhead. See for instance Jonathan's blog.

3. Run the query. You'll need to find it later, so it's best to include an arbitrary hint:

   SELECT /*+ gather_plan_statistics HelloAgain */ * FROM scott.emp;

   EXPLAIN PLAN FOR SELECT ... is not sufficient, as it will only create the estimates without running the actual query.

   Furthermore, as @Matthew suggested (thanks!), it is important to actually fetch all rows. Most GUIs will show only the first 50 rows or so. In SQL Developer, you can use the shortcut ctrl+End in the query result window.

4. Find the query in the cursor cache and note it's SQL_ID:

   SELECT sql_id, child_number, sql_text FROM V$SQL WHERE sql_text LIKE '%HelloAgain%';

   dbqbqxp9srftn 0 SELECT /*+ gather_plan...

5. Format the result:

   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dbqbqxp9srftn',0,'ALLSTATS LAST'));

Steps 4. and 5. can be combined:

SELECT x.* 
  FROM v$sql s, 
       TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) x 
 WHERE s.sql_text LIKE '%HelloAgain%';

The result shows the estimated rows (E-Rows) and the actual rows (A-Rows):

SQL_ID  dbqbqxp9srftn, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics HelloAgain */ * FROM scott.emp

Plan hash value: 3956160932

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       6 |
|   1 |  TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       6 |
------------------------------------------------------------------------------------