Cardinality

I received an email recently which said:

I have a question about Page 54 of Cost Based Oracle.

10g Update
As with equalities, 10.1.0.4 suddenly changes what happens when you fall outside the low/high limits. The arithmetic, or picture, used for ranges outside the limits is just the same as the new mechanism we saw for equalities.

I can’t work out how the formula provides a cardinality of 82 for the values ” month_no between 14 and 17″. Can you please elaborate on the out of bounds formula ?

The example in the book was only intended to show the general pattern of behaviour, and I didn’t attempt to explain what I thought the optimizer was doing – which is an odd little oversight.

Counting

In one of those little coincidences that seem to happen so often a question came up on the comp.databases.oracle. server news group that has an answer in a blog note I’d written just a few of days earlier . The question was simply asking for a way of counting the number of rows in each partition of a partitioned table.

Flash Cache

You may have heard about “flash cache” for Oracle 11.2 and Exadata – the clever trick where the code can use flash memory [corrected, thanks to Yas, comment #2] for data that’s “fairly popular” but not quite popular enough to be kept in the data cache by the LRU. The code works to keep a copy of the more useful data on the faster medium to avoid re-reading it from disk.

The technology was originally restricted to Exadata2, but as Guy Harrison reports, a recent patch allows it to run on Oracle Enterprise Linux even when you don’t have Exadata.

Update: See Kevin Closson’s blog item (pingback from comment #4 below) for corrections to this post.

Drop Table

Here’s an interesting question raised by Uwe Hesse: “if you drop a table when someone else is querying it, what happens to their query?”

It’s an interesting demonstration of how many features you have to consider before you reach a conclusion – and while you may start by assuming that the question is irrelevant to a production system, when you think a little more carefully that’s not necessarily the case.

ora_hash function

A few weeks ago I wrote a note demonstrating the way in which Oracle’s strategy for hash partitioning is engineered to give an even data distribution when the number of partitions is a power of two. In one of the follow-up comments, Christo Kutrovsky pre-empted my planned follow-up by mentioning the hashing function ora_hash() that appeared in 10g.

OOW09 Video

I’ve just discovered I’m on YouTube in an impromptu video interview I gave while I was at Oracle World this year. It’s only three minutes, and doesn’t have a lot of technical content, but here’s the URL.

Footnote: Rumour has it that next year the event will be rebranded as My Oracle World – implemented entirely in Flash.

 

No change

Every now and again I see a note on the OTN database forum, or Metalink, or the newsgroup asking why the time taken to run a query can vary so much between executions … even when the plan didn’t change (and the bind variable used as inputs didn’t change).

Just off the top of my head, here are a few possibilities:

• Sometimes the required data is perfectly cached, sometimes it isn’t
• Sometimes other processes are using the CPU or disks very aggressively
• The volume of data can change significantly over short periods of time
• A long-running transaction may require you to do more work generating read-consistent data sometimes.
• A small change in data can cause a huge  variation in the number of times a subquery runs
• A plan involving a hash join or sort may execute optimally on one occasion and multi-pass on another
• A parallel plan may get all the PX slaves it wants on one occasion and none of them on another

Any further suggestions are welcome – remember, though, we are assuming that (a) the plan really didn’t change, (b) the input bind values really didn’t change, (c) your environment didn’t change and (d) there isn’t a mad scientist (or novice DBA) in the background doing unusual things to a production system.

Index Freelists

A few months ago Saibabu Devabhaktuni let me know of an interesting issue he had had with indexes misbehaving, and pointed me to a blog note he had written to describe it. In the note he supplies a test case where Oracle walks through hundreds of blocks on an index freelist to find a single block that could be used as the target for a leaf node split.

The most interesting thing to note is that Sai has demonstrated a case where issuing a commit between the bulk delete and the subsequent insert results in a performance problem. On the other hand if you read my description in this blog posting, you’ll see that I have described a scenario where the commit between the delete and the insert is a very good idea – especially if you can fit a coalesce in between to clear up the mess.

Did you know …

… a few things about the use of index space to suprise your friends and amaze your colleagues:

• If you use “bigfile” tablespaces for your tables this can result in some indexes becoming more space-efficient than they would be otherwise.
• Creating tables in tablespaces built from multiple datafiles may cause some of their indexes to be less space-efficient than they would otherwise be.
• Unless you store tables in different tablespaces from indexes, rebuilding indexes can allow the indexes that you haven’t rebuilt to become less space-efficient.
• Dropping (or truncating) a table may allow unrelated indexes to become less space-efficient
• Using the partition exchange mechanism for data loading may make some unrelated indexes become less space-efficient.
• Some classes of index will be less space-efficient in a RAC system than they would be in a single-instance system.
• Using the “shrink” option on a table may cause some of its indexes to get bigger.

On the plus side: although there are many activities that contribute to indexes  being bigger than they would be if they were newly created, there aren’t many cases where the benefit of recreating them (or restructuring your database) is worth the effort. Note that many “space anomalies” in indexes are transient anyway and are self-correcting over time – given enough time and sufficient use.

Footnote: I am not going to supply an explanation for any of the above phenomena because I don’t think they’re sufficiently important to warrant any action – in general. This note has been published only for the purpose of answering trivia questions and similar entertainments.

 

Anonymous

From time to time I browse the comments that Akismet (the spam detector used by WordPress) has intercepted just to check that a useful comment hasn’t been unjustly labelled as spam; and a couple of days ago I was surprised to find that four comments (from the same person) had been marked as spam even though there was no obvious reason why they had been so marked.

They were clearly rubbish, of course – one expressed suprise that I hadn’t published my resume [sic] (it could do with an acute accent over the final e, but apart from that it’s American for C.V. ), another expressed the opinion that my blog showed I had no experience with ACTUAL [sic] working databases … and so on.

But how had Akismet worked out that these comments were garbage ?

The author had supplied a gmail account name – and I think that comments from the gmail domain have triggered spam exclusion in the past but only when the comment included a hyperlink. Then I thought of tracing the IP address that WordPress has captured – and it turned out to lead back to an “anonymizing proxy server”.

Clever trick that – if it’s coming from someone who’s trying to hide, a comment probably deserves to be binned.

Foreign Keys

People often create far more “foreign key” indexes than are needed – and any redundant index is a waste of several types of resources. I’ve just made some comments on an OTN thread about this topic, so rather than repeat the comments here I’ll just give you the URL.

Update 5th Nov 2009: and here’s another common issue about foreign keys that I also addressed by a link to OTN some time ago.