在10g以后我们可以通过利用gather_plan_statistics提示来了解更多的SQL执行统计信息，具体使用方法如下：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
SQL>  set  linesize 150
SQL>  set  pagesize 2000
SQL>  set  autotrace traceonly exp
 
SQL>  select  avg (SALARY),DEPARTMENT_NAME  from
employees e,departments d  where  e.DEPARTMENT_ID=d.DEPARTMENT_ID  group  by  DEPARTMENT_NAME;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3294250112
 
---------------------------------------------------------------------------------------------
| Id  | Operation             |  Name         |  Rows   | Bytes | Cost (%CPU)|  Time      |
---------------------------------------------------------------------------------------------
|   0 |  SELECT  STATEMENT          |         |    27 |   621 |     5  (20)| 00:00:01 |
|   1 |  HASH  GROUP  BY             |         |    27 |   621 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS            |         |   106 |  2438 |     4   (0)| 00:00:01 |
|   3 |     TABLE  ACCESS  FULL           | EMPLOYEES   |   107 |   749 |     3   (0)| 00:00:01 |
|   4 |     TABLE  ACCESS  BY  INDEX  ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  5 |      INDEX  UNIQUE  SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified  by  operation id):
---------------------------------------------------
 
    5 - access( "E" . "DEPARTMENT_ID" = "D" . "DEPARTMENT_ID" )
 
SQL> show parameter cursor_sharing
 
NAME                      TYPE    VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing               string  EXACT
 
SQL>  show parameter statistics_level
 
NAME                      TYPE    VALUE
------------------------------------ ----------- ------------------------------
statistics_level             string  TYPICAL
 
SQL>  set  autotrace  off ;
 
SQL>  select  /*+ gather_plan_statistics */    avg (SALARY),DEPARTMENT_NAME  from
employees e,departments d  where  e.DEPARTMENT_ID=d.DEPARTMENT_ID  group  by  DEPARTMENT_NAME;
 
SQL>  select  *  from  TABLE (dbms_xplan.display_cursor( NULL , NULL , 'ALLSTATS LAST' ));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bctzu9xuxay18, child number 0
-------------------------------------
select  /*+ gather_plan_statistics */   avg (SALARY),DEPARTMENT_NAME  from  employees e,departments d
where  e.DEPARTMENT_ID=d.DEPARTMENT_ID  group  by  DEPARTMENT_NAME
 
Plan hash value: 3294250112
-------------------------------------------------------------------------------------------------------
| Id  | Operation             |  Name         | Starts | E- Rows  | A- Rows  |   A- Time    | Buffers |
-------------------------------------------------------------------------------------------------------
|   1 |  HASH  GROUP  BY             |         |      1 |     27 |     11 |00:00:00.01 |     219 |
|   2 |   NESTED LOOPS            |         |      1 |    106 |    106 |00:00:00.01 |     219 |
|   3 |     TABLE  ACCESS  FULL           | EMPLOYEES   |      1 |    107 |    107 |00:00:00.01 |       7 |
|   4 |     TABLE  ACCESS  BY  INDEX  ROWID| DEPARTMENTS |    107 |      1 |    106 |00:00:00.01 |     212 |
|*  5 |      INDEX  UNIQUE  SCAN         | DEPT_ID_PK  |    107 |      1 |    106 |00:00:00.01 |     106 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified  by  operation id):
---------------------------------------------------
 
    5 - access( "E" . "DEPARTMENT_ID" = "D" . "DEPARTMENT_ID" )
 
/* 可以从starts列看到某种操作执行了多少次，例如这里的 INDEX  UNIQUE  SCAN为107次 */
 
/*也可以通过SQL_ID来定位计划信息 */
 
 
SQL>  select  t.*
from  v$sql s
    ,  table (dbms_xplan.display_cursor(s.sql_id,s.child_number, 'ALL IOSTATS LAST' )) t  where  s.sql_id =  '&SQL_ID'  ;
 
 
Enter value  for  sql_id: bctzu9xuxay18
 
old   3:    ,  table (dbms_xplan.display_cursor(s.sql_id,s.child_number, 'ALL IOSTATS LAST' )) t  where  s.sql_id =  '&SQL_ID'
new   3:    ,  table (dbms_xplan.display_cursor(s.sql_id,s.child_number, 'ALL IOSTATS LAST' )) t  where  s.sql_id =  'bctzu9xuxay18'
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bctzu9xuxay18, child number 0
-------------------------------------
select  /*+ gather_plan_statistics */  avg (SALARY),DEPARTMENT_NAME  from
employees e,departments d  where
e.DEPARTMENT_ID=d.DEPARTMENT_ID  group  by  DEPARTMENT_NAME
 
Plan hash value: 3294250112
 
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             |  Name         | Starts | E- Rows  |E-Bytes| Cost (%CPU)| E- Time    | A- Rows  |   A- Time    | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH  GROUP  BY             |         |      1 |     27 |   621 |     5  (20)| 00:00:01 |     11 |00:00:00.01 |     219 |
|   2 |   NESTED LOOPS            |         |      1 |    106 |  2438 |     4   (0)| 00:00:01 |    106 |00:00:00.01 |     219 |
|   3 |     TABLE  ACCESS  FULL           | EMPLOYEES   |      1 |    107 |   749 |     3   (0)| 00:00:01 |    107 |00:00:00.01 |       7 |
|   4 |     TABLE  ACCESS  BY  INDEX  ROWID| DEPARTMENTS |    107 |      1 |    16 |     1   (0)| 00:00:01 |    106 |00:00:00.01 |     212 |
|*  5 |      INDEX  UNIQUE  SCAN         | DEPT_ID_PK  |    107 |      1 |       |     0   (0)|          |    106 |00:00:00.01 |     106 |
---------------------------------------------------------------------------------------------------------------------------------------
 
Query Block  Name  / Object Alias (identified  by  operation id):
-------------------------------------------------------------
 
    1 - SEL$1
    3 - SEL$1 / E@SEL$1
    4 - SEL$1 / D@SEL$1
    5 - SEL$1 / D@SEL$1
 
Predicate Information (identified  by  operation id):
---------------------------------------------------
 
    5 - access( "E" . "DEPARTMENT_ID" = "D" . "DEPARTMENT_ID" )
 
Column  Projection Information (identified  by  operation id):
-----------------------------------------------------------
 
    1 -  "DEPARTMENT_NAME" [VARCHAR2,30],  AVG ( "SALARY" )[22]
    2 -  "SALARY" [NUMBER,22],  "DEPARTMENT_NAME" [VARCHAR2,30]
    3 -  "SALARY" [NUMBER,22],  "E" . "DEPARTMENT_ID" [NUMBER,22]
    4 -  "DEPARTMENT_NAME" [VARCHAR2,30]
    5 -  "D" .ROWID[ROWID,10]
 
SQL>  alter  session  set  statistics_level= ALL ;
Session altered.
 
 
/* 在session级别设置statistics_level为 ALL ,可以为我们提供更为详尽的执行统计信息 */