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 …………..

Comments

  1. admin says

    Quote
    “排序(sort)算法

    在Oracle的排序过程中,首先将获取到的数据放入一块私有内存区(Sort Area)中进行排序。如果需要排序的数据太大,无法一次在Sort Area中完成全部数据的排序,就会将Sort Area中排好序的数据直接写入(Direct Write,数据不被cache)临时空间作为一个数据集存在。当所有数据都在内存中排过序并写入了磁盘后,就会将磁盘上的数据集进行合并排序(Merge Sort)。合并排序是一个递归过程,直到所有数据集都被合入一个数据集,排序才算完成。

    初始化运行(Initial Runs)

    数据最初在Sort Area中排序的过程被称为初始化运行。Sort Area的80~90%的空间需要被用于读缓冲,其他空间则被用于写缓冲。如果我们知道有多少数据(可以通过需要排序的记录数、记录平均长度已经数据块大小估算出来)需要进行排序,那么就可以用下面的公式来估算初始化运行的次数(也就是会产生多少个初始数据集),
    Initial Runs = CEIL(SORT_DATA_BLOKCS/ROUND(0.8*SORT_AREA_SIZE/BLOCK_SIZE))
    合并(Merges)

    在进行合并时,可以同时合并2个或2个以上的数据集。一次合并多少个数据集就称为合并宽度(Merge Width)。合并也是在Sort Area中完成的,进行合并之前,需要将数据从磁盘直接读入(Direct Read)内存中。和全表扫描的多数据块读(MBRC)类似,对排序数据块的读取也可以是多个的,由 sort_multiblock_read_count(SMRC,这个参数在9i后是隐含参数)控制一次读取的数据块数。不过,请注意,这个参数控制的是一次能读取的最大数据块数,而实际上一次能读取的数据块数是由sort_area_size、数据块大小等数据计算得来的。要进行合并操作,最少需要夺取2个数据集;如果使用了异步IO(disk_asynch_io=TRUE),需要有2块读缓冲。因此实际的SMRC可以按照以下公式计算:
    SMRC = MIN(ROUND(SORT_AREA_SIZE/(2*2*BLOCK_SIZE))-1, _sort_multiblock_read_count)

    有了SMRC,合并宽度也可以计算出来了,
    merge width = ROUND(SORT_AREA_SIZE/(2*SMRC*BLOCK_SIZE))-1

    合并是个递归过程,直到所有数据集被合为一个数据集。每一轮合并过程中,如果还有少于合并宽度的数据集没有被合并,则会在下一轮中再进行合并。我们创建了以下函数来计算合并次数。

    注意:除最后一次合并外,所有合并都被称为中间运行(Intermediate run)。
    SQL代码

    SQL> create or replace function sort_merges(init_runs number, width number)
      2    return number as
      3    i number := init_runs;
      4    n number := 0;
      5  begin
      6    while i > width loop
      7      n := n + floor(i / width);
      8      i := floor(i / width) + mod(i, width);
      9    end loop;
     10    if i > 0 then
     11      n := n + 1;
     12    end if;
     13    return n;
     14  end;
     15  /
    Function created.
    

    我们来看一个例子:sort area大小为64k,估算到有721个数据块需要进行排序,数据块大小为8K。通过以下查询语句,我们就可以得到这次排序的相关统计数据。
    SQL代码

    SQL> select init_runs,
      2         SMRC,
      3         merge_width,
      4         sort_merges(init_runs, merge_width) merges
      5    from (select init_runs,
      6                 SMRC,
      7                 round(sort_area_size / (2 * SMRC * block_size), 0) - 1 as merge_width
      8            from (select sort_blocks,
      9                         sort_area_size,
     10                         block_size,
     11                         ceil(sort_blocks /
     12                              round(0.8 * sort_area_size / block_size, 0)) init_runs,
     13                         round(sort_area_size / (2 * 2 * block_size), 0) - 1 SMRC
     14                    from (select 721   as sort_blocks,
     15                                 65536 as sort_area_size,
     16                                 8192  as block_size
     17                            from dual)));
    
     INIT_RUNS       SMRC MERGE_WIDTH     MERGES
    ---------- ---------- ----------- ----------
           121          1           3         60
    

    新的排序算法

    从10gR2之后,oracle引入了新的排序算法以减少排序时内存和CPU的消耗。新算法由参数”_new_sort”控制,默认为TRUE。新算法在以下几个方面进行了改进。

    *内存排序
    对于那些仅需要在内存中就可以完成的小数据集的排序,新算法需要更少的内存
    *合并排序
    初始化运行完成后,会保存初始数据集的键值到内存中,在进行数据集进行合并时,会根据键值来选择数据集。从trace文件中可以看到一条新的统计数据:
    Comparisons while searching for key in-memory 120
    新算法过程中,合并过程中用到的临时数据块更容易被重用,节省磁盘空间
    Trace

    我们可以通过10031, 10032, 10033事件对排序过程进行跟踪,跟踪内容可以帮助我们更好的理解排序过程及其性能。下面的跟踪文件就是我们之前例子的产生的。
    SQL代码

       1. Recording run at 401ca9 for 6 blocks
       2. Recording run at 401cb0 for 6 blocks
       3. Recording run at 401cb6 for 6 blocks
       4. Recording run at 401d3c for 6 blocks
       5. Recording run at 401d42 for 6 blocks
       6. Recording run at 401d48 for 6 blocks
       7. ...
       8. ---- Sort Parameters ------------------------------
       9. sort_area_size                    65536
      10. sort_area_retained_size           65536
      11. sort_multiblock_read_count        1
      12. max intermediate merge width      3
      13. Merging run at 401d1a for 1 blocks
      14. Merging run at 401c3a for 6 blocks
      15. Total number of blocks to read: 7 blocks
      16. Recording run at 401d1b for 7 blocks
      17. Merging run at 401c40 for 6 blocks
      18. Merging run at 401c46 for 6 blocks
      19. Merging run at 401c5c for 6 blocks
      20. Total number of blocks to read: 18 blocks
      21. Recording run at 401d22 for 17 blocks
      22. ...
      23. ---- Sort Statistics ------------------------------
      24. Initial runs                              121
      25. Intermediate runs                         59
      26. Number of merges                          60
      27. Input records                             47582
      28. Output records                            47582
      29. Disk blocks 1st pass                      721
      30. Total disk blocks used                    896
      31. Total number of comparisons performed     651813
      32.   Comparisons performed by in-memory sort 320660
      33.   Comparisons performed during merge      331033
      34.   Comparisons while searching for key in-memory 120
      35. Temp segments allocated                   1
      36. Extents allocated                         56
      37. Uses version 2 sort
      38. Uses asynchronous IO
      39.     ---- Run Directory Statistics ----
      40. Run directory block reads (buffer cache)  240
      41. Block pins (for run directory)            1
      42. Block repins (for run directory)          239
      43.     ---- Direct Write Statistics -----
      44. Write slot size                           8192
      45. Write slots used during in-memory sort    2
      46. Number of direct writes                   2884
      47. Num blocks written (with direct write)    2884
      48. Block pins (for sort records)             2884
      49. Waits for async writes                    1731
      50.     ---- Direct Read Statistics ------
      51. Size of read slots for merge phase        8192
      52. Number of read slots for merge phase      6
      53. Size of read slots for output             8192
      54. Number of read slots for output           8
      55. Number of direct sync reads               1498
      56. Number of blocks read synchronously       1498
      57. Number of direct async reads              1386
      58. Number of blocks read asynchronously      1386
      59. Waits for async reads                     347
      60. ---- End of Sort Statistics -----------------------
    


    from http://www.hellodba.com/Doc/oracle_sorting_cn.htm

  2. 对我非常有用,多谢!

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号