Index trouble

I’ve been involved over the last few days  in a discussion on the OTN Database forum of a strangely behaved index.

The thread started with someone asking if a corrupted index could be the root cause of a job slowing down [probably not, a corrupted index is more likely to cause a job to crash] but the interesting bit came along later as a follow-up post introducing an index that seemed to grow unreasonably large for no apparent reason – apparently bypassing the automatic reuse of empty index blocks that normally takes place.

Bugs

There are times as I browse through Metalink when I see descriptions of bugs that make me wonder how on earth anyone managed to find them. There are bugs which are so bizarre in their combination of prerequisites that you might think they’d never,ever,  show up. Here’s one that got a mention on OTN some time back.

Problem: an update on a simple, unindexed, table takes 90 minutes if the table is in a tablespace using a 16KB block size; but closer to 90 seconds if the table is in a tablespace using a 4KB block size. The effect is totally reproducible.

Philosophy – 6

Testing for correctness:

• Have you tested with good data and all the different possibilities of bad data
• Does the code do the right thing … and nothing more.

[The Philosophy Series]

Processes

From time to time I see people asking about the impact of setting the  processes parameter to allow for a much larger number of Oracle processes than is really needed to support their application. I must have addressed various aspects of this question seven or eight times on the Internet, but haven’t yet got around to writing anything about it on my blog.

I’ve just addressed the topic yet again on the OTN database forum, so I thought I’d take advantage of the notes I’ve made there by referencing them from here. You might want to read the whole thread, but I’ve supplied links to two posts – one about the effects of the parameter on what goes on in the SGA and one on a follow-up question on how the parameter can affect O/S memory usage if the number of processes running actually jumps to take up the excess that you’ve allowed.

There are probably a few more details worth a mention – so this posting may get updated some time in the future.

Hash Partitions

I made a throwaway comment in a recent posting about using powers of two for the number of partitions when using hash partitioning. The article in question was talking about globally partitioned indexes, but the “power of 2″ principle was first associated with tables.

Here’s a simple demonstration of hash partitioning in action demonstrating why Oracle adopted this “power of 2″ rule. We start by creating a table that doesn’t obey the rule – with six partitions – and collect stats on it to see how many rows go into each partition:

Index ITLs

After wrting a short series of notes on a problem with indexes wasting a lot of space and growing to three (or even four) times the size you might expect due to a problem with concurrency and ITL (interested transaction list) entries accumulating, I thought it would be a good idea to create a little index for the series to make it easier to read them in order:

• Index Quiz 1 : An introductory question
• Index Quiz 2 :  Answering the question with block dumps
• Index Explosion : Demonstrating the issue
• Index Explosion – 2 : Explaining the problem
• Index Explosion – 3 : Solving the problem
• Index Explosion – 4 : Working around the problem
• treedump : A useful (though sometimes expensive) option for looking into index structures
• treedump 2: Another look at tree dumps.

Audit

Just a quick pointer to a comment about deleting from aud$ that came up recently on the OTN Database Forum.

I know the answer’s in the manuals, but sometimes you just don’t spot little details, or remember where you think you read them, so it’s nice to capture the comment when you can.  (And there’s a nice little follow-up from Mark Powell that extends the topic to fga_log$ – the “fine-grained audit” table)

Index Explosion – 4

After describing how to deal most effectively – but only after approval from Oracle Support – with the problem of indexes wasting space on unnecessary ITL entries, I left you with a short list of “supportable” options for addressing the problem. In this note I’m going to outline a few pros and cons of each of those options. The list was as follows:

Indexing

At present I have 2,130 SQL scripts on my laptop, of which about 130 are tools that I use for trouble-shooting. The rest are models of problems I have come across at client sites, demonstrations of bugs that I’ve sent in to Oracle, or test cases that I’ve used to investigate the way some feature of Oracle works.

Of the 2,000 scripts that aren’t aids to trouble-shooting, there are 123 scripts with the word “index” in their name – in second place are the scripts with “pt” (for partitioning) in their name with 72 scripts.

The numbers may not give you the same shock that they gave me, so you may have to take my word for it that if 6% of my scripts are about indexing that’s a pretty stark indication of the degree to which indexes mess up people and people mess up indexes.

It doesn’t quite make it to the “Philosophy” list, but it’s almost a truism for any badly performing system: “You need to review your indexing strategy.”

Philosophy – 5

Trouble-shooting with Statspack / AWR:

Something has to be top of the “Top 5 Timed Waits” … even when there are no performance problems.

[The Philosophy Series]
[Further Reading on Statspack]

Analytic Agony

When I wrote Practical Oracle 8i, version 8.1.5 was the most recent version of Oracle but version 8.1.6  came out just before I finished writing – and the only thing in 8.1.6 I thought important enough to add to the book was a section on Analytic Functions because they were the best new coding feature in the product.

Since then I’ve always warned people to be a little careful about how they use analytic functions because of the amount of sorting they can introduce. My suggestion has always been to crunch “large” volumes of data down to “small” volumes of data before applying any analytic functions to add “intelligence” to the intermediate result.

11gR2

I supppose it’s inevitable that there will be a flurry of 11gR2 posts in the next few days – so here’s mine. A really useful new feature (and it’s corollary) that caught my eye:

1.9.2.5 Segment Creation on Demand

The initial segment creation for nonpartitioned tables and indexes can be delayed until data is first inserted into an object.

Several prepackaged applications are delivered with large schemas containing many tables and indexes. Depending on the module usage, only a subset of these objects are really being used. With delayed segment creation, empty database objects do not consume any space, reducing the installation footprint and speeding up the installation.

1.9.2.6 Zero-Size Unusable Indexes and Index Partitions

Unusable indexes and index partitions no longer consume space in the database because they become segmentless.

Unusable indexes and index segments are not usable for any data access. Any space allocated by this unusable (dead) object is freed as soon as an object is marked unusable.

While we’re on the topic of 11.2 - here’s an interesting set of results published on the comp.databases.oracle.server newsgroup by Charles Hooper.

Update 7th Sept: And here’s another interesting post, from the Optimizer Development Group, on a new feature for comparing execution plans. It looks like a nice idea,  but it looks a little clunky at present so I’m not sure it will be of much to many people.

Update 11th Sept: I see that Greg Rahn has listed his top 10 – the parallel stuff looks as if it might be interesting, and “instance caging” could be very useful.

Real World

Who says that the scientific method can’t be applied to real-world problems ?

http://blag.xkcd.com/2009/09/02/urinal-protocol-vulnerability/

Update – in keeping with the recent “Friday night is quiz  night” pattern, here’s a quiz on the same topic. Apologies to any who feel excluded for cultural or gender reasons:

http://www.crazyhill.com/hung/other_game/urinal.swf

Queue Time

Since Richard Foote has started to encroach on my territory (by writing about the CBO), I’ve decided to responsed by moving briefly into Cary Millsap’s speciality (by writing about queueing). I don’t intend to get very technical, though, I just want to give an example of how queue theory relates to Oracle by answering a question I got from a client a few weeks ago:

“How can they be complaining that response times are worse, the throughput is up by 5%?”