Top 60 Oracle Blogs

Recent comments

February 2013


That’s SQL Tuning Sets, optimizer_features_enable, and SQL Plan Management.

There’s a recent post on OTN describing an issue when using SQL Tuning Sets to enforce plan stability when upgrading from to – it doesn’t always work. Here’s a very simple model to demonstrate the type of thing that can happen (the tables are cloned from a completely different demo, so don’t ask why I picked the data they hold):

Recover from unusable dictionary indexes in 10g using 11g

Some time ago was contacted by a company where someone had generated a script to move tables to another tablespace. Unfortunately, they had made an error and included sys and system tables.
Luckely, Oracle does not allow you to move base dictionary tables, throwing an ORA-00701 error.
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
Anyway, some tables were moved out of the system tablespace and this left a trail of unusable dictionary indexes.
This resulted in a database throwing various errors and when the database was restarted, it just gave up.


If you’ve used Tanel Poder’s snapper script then you probably want to know about the latest release, and webinar coming up this Wednesday.

Optimisation ?

I was at a client site recently where one of the end-users seemed to have discovered a cunning strategy for optimising a critical SQL statement. His problem was that his query screen times out after 2 minutes, so any query he runs has to complete in less than two minutes or he doesn’t see the results. Unfortunately he had a particular query which took nearly 32 minutes from cold to complete – partly because it’s a seven-table join using ANSI OUTER joins, against tables ranging through the 10s of millions of rows and gigabytes of data – the (necessary) tablescan of the table that had to be first in the join order took 70 seconds alone.

Session Snapper v4 – The World’s Most Advanced Oracle Troubleshooting Script! – Webinar

Snapper V4 Webinar

Catchy title, huh? :-)

The Snapper v4 is getting ready! I am going to launch it on Wednesday 13 Feb 11am PST at the Snapper V4 webinar (register here).

The major new features include:

  1. RAC support – ability to query stats from remote instances
  2. Manual Before and After snapshot support – no need to use DBMS_LOCK sleeps anymore
  3. Show useful averages and ratios *in addition to* raw metrics for faster troubleshooting
  4. And more! :)

Snapper is still a free-to-use tool and it still does NOT require any object creation nor changes in your databases for use. Now even DBMS_LOCK access isn’t needed, although it’s still useful for convenience.

That is just *weird*

OK, so there's a 365/1 chance of this happening (actually, I suppose Leap Years change the odds slightly) but when I decided to have a quick look at just how atrocious my blogging frequency has been over the past year, I thought I'd take a quick look back at old entries.

It's not something I do often (... and, yes, I know dates on my blog posts would be nice. It isn't like people haven't told me. I've just had other stuff to do and that one is filed under 'Migrate to WordPress'. But that isn't the point here.)

RMOUG – Here I Come

Well, I’ve just finished pushing the last few bits into my suitcase for my trip to the US for the Rocky Mountain User Group Training Days 2013.

It is a few years since I went to the US for pleasure (3 years?) and much longer since I went there on a combined work/pleasure trip – as I HATE going through US immigration.

OakTable World 2012 - Greg Rahn video

New video from OakTable World 2012 is available - Greg Rahn - Beyond Relational Databases

OakTable World 2012 - Greg Rahn video

New video from OakTable World 2012 is available - Greg Rahn - Beyond Relational Databases