Display multi-column composite indexes

-- *******************************
-- Display composite indexes
-- *******************************

break on index_name skip 1

col index_name format a30
col column_name format a30

select
   index_name,
   column_position,
   column_name
from
   user_ind_columns i
where
   (select
      count(*)
    from
      user_ind_columns u
    where
      u.index_name = i.index_name) > 1
group by
   index_name,
   column_position,
   column_name
order by
   index_name,
   column_position;

In the aggregate, this query can be used to display how often a composite index is being used for a one month period.  This can help identify un-used indexes:

-- *********************************
-- Are composite indexes being used?
-- *********************************
select
   to_char(sn.begin_interval_time,'yyyy-mm') c1,
   p.object_name c2,
   p.search_columns c3,
   count(*)
from
   see code depot for full script
   dba_hist_snapshot sn,
   dba_hist_sql_plan p,
   dba_hist_sqlstat st
where
-- **************************************************
-- Only show data for multi-column composite indexes
-- **************************************************
   (select
      count(*)
   from
      user_ind_columns u
   where
      u.index_name = p.object_name) > 1
and
   st.sql_id = p.sql_id
and
   sn.snap_id = st.snap_id
and
   p.object_owner = 'PUBS'
and
-- ************************************************
-- This is supposed to just show the last 30 days
-- ************************************************
   sysdate > sysdate - 30
group by
   begin_interval_time,
   object_name,
search_columns;

Once we see if composite indexes are being used, we can drill-down an look at a list of all multi-column indexes.

-- ***********************************************
-- Show frequency of use of multi-column indexes
-- ***********************************************
with
   composite_index_name_list
as
   (select
      i.index_name
   from
      user_ind_columns i
   where
      (select
           count(*)
       from
           user_ind_columns u
       where
           u.index_name = i.index_name) > 1
       group by
           index_name
       order by
           index_name)
select
   to_char(sn.begin_interval_time,'yyyy-mm') c1,
   p.object_name c2,
   p.search_columns c3,
   count(*)
from
   see code depot for full script
   dba_hist_snapshot sn,
   dba_hist_sql_plan p,
   dba_hist_sqlstat st,
   composite_index_name_list i
where
-- ****************************************************
-- Only show multi-column indexes from the WITH clause
-- ****************************************************
   i.index_name = p.object_name
and
   st.sql_id = p.sql_id
and
   sn.snap_id = st.snap_id
and
   p.object_owner = 'PUBS'
and
-- ************************************************
-- only for the last 30 days . . .
-- ************************************************
   sysdate > sysdate - 30
group by
   begin_interval_time,
   object_name,
   search_columns;

For each index returned by this query, you can query AWR and see how often an individual index was invoked: