OTHER_XML

Doug's Oracle Blog

Just a small tip that could make things a little easier for you one day when you are trying to work out the underlying cause of a SQL execution plan change that leads to degraded performance, after the problem has occurred.

There are plenty of more technical blog posts out there referring to the contents of the OTHER_XML column in DBA_HIST_SQL_PLAN and other dictionary views. For example, this Jonathan Lewis post and the follow-up comments focus on the peeked values of bind variables.

However, it occurred to me one day that the content of the OTHER_XML column, as well as containing potentially very useful information to help understand the plan difference is also, well, erm XML. Which means that rather than trying to decipher text output in sqlplus or TOAD or whatever you use, you can just spool it to a file and open it in a browser, where the implicit structure makes for an easier read. To show you a specific example from a real performance issue where I knew the SQL_ID of the problematic statement already (although I don't have the statement outputs any more) :-

1) Identify the various execution plans :-

SELECT snap_id, sql_id, plan_hash_value
FROM dba_hist_sqlstat
WHERE sql_id='a01f43qrd6a7g'
ORDER BY snap_id;

2) Drag out the various contents of the OTHER_XML column for this statement :-

SELECT other_xml
FROM dba_hist_sql_plan
WHERE sql_id='a01f43qrd6a7g'
AND other_xml is not null;

By spooling individual results to different XML files, you end up with a couple of files like this.

Opening these in two different browser tabs allows you to flick between the two and, as well as seeing useful information about peeked binds, optimiser version and configuration, you should be able to spot quite quickly that Cardinality Feedback was used in generating one of the execution plans. That was the cause of the performance degradation in this case.

Of course this is just a simple example, but the main point of the post was that there might be some pretty detailed information about the optimiser environment for old execution plans in your AWR repository and that viewing it in a browser might make it a little easier to interpret if you're not absolutely married to parsing text files.

OTHER_XML is one of my favorite places to look for info about SQL statements. Seems like the developers use it like a kitchen drawer where you stuff anything that doesn't really have a designated place. Just throwing the raw XML into a file and opening with a browser is a great idea, since writing queries to deal with XML in the database is a pain for most of us. One of these days maybe I'll get around to doing a post on some of the things I've used OTHER_XML for.

Disclaimer

For the avoidance of any doubt, all views expressed here are my own and not those of past or current employers, clients, friends, Oracle Corporation, my Mum or, indeed, Flatcat. If you want to sue someone, I suggest you pick on Tigger, but I hope you have a good lawyer. Frankly, I doubt any of the former agree with my views or would want to be associated with them in any way.