Skip Locked

You may already be familiar with the syntax:

select ... for update skip locked;

It appeared (internally) some time back in Oracle 8i to implement some features of advanced queueing, but was only legalised and documented in one of the more recent versions of Oracle. If, like me, you checked the manuals to understand what this did you probably came to the conclusion that it allowed you to issue a query that selected, locked, and returned the rows in a table that matched your query and were not already locked. This isn’t quite correct – the rows your query selects, locks and returns are the ones that can be locked … and that may not give you the results you expect.

I’m in Detroit at the moment, about to start a consulting assignment, and Mark Bobak mentioned this one to me on the drive into town from the airport. As he said: “It’s one of those things that’s obvious after you’ve worked out what’s going on.” If you want to read more, here’s a link to the blog item he wrote about it.

Index Rebuilds

A question came up on OTN a little while ago about an oddity during an online index rebuild. The original question describes how a relatively small index (500MB) takes several minutes to rebuild, with a few minutes where users who are trying to update the table seem to be stuck on enqueue wait events, waiting for locks on the table to be released. But Oracle tells us that “online” rebuilds of indexes don’t cause long-lasting locks to appear – is this a bug, or a new myth ?

My response to the question is speculative but, I hope, a reasonable suggestion that fits the original description. The amount of time that the locks are held at either end of the rebuild are dependent on the level (and timing) of activity on the table.

In Oracle 10g, I have managed to cause long waits for other users – even when the process doing the index rebuild took fractions of a second to get its final lock – by generating a very large volume of change as the initial stage of the index rebuild was taking place.

Note: this was not the same problem as the process doing the rebuild having to wait for the final lock because another user had uncommitted transactions on the table.

SQL Server 3

I haven’t had much spare time for writing this week – although I’ve got about 45 drafts of notes to publish – but Simpletalk have just published the “learning SQL Server” paper I sent them a couple of weeks ago, and it’s worth a link.

Visual Tuning

I’ll be doing a joint event with Kyke Hailey of Embarcadero in a few days time. It’s going to be a little unusual – a simultaneous webcast from opposite sides of the Atlantic – mixing my presentation about using a graphic method of tuning or designing efficient SQL with Kyle taking on the role of an active audience asking questions.

For more details and to register for this online event see this link

Update: the link now allows you to register to download the presentation and whitepaper, and to view a recording of the presentation. (See comment 7 below)

Philosophy – 11

The English language is full of irregular verbs, for example:

I am hypothesising about possible explanations
You are guessing
He’s talking rubbish

Addendum: The point, of course, is that your interpretation of an individual’s words may be critically affected by who the individual is. The use of the expresssion “English irregular verb” to describe this phenomenon was current around the time that I was at University.

[The Philosophy Series]

Ignoring Hints

I’ve previously published a couple of notes (here and here) about the driving_site() hint. The first note pointed out that the hint was deliberately ignored if you write a local CTAS or INSERT that did a remote query. I’ve just found another case where the hint is ignored – this time in a simple SELECT statement.

Try running an ordinary distributed query from the SYS account, and then try using the driving_site() hint to make it run at the remote site. When I tried this a few days ago I ended up wasting half an hour translating some SQL from ANSI to Oracle dialect because I thought that the ANSI was making Oracle transform the query in a way that lost the hint – then I discovered that both versions of the code worked correctly if I logged in as a different user.

I was running my queries between two databases using 11.1.0.7 – I won’t guarantee you get the same results on other versions, but it looks like SYS doesn’t honour the driving_site() hint. I can’t think of a robust argument why this should be the case, but if I were forced to do some vague hand-waving I’d probably mumble something about potential security loopholes.

Footnote: I should, of course, have mentioned that there are all sorts of things that behave in unexpected ways if you are logged on as SYS, and that you shouldn’t be logged on as SYS – especially in a production system.

[Further reading on distributed databases]
[Further reading on "ignoring hints"]

double trouble

In the latest Quiz Night, I asked how you could make a query more efficient by changing a two table join into a three table join – with the clue that my third table was a repeat of the first table. Gary Myers, in comment 4,  provided the type of answer I was looking for. Sometimes it is more efficient to get a small amount of data from a table on a first pass then go back and get the rest of the data on a second pass – especially if the first pass is an ‘index only’ operation.

Contention

There was a news item in the UK last week about a man in Chideock, Dorset who staged a protest about the volume of heavy traffic that has to come through the village where he lives.

A pedestrian crossing has recently been installed on the road, using traffic-light control. So one morning he decided to cross the road, and then come back again, non-stop, for an hour. Each time he got across the road he pressed the crossing control button to come back. A few vehicles got through on the green light each time, but after just one hour he had caused a four mile tailback of traffic.

Let this be a lesson to Oracle DBAs and developers – even a small job, if repeated very frequently, can create havoc with your system.

Fame

I’ve just received an email telling me that I’m a linchpin – according to this picture on Seth Godin’s website.

Quiz Night

I have two queries designed to return the same result set in the same order. In outline they look like this (look carefully at the from clauses):

select  ...
from    tableA  t1,
        tableB  t2
where
        t1.filter = ...
and     t2.join = t1.join
and     t2.filter = ...
order by ...


select  ...
from    tableA  t1,
        tableB  t2,
        tableA  t3
where
        t1.filter = ...
and     t2.join = t1.join
and     t2.filter = ...
and     ...  -- to be continued
order by ...

How did I manage to take the first query and make it more efficient by turning it from a two-table join to a three-table join ?

Philosophy – 10

The most significant question to ask when thinking about adding a new index:

“Will the index eliminate significantly more work than it introduces (at the moments when it really matters) ?”

A few examples of “moments that matter”:

• Bulk housekeeping
• Highly concurrent OLTP activity
• Frequent high-precision reporting
• Acceptance testing for side effects

[The Philosophy Series]

Rowid

In a recent discussion in the comments of a blog item by Charles Hooper, I made a comment about how hard it is to be accurate (and unambiguous) when talking about the space needed for a rowid. So I thought I’d try to list all the possible options for rowids that you might find in the field. Frankly I’m not sure I’ll get it right first time. So, how much space do you need to store a rowid ?

SQL*Net compression

Here’s a little demonstration I’ve been meaning to write about for the last few years – it’s very simple: create a table, then query it a few times.

Row count 2

Following my notes about counting rows in blocks, someone emailed me to ask how the query I’d published would deal with migrated rows. Remember, a migrated row is one that has been moved in its entirety to a different block because an update to the row resulted in the row needing more space than was currently available in its original block.

Cursor Sharing 3

Here’s a simple piece of code demonstrating an irritating problem. I’ve created a table, a function-based index, collected stats (without histograms), and then run a query that should use that index – but doesn’t.