sort_area_size参数的一些表现

我们来看看该sort_area_size参数对创建索引时排序的具体影响:

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;

/* 注意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 ..............