Skip to content

Author Archives Jonathan Lewis

RMOUG

Just a quick reminder that the Rocky Mountain Oracle User Group Training days are just eleven days away. It’s one of the best Oracle events I’ve attended, and I’ll be there again this year. There are plenty of good speakers and interesting presentations on a wide range of topics – and if you’re wandering around [...]

Posted 03 February 2012 § Uncategorized ‡ Comments Off °

Viewing Figures

It’s time for another landmark. The blog has just hit 2,500,000 views (taking three weeks less than the previous half million increment) – here’s a little breakdown on the numbers. First the top five across the lifetime of the blog: AWR / Statspack menu 40,714 Updated from time to time NOT IN 33,983 Feb 2007 Cartesian [...]

Posted 02 February 2012 § Uncategorized ‡ Comments Off °

Subquery Factoring

It’s always worth browsing through the list of Oracle’s bug fixes each time a new release or patch comes out because it can give you clues about where to look for problems in your production release – and how to anticipate problems on the upgrade. This article is an example of a fix that I [...]

Posted 01 February 2012 § Oracle § Uncategorized ‡ Comments Off °

Bug fixes

From MOS (Metalink) a search for “Patch Set – List of Bug Fixes by Problem” is a useful search, andother is “Availability and Known Issues”. Whenever you find some behaviour that looks like a bug, it’s worth checking the patch sets for the patches or release that are newer than the version that you’re running [...]

Posted 31 January 2012 § Oracle § Uncategorized ‡ Comments Off °

Index Hash

You might think from the title that this little note is going to be about the index hash join – you would be half right, it’s also about how the optimizer seems to make a complete hash of dealing with index hash joins. Let’s set up a simple data set and a couple of indexes [...]

Posted 30 January 2012 § Oracle § Uncategorized ‡ Comments Off °

Purrpetual Motion

This is a really old idea – but it’s the first time I’ve seen it illustrated.
(The obvious flaw in the concept appears  in comment 22.)

Posted 27 January 2012 § Uncategorized ‡ Comments Off °

Ouch!

Here’s a set of Instance Activity stats I’ve never seen before, and I’d rather never see again. From an active standby running 11.1.0.7 on AIX: The instance has been up for about 60 hours – and 95% of the work it has done has been trying to find the commit times for transactions affecting blocks [...]

Posted 26 January 2012 § Oracle § Uncategorized ‡ Comments Off °

Quiz Night

In my previous post, I made the comment: In general, if you have a three-column index that starts with the same columns in the same order as the two-column index then the three-column index will be bigger and have a higher clustering_factor. So what scenarios can you come up with that fall outside the general [...]

Posted 19 January 2012 § Oracle § Uncategorized ‡ Comments Off °

Quiz Night

Browsing a little history recently I came across a note I’d written about the new-style index hint. In that note I claimed that: … the index has to start with the columns (product_group, id) in that order – with preference given to an exact match, otherwise using the lowest cost index that starts the right [...]

Posted 13 January 2012 § Oracle § Uncategorized ‡ Comments Off °

ASH

You may have noticed that I’m having a little trouble keeping up to date on the blog at the moment – I know I’ve got several comments on Oracle Core to respond to but haven’t had time to look at them yet. Very briefly, though, I thought I’d point to a note that Doug Burns [...]

Posted 11 January 2012 § Uncategorized ‡ Comments Off °