There’s no such thing as proactive tuning. Either you do it right before it goes into production (in which case it’s called “design”) or you react to the nasty surprises you get after it’s gone live.
Even if you do something about the problems before anyone has complained that’s still “reactive” not “proactive” – and the trick is to be reactive as soon as possible. For most companies the best strategy is probably to stick a big screen on the wall with the “Top Activity” page from OEM (or equivalent) so that the key players (DBAs and Developers) can keep a casual eye out for resource hogs and prioritise dealing with them as they appear.
Footnote: if you fall into that rare group of people who can look at what’s currently happening, identify the design errors, and explain how to fix them before the performance starts to nose-dive, then I’ll let you say you’re doing proactive tuning; but personally I tend to call that process “eliminating design errors”.
You might have expected the following query ought to run reasonably efficiently, after all it seems to be targeted very accurately at precisely the few rows of information I’m interested in:
owner = 'TEST_USER'
and table_name = 'TEST_COMP'
and subpartition_name = 'P_MAX_D'
Here’s a model of a problem I solved quite recently at a client site. The client’s query was much more complex and the volume of data much larger, but this tiny, two table, example is sufficient to demonstrate the key principle. (Originally I thought I’d have to use three tables to model the problem, which is why you may find my choice of table names a little odd). I ran this example on 126.96.36.199 – which was the client version:
There are many little bits and pieces lurking in the Oracle code set that would be very useful if only you had had time to notice them. Here’s one that seems to be virtually unknown, yet does a wonderful job of eliminating calls to decode().
The nvl2() function takes three parameters, returning the third if the first is null and returning the second if the first is not null. This is convenient for all sorts of example where you might otherwise use an expression involving case or decode(), but most particularly it’s a nice little option if you want to create a function-based index that indexes only those rows where a column is null.
A recent posting on the comp.databases.oracle.server newsgroup pointed me to a rather elderly Ask Tom question (originally posed in July 2004, last updated June 2011) where Tom produced an extraordinary observation. The response times for the following two queries are completely different (on Oracle 9.2 on his data set):
My note on “NOT IN” subqueries is one of the most popular on my blog, staying in the top 5 hits for the last five years – but it’s getting a bit old, so it’s about time I said something new about “NOT IN” – especially since the Null Aware Anti Join has been around such a long time. The example I want to talk about is, as so often, something that came up as a problem on a customer site. Here’s a bit of SQL to model the situation, which is currently running under Oracle 188.8.131.52:
From time to time a request for a query to “show the current SQL” for each session appears on the internet. It’s not really difficult to write something suitable, but this particular request is an interesting demonstration of how much inertia there is in the Oracle world. Here’s a recent suggestion of what the query should look like:
The UKOUG conference is over for another year – but it has left me with plenty to do and lots of things to investigate. Here’s just one little point that I picked up during one of the 10 minute “Oak Talks” that members of the Oak Table Network were doing in the lunch breaks.
There is a fairly well-known strategy for generating a list of numbers by using a “select from dual … connect by …” query, but I hadn’t realised that there were two ways of using it. The code I’ve usually used is this:
rownum <= 4000
Here’s a video of the presentation that I did as the keynote for the Turkish Oracle Users’ Group conference earlier on this year. Recording courtesy of the people at Formspider.
The topic is about tuning a two-table join, and the video is about an hour long (so there’s a lot more that could be said on the topic, and one day I might do a half day “masterclass” on it).
A question came up on Oracle-L recently about the difference in work done by the following two queries:
SELECT /*+ RULE */
TABLE(CAST(:B1 AS DOMAIN_LIST)) DL
DOM_NAME = DL.COLUMN_VALUE
DOM_NAME IN (
FROM TABLE(CAST(:B1 AS DOMAIN_LIST))
Before saying anything else, I should point out that these two queries are NOT logically equivalent unless you can guarantee that the table() operator returns a unique set of values – and Oracle doesn’t allow uniqueness to be enforced on collections.
Here’s an interesting question from the OTN database forum:
“If I delete 90% of the rows from a table which has a few indexes, without rebuildling or coalescing indexes afterwards, will this improve the performance of index range scans ?”
The thing that makes it interesting is the scope it gives you for imagining reasons why the performance won’t change, or might get better, or could get worse. So how about it – can you think of an argument for each of the three possibilities ?
A little while ago I published a note explaining how it was possible to find queries which ran faster if you manually de-coupled the index and table accesses. Here’s a further example that came up in discussion on a client site recently. The query looks something like this (at least in concept, although it was a little more complex, with some messy bits around the edges):
Following on from yesterday’s post on consistent reads, I thought I’d make the point that the way you work can make an enormous difference to the amount of work you do. Here’s a silly little demo (in 10.2.0.3):
Here’s a quick demo to make a point about consistent reads (prompted by a question on the Oracle-L mailing list):
One of the problems of building models of Oracle activity is that it’s easy to build the wrong model. One of the commonest issues appears with repetitive actions – how do you write code that repeats a simple action many times in a row. It’s often enough to write a simple pl/sql loop but there are cases where a pl/sql loop behaves very differently from a long list of individual SQL statements – which is why I’ve occasionally used a very simple-minded approach to avoid that particular trap.