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

DISPLAY Function displays a content of PLAN_TABLE generated (filled) by EXPLAIN PLAN FOR command. So you can use it to generate and display an (theoretical) plan using EXPLAIN PLAN FOR command, for example in this way:

create table emp as select * from all_objects;

explain plan for
select /*+ gather_plan_statistics */ count(*) from emp where object_id between 100 and 150;

select * from table(dbms_xplan.display );

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   351   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |    12 |    60 |   351   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"<=150 AND "OBJECT_ID">=100)

---

/*+ gather_plan_statistics */ hint does not save data into PLAN_TABLE, but it stores execution statistics in V$SQL_PLAN performance view.
To display these data you can use a method described here: http://www.dba-oracle.com/t_gather_plan_statistics.htm, but this not always work, because you must execute the second command immediately after the SQL query.

The better method is to query V$SQL view to obtain SQL_ID of the query, and then use DISPLAY_CURSOR function, for example in this way:

select /*+ gather_plan_statistics */ count(*) from emp where object_id between 100 and 150;

select sql_id, plan_hash_value, child_number, executions, fetches, cpu_time, elapsed_time, physical_read_requests, physical_read_bytes
from v$sql s
where sql_fulltext like 'select /*+ gather_plan_statistics */ count(*)%from emp%'
  and sql_fulltext not like '%from v$sql' ;

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS    FETCHES   CPU_TIME ELAPSED_TIME PHYSICAL_READ_REQUESTS PHYSICAL_READ_BYTES
------------- --------------- ------------ ---------- ---------- ---------- ------------ ---------------------- -------------------
9jjm288hx7buz      2083865914            0          1          1      15625        46984                     26            10305536

The above query returns SQL_ID=9jjm288hx7buz and CHILD_NUMBER=0(child number is just a cursor number). Use these values to query the colledted plan:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9jjm288hx7buz', 0, 'ALLSTATS'));

SQL_ID  9jjm288hx7buz, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from emp where object_id 
between 100 and 150

Plan hash value: 2083865914

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      2 |        |      2 |00:00:00.05 |   10080 |
|   1 |  SORT AGGREGATE    |      |      2 |      1 |      2 |00:00:00.05 |   10080 |
|*  2 |   TABLE ACCESS FULL| EMP  |      2 |     47 |     24 |00:00:00.05 |   10080 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("OBJECT_ID"<=150 AND "OBJECT_ID">=100))

Answer 2

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.

Answer 3

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 |
------------------------------------------------------------------------------------

Answer 4

ALLSTATS LAST starts working after you ran the statement twice.