Analyze this

Here’s a little script I wrote a few years ago to make a point about using the dbms_stats package. I’ve just re-run it on 10.2.0.3 to see if it still behaves the way it used to – and it does. If you want to be just a little bit baffled, set up a database with an 8KB blocks size, a tablespace that is locally managed, uniform extent size of 1MB, using freelist management, then run the script:

heap block compress

In a recent note showing how an index could become much larger than the underlying table because of the different ways that Oracle handles deletion from table and index blocks, I pointed out that Oracle would have to pick a moment to replace rows marked for deletion with a stub, or place-holder, showing where the row had been but reserving the rowid in case a process rolled back the delete. (This tied back to a note I had written showing that the row directory in a table block could become much larger than you might think possible.)

My closing question asked when Oracle would do this replacement, and how you could monitor it. This note is a partial answer to that question.

Philosophy – 9

There is an old joke about an engineer, a mathematician, and a philosopher sitting together in a train travelling from London (England) to Cardiff (Wales) ***

As the train crosses the border, the engineer glances out of the window and exclaims: “Oh, look! Welsh sheep are black”.
The mathematician responds: “No; all you can say is that there is at least one sheep in Wales that is black.”
The philosopher corrects both of them: “Gentlemen, all you can claim is that there appears to be a sheep in Wales that seems to be black on one side.”

(Trust me, in 1970, this was quite funny).

The point of telling the tale is this: the best viewpoint to take when trouble-shooting an Oracle database is that of the mathematician – don’t, as the engineer did, leap to extreme conclusions based on just one observation , but don’t, as the philosopher did, get so stuck into such tiny details of theoretical correctness that reasonable assumptions are swept aside.

*** Footnote: for those not familiar with the geography of the UK: “The UK” is “The United Kingdom of Great Britain and Northern Ireland” and “Great Britain” is the union of England, Scotland (most of the top half of the island), and Wales (the lump at the left hand side, excluding the thin pointy bit at the bottom).

[The Philosophy Series]

Index too big

I thought I’d posted this a couple of years ago – but maybe it was something I put on the OTN database forum in response to a question. If it was, the same (or similar) question has recently appeared.  “How come my index is so big when there’s no data in the table ?”

Fake Histograms

I got an email a few days ago asking me how to create a “height-balanced” histogram using the dbms_stats.set_column_stats() procedure.

I posted an example of creating a frequency histogram a few months ago – and it doesn’t take much to turn this into an example for the height-balanced type; the key change apppears in three critical lines.

Nutshell – 2

There is a lot of confusion around about the significance of the statistic “parse calls”. The important thing to remember is that it is simply counting a certain type of call from the OCI library – the amount of work done by a parse call may vary enormously depending upon circumstances, and sometimes the amount of work is so tiny that it’s not worth worrying about.

A “parse call” may:

a) Have to optimise the statement because it failed to find it after searching the library cache

b) Find the statement after searching the library cache, and still have to optimise it for various reasons, e.g. the previous plan has been flushed from memory or the same text applies to different objects depending on who is executing it.

c) Find the statement after searching the library cache and not have to optimise it because the plan is still available and the user has the appropriate privileges.

d) Operate through the session cursor cache or pl/sql cursor cache allowing it to use a short cut to the statement’s location in the library cache without having to search the cache.

When the Oracle increments the counter for “parse calls”  you still have to work out whether that call turned into (a), (b), (c) or (d).

Just to confuse the issue, Oracle may also record a “parse count (hard)” without recording a “parse call”.

[Back to Nutshell -1 (Redo and Undo)]

Update Jan 2011

Randolf Geist has been looking at adaptive cursor sharing, and has noted that the parse call – including parse calls that go through the session cursor cache – seems to be the point in the code where adaptive cursor sharing can take place: in other words, it’s not an event that gets triggered or flagged by executions.

Not KEEPing

I’ve recently spent some time working with a client to get the maximum benefit from their KEEP pool – I’ll be publishing some interesting demonstrations when I get some time – and thought that some of you would be keen to hear about bug 8897574.

The bug applies to 11.1.0.7 – luckily my client is still on 10.2 – and has the following abstract: KEEP BUFFER POOL DOES NOT WORK.

Something Changed

I’ve been a bit busy over the last few days and haven’t had much time to do any new postings or catch up with comments; but I’ve just got a few moments to let you know about a client I visited a few weeks ago who didn’t give me the usual “nothing’s changed” story.

“We’ve only changed two things,” he said, “the hardware and the software.”***

Mind you, I wasn’t sure whether the change in the network infrastructure should have counted as a third thing.

*** I think he’d read the earlier blog item.

Partition Stats

It’s possible to spend ages talking about the best ways of collecting, or creating, statistics on partitioned tables.

The possible strategies for maintaining partitioned tables, (exchange partition, split partition, drop partition etc.) the types of partitioning available, and the way that the optimizer plays with the stats as you do so, have kept changing over the years, and I’ve got a large set of examples designed to test what happens to the stats as you do things to the table – but it’s impossible to keep it up to date.

Doug Burns is writing a series of articles about the trials, tribulations, and successes about partitioned tables and statistics. The series was well worth reading and will give you an insight into the problems you may have to address, so I’ve produced a catalogue to make it easy to visit the individual chapters in order.  Make sure you also read the comments and related links.

• Part 1 – In which we see a simple example and do a default stats collection
• Part 2 – In which we consider Global Stats
• Part 3 – In which subpartitions and aggregation cause problems
• Part 4 – In which our hero fights his way through stats aggregation woes
• Part 5 - In which we encounter a partition exchange
• Part 6a – In which we start to use dbms_stats.copy_table_stats()
• Part 6b – In which we see how reputable individuals handle their mistakes
• Part 6c – In which we hear about 10.2.0.5 and lots of bugs
• Part 6d – In which we revisit earlier errors and discuss the benefit of discussion
• Part 6e – In which we revisit earlier problem again and talk about a bug.
• Part 7 – Not in the original series, but an interesting (slow) experience in 11g

I thought I’d collate a few other items on partition stats and optimizer behaviour - mainly from Randolf Geist’s blog:

• March 2007: Subpartitions and Optimizer Stats
• April 2008: (a) Subpartition Pruning
• April 2008: (b) Exchange Partition and Aggregated Stats
• Mar 2009: (a) Partition Oddities and 10.2.0.4 bug
• Mar 2009: (b) List partitions

And one from Kerry Osbourne – which lists a new granularity option, and a patch for 10.2.0.4

• Feb 2009: Maintaining statistics on a large partitioned table.  (See also Metalink Doc ID: 6526370.8)

And an investigation into an oddity with the optimizer when using partitioned indexes

• Feb 2011: Jokes of the CBO with local indexes (10.2.0.4, 11.1.0.7)

Sample Clause

In some of my previous posts (particularly the ones about analysing the data by running aggregate queries) I’ve mentioned the “sample” clause from time to time, so I thought I’d better write a short note about it for those not familiar with it.

To demonstrate the feature, my first step was to run a test script I wrote a few years ago – and discovered that the feature is broken (at least, in 10.2.0.3 and 11.1.0.7) – so at the end of this post I’ve supplied a little test that you can try at home on your own versions.

Dropping OUTLN

I had a very pleasant day yesterday at a SIG meeting of the UK Oracle User Group where I did the presentation about “drawing your SQL” that is the basis of the article I wrote for Simple Talk a little while ago.

One of the other sessions had the entertaining title: “What happens if you drop the OUTLN user ?”. You might wonder why you would ever think of dropping the outln user, of course, but the presenter was able to justify that. (If you’re wondering, the outln user is the one that first appeared in Oracle 8i as the owner of the tables that record Stored Outlines.)

CBO arithmetic

Anyone who’s keen to keep track of how the cost based optimizer does its arithmetic, and how that arithmetic can change with version, may want to keep an eye on this blog.

Treedump – 2

In an earlier article about investigating the state of an index in detail I supplied a piece of SQL that would analyse an index (no, not using the Analyze command) and summarise the number of entries in each leaf block that currently held any entries at all. Here’s a sample of the type of output it produced:

French Translations

I’ve just had another email about translating some of my postings, in this case into French. Franck Pachot has asked if he can post translations on his blog, and the answer is yes.

His intention is to select the postings which are more about how the database engine works, rather than examples of specific solutions, and he hopes to collate articles from some of the other Oracle specialist on the same blog.

I’ve created a catalogue entry for him in the Special Links panel to the right.

SQL Server 2

Following on from my posting about the presentation (about Enterprise databases) that I did at a Microsoft event on SQL Server 2008, I’ve just had an article on designing efficient SQL published on a website that’s strongly biased towards SQL Server developers – using an example of SQL written in the SQL Server dialect.

It’s worth a read. Don’t be put off by the change in dialect, the topic and technique are more significant than the language. (In fact it’s a write up of a presentation that I gave at the UKOUG Conference Series 2009 in Birmingham.)