SQL Profile for INSERT-ALL

We’ve had a performance issue after our last release with one insert statement – the plan was not the one we were expecting – which we decided to address by creating a SQL profile from the same statement’s “good” plan we’ve got in UAT environment. We’ve hit a problem though – the profile wasn’t kicking in, and our DBA suggested that the reason for that was that the SQL we used as the source of the outline information was missing the actual outline information. Oracle keeps the outline information in V$SQL_PLAN.OTHER_XML in a outline_data section. I decided to check out whether there were any other SQLs which missing outline_data section:

SQL> select count(*)
  2  from   (select p.*
  3                ,value(d) info
  4          from   dba_hist_sql_plan p
  5                ,table(xmlsequence(extract(xmltype(p.other_xml),
  6                                           '/other_xml/outline_data')))(+) d
  7          where  other_xml is not null)
  8  where  info is null;

  COUNT(*)
----------
        82

SQL>

So there were a few of those. What caught my eye though when I checked the actual query plans was the operations which were missing the outlines:

SQL> select unique operation
  2  from   (select p.*
  3                ,value(d) info
  4          from   dba_hist_sql_plan p
  5                ,table(xmlsequence(extract(xmltype(p.other_xml),
  6                                           '/other_xml/outline_data')))(+) d
  7          where  other_xml is not null)
  8  where  info is null;

OPERATION
------------------------------
LOAD TABLE CONVENTIONAL
INDEX BUILD
MULTI-TABLE INSERT
PX COORDINATOR

SQL>

Notice multi-table insert (which was exactly our case) – for some reason Oracle is not creating (or populating) the outline section when you are doing multi-table insert, at least not on 11.2.0.3 I’m using. I’ve also checked whether there are any multi-table inserts which have got outline_data and as expected have found none.