SQL Tuning 基础概述01
来源:互联网 发布:我的淘宝流量来自哪里 编辑:程序博客网 时间:2024/06/09 14:37
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显示的执行计
阅读全文
0 0
- SQL Tuning 基础概述01
- SQL Tuning 基础概述02
- SQL Tuning 基础概述03
- SQL Tuning 基础概述04
- SQL Tuning 基础概述05
- SQL Tuning 基础概述06
- SQL Tuning 基础概述07
- SQL Tuning 基础概述08
- SQL Tuning
- SQL Tuning
- SQL Tuning
- SQL基础概述
- tuning 11--SQL Statement Tuning
- ORACLE9i_性能调优基础八(SQL tuning)
- SQL SEVER 概述 和 基础
- Camera tuning 基础知识点
- ORACLE PERFORMANCE SQL TUNING
- Tuning SQL note1
- 最小的K个数(剑指offer)
- maven中关于oracle驱动配置
- 斯坦福开学演讲:不要在不断的优秀里走向平庸!
- 利用新版Spring roo生成数据库表
- concurrent-04(volatile关键字)
- SQL Tuning 基础概述01
- java -jar -Dspring.config.location=conf/application.properties noah.jar
- 使用华硕xtion 摄像头遇到的问题(ros系统、turtlebot)
- Hadoop2.7.2集群搭建详解(三台)
- phaser anchor属性的使用方法 (6)
- 【原】React Native踩坑系列之--Configuration with name 'default' not found react-native
- Python编程细节(一)
- 【js】DOM基础
- javascript之BOM对象(三其他对象)