JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Appendix C: Testing to destruction.


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.


Back to Book Index