Problem plans

I was looking at a performance problem a week or so before Christmas which I touched on in previous posts recently.

These are big plans and I had trouble uploading them in a decent format for this blog.

The format isn’t great but I’ve finally put them up on on one of these pastebin things for now:

As to how long they remain available, we’ll see.

These are probably of limited interest to anyone.

But they do illustrate a couple of things.

1. There are a lot of distractions in a big plan.

These are big plans at around 2800 lines.

You can see a lot of the deadly duo – MERGE JOIN CARTESIAN and BUFFER SORT operations- but these themselves are not the cause of the performance degradation (see point 2 about relative goodness).

It’s not until you get to lines six to seven hundred that you start to see any symptom of the performance difference.

The main performance problems stem from the full table scans of POSITIONS (e.g. See Id 717 in Bad Plan) and the HASH JOIN (Id 605) rather than the INDEX ACCESS (Id 719 in Good plan) and NESTED LOOP (605/6) operations, operations which are repeated throughout the rest of the plan

2. Good can be relative

I don’t think anyone would look at the “good” plan and be comfortable with it.

Then again could you could ever look at that sort of length of plan and think “Yes!” ?

The point is that the business were happy with the report performance that this plan provided and in the current business environment there’s rarely appetite to change things just because they’re not good technically.

3. It’s difficult to hint adequately

There are a handful of hints in the source code which are presumably intended to offer some sort of plan stability.

However, with a plan of this size, a couple of USE_NL hints are not going to be adequate.

As Jonathan Lewis points out, it’s very difficult to hint well/completely.

There are other ways to achieve plan stability if that’s what you want.

In this case, those handful of hints have probably contributed as much to plan instability as anything else.

4. Reusability is not always a good thing

These plans come from a reporting system which is doing too much calculation dynamically.

There is a core of Views on Views on Pipelined Table Functions on top of more Views which aims to provide a reusable interface to all the information being reported on and the various reports on top.

It’s not unusual to get performance problems from that sort of setup and probably one of the reasons that a handful of hints were used in the first place.

I would nearly always favour performance over reusability.

P.S. These plans also show the wrapping issue mentioned previously. At the time of writing, I still have an open Oracle SR for that. They had asked for full dumps of tables, statistics, etc. However I reproduced it with a standalone query joining dba_objects 50 times.


5 Responses to Problem plans

  1. Gary says:

    How do you look at a plan like that and not go screaming from the room ?
    There appear to be chunks that sort of repeat but sometimes not quite. It’s got to be some query.

  2. dombrooks says:

    It’s a challenge, isn’t it?
    Small plan, big plan, the principles are the same, right?

    Actually the query isn’t too bad. Each layer of view hides a bit of the complexity so that when you look at it from the uppermost level, there’s really not that much to see.

  3. Narendra says:

    Hmm. I would probably be switched off just pressing PAGE-DOWN a couple of times. 🙂
    BTW, do you mind sharing the standalone query joining dba_objects 50 times that produced the problem ?

  4. Dom Brooks says:

    Sorry folks – the place were I put up the big plans has disappeared. Haven’t got them anymore.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: