This discussion is archived
1 Reply Latest reply: Jan 12, 2012 10:36 AM by AndyKlock RSS

gather_plan_statistics hint result not visible

Dario Botkuljak Newbie
Currently Being Moderated
on my Oracle 11.2.0.1, I tried to use that hint as in example:
set autotrace on explain
select /*+ gather_plan_statistics */ 'x' from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
as you see, there's no additional columns (E-rows, A-rows), and I don't know why.
what could cause this?

regards
  • 1. Re: gather_plan_statistics hint result not visible
    AndyKlock Journeyer
    Currently Being Moderated
    This is just yet another limitation to autotrace. Use dbms_xplan and format the data how you need it.
    SQL> select /*+ gather_plan_statistics */ 'x' from dual;
    
    '
    -
    x
    
    SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'allstats'));
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    SQL_ID  gxug7sz6nhqc3, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */ 'x' from dual
    
    Plan hash value: 1388734953
    
    -------------------------------------------------------------------------
    | Id  | Operation        | Name | Starts | E-Rows | A-Rows |   A-Time   |
    -------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |      |      4 |        |      4 |00:00:00.01 |
    |   1 |  FAST DUAL       |      |      4 |      1 |      4 |00:00:00.01 |
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    -------------------------------------------------------------------------
    
    
    13 rows selected.
    Read more about the parameters and usage here:

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xplan.htm

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points