Filter “Bug”

Here’s an example to remind you how important it is to look at the “Predicate Information” supplied with an execution plan. Here are two execution plans that look very similar in shape – a continuous set of steps inwards and downwards from parent to child, with no “multi-child” parent rows:

Fair Comparison

From time to time someone will post a question about query performance on the OTN database forum asking why one form of a query returns data almost immediately while another form of the query takes minutes to return the data.

Obviously there are all sorts of reasons – the optimizer is not perfect, and different transformations may take place that really do result in a huge differences in work done by two queries which return the same result set – but a very simple reason that can easily be overlooked is the front-end tool being used to run the query.

Quiz Night.

I was on a customer site recently where I needed to add a NOT NULL constraint to a table of 200 million rows – without taking any downtime. It’s not difficult (provided you are happy with a check constraint rather than a column definition.)

alter table t1
	add constraint t1_ck_colX_nn check (colX is not null)
	enable novalidate
;

The first step creates the constraint and enables it – but doesn’t validate it. This means that future data (and changes) will obey the constraint, but there may be illegal data already in the table that will not be checked. You will have an interruption to service doing this, as your session will wait to lock the table in share mode (mode 4) to add the constraint – so will be blocked by current update transactions, and will block new update transactions. In a typical OLTP system this should result in just a brief pause.

DMCA

Some readers have noticed that a few links to my blog seem to be broken. Don’t panic, it’s not permanent it’s just the result of Don Burleson losing his temper.

Let me start by telling you about DMCA, the “Digital Millenium Copyright Act”. DMCA is a mechanism designed to protect Internet service providers (ISPs) from being sued over content published by their customers by allowing them to act as a communication channel and staying out of the line of fire.

Index rebuilds

A couple of years ago I wrote about a poster on the OTN db forum who was puzzled by the fact that when he started rebuilding tables they got bigger.  (Bad luck, sometimes that’s what happens !)

A few days ago a related question appeared: I rebuilt some indexes and my query got slower. (Bad luck, sometimes that’s what happens – again!)

If you rebuild an index it’s physically different and its statistics are different. Plans can change and go slower because the index stats look sufficiently different; plans can stay the same and go slower because the index is physically different. 

I’ve added a couple of comments to the thread – there may still be some further mileage in it.

[Further reading on rebuilding indexes]

Cardinalilty One

I think anyone who has read Wolfgang Breitling’s material about the optimizer will be familiar with the concept of Cardinality Feedback and one particular detail that when Oracle gets a cardinality estimate of one for a “driving” table then there’s a good chance that the execution plan will go wrong. (That’s not rule, by the way, just a fairly common observation after things have gone wrong.)

A recent note on OTN reminded me of a particular scenario where this specific problem can occur. It’s not particularly common, but it may hit people who are building data warehouses from multiple different sources. We start with an unlikely looking data set and very simple query:

Index Space

I’ve just been reminded of a thread on OTN which turned into a Q&A about index space usage and various related topics. On re-reading it, I decided it was too good to waste in the backwaters of OTN, so here’s a link to it.

Index Space Utilization.

FBI Bug

Here’s an odd little bug (I think) in the optimizer that showed itself in a thread on the OTN database forum some time ago. The optimizer can choose an index which is NOT the cheapest index for satisfying a query against a single table. Here’s the demo – which I’ve run against 11.1.0.6 using an 8KB block size, ASSM and system allocated extent sizes:

Joins – MJ

The final join mechanism in my “all joins are nested loop joins” argument is the Merge Join – a join mechanism that depends on both its row sources being pre-sorted on the join columns. (For a quick reference list of URLs to all three articles in turn, see: Joins.)

Joins – HJ

In the second note on my thesis that “all joins are nested loop joins with different startup costs” I want to look at hash joins, and I’ll start by going back to the execution plan I posted on “Joins – NLJ”. (For a quick reference list of URLs to all three articles in turn, see: Joins.)

Joins – NLJ

This is part one of my thesis that “all joins are nested loop joins – it’s just the startup overheads that vary”; there will be a note on “Joins – HJ” and “Joins – MJ” to follow. (For a quick reference list of URLs to all three articles in turn, see: Joins.)

Joins

There are only three join mechanisms used by Oracle: merge join, hash join and nested loop join.

Here’s an important thought: all three join methods are nested loop joins with different startup cost.

Discuss.