gather_plan_statistics 힌트

select /*+ gather_plan_statistics ordered use_nl(t1) index(t1) */
         count(t1.n2), count(t2.n2)
from t2, t1
where t2.n2 = 45
and t1.n1 = t2.n1
;       

COUNT(T1.N2) COUNT(T2.N2)
------------ ------------
         225          225       

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

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  98cw5a9c0pw33, child number 0
-------------------------------------
select  /*+   gather_plan_statistics   ordered use_nl(t1) index(t1)  */  count(t1.n2),
count(t2.n2) from  t2, t1 where  t2.n2 = 45 and t1.n1 = t2.n1       

Plan hash value: 3795562434       

-----------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                       |      |      1 |      1 |      1 |00:00:00.01 |     146 |
|   2 |   TABLE ACCESS BY INDEX ROWID | T1   |      1 |     15 |    225 |00:00:00.01 |     146 |
|   3 |    NESTED LOOPS                          |      |      1 |    225 |    241 |00:00:00.02 |     116 |
|*  4 |     TABLE ACCESS FULL                 | T2   |      1 |     15 |     15 |00:00:00.01 |      99 |
|*  5 |     INDEX RANGE SCAN                   | T_I1 |     15 |     15 |    225 |00:00:00.01 |      17 |
-----------------------------------------------------------------------------------------------       

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T2"."N2"=45)
   5 - access("T1"."N1"="T2"."N1")       



참고 : GATHER_PLAN_STATISTICS 힌트
         dbms_xplan in 10g
         http://rwijk.blogspot.com/2008/03/dbmsxplandisplaycursor.html