Identify the Tables you wish to monitor Indexes

set lines 110 pages 500
col tbln format a30 hea 'Table'
col indx format a30 hea 'Index'
col ityp format a3 hea 'Typ'

variable usn varchar2(20);
exec :usn := 'SH';

break on tblo on tbln

select table_name tbln, index_name indx,
decode(INDEX_TYPE,'NORMAL','NML','BITMAP','BMP','IOT - TOP','IOT','DOMAIN','DOM','LOB','LOB',
'FUNCTION-BASED BITMAP','FBB','FUNCTION-BASED NORMAL','FBN',INDEX_TYPE) ityp
from all_indexes
where owner=:usn and table_owner=:usn
order by table_name, index_name;

Table..........................Index..........................Typ
------------------------------ ------------------------------ ---
CHANNELS...................... CHANNELS_PK....................NML
COSTS..........................COSTS_PROD_BIX................ BMP
.............................. COSTS_TIME_BIX................ BMP
COUNTRIES......................COUNTRIES_PK.................. NML
CUSTOMERS......................CUSTOMERS_GENDER_BIX.......... BMP
.............................. CUSTOMERS_MARITAL_BIX..........BMP
.............................. CUSTOMERS_PK.................. NML
.............................. CUSTOMERS_YOB_BIX..............BMP
DR$SUP_TEXT_IDX$I..............DR$SUP_TEXT_IDX$X..............NML
DR$SUP_TEXT_IDX$K..............SYS_IOT_TOP_50730..............IOT
DR$SUP_TEXT_IDX$N..............SYS_IOT_TOP_50735..............IOT
FWEEK_PSCAT_SALES_MV.......... FW_PSC_S_MV_CHAN_BIX.......... BMP
.............................. FW_PSC_S_MV_PROMO_BIX..........BMP
.............................. FW_PSC_S_MV_SUBCAT_BIX........ BMP
.............................. FW_PSC_S_MV_WD_BIX............ BMP
PRODUCTS...................... PRODUCTS_PK....................NML
.............................. PRODUCTS_PROD_CAT_IX.......... NML
.............................. PRODUCTS_PROD_STATUS_BIX...... BMP
.............................. PRODUCTS_PROD_SUBCAT_IX........NML
PROMOTIONS.....................PROMO_PK...................... NML
SALES..........................SALES_CHANNEL_BIX..............BMP
.............................. SALES_CUST_BIX................ BMP
.............................. SALES_PROD_BIX................ BMP
.............................. SALES_PROMO_BIX................BMP
.............................. SALES_TIME_BIX................ BMP
SUPPLEMENTARY_DEMOGRAPHICS.... SUP_TEXT_IDX.................. DOM
TIMES..........................TIMES_PK...................... NML

Turn Monitoring Usage ON the Indices related to the Table you wish to Examine

For this example we will monitor the indices pertaining to the SH.PRODUCTS table

alter index SH.PRODUCTS_PK monitoring usage;
alter index SH.PRODUCTS_PROD_CAT_IX monitoring usage;
alter index SH.PRODUCTS_PROD_STATUS_BIX monitoring usage;
alter index SH.PRODUCTS_PROD_SUBCAT_IX monitoring usage;

Examine the V$OBJECT_USAGE view to see if the Indices are being Used

After a reasonable period of time or if you have a sample set of user queries to execute you could begin to examine the use of the indices on your table.

col tbln format a30 hea 'Table'
col indx format a30 hea 'Index'
col mntr format a10 hea 'Monitoring'
col used format a4 hea 'Used'

break on tbln

select table_name tbln, index_name indx, monitoring mntr, used
from v$object_usage
where table_name = 'PRODUCTS'
order by table_name, index_name;

See if the PRODUCTS_PK index is being used.

select distinct PROD_ID from SH.PRODUCTS;

Table..........................Index..........................Monitoring Used
------------------------------ ------------------------------ ---------- ----
PRODUCTS...................... PRODUCTS_PK....................YES........YES
.............................. PRODUCTS_PROD_CAT_IX.......... YES........NO
.............................. PRODUCTS_PROD_STATUS_BIX...... YES........NO
.............................. PRODUCTS_PROD_SUBCAT_IX........YES........NO

See if the PRODUCTS_PROD_CAT_IX index is being used.

select distinct PROD_CATEGORY from SH.PRODUCTS;

PROD_CATEGORY
--------------------------------------------------
Electronics
Hardware
Peripherals and Accessories
Photo
Software/Other

Examine the V$OBJECT_USAGE view to see if the Indices are being Used (see above example)

Table..........................Index..........................Monitoring Used
------------------------------ ------------------------------ ---------- ----
PRODUCTS...................... PRODUCTS_PK....................YES........YES
.............................. PRODUCTS_PROD_CAT_IX.......... YES........YES
.............................. PRODUCTS_PROD_STATUS_BIX...... YES........NO
.............................. PRODUCTS_PROD_SUBCAT_IX........YES........NO

See if the PRODUCTS_PROD_STATUS_BIX index is being used.

select count(*) from SH.PRODUCTS;

Examine the V$OBJECT_USAGE view to see if the Indices are being Used (see above example)

Table..........................Index..........................Monitoring Used
------------------------------ ------------------------------ ---------- ----
PRODUCTS...................... PRODUCTS_PK....................YES........YES
.............................. PRODUCTS_PROD_CAT_IX.......... YES........YES
.............................. PRODUCTS_PROD_STATUS_BIX...... YES........YES
.............................. PRODUCTS_PROD_SUBCAT_IX........YES........NO

See if the PRODUCTS_PROD_SUBCAT_IX index is being used.

select distinct PROD_SUBCATEGORY from SH.PRODUCTS;

Examine the V$OBJECT_USAGE view to see if the Indices are being Used (see above example)

Table..........................Index..........................Monitoring Used
------------------------------ ------------------------------ ---------- ----
PRODUCTS...................... PRODUCTS_PK....................YES........YES
.............................. PRODUCTS_PROD_CAT_IX.......... YES........YES
.............................. PRODUCTS_PROD_STATUS_BIX...... YES........YES
.............................. PRODUCTS_PROD_SUBCAT_IX........YES........YES

Turn Monitoring Usage OFF for the Indices related to the Table you examined

For this example we will stop monitoring the indices pertaining to the SH.PRODUCTS table.

alter index SH.PRODUCTS_PK nomonitoring usage;
alter index SH.PRODUCTS_PROD_CAT_IX nomonitoring usage;
alter index SH.PRODUCTS_PROD_STATUS_BIX nomonitoring usage;
alter index SH.PRODUCTS_PROD_SUBCAT_IX nomonitoring usage;