SQL Tuning 基础概述01 - Autotrace的设定
SQL Tuning 基础概述01 - Autotrace的设定
1.autotrace的设定
1
2
|
SQL> set autotrace Usage: SET AUTOT[RACE]
{ OFF | ON |
TRACE[ ONLY ]}
[EXP[LAIN]] [STAT[ISTICS]] |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
set autot on --打开autotrace,之后执行的sql,会显示sql执行结果、执行计划、统计信息 set autot on exp --会显示sql执行结果、执行计划 set autot on stat --会显示sql执行结果、统计信息 set autot
trace --只显示执行计划、统计信息 set autot
trace exp --只显示执行计划(可能不准,sql查询并没有真正执行) set autot
trace stat --只显示统计信息 set autot off --关闭autotrace |
2.实验验证 set autot trace exp 没有真正执行查询类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
|
SQL> set autot
trace exp SQL> select * from t_jingyu; Elapsed:
00:00:00.04 Execution
Plan ---------------------------------------------------------- Plan
hash value: 2809386205 ------------------------------------------------------------------------------ |
Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ |
0 | SELECT STATEMENT
| | 1864K| 69M| 981 (2)| 00:00:12 | |
1 | TABLE ACCESS FULL |
T_JINGYU | 1864K| 69M| 981 (2)| 00:00:12 | ------------------------------------------------------------------------------ Note ----- - dynamic sampling
used for this
statement ( level =2) SQL> set autot
trace SQL> select * from t_jingyu; 2097152 rows selected. Elapsed:
00:00:24.89 Execution
Plan ---------------------------------------------------------- Plan
hash value: 2809386205 ------------------------------------------------------------------------------ |
Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ |
0 | SELECT STATEMENT
| | 1864K| 69M| 981 (2)| 00:00:12 | |
1 | TABLE ACCESS FULL |
T_JINGYU | 1864K| 69M| 981 (2)| 00:00:12 | ------------------------------------------------------------------------------ Note ----- - dynamic sampling
used for this
statement ( level =2) Statistics ---------------------------------------------------------- 0
recursive calls 0
db block gets 143066
consistent gets 3484
physical reads 0
redo size 51171186
bytes sent via SQL*Net to client 1538429
bytes received via SQL*Net from client 139812
SQL*Net roundtrips to / from client 0
sorts (memory) 0
sorts (disk) 2097152 rows processed SQL> --试验表明set
autot trace exp不真正执行sql显示的执行计划,set autot trace 执行了sql显示的执行计 |