OTHER_XML

Some features in this post require a Diagnostics Pack license.

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.

test1.xml
test2.xml

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.

P.S. Yes, that optimizer_index_cost_adj value is for real, too