1.正常查询
SQL> select count(*) from system.wh where num1=100;


  COUNT(*)
----------
       101


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7uc0kqyghskzs, child number 0
-------------------------------------
select count(*) from system.wh where num1=100


Plan hash value: 1206455612


--------------------------------------------
| Id  | Operation          | Name | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT   |      |        |
|   1 |  SORT AGGREGATE    |      |      1 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| WH   |    101 |
--------------------------------------------


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


   2 - filter("NUM1"=100)


Note
-----
   - dynamic sampling used for this statement (level=2)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - 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 leve
l






26 rows selected.




2.使用 gather_plan_statistics 查询
SQL> select  /*+ gather_plan_statistics */ count(*) from system.wh where num1=100;


  COUNT(*)
----------
       101


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  g9rdy6074r3uw, child number 0
-------------------------------------
select  /*+ gather_plan_statistics */ count(*) from system.wh where
num1=100


Plan hash value: 1206455612


--------------------------------------------------------------------------------
-----


| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buff


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ers |


--------------------------------------------------------------------------------
-----


|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |
 23 |


|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |
 23 |




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| WH   |      1 |    101 |    101 |00:00:00.01 |
 23 |


--------------------------------------------------------------------------------
-----




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


   2 - filter("NUM1"=100)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




24 rows selected.




区别很明显