Oracle PLAN_TABLE: Column OTHER_XML

info

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;

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
;