SQL_PLAN – other_xml

Just a quick follow-up to my previous post on investigating bad plans using AWR & ASH.

In that article, I mentioned

  1. that it was a shame that the ACCESS_PREDICATES and FILTER_PREDICATES columns were not populated (how this cannot be classified as a bug is beyond comprehension) and
  2. that I was using DBA_HIST_SQLBIND to investigate various peeked binds for that particular SQL based on the LAST_CAPTURED column

However, I should also have mentioned that, from 10gR2 onwards, populated in both V$SQL_PLAN and DBA_HIST_SQL_PLAN, there is a very useful column called OTHER_XML.

From the documentation:

OTHER_XML: “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.

Here is a sample of the peeked bind section from my sql with the problem plan:


  <peeked_binds>
    <bind nam=”:B3″ pos=”3″ dty=”2″ pre=”0″ scl=”0″ mxl=”22″>c3023102</bind>
    <bind nam=”:B2″ pos=”4″ dty=”1″ csi=”873″ frm=”1″ mxl=”32″>524d46</bind>
    <bind nam=”:B1″ pos=”5″ dty=”12″ mxl=”7″>786d0a1f010101</bind>
    <bind nam=”:B3″ pos=”13″ ppo=”3″ dty=”2″ pre=”0″ scl=”0″ mxl=”22″/>
    <bind nam=”:B2″ pos=”14″ ppo=”4″ dty=”1″ csi=”873″ frm=”1″ mxl=”32″/>
    <bind nam=”:B1″ pos=”15″ ppo=”5″ dty=”12″ mxl=”7″/>
    <bind nam=”:B3″ pos=”16″ ppo=”3″ dty=”2″ pre=”0″ scl=”0″ mxl=”22″/>
    <bind nam=”:B1″ pos=”19″ ppo=”5″ dty=”12″ mxl=”7″/>
    <bind nam=”:B3″ pos=”20″ ppo=”3″ dty=”2″ pre=”0″ scl=”0″ mxl=”22″/>
    <bind nam=”:B5″ pos=”21″ ppo=”1″ dty=”2″ pre=”0″ scl=”0″ mxl=”22″/>
    <bind nam=”:B3″ pos=”22″ ppo=”3″ dty=”2″ pre=”0″ scl=”0″ mxl=”22″/>
    <bind nam=”:B6″ pos=”23″ dty=”2″ pre=”0″ scl=”0″ mxl=”22″>c10d</bind>
    <bind nam=”:B1″ pos=”24″ ppo=”5″ dty=”12″ mxl=”7″/>
    <bind nam=”:B6″ pos=”26″ ppo=”23″ dty=”2″ pre=”0″ scl=”0″ mxl=”22″/>
    <bind nam=”:B1″ pos=”27″ ppo=”5″ dty=”12″ mxl=”7″/>
    <bind nam=”:B1″ pos=”29″ ppo=”5″ dty=”12″ mxl=”7″/>
    <bind nam=”:B3″ pos=”31″ ppo=”3″ dty=”2″ pre=”0″ scl=”0″ mxl=”22″/>
    <bind nam=”:B6″ pos=”32″ ppo=”23″ dty=”2″ pre=”0″ scl=”0″ mxl=”22″/>
    <bind nam=”:B1″ pos=”33″ ppo=”5″ dty=”12″ mxl=”7″/>
  </peeked_binds>

I’m not sure if the repetition of bind names and positions but not values is normal – one for further investigation.

But bind variable 1 is what I was talking about previously as my date bind with the data skew and we can convert the values back out of their raw value using, for example, the overloaded DBMS_STATS.CONVERT_RAW_VALUE.

So, from above, taking my B1 bind of “786d0a1f010101″:

declare
l_date date;
begin
dbms_stats.convert_raw_value(rawval => '786d0a1f010101', resval => l_date);
dbms_output.put_line('dt: '||to_char(l_date,'DD/MM/YYYY HH24:MI:SS'));
end;
/

dt: 31/10/2009 00:00:00

Some other posts regarding OTHER_XML:
Kerry Osborne using OTHER_XML to create test scripts with bind variables
Randolf Geist using OTHER_XML to get outlines / create profiles

Follow

Get every new post delivered to your Inbox.

Join 70 other followers