传一个文档,这样通过比较max intermediate merge width 和Initial runs可以找到最节约的排序内存
*** Sample trace file: Event 10032 and 10033
Recording run at 40f609 for 3918 blocks
Recording run at 40e958 for 3936 blocks
Recording run at 40d8b8 for 3946 blocks
Recording run at 40c822 for 3925 blocks
Recording run at 40bb77 for 2236 blocks
---- Sort Parameters ------------------------------
sort_area_size 52428800
sort_area_retained_size 52428800
sort_multiblock_read_count 2
max intermediate merge width 678
Merging run at 40bb77 for 2236 blocks
Merging run at 40c822 for 3925 blocks
Merging run at 40d8b8 for 3946 blocks
Merging run at 40e958 for 3936 blocks
Merging run at 40f609 for 3918 blocks
Total number of blocks to read: 17961 blocks
---- Sort Statistics ------------------------------
Initial runs 5
Number of merges 1
Input records 6087978
Output records 6087978
Disk blocks 1st pass 17961
Total disk blocks used 17963
Total number of comparisons performed 132818401
Comparisons performed by in-memory sort 118556515
Comparisons performed during merge 14261886
Temp segments allocated 1
Extents allocated 36
---- Run Directory Statistics ----
Run directory block reads (buffer cache) 6
Block pins (for run directory) 1
Block repins (for run directory) 5
---- Direct Write Statistics -----
Write slot size 1048576
Write slots used during in-memory sort 5
Number of direct writes 142
Num blocks written (with direct write) 17961
Block pins (for sort records) 17961
Cached block repins (for sort records) 4
Waits for async writes 19
---- Direct Read Statistics ------
Size of read slots for output 1048576
Number of read slots for output 50
Number of direct sync reads 51
Number of blocks read synchronously 686
Number of direct async reads 140
Number of blocks read asynchronously 17275
Waits for async reads 45
再来一个merge多次的
*** 2005-01-27 07:06:05.615
*** SESSION ID
17.3977) 2005-01-27 07:06:05.599
---- Sort Parameters ------------------------------
sort_area_size 65536
sort_area_retained_size 65536
sort_multiblock_read_count 1
max intermediate merge width 3
---- Sort Parameters ------------------------------
sort_area_size 65536
sort_area_retained_size 65536
sort_multiblock_read_count 1
max intermediate merge width 3
Recording run at 400090 for 5 blocks
Recording run at 400096 for 54 blocks
Recording run at 4085d4 for 52 blocks
Recording run at 408418 for 2 blocks
Recording run at 40841a for 5 blocks
Recording run at 40841f for 57 blocks
Recording run at 408618 for 52 blocks
Recording run at 40864c for 3 blocks
Recording run at 40864f for 5 blocks
Recording run at 408654 for 56 blocks
Recording run at 40868c for 52 blocks
Recording run at 4086c0 for 4 blocks
Recording run at 4086c4 for 59 blocks
Recording run at 4086ff for 52 blocks
Recording run at 408733 for 2 blocks
Recording run at 408735 for 3 blocks
---- Sort Parameters ------------------------------
sort_area_size 65536
sort_area_retained_size 65536
sort_multiblock_read_count 1
max intermediate merge width 3
Merging run at 408418 for 2 blocks
Merging run at 408733 for 2 blocks
Recording run at 408738 for 3 blocks
Merging run at 40864c for 3 blocks
Merging run at 408735 for 3 blocks
Merging run at 408738 for 3 blocks
Recording run at 40873b for 8 blocks
Merging run at 4086c0 for 4 blocks
Merging run at 400090 for 5 blocks
Merging run at 40841a for 5 blocks
Recording run at 408743 for 13 blocks
Merging run at 40864f for 5 blocks
Merging run at 40873b for 8 blocks
Merging run at 408743 for 13 blocks
Recording run at 408750 for 25 blocks
Merging run at 408750 for 25 blocks
Merging run at 400096 for 54 blocks
Merging run at 40841f for 57 blocks
Recording run at 408769 for 135 blocks
Merging run at 4085d4 for 52 blocks
Merging run at 408618 for 52 blocks
Merging run at 408654 for 56 blocks
Recording run at 4087f0 for 159 blocks
Merging run at 40868c for 52 blocks
Merging run at 4086ff for 52 blocks
Merging run at 4086c4 for 59 blocks
Recording run at 40862c for 162 blocks
Merging run at 408769 for 135 blocks
Merging run at 40862c for 162 blocks
Merging run at 4087f0 for 159 blocks
Recording run at 4086ae for 455 blocks
Merging run at 4086ae for 455 blocks
---- Sort Statistics ------------------------------
Initial runs 16
Intermediate runs 8
Number of merges 8
Input records 98684
Output records 98684
Disk blocks 1st pass 463
Total disk blocks used 659
Total number of comparisons performed 531355
Comparisons performed by in-memory sort 195082
Comparisons performed during merge 336273
Temp segments allocated 1
Extents allocated 10
---- Run Directory Statistics ----
Run directory block reads (buffer cache) 33
Block pins (for run directory) 1
Block repins (for run directory) 32
---- Direct Write Statistics -----
Write slot size 8192
Write slots used during in-memory sort 2
Write slots used during merge 2
Number of direct writes 1423
Num blocks written (with direct write) 1423
Block pins (for sort records) 1423
Cached block repins (for sort records) 94
Waits for async writes 505
---- Direct Read Statistics ------
Size of read slots for merge phase 8192
Number of read slots for merge phase 6
Size of read slots for output 32768
Number of read slots for output 2
Number of direct sync reads 586
Number of blocks read synchronously 589
Number of direct async reads 574
Number of blocks read asynchronously 834
Waits for async reads 11