Oracle PLAN_TABLE: Column OTHER

Second level XML elements

The following query is an attempt to find the second level (one below the root element other_xml, that is) XML elements:

select
   count(*),
   t.name
from
-- v$sql_plan        p,
   dba_hist_sql_plan p,
   xmlTable(
        '/other_xml/*'
         passing xmlType(p.other_xml)
      columns
         name varchar2(4000) path './name()'
   ) t
where
   p.other_xml is not null
group by
   t.name
order by
   t.name;

On a 19c database, this query returned these elements:

display_map
hint_usage
info
outline_data
peeked_binds
qb_registry
spd
stats

info

Attributes of info

The following query finds the possible attribute names of the <info> element. In 19c, these are

id
note
type

select
   count(*),
   t.name
from
-- v$sql_plan p,
   dba_hist_sql_plan p,
   xmlTable(
        '/other_xml/info/@*'
         passing xmlType(p.other_xml)
      columns
         name varchar2(4000) path './name()'
   ) t
where
   p.other_xml is not null
group by
   t.name
order by
   t.name;

Values of the type attribute

The values of the type attribute incude

adaptive_plan
baseline_repro_fail
cardinality_feedback
cpu_rate
db_version
derived_cpu_dop
derived_io_dop
dop
dop_op_reason
dop_reason
dynamic_sampling
has_user_tab
idl_reason
index_size
io_rate
nodeid/pflags
parse_schema
pdml_reason
performance_feedback
plan_hash
plan_hash_2
plan_hash_full
px_ext_opns
px_in_memory
px_in_memory_imc
queuing_reason
slave_parse
sql_patch

These values can be queried like so:

select
   count(*),
   t.name
from
-- v$sql_plan p,
   dba_hist_sql_plan p,
   xmlTable(
        '/other_xml/info/@type'
         passing xmlType(p.other_xml)
      columns
         name varchar2(4000) path '.'
   ) t
where
   p.other_xml is not null
group by
   t.name
order by
   t.name;

hint_usage

--
-- As so often, Frank Pachot's insights were very helpful
-- https://franckpachot.medium.com/oracle-19c-hint-usage-reporting-345563a461f0
--
with h as (
select
-- plan.sql_id,
-- other_xml,
   plan.qblock_name                                          query_block_name_plan,
   qb.name                                                   query_block_name,
   qb.status                                                 query_block_status  ,
-- qb.hints.getClobVal()                                     hints,
-- hint.o,                                                   -- ???
   case  hint.status
         when 'PE'   then 'Parse error'
         when 'UR'   then 'Unresolved'
         when 'NU'   then 'Not used (NU)'
         when 'PU'   then 'Not used (PU)'
         else              hint.status
   end                                                       hint_status   ,
   --
   lower(regexp_replace(hint, '([^(]*).*'   , '\1'))         hint_name     ,
   lower(regexp_substr(hint, '\((*.*)\)'    ))               hint_arguments,  
   hint.hint                                                               , -- Text of hint
   hint.reason,   -- index specified in the hint doesn't exist,
                  -- rejected by IGNORE_OPTIM_EMBEDDED_HINTS
                  -- same QB_NAME hints for different query blocks
                  -- ORDER_SUBQ referenced query block name, which cannot be found
                  -- hint overridden by another in parent query block
   hint.obj_alias,
   hint.hint_ts
from
-- plan_table        plan,
-- v$sql_plan        plan,
   dba_hist_sql_plan plan,
   xmlTable('/*/hint_usage/q' passing xmltype(other_xml)
                 columns
                    name     VARCHAR2(4000) path './n',
                    status   varchar2(   3) path '@st',  -- @st = status ?
                    hints    xmlType        path './*'
   ) qb,
--
-- Search for hints with '//h' because
-- the <h> tag sometimes occurs directly below <hint_usage> and
-- sometimes below <t>.
--
   xmlTable('//h'   passing qb.hints
                    columns
                       o              varchar2(   3) path '@o'    ,       -- EM or OU or ?
                       status         varchar2(   3) path '@st'   ,
                       hint           varchar2(4000) path 'x'     ,       -- The text of the hint (clob?)
                       reason         varchar2(4000) path 'r'     ,      
                       obj_alias      varchar2(4000) path './../f',
                       hint_ts        varchar2(   3) path './../@st'
   ) hint  
where
   other_xml is not null
)
select
   *

-- hint_status,
-- hint_name,
-- hint_arguments
-- hint,
-- obj_alias,
-- hint_ts

   -- count(*), hint_name, hint_status
from
   h
;

Querying OTHER_XML with XPATH expressions

other_xml can be queried with XPath expressions.