In
my career I saw when a VLDB or Old database (>10 Years) contains minimum 10
to 30 % Unused Index created .Its overall reduces database output. Its global cumulative
loss is also huge.
How
can impact it in database if index are extra in number. It can consume
significant amount of CPU usage and memory too which finally can impact on
write intensive database.
So
its better to make regulation and stringent audit and review process before creation
any index on a database.If
already its created historically in database and database are mission critical
then its very tough at the same time its very important to find out unused
Index
to
make database performance improvement.
You
can use monitoring index feature of Oracle to find out which index are unused
during some time window. For some time period we can activate its monitoring
feature of all
index
or suspected index. Once complete time window (Say for One Month) monitoring
need to stop and then check weather its used or not in dba_object_usage table
.
But
which time window is best for index monitoring are very critical .Its depends
upon application and best time could be year
end Dec to Jan .Normally some critical report are run once in a year and some
index are used that time only .To
smoothen the year end process index are important .
Process
of monitoring Index are describe below :
1.
Alter index for Monitoring using below SQL
alter index '<Index Name >' monitoring
usage ;
2.
Wait for time here say for One month .Its better for Dec to Feb .
3.
Alter index for No Monitoring using below SQL
alter index
'<Index Name >'
nomonitoring usage ;
4.
Now check it in dba_object_usage table using below SQL
SELECT * FROM dba_object_usage WHERE used =
'NO';
We
can use below script for all index monitoring script
1.
Create script for activation monitoring of all index and run it
set
pages0
spool
enable_monitoring.sql
select
'set echo on' from dual;
select
'alter index ' || owner || '.' ||
index_name || ' monitoring usage ;'
from
user_indexes
order
by 1;
spool
disable_monitoring.sql
2.
Wait for time here say for One month .It’s better for Dec to Feb .
3.
Create script for disable No monitoring of all index and run it
select
'set echo on' from dual;
select
'alter index ' || owner || '.' ||
index_name || ' nomonitoring usage ;'
from user_indexes order by 1;
spool
off
4.
SELECT * FROM dba_object_usage WHERE used = 'NO';
5.
OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session
or system level
6.
We can alter index invisible and wait for some time to see its effect on
application .If it’s on then we can remove it from database safely.
ALTER INDEX '<Index Name >' INVISIBLE;
Create script for make index invisible
select 'ALTER INDEX ' || owner || '.' ||
index_name || ' INVISIBLE;' FROM dba_object_usage WHERE used = 'NO';
7.
Its helps us to check VISIBILITY check of index status.
SELECT INDEX_NAME, VISIBILITY FROM
USER_INDEXES WHERE INDEX_NAME in ('<Index Name >');
8.
If its impact on application then make it visible once again using below SQL
ALTER INDEX '<Index Name >' VISIBLE;
On EMP table I am creating one Index for showing its usage
SQL>
create index idx_empeno on emp(empno);
SQL>
alter index idx_empeno monitoring usage;
SQL>
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
----------
---------- --------- ---------- --------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 20
2
row selected.
SQL>
alter index idx_empeno monitoring usage;
SQL>
select index_name, table_name, used from v$object_usage;
INDEX_NAME TABLE_NAME USE
------------------------------
------------------------------ ---
idx_empeno EMP NO
1
row selected.
SQL>
select * from emp where deptno = 30;
EMPNO
ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
----------
---------- --------- ---------- --------- ---------- ---------- ----------7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
SQL>
select index_name, table_name, used from v$object_usage;
INDEX_NAME TABLE_NAME USE
------------------------------
------------------------------ ---idx_empeno EMP NO
1
row selected.
SQL> select * from emp where empno < 7380;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
1 row selected.
SQL> select index_name, table_name, used from v$object_usage;
INDEX_NAME TABLE_NAME USE
------------------------------
------------------------------ ---idx_empeno EMP YES
1
row selected.
