The info in OTHER_XML of view DBA_HIST_SQL_PLAN

I had some time to spend, killing time, and thought about something that was “on my list” of things to do, to investigate, for a long time. While showing results to some OakTable members, Jonathan Lewis pointed me to this great article from Martin Bach, also touching the subject of this data in the OTHER_XML column of view DBA_HIST_SQL_PLAN or V$SQL_PLAN.

Martin’s great post is about the relations between all the bits and pieces out there regarding Adaptive Plan’s. I urge you to also read the great links pointing to articles, the documentation and post’s touching this subject, mentioned in the beginning of the article.

Sometimes it is funny when stuff starts happening around the same time. I had no idea of Martin’s post, but was merely driven by Doug Burns post, “OTHER_XML”, from a while ago (2012). You might also think, “Man, you are slow following up on things on your to do list”. You are probably right. “Time to do things is sparse sometimes…” 😉

Anyway, as the documentation shows about the view DBA_HIST_SQL_PLAN:

OTHER_XML CLOB Provides extra information specific to an execution step of the execution plan. The content of this column is structured using XML because it allows multiple pieces of information to be stored, including the following:

Name of the schema against which the query was parsed

Release number of the Oracle Database that produced the explain plan

Hash value associated with the execution plan

Name (if any) of the outline or the SQL profile used to build the execution plan

Indication of whether or not dynamic sampling was used to produce the plan

The outline data, a set of optimizer hints that can be used to regenerate the same plan

`OTHER_XML`	`CLOB`		Provides extra information specific to an execution step of the execution plan. The content of this column is structured using XML because it allows multiple pieces of information to be stored, including the following: Name of the schema against which the query was parsed Release number of the Oracle Database that produced the explain plan Hash value associated with the execution plan Name (if any) of the outline or the SQL profile used to build the execution plan Indication of whether or not dynamic sampling was used to produce the plan The outline data, a set of optimizer hints that can be used to regenerate the same plan

Annoyingly enough this column is a CLOB (aka not XMLType). So be very aware that, if you want to using the following code to show the flattened XML content, it will not perform.

[begin rant]

Somehow internally, Oracle development is still stuck in old deprecated operators and functions like EXTRACTVALUE (deprecated since 11.2.0.1), and using storage for XML that will not perform, when using XQuery or the old XML/SQL stuff. If not only, it, the use of CLOB for XML content, bypasses all the performance optimizations the Oracle XML DB team introduced since Oracle database version 10.2. Especially from database version 11.2.0.3 and onwards, I start noticing optimizations that surpass “the old methods” and are way more efficient.

[end rant]

The following is “as is” and I hope it might be useful. I am not sure if I touched all sections in the XML content, so always cross-reference with the original “OTHER_XML” column data or my Pretty Printed column section. Regarding the “massive” statement and formatting issues of XML, I put the example in a more readable text file (see link below). The SQL/XQuery statement has been tested on a version 11.2.0.3 and 12.1.0.2 database. Use bits and pieces of the “XMLTABLE construct/statement”, improve it and share your version with the community.

Do something like the following and then use the SQL mentioned in the text file below to investigate further:

--
SELECT COUNT(*) 
  FROM dba_source 
 WHERE owner = 'SYS';
--
-- count(*) = 148117
--
select PREV_SQL_ID,
       PREV_CHILD_NUMBER
  from v$session
where sid = 
( select sid 
    from v$mystat 
   where rownum=1 ) ;
--
-- PREV_SQL_ID = 'gypnfv5nzurb0', PREV_CHILD_NUMBER =	1
--
SELECT count(*)  FROM DBA_HIST_SQL_PLAN 
 WHERE sql_id = 'gypnfv5nzurb0' 
   AND OTHER_XML is not null;
--
-- count(*) = 1
--
-- -------------------------------------------------------------------------------
-- use the XMLTABLE based statement to investigate further 
-- to gather output like for example:
-- -------------------------------------------------------------------------------
1	IGNORE_OPTIM_EMBEDDED_HINTS                       IGNORE_OPTIM_EMBEDDED_HINTS
2	OPTIMIZER_FEATURES_ENABLE('11.2.0.3')             OPTIMIZER_FEATURES_ENABLE
3	DB_VERSION('11.2.0.3')                            DB_VERSION
4	ALL_ROWS                                          ALL_ROWS
5	OUTLINE_LEAF(@"SEL$88122447")                     OUTLINE_LEAF
6	MERGE(@"SEL$641071AC")                            MERGE
7	OUTLINE(@"SEL$1")                                 OUTLINE
8	OUTLINE(@"SEL$641071AC")                          OUTLINE
9	MERGE(@"SEL$07BDC5B4")                            MERGE
10	OUTLINE(@"SEL$2")                                 OUTLINE
11	OUTLINE(@"SEL$07BDC5B4")                          OUTLINE
12	MERGE(@"SEL$4")                                   MERGE
13	OUTLINE(@"SEL$3")                                 OUTLINE
14	OUTLINE(@"SEL$4")                                 OUTLINE
15	FULL(@"SEL$88122447" "X$KGLCURSOR_CHILD"@"SEL$4") FULL
------------------------------------------------------------------------------- */

Have a look at the following output [click link here]

Don’t forget to read Martin’s excellent post, diving further in the meaning of bits and pieces of the OTHER_XML content.

HTH