Row size in sorts (19th Jan 2008)
Typos (Updated 20th Feb 2006)
Freeing memory (2nd Dec 2005)
Max_area_size (4th Nov 2005)
Rowids (4th Nov 2005)
Page 372: In the last bullet point at the end of the page “Row size” I have the formula for the row size as 12 + sum(avg_col_length) + (n – 1), where n is the number of columns involved. This is wrong. The formula (as derived by Vladimir Begun on his blog is 10 + sum(avg_col_length) + ceil(sum(avg_col_length)/10). I’m not sure how I managed to get the formula I did, when it’s so easy to show that it’s wrong, but at the very least I did not do enough tests to disprove my hypothesis once I had formed it.
Page 393: At the very top of the page, there is a comment about memory being freed properly when you are running with workarea_size_policy set to auto. Technically, this happens if you have set the pga_aggregate_target to a non-zero value, which causes the hidden parameter _use_real_free_heap to be set to true.
Page 372: I spotted the first error in Practical Oracle 8i a few seconds after the first copy had landed on my front door-step. I spotted the first error in Cost Based Oracle before the printer had even finished printing it – I woke up at three a.m. a few days before the book came out, realizing that I had introduced an error in the last pass of proof-reading the galley pages.
In the bullet point Max Area Size, you will find the comment: “When the two values differ, the calculation of cost seems to be based on the Max area size, rather than the Area size.” This is the wrong way round – for sorting, the optimizer uses the Area size for the calculation of cost, not the Max area size. (This is the opposite way around to the calculations used for hash joins).
This error is repeated on page 373, in the bullet point Initial runs, which says: “You can derive this as Block to Sort * block size / Max Area size.” Again, you should replace Max Area size with Area size.
Page 361, last line: There is a reference to event 1033 – which also appears in the index – this should be event 10033.
Page 358, after the first paragraph there is a little table of space usage, of which the first line reads:
6 bytes for the length of the entry 00000008
As you can tell by checking the entry, this should be 4 bytes, not 6 bytes.
Page 387, fourth paragraph, lines 3-4
The earlier query did not lose it’s
joins predicate though transitive closure – so …
This almost incomprehensible statement should be:
The earlier query did not lose its join predicate through transitive
closure – so …
Page 392, the section titled Indexes points out that when you are doing the calculation of memory for building an index that you need to allow for the rowid as an extra column to be sorted. This is correct, but I then quote the lengths of the rowids as they appear in the final index – which is not necessarily the same as the lengths they take up whilst the sort is taking place. For example, the rowid for a simple B-tree index seems to use the full 10 byte extended rowid in the dump to disc if the sort is too large to complete in memory, not the 6 byte restricted rowid that ends up in the index.