Ancient History 2

Some time ago I posted an extract from a short presentation I had given at the UKOUG annual conference about 12 years previously.

When I found that set of slides, I also found the paper copies of another set of slides I had used at another UKOUG SIG event in 1995 where I had been explaining the mechanisms used by the cost based optimizer to decide whether or not to use an index. I thought it might be quite revealing to reproduce those slides to show how much (or how little) things have changed since then.

Frequency Histograms

In a recent article on changes to the calculation of cardinality with frequency histograms I made a comment about writing programs to create fake but realistic and complete frequency histograms.

As a follow-up to that comment, here’s a little script to demonstrate the method (for a numeric column), and a few points to remember when using this method.

CPU used

[Further Reading on Statspack]

From time to time users of Statspack on the newer versions of Oracle are surprised to see the “CPU Time” in the “Top N Timed Events” section of the report looking very different from the “BUSY_TIME” that appears in the “OS Statistics” part of the report.

There are various reasons why the numbers can differ, but one of the reasons is simple and highly beneficial – prior to 10g Oracle usually updated time figures at the end of each database call; but from 10g there are some views where time is updated more regularly.

Index Size

How do you find indexes that might be worth the effort of the rebuild.

One option is to report indexes that take up much more space than they need to – and there are some fairly easy ways to find those indexes. 10g, for example, gave us the create_index_cost procedure in the dbms_space package so, for a single index, we can write a little routine that does something like the following example – running on a 10.2.0.3 database:

Row Directory

Yesterday I asked the question: how many row entries can you create in an 8KB block with pctfree 0. It brought out some interesting observations – including one I hadn’t thought of relating to a small difference between ASSM and freelist management.

I suspect, however, that most people didn’t quite realise the significance of the wording: “row entries”- I  wasn’t asking about “rows”. So here’s a little script you can run after you create the table, followed by the result of dumping the first block in the table.

Lunchtime Quiz

drop table t1;
create table t1(n1 number) pctfree 0;

Assuming that the table is created in a tablespace with an 8KB block size, what’s the largest number of row entries I can create in a block ?

P.S. it’s a bit of a trick question.

Update:  The answer is here, but do read the comments on this post before you look at it.

Odd Filter

A little while ago someone sent me a brief email about an odd section of execution plan that they had seen. To make things a little more challenging they didn’t send the SQL statement, and they only sent me a few lines from the middle of the plan to see if I could explain what was going on. This is what is looked like:

Philosophy – 2

Here’s another of those ideas that are so fundamental that you should always keep them in mind when dealing with an Oracle database.

The fundamental strategy embedded in the optimizer is based on just two key points:  

•  How much data are you after.
• Where did you put it.

If you “tune” SQL by fiddling with it until it goes fast enough then you’ve missed the point and you’re wasting time.

 If you start your tuning exercise by thinking about how much data you’re supposed to collect and how randomly scattered it is, then you’re going to minimise the time you spend working out the best way of acquiring that data efficiently.

[The Philosophy Series]

Not Waiting

A recent posting on the OTN database general forum included the comment:

“… the wait event is : latch shared pool, and CPU is up to 100%, it has run over few hours and seems hang…”

This reminded me of a very common error that appears when people check v$session_wait … they forget to check the state column. The following query is the sort of thing you might check: 

Consistent Gets

Today’s little quiz – just for fun, but prompted by a few comments in this posting on OTN:

One SQL

When I am in America for a few days I usually try to find a local user group and ask them if they’d like to arrange an evening slot for their members. (Last time I was at Oracle Open World I went so far as to do a day trip up to Calgary because I didn’t think I was likely to go there any other way).

Collaborate 09 was no exception, and I contacted the CFOUG who arranged an event on the Monday evening at which I did a presentation called “Optimising through Understanding” where I talk about the analysis that could go into one simple SQL statement, producing reasons why any of several different execution plans might be the most appropriate depending on circumstances.

The presentation is a variation of one I have now given several times – and there is even a voice-over webinar version of it that I linked to from an earlier blog item.

After the event James Taylor, the chairman of CFOUG, asked if I could let him have a copy of the presentation for their website – so I sent him a pdf file of the slides, and I’ve also posted the same pdf file on my blog.

Cardinality feedback

One of my faviourite presentations in Collaborate 09 was a quick tip (30 minutes) on Tuning by Cardinality Feedback – i.e. comparing the optimizer’s predictions of cardinality with the actual rowcounts returned when you run the query.

The strategy is one that I first saw demonstrated in a presentation by Wolfgang Breitling a few years ago at one of the Hotsos seminars – but this latest example demonstrated an important variation on the theme in a short, precise, lesson.

Hints on Hints

This is the title of a presentation I have given a few times in the last couple of years – most recently at Collaborate 09 in Orlando.

The IOUG has now posted on their website the presentation and a short article I wrote to go with it; but that’s for members only, so it seemed reasonable to publish the same thing on my own blog as well.

So here are links for a pdf file of the presentation, and the article in Word format.

If you still want to see the presentation live after you’ve downloaded the files, I’ve offered the abstract for inclusion at OOW this year – and I’ll be doing a shortened version of it at the UKOUG Conference event in Scotland on June 23rd.

IOUG Day 4

Not so much a little gem today as a little surprise and a few consequential thoughts. In a presentation on optimising star transformations the presenter pointed out that bitmap indexes are only available in Oracle Enterprise Edition.

IOUG Day 3

Today was the day of my presentation – which took out two sessions while I was talking, plus the one before so I didn’t have to run to my room.

I think it went well, they laughed in all the right places and several people have come up to me since to say nice things and have a chat.

Today’s gem was Cary Millsap’s presentation. I don’t think he said anything I didn’t know – but he says it so well it’s worth hearing again, and always makes me review how I present things.

I particularly liked his image of “fast” meaning two things: “fast now fast later” – where “fast later” then translates into “scalable”. 

The phrase makes it so easy to remember to ask yourself the question: “will it still be fast when XXX has changed ?”.