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:

• Good Plan
• Bad Plan

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.

DBMS_XPLAN display issues

DBMS_XPLAN is a great little tool for getting formatted SQL execution plans and associated information. Good enough to make my revised top 3 yesterday.

Whether following an explain plan:

EXPLAIN PLAN FOR 
SELECT ... FORM ...;

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY);

Or pulling a plan from memory using:

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(<sql_id>,<child_cursor>,<format>));

Typically used with the gather_plan_statistics hint like this:

SELECT /*+ gather_plan_statistics */ .... 
FROM ... 
WHERE...;

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

Or pulling a plan still in memory by specifying the sql_id and, optionally, the child cursor.

For further information on the options for the third format parameter, here is a nice post on Rob van Wijk’s blog.

Jonathan Lewis also has some articles on dbms_xplan here and here.

Another handy option which I’ve been using recently is the ability to pull plans which are no longer in memory from the AWR repository using:

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(<sql_id>,<child_cursor>,<db_id>,<format>));

Whilst troubleshooting a recent performance problem where a good (all things are relative) plan had gone bad, the bad plan was no longer in memory. However, I was able to pull the bad plan from the AWR repository and also a number of variations on the good plan.

However, this was a big plan – some 3000 lines – and illustrates some display issues which you can get.

See this excerpt:

| 316 | E JOIN CARTESIAN                                        MERG |                                |    790 |                                                                                                                                                                                             
| 317 | IST ITERATOR                                             INL |                                |        |                                                                                                                                                                                             
| 318 | BLE ACCESS BY INDEX ROWID                                 TA | CLASSIFICATIONS                |      5 |                                                                                                                                                                                             
| 319 | NDEX RANGE SCAN                                            I | CLASSIFICATIONS_IX1            |      5 |                                                                                                                                                                                             
| 320 | FER SORT                                                 BUF |                                |    158 |                                                                                                                                                                                             
| 321 | RT UNIQUE                                                 SO |                                |    158 |                                                                                                                                                                                             
| 322 | NDEX FAST FULL SCAN                                        I | PK_ELEMENT_AGGREGATES          |    158 |                                                                                                                                                                                             
| 323 | E ACCESS FULL                                           TABL | PERFORMANCE_ELEMENTS           |   1863 |                                                                                                                                                                                             
| 324 |  UNIQUE SCAN                                           INDEX | PK_CLASSIFICATION_ELEMENTS     |      1 |                                                                                                                                                                                             
| 325 |  SCAN                                            INDEX RANGE | VEHICLE_METRICS_PK             |      1 |                                                                                                                                                                                             
| 326 | S BY INDEX ROWID                                 TABLE ACCES | POSITIONS                      |      1 |                                                                                                                                                                                             
| 327 | E SCAN                                            INDEX RANG | POSITIONS_UK                   |      1 |                                                                                                                                                                                             
| 328 |  INDEX ROWID                                 TABLE ACCESS BY | PRR_MAPPINGS                   |      1 |                                                                                                                                                                                             
| 329 | AN                                            INDEX RANGE SC | PRR_MAPPINGS_UX1               |      1 | 

The OUTPUT column is wrapped and does not make such investigations and interpretations any easier.
Note that this is NOT a SQL*Plus display issue as far as I can work out. It’s not the whole line which is wrapping.

As far as I know, there is no solution to this.
I have an Oracle SR open for confirmation but after 4 days I’ve yet to even receive confirmation of my ticket…

If there’s a solution, let me know.