Already a member?
Sign in
Monitor Index Usage
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;
Latest page update: made by olapdba
, Apr 17 2008, 3:51 PM EDT
(about this update
About This Update
Edited by olapdba
2 words added
1 word deleted
view changes
- complete history)
2 words added
1 word deleted
view changes
- complete history)
Keyword tags:
None
More Info: links to this page

