From time to time I check the site statistics to see if they give me any clues about why people are coming to blog – and recently I noticed that over the last year a particular referral from the OTN Database forum was had appeared fairly regularly – and it’s one that covers a small but significant optimizer detail that combines two crtical questions: why is the optimizer not using my index and why is the optimizer ignoring my hint under the heading “Index hint does not work”.
I’ll leave you to read the thread – but the short answer is NULL.
A hint is illegal if using it could produce the wrong answer, and indexes where every column is nullable won’t necessarily reference every row in its table.
In a recent article on changes to the calculation of cardinality with frequency histograms I made a comment about writing programs to create fake but realistic and complete frequency histograms.
As a follow-up to that comment, here’s a little script to demonstrate the method (for a numeric column), and a few points to remember when using this method.
(more…)
Here’s another of those ideas that are so fundamental that you should always keep them in mind when dealing with an Oracle database.
The fundamental strategy embedded in the optimizer is based on just two key points:
- How much data are you after.
- Where did you put it.
If you “tune” SQL by fiddling with it until it goes fast enough then you’ve missed the point and you’re wasting time.
If you start your tuning exercise by thinking about how much data you are supposed to collect and how randomly scattered it is, then you’re going to minimise the time you spend working out the best way of acquiring that data efficiently.
When I am in America for a few days I usually try to find a local user group and ask them if they’d like to arrange an evening slot for their members. (Last time I was at Oracle Open World I went so far as to do a day trip up to Calgary because I didn’t think I was likely to go there any other way).
Collaborate 09 was no exception, and I contacted the CFOUG who arranged an event on the Monday evening at which I did a presentation called “Optimising through Understanding” where I talk about the analysis that could go into one simple SQL statement, producing reasons why any of several different execution plans might be the most appropriate depending on circumstances.
The presentation is a variation of one I have now given several times – and there is even a voice-over webinar version of it that I linked to from an earlier blog item.
After the event James Taylor, the chairman of CFOUG, asked if I could let him have a copy of the presentation for their website – so I sent him a pdf file of the slides, and I’ve also posted the same pdf file on my blog.
One of my faviourite presentations in Collaborate 09 was a quick tip (30 minutes) on Tuning by Cardinality Feedback – i.e. comparing the optimizer’s predictions of cardinality with the actual rowcounts returned when you run the query.
The strategy is one that I first saw demonstrated in a presentation by Wolfgang Breitling a few years ago at one of the Hotsos seminars – but this latest example demonstrated an important variation on the theme in a short, precise, lesson.
(more…)
Not so much a little gem today as a little surprise and a few consequential thoughts. In a presentation on optimising star transformations the presenter pointed out that bitmap indexes are only available in Oracle Enterprise Edition.
(more…)
I attended four sessions today (one of them was a 2-hour session), and the highlight of the day for me was a lunchtime “quick tip” from Michelle Deng of Sanofi Aventis who gave a 30-minute talk with the title “Cardinality Analysis – a life saver for DBAs and Developers”.
(more…)
There’s a question on the Database General forum on OTN containing the following observation:
I have some chained rows in some of my tables. I have exported, truncated, import back the data in the table. Then collect the stats. But I have the same Chained_count. Nothing has changed.
(more…)
When you upgrade from 10.2.0.3 to 10.2.0.4 or 11g, watch out for SQL that depends on the existence of frequency histograms.
In 10.2.0.3 (and earlier versions of Oracle) if you have a frequency histogram on a column, and then use a predicate of the form:
where colX = {value not found in histogram}
then the optimizer would calcualte a cardinality of one for this predicate (having recorded a density of 1/(2 * num_rows) in the data dictionary (see comments 21 and 22 of this note on dbms_xplan).
(more…)
So much for my belief that I’d have some quiet time for catching up with a little internet gossip while attending Hotsos 2009.
The days were busy and I crashed out at about 7:00 pm (local time) each evening and was asleep by 7:30 pm and up by 2:00 a.m – then I spent the morning (until breakfast) writing up notes I had taken the day before. So hardly a moment for blogging or answering questions on OTN.
Best topic of Hotsos (for me, at any rate – there were lots of very good presentations): Amit Poddar’s presentation on the new “approximate NDV” mechanism that Oracle 11g uses to do a one pass, accurate, estimate of the number of distinct values in a column (no more massive sorts for “count(distinct)” and how it manages to keep a “synopsis” for each partition of a partitioned table so that there is no need to scan the entire table when you need to recalculate statistics for a single partition.
The mathematics is brilliant, and I’m going to have to review my previous strategy for stats collection as a consequence.
The upside to starting the day at 2:00 am in Dallas, by the way – no jet lag when I got home !
It’s very easy to forget that 10g creates statistics on an index as it builds (or rebuilds) the index – and this can lead to some oddities when you add a function-based index (or, as I tend to name them, index on virtual columns) to a table.
This point (along with a couple of other observations) came up in a discussion on OTN a little while ago, with some useful responses from Richard Foote (the “index king”), Christian Antognini (author of “Trouble-shooting Oracle Performance”) and a few others, including me.
In an earlier article on Cost Based Query Transformation, I showed an example of “complex view merging” where the optimizer turned an in-line aggregate view into a simple join with late aggregation.
In 11g, the optimizer can do the opposite transformation using a mechanism known as “Group by Placement”.
(more…)
Whenever you look at an execution plan, you must look at the predicate section (introduced in 9i) before you start trying to work out what’s gone wrong. Here’s an example (stripped down to the basic issue from a more complex query) that show’s the importance of this check.
(more…)
I see that Tom Kyte has found a nasty little bug waiting to trap a few unlucky people as they patch to 10.2.0.4, or upgrade to 11g.
(more…)
When it comes to setting the optimizer_mode parameter you often hear people say that first_rows_N (for one of the legal values of N) should be used for OLTP systems and all_rows should be used for decision support and data warehouse systems.
There is an element of truth in the statement – but it’s really a hangover from the early days of CBO, and remembrance of the old first_rows optimizer mode (** See footnote).
(more…)
