我们来看看该sort_area_size参数对创建索引时排序的具体影响:
|
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
|
SQL>
select
*
from
v$version;
BANNER
----------------------------------------------------------------
Oracle
Database
10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS
for
Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
/* 测试使用版本10.2.0.4 */
SQL> archive log list;
Database
log mode
No
Archive Mode
Automatic archival Disabled
Archive destination /s01/arch
Oldest online log
sequence
27
Current
log
sequence
34
/* 为了不受影响我们采用非归档模式 */
SQL> conn maclean/maclean
Connected.
SQL>
alter
session
set
workarea_size_policy=MANUAL;
Session altered.
/* 只有使用手动PGA管理时sort_area_size等参数才生效 */
SQL>
alter
session
set
db_file_multiblock_read_count=128;
Session altered.
/* 加大多块读参数帮助我们节约时间 */
SQL>
alter
session
set
"_sort_multiblock_read_count"
=128;
Session altered.
/* 10g中sort_multiblock_read_count成为隐式参数，我们尝试手动固定它 */
SQL>
set
timing
on
;
SQL>
alter
session
set
events
'10032 trace name context forever ,level 10'
;
Session altered.
Elapsed: 00:00:00.00
/* 在session级别设置10032事件，该事件帮助输出排序相关的统计信息*/
SQL>
drop
index
ind_youyus;
alter
session
set
sort_area_size=1048576;
alter
session
set
sort_area_size=1048576;
|
|
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
|
/* 注意10g目前存在一个bug，sort_area_size需要手动设置2次方能生效，否则无效！ */
create
index
ind_youyus
on
youyus(t1,t2) nologging;
Index
dropped.
Elapsed: 00:00:00.07
SQL>
Session altered.
Elapsed: 00:00:00.00
SQL>
Session altered.
Elapsed: 00:00:00.00
SQL>
Index
created.
Elapsed: 00:00:35.70
/* 以下为对应创建索引排序的10032 trace * /
---- Sort Parameters ------------------------------
sort_area_size 1048576
sort_area_retained_size 1048576
sort_multiblock_read_count 29
max
intermediate merge width 2
*** 2010-09-09 21:15:52.703
---- Sort Statistics ------------------------------
Initial runs 1
Input records 10000001
Output
records 10000001
Disk blocks 1st pass 58690
Total disk blocks used 58692
Total number
of
comparisons performed 10104798
Comparisons performed
by
in
-memory sort 10098798
Comparisons while searching
for
key
in
-memory 6000
Temp
segments allocated 1
Extents allocated 459
Uses version 2 sort
Does
not
use asynchronous IO
---- Run Directory Statistics ----
Run directory block reads (buffer cache) 2
Block pins (
for
run directory) 1
Block repins (
for
run directory) 1
---- Direct Write Statistics -----
Write slot
size
49152
Write slots used during
in
-memory sort 2
Number
of
direct writes 10011
Num blocks written (
with
direct write) 58690
Block pins (
for
sort records) 58690
Cached block repins (
for
sort records) 667
---- Direct Read Statistics ------
Size
of
read
slots
for
output
524288
Number
of
read
slots
for
output
2
Number
of
direct sync reads 58627
Number
of
blocks
read
synchronously 58690
---- End of Sort Statistics -----------------------
/* 可以看到这里实际的sort_multiblock_read_count是29，而非我们设置的128，而
max
intermediate merge width最大中间运行合并宽度为2；我们知道实际的SMRC有公式
MIN
(ROUND(SORT_AREA_SIZE/(2*2*BLOCK_SIZE))-1, _sort_multiblock_read_count)=
MIN
(32-1,128)=31，而此处的实际值为29； merge width = ROUND(SORT_AREA_SIZE/(2*SMRC*BLOCK_SIZE))-1= round( 1048576/(2*29*8k))-1= round(2.2)-1=2-1=1；看起来实际的公式有所修正。*/
SQL>
drop
index
ind_youyus;
alter
session
set
sort_area_size=524288000;
alter
session
set
sort_area_size=524288000;
create
index
ind_youyus
on
youyus(t1,t2) nologging;
Index
dropped.
Elapsed: 00:00:00.04
SQL>
Session altered.
Elapsed: 00:00:00.00
SQL>
Session altered.
Elapsed: 00:00:00.00
SQL>
Index
created.
Elapsed: 00:00:36.82
---- Sort Parameters ------------------------------
sort_area_size 524288000
sort_area_retained_size 524288000
sort_multiblock_read_count 128
max
intermediate merge width 225
*** 2010-09-09 21:32:06.517
---- Sort Statistics ------------------------------
Initial runs 2
Number
of
merges 1
Input records 10000001
Output
records 10000001
Disk blocks 1st pass 58690
Total disk blocks used 58692
Total number
of
comparisons performed 17571986
Comparisons performed
by
in
-memory sort 10098438
Comparisons performed during merge 7473532
Comparisons while searching
for
key
in
-memory 16
Temp
segments allocated 1
Extents allocated 459
Uses version 2 sort
Does
not
use asynchronous IO
---- Run Directory Statistics ----
Run directory block reads (buffer cache) 3
Block pins (
for
run directory) 1
Block repins (
for
run directory) 2
---- Direct Write Statistics -----
Write slot
size
1048576
Write slots used during
in
-memory sort 50
Number
of
direct writes 460
Num blocks written (
with
direct write) 58690
Block pins (
for
sort records) 58690
Cached block repins (
for
sort records) 1
---- Direct Read Statistics ------
Size
of
read
slots
for
output
1048576
Number
of
read
slots
for
output
500
Number
of
direct sync reads 58563
Number
of
blocks
read
synchronously 58690
---- End of Sort Statistics -----------------------
/* 10g中引入了新的排序算法， 排序初始化运行完成后，会保存初始数据集的键值到内存中，在进行数据集进行合并时，会根据键值来选择数据集。从trace文件中可以看到这样的统计信息:Comparisons while searching
for
key
in
-memory 16;*/
/* 可以看到write slot的大小也随sort_area_size变化，sort_area_size增大的同时Number
of
direct writes由10011次下降到460次,此外
read
slots的总大小(524288*2=1048576=sort_area_size，1048576*500=sort_area_size) */
/* 在合并merge阶段，因为读取缓存足够大，因此合并次数下降到1 */
|
to be continued ..............
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277650
