在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,可以为我们提供更为详尽的执行统计信息 */