January 15, 2010 5 Comments
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.