Top Ten

Sorry, this isn’t a posting about efficient ways of getting the first 10 rows from a result set – it’s a little note about Oracle Open World. I don’t brag very often, but sometimes it’s hard to resist.

I’ve just received an email about Openworld with the following content:

logging

I’ve just jotted down a few notes about “log file sync” waits, “log file parallel write” waits, and the nologging option in response to a question on OTN about redo activity when creating a large index. The ensuing conversation also picks up various topics relating also to backup, recovry and dataguard.

XStreams

If you’re into using Streams, you may want to take a look at the latest enhancement: XStreams.

There’s a little overview, plus a couple of Java examples at the following links:

• Overview/FAQ
• Inbound streams data
• Outbound streans data

Philosophy – 8

Btree indexes vs. Bitmap indexes – the critical difference

• A single B-tree index allows you to access a small amount of data very precisely.
• It is the combination of a subset of the available bitmap indexes that offers the same degree of precision.

You should not be comparing the effectiveness of a bitmap index with the effectiveness of a b-tree index.

(Inevitably it’s a little more subtle than this – you may create some low-precision b-tree indexes to avoid foreign key locking issues,the optimizer can combine b-tree indexes, and so on - but if you start from this basis you will have a rational view about how to use bitmap indexes).

Footnote: remember, also, that bitmap indexes introduce massive concurrency issues and other maintenance overheads – if you see them in an OLTP system it’s very likely that they’re causing problems.

Update 23rd Dec 2009: I’ve written a follow-up article to this note since the point I was trying to make seemed to cause some confusion.

[The Philosophy Series]

Quiz Night

Okay, so it’s not night-time, or even early evening, in some parts of the world – but somewhere it’s Friday night, and Friday night is quiz night.

Now, most people are aware that the clustering_factor of an index measures the ordering (or, if you want to be slightly more accurate, the clustering) of the rows in a table relative to the order of entries in the index. Consequently  if you check the clustering_factor before and after rebuilding an index it cannot change.

Bitmap Updates

It is fairly well-known that bitmap indexes are very dense structures that can behave badly if their underlying tables are subject to even fairly low levels of insert, update or delete activity. Problems include contention, space management and performance, and these problens have spawned a couple of well-known guidelines relating to bitmap indexes:

• Avoid concurrent modification of data by multiple processes – otherwise you end up with processes dead-locking
• Drop/disable bitmap indexes before data loads and rebuild them afterwards.

Of course, with a little care and experimentation, you may find that you don’t need to apply the second guideline in all cases – especially for bulk inserts.

Understanding

Karen Morton has a few wise words to say about understanding vs. memorization. Definitely worth reading and understanding – and maybe even memorizing.

Experts

Chen Shapira wrote a very nice note about the unconference (and Oracle Closed World) presentation that I did at Oracle Open World this year, loosely titled “The Beginner’s Guide to being an Oracle Expert”. In her comments she has captured a point more clearly than I have ever expressed it: “DBAs are under a lot of pressure not to be experts.”

Correlation

One of the “inspirational thoughts” on my opening page is the observation by the late Stephen J. Gould that

“The invalid assumption that correlation implies cause is probably among the two or three most serious and common errors of human reasoning.”

 It’s very easy to equate correlation with causation and take inappprioate action as a result – it’s an example of faulty thinking that I see fairly frequently on forums such as OTN or the Oracle newsgroups.

If you want to get an insight into the difference between correlation and causation, you ought to read Robyn Sands’ note on “Nonsense Correlation”.

Nutshell – 1

Undo, redo, and recovery in a nutshell. (The “1″ in the title may turn out to be optimistic, I offer no guarantee about further nutshells.)

In the most general case of updating a block – when you change one row in an table, say, or mark an index entry as deleted – Oracle does the following:

Cursor_sharing (2)

In response to a question about a system that needed to set a very large shared_pool_size, I’ve just posted on the OTN database forum a brief summary of the impact of the cursor_sharing parameter on your system. It seemed like a waste not to link to it from the blog.

An earlier posting on my blog about one of the little oddities that can appear when you use cursor_sharing.

Philosophy – 7

There are no “secrets”.

At least, there are no secrets involved in making a database perform properly. Occasionally a useful new piece of information is uncovered – and if it’s worth knowing it will be documented, discussed, and validated in public.  (It won’t necessarily be documented on Metalink, OTN, or tahiti.oracle.com – but that doesn’t make it a secret.)

Whenever I’ve seen people doing presentations about “secrets” they’ve usually split their time between quoting the documentation, stating the obvious,  making mistakes, and offering sweeping generalisations that needed careful justification.

I have a simple rule of thumb for presentations – the more glamorous, trendy or exciting the title sounds the less likely it is that the presentation will be useful (but that won’t stop me reading the abstract – just in case).

[The Philosophy Series]

Bugs – 2

After asking people about their strangest bugs, it’s a little ironic that I should immediately be subject to one. Here’s a transcript (edited slightly for cosmetic reasons) from my laptop shortly after I started up my 10.2.0.3 test database.

Undone

Here’s a little problem that was thrown at me a little while ago during a visit to a client of mine. It only took about five minutes to fix, but it seemed like a nice demonstration of trouble-shooting so I thought I’d write it up for the blog.

We start with the moment when one of the developers told me that they were seeing lots of session failing with the error “unable to extend segment in undo tablespace”.

Hash Clusters – 2

There’s an interesting conversation about hash clusters growing on the OTN database forum at the moment; I’ve made a couple of contributions to it, and think it’s worth reading.

For some other comments I’ve made about hash clusters in the past:

Hash Clusters – 1: also references a link on OTN where I’ve made a couple of comments
Sorted Hash Clusters: An introduction to a relatively new, interesting, but (at the time) buggy feature
Sorted Hash Clusters – 2: A closer look at what goes on under the covers.
Manual Optimisation – 3: Where I link a dirty trick for optimising a query with the optimizer’s “dirty tricks” with sorted hash clusters.