{"id":1684,"date":"2012-07-14T12:00:00","date_gmt":"2012-07-14T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1684"},"modified":"2012-07-14T12:00:00","modified_gmt":"2012-07-14T12:00:00","slug":"other_xml","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2012\/07\/14\/other_xml\/","title":{"rendered":"OTHER_XML"},"content":{"rendered":"

Some features in this post require a Diagnostics Pack license.<\/em><\/p>\n

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.<\/p>\n

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<\/a> and the follow-up comments focus on the peeked values of bind variables. <\/p>\n

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) :-<\/p>\n

1) Identify the various execution plans :-<\/p>\n

SELECT \u00a0\u00a0\u00a0 snap_id, sql_id, plan_hash_value \nFROM \u00a0\u00a0\u00a0 dba_hist_sqlstat \nWHERE \u00a0\u00a0\u00a0 sql_id='a01f43qrd6a7g' \nORDER BY snap_id; \n<\/pre>\n
2) Drag out the various contents of the OTHER_XML column for this statement :-<\/p>\n
SELECT other_xml\nFROM dba_hist_sql_plan \nWHERE sql_id='a01f43qrd6a7g' \nAND other_xml is not null; \n<\/pre>\n
By spooling individual results to different XML files, you end up with a couple of files like this. <\/p>\n
test1.xml<\/a>
test2.xml<\/a><\/p>\n
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.<\/p>\n
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>\n
P.S. Yes, that optimizer_index_cost_adj value is for real, too 😉<\/p>\n","protected":false},"excerpt":{"rendered":"
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… Continue reading OTHER_XML<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1684","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":953,"url":"http:\/\/orcldoug.com\/blog\/2005\/08\/17\/more-on-and-catpatch-sql\/","url_meta":{"origin":1684,"position":0},"title":"More on ? and catpatch.sql","date":"August 17, 2005","format":false,"excerpt":"When I was writing about the ? shortcut in sqlplus, I half-remembered that I'd discovered this while some time ago while looking at Oracle-supplied scripts (always a useful learning experience). So I went to check that I was right that Oracle uses this and couldn't find an example. Annoying, because\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1555,"url":"http:\/\/orcldoug.com\/blog\/2009\/12\/18\/bug-hunting\/","url_meta":{"origin":1684,"position":1},"title":"Bug Hunting","date":"December 18, 2009","format":false,"excerpt":"It's early days but I think I became the joint father of a bug report this week. This is bug number 9219636. SQL> CREATE TYPE INTEGER_ARRAY_T AS TABLE OF INTEGER \u00a0 2\u00a0 \/ Type created. SQL> SQL> CREATE TABLE V_SESSION_VALID ( \u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SID NUMBER , \u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SESSION_ID NUMBER\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1505,"url":"http:\/\/orcldoug.com\/blog\/2009\/07\/02\/session-level-ash-reports\/","url_meta":{"origin":1684,"position":2},"title":"Session Level ASH Reports","date":"July 2, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.I noticed a post on H.Tongu\u00e7 Y\u0131lmaz's blog about filtering ASH data to look at the actions of a specific instrumented query. There are a few strange things that I was going to comment on but the blog requires me to\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/18.133.199.212\/wp-content\/uploads\/recovered\/ash_report.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1481,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/09\/diagnosing-locking-problems-using-ash-part-4\/","url_meta":{"origin":1684,"position":3},"title":"Diagnosing Locking Problems using ASH \u2013 Part 4","date":"April 9, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.No sooner had I finished part 3 with some conclusions than I thought of another example I should have included and then someone else made a comment in an email which suggested another. (Thanks, JB!) \"It's probably worth pointing out that\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1495,"url":"http:\/\/orcldoug.com\/blog\/2009\/05\/01\/diagnosing-locking-problems-using-ashlogminer-the-end\/","url_meta":{"origin":1684,"position":4},"title":"Diagnosing Locking Problems using ASH\/LogMiner \u2013 The End","date":"May 1, 2009","format":false,"excerpt":"Except\u00a0it's not the\u00a0end, of course. What I mean is\u00a0that I usually agree with\u00a0what Miladin Modrakovic said in one of his comments on his first deadlock blog post.\"There is always way around.\"As I keep saying, there are many different ways of diagnosing locking problems. Which one works best\u00a0depends on the situation\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1449,"url":"http:\/\/orcldoug.com\/blog\/2008\/09\/29\/oow-2008-presentations\/","url_meta":{"origin":1684,"position":5},"title":"OOW 2008 Presentations","date":"September 29, 2008","format":false,"excerpt":"I noticed via H.Tongu\u00e7 Y\u0131lmaz's blog that some of the presentation slides have started to appear.1) Go to http:\/\/www28.cplan.com\/cc208\/login.jsp 2) Username\/Password is cboracle\/oraclec6 (Updated later - actually, maybe this doesn't work for unregistered attendees yet)3) Search for sessions4) Click link in right hand column where the presentation exists5) You might\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1684","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1684"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1684\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1684"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1684"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1684"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}