Who's online

There are currently 0 users and 19 guests online.

Recent comments

October 2010

COUNT STOPKEY operation (the where ROWNUM

I was running tests on some very large tables on an Exadata cluster and found an interesting bug.

Instead of having to query 4Billion row tables, I have reproduced this case with a cartesian join…

Check this. I’m generating 8 Billion rows using a 3-way cartesian join of set of 2000 rows. So, this results in 2000 * 2000 * 2000 rows, which is 8 billion rows.

SQL>  with sq as (select null from dual connect by level <= 2000)
      select count(*)
      from sq a, sq b, sq c;


Everything worked well as you see. All 8 billion rows were nicely counted. Let’s modify this query a bit, by adding a WHERE rownum <= 8 000 000 000 predicate, which shouldn’t modify the outcome of my query as 8 billion rows is exactly what I have:

SQL> with sq as (select null from dual connect by level <= 2000)
     select count(*)
     from sq a, sq b, sq c
     where rownum <= 8000000000;


Ding! (as I’m unable to come up with a better word to present this problem :)

Seems like we have a problem! The COUNT operation thinks that we have only a bit over 4 billion rows returned from the query plan branches below it.

We clearly have a(n easily reproducible ) bug here!

What happens here is that whenever you use ROWNUM < N operator in a query predicate like I have done, you will introduce an additional step to the query plan (COUNT STOPKEY).

Check this simple query:


Plan hash value: 508354683

| Id  | Operation          | Name | E-Rows |
|   0 | SELECT STATEMENT   |      |        |
|*  1 |  COUNT STOPKEY     |      |        |
|   2 |   TABLE ACCESS FULL| T    |     41 |

Predicate Information (identified by operation id):

   1 - filter(ROWNUM<=1000)

As the above plan explains, the “WHERE rownum <” predicate is applied in the execution plan line (row source) 1 – COUNT STOPKEY. You won’t see this line when you are just counting rows without any “ROWNUM <” predicate.

How this works is that the COUNT STOPKEY knows how many rows you want and will just keep calling its child function under it in the execution plan tree to get more and more rows, until the required amount of rows have been returned. And then the COUNT STOPKEY function just stops calling its child row sources and declares the end-of-data condition.

And here’s the catch – due to a bug, the variable used to keep track of number of processed rows by COUNT STOPKEY is only 4 bytes, even on 64bit platforms. So, it can hold values up to 4294967295 in it (the count returned above), which is 2 ^ 32 – 1. That wouldn’t be so much of a problem in practical real world applications, but what I don’t like is that the operation will silently return wrong data – it will just stop fetching more rows, even though we haven’t reached the ROWNUM <= 8 000 000 000 limit yet and there are rows to fetch from the child row-source, but COUNT STOPKEY declares end-of-data condition and returns ~4B as a result.

This is (obviously a bug) and now there’s a bug number for that as well (thanks to Greg Rahn for letting me know) – Bug# 10214991 (unfortunately it’s not public in MOS).

Now, there’s a reason why this bug has gone unnoticed for so long despite that 4+ billion-row tables have existed for long time (I worked first with such Oracle database in year 2000 – on Windows NT :).

  1. A real-life business query with a construct of WHERE ROWNUM <= X makes sense only when the data is ordered by some meaningful business attribute (a column). Otherwise you’ll get the query results in quite random order. Note that I’m talking about real, business queries here.
  2. The only right way to order data in SQL is via an ORDER BY clause. There is no other right way to do it, even though some hacks sometimes work (and will stop working after the next upgrade)
  3. Nobody (or not many people) have written queries like: give me the sum of 5 billion biggest purchases ordered by the selling price or give me the sum of last 5 billion purchases up to this date. If you replace the billion by just ten, hundred, or just a thousand, then hell yeah, such types of queries are being executed every day (or should I say minute, second).
    Whether using ROWNUM in your business queries (instead of actual date range or purchase price in $$$) is a good or bad design is a completely different question – the fact is that ROWNUM is used in such SQLs.

Now we’ll just need to wait until the data volumes (and reports) get so big that someone actually hits this problem in real life!

So, better patch your database before the end of this decade! :-)


Left outer join equivalence - Oracle vs ANSI syntax

I was reminded last week while visiting on customer site that it's easy to confuse the syntax you should use if you switch between Oracle's syntax and ANSI syntax for outer joins. Here's an example using the good old standby SCOTT schema to show what I mean. First, I'll show the data from both tables we'll use (emp and dept) so the rest of the examples will be easier to follow.

SCOTT@ORA11R2> select * from emp ;

---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

Cricinfo All Time World XI

The Cricinfo all time world 11 is now out. Unsurprisingly there are a few differences (though I was expecting more). Hutton ahead of Greenidge is on pure style terms pretty straightforward. Similarly Wasim would make a great replacement for Freddie Trueman. I personally don’t subscribe to the view that an all-rounder is needed in a world XI [...]

Prague, Czech Republic

Although the seminar in Munich only had a relatively small attendance (12 in total), it was a great bunch and went off rather well (Martin Decker in this blog piece certainly seemed to enjoy it). Next stop on my little whirlwind tour of Europe is Prague in the Czech Republic, where I’ve had two fantastic days exploring [...]

Hong Kong – Day Off 2

In the morning I got a taxi over to my mate Alan’s house and the pair of us, along with his 16 year old step son Kevin, drove up the nearest hill (Victoria Peak) to have a look down over the city. The Hong Kong approach to tourist attractions seems to be, if you think tourists might come to see something, build a shopping mall next to it. I’ve added some more photos to yesterdays. The pollution in Hong Kong is pretty bad, so you will notice that anything in the distance is concealed by a haze. When you are looking at it you still get a lot of detail, but when you photograph it all you can see is the haze.

From there we drove down to the beach. It seems that if public transport can get you to a beach, it is heaving with people (and there will be a shopping mall there). If you need a car it is much quieter. I’m not a beach person myself, but I do like being near the sea.

From there we went back to Alan’s house, where I bonded with his step sons (Adam 13 and Kevin 16) by helping them speed up their computer so their games could run faster. They were playing MMORPGs like Aeon, Warcraft and some Chinese-specific things. I couldn’t understand a word of anything on the screen, because it was all in Chinese, so Captain Support needed a little translation help from Kevin. I did a little tweaking, but most of the performance improvements came from explaining that having 50 programs on all at once, including streaming video and music kinda interferes with the performance of the game, especially one that has to connect to a server, like MMORPGs do. As soon as I closed down the Justin Bieber video playing in HD on YouTube things got a lot faster and sounded a lot better. :)

From there we went out to meet a couple of the guys Alan works with. Both were Ausies, but have been living on and off in Hong Kong for a long time. We had a couple of drinks, some food, then early to bed ready for my class today.

Regarding food, being vegetarian in Hong Kong is not a great idea. It’s often difficult to find vegetarian food because even things that look vegetarian have a little something-something thrown in for good measure. During the day Kevin was keeping me on track. In the evening Phil was helping me out. My mate Alan speaks conversational Cantonese, after 5 years in Hong Kong, but can’t help throwing in sentences like, “Did you know Eskimos like peanut butter?”, or, “A big monkey is a gorilla and a big gorilla is King Kong!”, in an attempt to amuse/confuse the waiters, so when he was in charge I was a little worried. :)



Hong Kong – Day Off

I did manage to sleep a little on the plane from Melbourne, but not much, so I arrived in Hong Kong at 07:00 feeling very tired. I got the airport shuttle bus to my hotel. It was HK$140, which is about £11 and I was the only person in the full size coach. I think they may have made a loss on petrol for that journey. :) The journey from the airport to the city is very strange. Most of the Hong Kong is forest, but then you will have 3-5 enormous skyscrapers more or less springing out of the forrest at intervals. It looks really odd. :)

Arriving at a hotel that early is often problematic because the guests from the previous night haven’t checked out yet. I got lucky in Melbourne and they had a room for me. In HK they didn’t. I checking in my bags and decided to have a look round. After about 30 minutes (including some moments of feeling very lost) I decided I was just too tired to attempt to navigate Hong Kong, so I went back to the hotel and parked on a chair in the reception. After a couple of hours of nodding off and generally making the place look untidy a room became available and I went straight up hoping to sleep. By the time I had scrubbed the stench of a 9 hour plane ride off myself, I only had about 1 hour to rest before meeting up with one of my friends.

A mate of mine from university moved to Hong Kong about 5 years ago (after a year in Sydney). We had met up a couple of time since he left, but I guess we had not crossed paths for about 3 years. He dropped by the hotel and whisked me off into the city to show me some of the sights (and give me lots of money, because my cards seem to be blocked again. :( ).

From the perspective of an adopted Brummie, this city is very intimidating. I mentioned in a previous post that Singapore is full of really tall buildings, but there is a feel of open space between them. The buildings in Hong Kong are truly massive and very tightly packed. In Hong Kong a bungalow is 10 stories. :) What’s more, many of the skyscrapers seem really thin, making them look rather fragile. We started off by dropping in at his office so I could get an aerial view of some of the city. We were only part way up the building he works in and it made me feel a bit sick.

From there is was back down to the street and a trip over to Mong Kok, which I believe is still listed as the most densely populated place in the world. Something like 130,000 people per square kilometre. Alan seemed to think it was a little quiet compared to usual, but it seemed very busy to me. I took a few photos (here), but to be honest all the streets were so tightly packed I quickly realised that taking photos was a bit pointless because it all started to look the same and it also meant I was interrupting the flow of the people, which was not a great idea with 130,000 per square km. :)

So my impression from day 1 is Hong Kong = Blade Runner.

The plan today is to get out of the city and have a drive around. Hopefully I’ll get some more photos today.



Melbourne Update

The Melbourne class was a little bigger than the previous classes, with 12 people including several people from Oracle Support. Being the 3rd class in quick succession, everything felt really smooth, with no real surprises.

I actually managed to get a reasonable amount of sleep before both classes, which was a bonus. Saying that, I fell asleep at 20:00 last night, so I ended up waking up at 03:00, which gave me some time to play catch-up with emails and forum questions. It does mean that today will end up being a pretty long day, since my flight to Hong Kong isn’t until 00:15 (including a slight delay).

Apparently there have been typhoon warnings in Hong Kong and the schools have been closed, but surprisingly it hasn’t really affected flights. Let’s see how that works out.

This was my third visit to Australia, but only my second visit to Sydney and Melbourne. The real take-home message from this trip is I felt really at ease all the time I’ve been in Australia. I’m not surprised a lot of British people decide to move to Australia. It’s probably one of the easiest places for a Brit to relocate to.



Update: I’m in the hotel in Hong Kong now. No signs of a typhoon, but I’ve received a mail saying the class may be cancelled if the weather is too bad. Weird. I’m just off to lunch in the tallest building in Hong Kong, or so my mate tells me.


A little while ago I noticed a couple of page views that had come from the AskTom website – and I’m always interested to see what question has prompted a link and visit to my blog. In this case it was a question that raised a piece of (nearly ancient) history. In a question dated July 2009 someone was asking about a comment I had made in “Practical Oracle 8i”. (Despite the book being over 10 years old I’m still getting royalty cheques on it – small ones, but they keep on coming – and it’s still a book worth reading).

The question was about the following text:

Avoid naming tablespaces according to time-periods; this is particularly relevant to large, time-based data warehouses where very old data is ultimately dropped off the back end of the database.

There is an unfortunate quirk of tablespace naming – Oracle never forgets a tablespace – in other words it never deletes it from the data dictionary table TS$ (I think this is some sort of hangover from Trusted Oracle where tablespaces have security labels associated with them). Unfortunately, one of the permanently running Oracle processes called SMON (the System monitor) scans this list of tablespaces every 3 minutes looking for tablespaces that might have some free space to coalesce; the size and cost of this scan will grow indefinitely unless you adopt a strategy of recycling tablespace names. It is actually surprising how many databases suffer a massive I/O load on the SYSTEM tablespace because of SMON.

The problem will be reduced somewhat by the introduction of locally managed tablespaces, since the scanning problem is actually exacerbated by the number of free extents that gets generated in poorly managed systems in a way that can’t happen with locally managed tablespaces; nevertheless you need to make sure that the list of tablespace names does not get out of hand.

You’ll be pleased to hear that this is no longer a problem. If you have access to Metalink (MOS), you can find information about (unpublished) bug 5861536: “Slow DDL due to Tablespace lookup with large number of tablespaces” dated 24th Sept 2008; and see in note 401436.1 ( Patch Set – List of bug fixes by Problem Type) that this problem has now been addressed.

UKOUG Conference Series 2010

The annual UKOUG Technology and E-Business Suite Conference takes place from November 29th to December 1st in Birmingham (UK not Alabama). You can book here. One of the neat features of this event is that you can pre-register your interest in sessions and build a handy one page personalized agenda. This saves carting around the [...]

Keynote Presentation at NOUG Oct 2010

Thank you all who came to watch my keynote presentation - "Real World DBA Best Practices" - for Northeast Oracle User Group in Boston, 21st October, 2010.

You can download the presentation here. Before downloading, please let me reiterate what I mentioned during the meeting - a best practice is not one if you do not understand the reason behind it and do not understand the applicability to your specific situation. I was not selling a product or service nor was I asking you to blindly follow it. All I wanted from you was to consider the points.