Not only are my regular blog readers a good deal better looking than the average person, but they’re quite a bit smarter as well As most people have correctly identified, the answer I was after to my previous Min/Max Quiz is that Option 1 is indeed the odd one out, as it’s the only option [...]
Jonathan Lewis recently wrote a really nice blog piece blevel=1 on the dangers of an index toggling between BLEVEL 1 and BLEVEL 2. I thought it would be useful to demonstrate this issue with a quick demo (Note: this example is on 22.214.171.124, with an 8K block size). First, create a simple little table with 336,000 rows [...]
When one thinks about a column that might benefit from being indexed, one generally considers columns with lots of different values so that the selectivity of the column is such that relatively few rows get selected, making the index appealing to the cost based optimizer. There are of course many exceptions to this generalisation [...]
Here’s one of those quick answers I give sometimes on forums or newsgroups. I forget when I wrote this, and when, and what the specific question was – but it was something to do with rebuilding an index on a small table where data was constantly being deleted and inserted.
Another problem with high insert/delete rates appears with very small indexes.
If you have a table that is small but constantly recycles its space you may also find you have an index where the number of leaf blocks puts you close to the borderline between having blevel = 1 and blevel = 2. If the size crosses that border occasionally and the statistics are updated to reflect the change – which is quite likely for a table subject to lots of updates and deletes if you have automatic stats collection enabled – then execution plans could change, resulting in dramatic changes in performance.
The workaround is fairly obvious – don’t let Oracle collect stats automatically on that table, instead create a stats-collection strategy for eliminating the change in blevel. For example, keep the stats locked except when you run your own code to deal with the stats, making sure that you overwrite the index blevel with 1 even if it has just crossed the boundary to 2.
Footnote: the reason why a change from 1 to 2 is dramatic is because Oracle ignores the blevel in the optimizer arithmetic when it is set to 1; so the change from 1 to 2 actually has the impact of a change from zero to 2. Then the cost of a nested loop access is “cost of single access multiplied by number of times you do it” – so the sudden appearance of a 2 in the formula gives an increment in cost of “2 * number of times you visit the table” of your small table is the second table in a nested loop – and suddenly a nested loop becomes much more expensive without a real change in the data size.
Footnote 2: it should be obvious that you don’t need to rebuild the index once you know what the problem is; but since we’re talking about a small index with a blevel that is usually 1 it probably won’t take more than a fraction of a second to rebuild the index and there’s a fair chance you can find a safe moment to do it. In terms of complexity the solution is just as simple as the stats solution – so you might as well consider it. The only thing you need to be careful about is that you don’t happen to rebuild the index at a time when the blevel is likely to be 2.
Footnote 3: For an example of the type of code that will adjust the blevel of an index see this URL. (Note, the example talks about copying stats from one place to another – but the principle is the same.)
I've already outlined in one of my previous posts that getting a reasonable cardinality estimate for multi-column joins can be tricky, in particular when dealing with correlated column values in the join columns.
Since Oracle 10g several "Multi-Column Join Cardinality" sanity checks have been introduced that prevent a multi-column join from producing too low join cardinalities - this is controlled via the "_optimizer_join_sel_sanity_check" internal parameter that defaults to true from 10g on.
It looks like Oracle has introduced with the Oracle 126.96.36.199 patch set a new "cost is time" model for the time estimate of the Cost-Based Optimizer (CBO).
In order to understand the implications let me summarize the evolution of the CBO in terms of cost / time estimate so far:
1. Oracle 7 and 8
Here is a nice example of what Oracle 188.8.131.52 is able to do with a subquery inside an expression. It can unnest it – that is a new 184.108.40.206 feature of the transformation part of the CBO.
This is just a short note prompted by a recent thread on the OTN forums. In recent versions Oracle changes the costs of a full table scan (FTS or index fast full scan / IFFS) quite dramatically if the "flashback query" clause gets used.
It looks like that it simply uses the number of blocks of the segment as I/O cost for the FTS operation, quite similar to setting the "db_file_multiblock_read_count" ("dbfmbrc"), or from 10g on more precisely the "_db_file_optimizer_read_count", to 1 (but be aware of the MBRC setting of WORKLOAD System Statistics, see comments below) for the cost estimate of the segment in question.
This can lead to some silly plans depending on the available other access paths as can be seen from the thread mentioned.
The Cost Based Optimizer (CBO) supports since at least Oracle 9i the automatic generation of additional predicates based on transitive closure.
In principle this means:
If a = b and b = c then the CBO can infer a = c
As so often with these optimizations the purpose of these automatically generated additional predicates is to allow the optimizer finding potentially more efficient access paths, like an index usage or earlier filtering reducing the amount of data to process.
Being a very reserved British type of character I’m not really one to make a big fuss about advertising myself, which is why it’s taken me five years to realise that I ought to make it easy for people to find the free download of Chapter 5 (Clustering Factor) of Cost Based Oracle Fundamentals.
Apress changes the relevant URL from time to time, and I’ve just discovered that they’ve now bundled the pdf file of the chapter into this zip file.
The thing that prompted me to post this special note was that some time ago Mohamed Houri translated the chapter into French as a gesture of appreciation for the fact that I had written the book and Apress has given me permission to post the translation, which is this pdf file.
While I’m on the topic of French translations I’ll just add a temporary note to point people to the listing of articles translated into French by Franck Pachot, where I’ve just added seven new items, five of mine and two from Greg Rahn.