Ch.4 Code Depot (30th Dec 2005)
Rounding errors (24th Mar 2006)
Chained Rows and clustering_factor (10th Feb 2006)
_sort_elimination_cost_ratio (10th Jan 2006)
ASSM and clustering_factor (10th Jan 2006)
Index Only Anomaly (30th Dec 2005)
Index Selectivity (14th Dec 2005)
Typos (8th Mar 2006)
Density and num_distinct (10th Feb 2006)
The middle of an index (10th Feb 2006)
Page 83, sidebar: I compare the way using system statistics increases the costs of multi-block reads against the way that using optimizer_index_cost_adj (typically) decreases the cost of single block reads, and point out that the latter option results in increased problems of rounding errors. In fact you can set event 10183 to make Oracle use non-rounded figures in its calculations to avoid this problem. I didn’t mention it in the book, because I see it as introducing an undocumented feature to handle a problem caused by abusing a feature that is poorly documented.
However, Martin Frauendorfer has informed me that this event is well known to users of SAP, where the standard recommendation is to set optimizer_index_cost_adj to 10, and then enable event 10183. So if you are a SAP user, you may want to know about this. The same approach may well be useful (but only after receiving vendor approval) for other large 3rd party packages. Bear in mind though, that if you are already using either of the cost adjusting parameters, enabling this event will have wide-ranging effects on lots of your costs, so the access paths for many statements may change simultaneously – and some of them will be changes for the worse.
Page 68, penultimate paragraph: Another possible special case for the clustering factor. If you have lots of long rows, which chain into multiple blocks, that’s another case where the clustering_factor could be smaller than the number of blocks in the table – after all, the index only knows about the position about the first row-piece. Imagine a table where every row fills exactly four blocks – the code that counts the clustering_factor can only know about one block in four.
Less likely, but still technically possible, you could manage to get the same effect from migrated rows – i.e. rows that could fit into a single block, but have been moved to a new block as they were updated, leaving a forwarding address in the original block. Again, the clustering_factor is only looking at the original address, and knows nothing about the extra block.
Thanks to Yong Huang for pointing out this detail.
Page 76, Side-bar on first_rows optimization: I reference the hidden parameter _sort_elimination_cost_ratio and the possibility of that you could change it to affect the optimizer’s enthusiasm for using indexes for order by clauses under first_rows optimization. Of course, by the time you get to Oracle 10.2, you won’t be using first_rows optimization – but if you are, and if you have played with this parameter, be aware that 10.2 seems to ignore it – so some of your execution plans may change “for no apparent reason” on the upgrade.
Page 68, penultimate paragraph: I point out that the smallest possible value for the clustering_factor has to be the same as the number of blocks in the table. It occurred to me just a little too late that if you create a small table in a tablespace using ASSM (automatic segment space management), you could find that inserting a single row in the table could (for example) make Oracle format the last 16 blocks of a 128 block extent – that’s just the way things happen under ASSM. If that did happen, the ‘number of blocks’ reported for the table would be 128, but the clustering_factor would be just one.
So the statement is no longer true – but only under slightly quirky circumstances. (And, if you haven’t read ahead to the chapter on bitmap indexes – the statement doesn’t apply to them anyway because Oracle is using the column for something completely different).
One of the oddities of learning about Oracle is the frequency with which you discover things by accident. This is how I came to discover an error in the costing of index-only queries when the index is a function-based index (or index with virtual columns, as I prefer to name them).
I was testing a claim I had seen in an article on the Internet that Oracle costs for index-prefetching, and I happened to start my test by cloning a script I had previously used to create an index with the definition (lpad(n1,30)). Index prefetching usually happens on large index range scans, or index full scans., so I decided to check the costing claim with a query that did the following:
/*+ index(t1) */
lpad(n1,30) is not null
Hinted in this way, I could see that the optimizer chose an index full scan,
Execution Plan (188.8.131.52)
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=801 Card=100000 Bytes=500000)
1 0 INDEX (FULL SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=587 Card=100000 Bytes=500000)
I could confirm that prefetching was supposed to be taking place because the 10053 trace file included the statement: prefetching is on for T1_I1.
Notice the cost of the query, though – the 587 (line 1) is the cost of doing the index full scan on my test data using the standard formula (see page 77), so why does the total cost of the query come out as 801 (line 0). The difference of 214 just happens to be the cost of sorting the data (which I could check by forcing a full tablescan with sort) even though Oracle does not sort the data. I haven’t tested this problem exhaustively, but as a first approximation, it is only relevant to indexes with virtual columns – and is probably only relevant if you reference the virtual column in the query.
This bug is fixed by 10.1.0.4 onwards. The script to build my test case is included in the download for this chapter as index_only_bug.sql. As ever, though, even a bug-fix is a threat on an upgrade. Do you use “function-based” indexes, and if so are some of your queries currently NOT using an index but waiting to switch plans on the next upgrade because the cost of using the index drops in 10g as the bug gets fixed.
There isn’t a really appropriate place for this note about index selectivity and bind variables – but Chapter 4 seems to be the best place. I’ve had a note from Boris Dali, who had been experimenting with the script partition.sql in Chapter 2, which was demonstrating the effects of table level and partition level statistics on queries involving bind variables. While investigating this further, Boris set up a couple of examples with indexes, and then discovered an anomaly that turned out to be relevant not only to partitioned tables, but to ordinary tables. (I have added script index_bind_sel.sql to the download for chapter 4 to demonstrate this).
In 9i and 10g, you have to set the hidden parameter _optim_peek_user_binds to false to run simple tests, but you will find that Oracle does not use the standard 5% (and 5% * 5%) for range scans in all the calculations involving bind variables. Specifically, there is an alternative calculation that is used to generate a number (which isn’t a real selectivity, but gets named as the selectivity) that can be used for estimating the number of leaf blocks to be visited.
The following example of a 10g trace file for the query: select count(*) from t1 where rep_col between :b1 and :b2; demonstrates the concept.
SINGLE TABLE ACCESS PATH
Size: 4 NDV: 500 Nulls: 0 Density: 2.0000e-003 Min: 0 Max: 499
TABLE: T1 Alias: T1
Original Card: 250000 Rounded: 625 Computed: 625.00 Non Adjusted: 625.00
^^^^^^ ^^^^^^ 625/250000 = 1/400
Access Path: table-scan Resc: 401 Resp: 401
Access Path: index (index-ffs)
rsc_cpu: 0 rsc_io: 81
ix_sel: 0.0000e+000 ix_sel_with_filters: 1.0000e+000
Access Path: index-ffs Resc: 81 Resp: 81
Access Path: index (index-only)
rsc_cpu: 0 rsc_io: 5
ix_sel: 4.5000e-003 ix_sel_with_filters: 4.5000e-003
BEST_CST: 5.00 PATH: 4 Degree: 1
As you can see, to work out the number of rows returned, Oracle has used 0.0025 (or 5% of 5%) as the selectivity – the standard fixed value for a closed range using bind variables. So the cardinality is 250,000 * 0.0025 = 625, as highlighted.
But if you check the last line but one, where Oracle is working out the cost of using an index-only range scan, you see the odd value 4.50000-003, or in it’s more usual form: 0.0045. Where did this come from ? Who knows!
For open ranges (colx > :bind, col < :bind) Oracle uses a formula based on the density of the column, but with a lower bound of 0.009; and for closed ranges (colx between :bind1 and :bind2) the formula has a lower bound of 0.0045.
Remember, this is not a real selectivity – it is just a number that has been derived as a suitable value to stick into the cost equation at the point where the optimizer is trying to work out the number of leaf-blocks to be visited, i.e. the line that goes ceil(selectivity[ix_sel] * leaf_blocks)
Page 72, formulae in middle of page:
ceiling(9,745 * 0.00082105) -- 81
ceiling(9,745 * 0.0082105) -- 81
Thanks to Scot Martin for picking this one up (if you can’t spot it straight away, there’s a spare zero after the decimal point in the first line).
Page 66, section Effective Index Selectivity, second para: I mention the density and num_distinct for columns. I should have mentioned that these values come from the views user_tab_columns, user_tab_cols, or user_tab_col_statistics (or their partitioned equivalents).
Page 74, second paragraph: I make the comment: “Because as soon as we have a range scan on a column used somewhere in the middle of an index definition ..” It would be better to phrase this as “the earlier part of an index definition”, because the problem would appear if you happened to use a range-scan the first column of a multi-column index.