Are you actually *using* Oracle partitioning?

Partitioning is sometimes sold as the panacea to all performance problems. Anyone who has added local indexes on a column other than the one on which the table was partitioned quickly finds out this isn’t always the case.

Keep in mind that partitioning is not only about improving performance, but also increasing availability and manageability.

I came up with what is below to run across our databases and determine if we were actually using it, and where. It is an expensive option ($11.5K US per processor), so if you’re not using it, why pay for it?

select *
  from (select dhss.sql_id,
               object_name,
               dbms_lob.substr(sql_text,2000,1),
               sum(executions_delta) execs,
               operation,
               options
          from dba_hist_sql_plan dhsp,
               dba_hist_sqlstat dhss,
               dba_hist_sqltext dhst
          where dhsp.sql_id = dhss.sql_id
            and dhst.sql_id = dhss.sql_id
            and operation like '%PARTITION%'
            and options like '%SINGLE%'
          group by dhss.sql_id,
                   dbms_lob.substr(sql_text,2000,1),
                   operation,
                   object_name,
                   options
          order by 4 desc)
  where rownum <= 10000;

In general, you want to focus on the ones that have an OPTIONS value of SINGLE. This indicates partition pruning, which is where partitioning really shines from a performance perspective.