Addenda:
Events for hashing: When I wrote the book, I decided against including the event I use for dumping information about hash join activity, as the amount of information involved is quite large, and the time-pressure was beginning to tell. However there is not much you can do in Oracle 8.1.5 to see the detailed effect of hash joins, so I note here that you can get a hash join dump by using event 10104:
alter session set events '10104 trace name context forever, level 1';
I shall be writing up a lengthier document describing the two main strategies that appear when a hash joins starts, with examples of dumps - at present let me just say that it is quite a good idea to use a 10046 level 8 dump with the 10104 trace when you are investigating hash joins, as this allows you to see the interesting disk activity associated with large hash joins..
Events for sorting: I have mentioned event 10033 in this appendix (p.600) as a way of getting information detailed information about sort activity. This event does give some information about sorts to disc, as shown below, but the event described and the sample output in the book is from event 10032.
If you set event 10033, the output you get is a list of the write/merge passes that Oracle has to make to do the sort, for example:
Recording run at 401c21 for 24 blocks Recording run at 401c3a for 67 blocks Recording run at 401c7d for 48 blocks Recording run at 401cad for 48 blocks Recording run at 401cdd for 48 blocks Recording run at 401d0d for 24 blocks Recording run at 401d25 for 20 blocks Merging run at 401d25 for 20 blocks Merging run at 401c21 for 24 blocks Merging run at 401d0d for 24 blocks Merging run at 401c7d for 48 blocks Merging run at 401cad for 48 blocks Merging run at 401cdd for 48 blocks Merging run at 401c3a for 67 blocks
As you can see, Oracle lists the sections of sorted data that it is writing to disc, and then describes how it is re-reading them to merge them into order. To put this into context, the following listing is produced by setting both events at once with:
alter session set events '10032 trace name context forever, level 1'; alter session set events '10033 trace name context forever, level 1';
Recording run at 401c21 for 24 blocks
Recording run at 401c3a for 67 blocks
Recording run at 401c7d for 48 blocks
Recording run at 401cad for 48 blocks
Recording run at 401cdd for 48 blocks
Recording run at 401d0d for 24 blocks
Recording run at 401d25 for 20 blocks
---- Sort Parameters ------------------------------
sort_area_size 65536 -- Choked to get some disk i/o
sort_area_retained_size 65536 -- ditto
sort_multiblock_read_count 2
max intermediate merge width 7
Merging run at 401d25 for 20 blocks
Merging run at 401c21 for 24 blocks
Merging run at 401d0d for 24 blocks
Merging run at 401c7d for 48 blocks
Merging run at 401cad for 48 blocks
Merging run at 401cdd for 48 blocks
Merging run at 401c3a for 67 blocks
---- Sort Statistics ------------------------------
Initial runs 7 -- matches the number of runs written
Number of merges 1 -- all 7 runs merged in one pass
Input records 12175
Output records 12175
Disk blocks 1st pass 279
Total disk blocks used 281
Total number of comparisons performed 121434
Comparisons performed by in-memory sort 97836
Comparisons performed during merge 23598
Temp segments allocated 1
Extents allocated 1
---- Run Directory Statistics ----
Run directory block reads (buffer cache) 8
Block pins (for run directory) 1
Block repins (for run directory) 7
---- Direct Write Statistics -----
Write slot size 2048
Write slots used during in-memory sort 3
Number of direct writes 279
Num blocks written (with direct write) 279
Block pins (for sort records) 279
Cached block repins (for sort records) 11
Waits for async writes 3
---- Direct Read Statistics ------
Size of read slots for output 4096
Number of read slots for output 16
Number of direct sync reads 44
Number of blocks read synchronously 51
Number of direct async reads 117
Number of blocks read asynchronously 228
Waits for async reads 1
As Steve Adams points out in his December 2000 newsletter, when a sort gets very large, the sort time is most heavily governed by the number of merge passes that takes place; so the combination of 10032 and 10033 dumps may give you that extra little bit of information that allows you to pick the best parameters for the odd special sort.
Note, in particular that setting the sort_multiblock_read_count too high can SLOW DOWN the sort, because it encourages Oracle to merge larger sections of data from each run, which reduces the number of runs that can be handled in one pass. The following sample is from the same system and query as the previous one, but has an increased read count - note in particular how the 'max intermediate merge width has been reduce' as the 'sort_multiblock_read_count' has gone up::
Recording run at 401c21 for 24 blocks
Recording run at 401c3a for 67 blocks
Recording run at 401c7d for 48 blocks
Recording run at 401cad for 48 blocks
Recording run at 401cdd for 48 blocks
Recording run at 401d0d for 24 blocks
Recording run at 401d25 for 20 blocks
---- Sort Parameters ------------------------------
sort_area_size 65536
sort_area_retained_size 65536
sort_multiblock_read_count 7
max intermediate merge width 2
Merging run at 401d25 for 20 blocks
Merging run at 401c21 for 24 blocks
Recording run at 401d39 for 44 blocks -- More disc writes
Merging run at 401d0d for 24 blocks
Merging run at 401c7d for 48 blocks
Recording run at 401d65 for 72 blocks -- More disc writes
Merging run at 401cad for 48 blocks
Merging run at 401cdd for 48 blocks
Recording run at 401dad for 96 blocks -- More disc writes
Merging run at 401d39 for 44 blocks
Merging run at 401c3a for 67 blocks
Recording run at 401e0d for 110 blocks -- More disc writes
Merging run at 401d65 for 72 blocks
Merging run at 401dad for 96 blocks
Recording run at 401e7b for 168 blocks -- More disc writes
Merging run at 401e0d for 110 blocks -- Extra disc reads
Merging run at 401e7b for 168 blocks -- Extra disc reads
---- Sort Statistics ------------------------------
Initial runs 7
Intermediate runs 5
Number of merges 6
Input records 12175
Output records 12175
Disk blocks 1st pass 279
Total disk blocks used 771 -- Was 281 above
Total number of comparisons performed 125222
Comparisons performed by in-memory sort 97836
Comparisons performed during merge 27386
Temp segments allocated 1
Extents allocated 1
---- Run Directory Statistics ----
Run directory block reads (buffer cache) 18
Block pins (for run directory) 1
Block repins (for run directory) 17
---- Direct Write Statistics -----
Write slot size 2048
Write slots used during in-memory sort 3
Write slots used during merge 3
Number of direct writes 769 -- Was 279 above
Num blocks written (with direct write) 769
Block pins (for sort records) 769
Cached block repins (for sort records) 11
Waits for async writes 8
---- Direct Read Statistics ------
Size of read slots for merge phase 14336 -- Bigger read slot, but
Number of read slots for merge phase 4 -- fewer available
Size of read slots for output 16384
Number of read slots for output 4
Number of direct sync reads 84
Number of blocks read synchronously 158 -- Up from 51
Number of direct async reads 90
Number of blocks read asynchronously 611 -- Up from 228
Waits for async reads 14
This example has been contrived a little artificially to demonstrate the point - don't rush off and reduce your sort_multiblock_read_count to 2 just because that is the figure I have here. Remember though that it is in theory possible to run with this parameter set to give a 1MB read - and this isn't necessarily the best thing to do..
Errata:
I cheated by including an error in the addenda section. But just for clarity - change the references to event 10033 (p.600) in your book to read 10032.