1 Reply Latest reply: Jan 12, 2012 12:36 PM by Andy Klock RSS

    gather_plan_statistics hint result not visible

    Dario Botkuljak
      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
          Andy Klock
          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