Applies to ONLY Oracle Database 10gR1 through to 12cR1.
In Oracle Database 12cR2, this feature is replaced. A new blog post of this feature will be posted in due course.
Since Oracle 10g, you can monitor indexes to see if they are being used or not. Which is very useful as indexes, consume unnecessary CPU and I/O on DML activity if not used. Therefore, it’s recommended to monitor indexes and any unused indexes can be dropped, not only freeing vital space but CPU and I/O, resulting in better throughput.
It recommended to enable index monitoring for an appropriate period that is representative of your full workload cycle. For example if you run a report every week, month or even year, just to catch all those activities.
Please Note: It’s important that foreign key constraints have associated indexes to avoid any table level lock on DML. These indirect uses of indexes will not be detected by index monitoring. So be mindful when dropping indexes that are associated with foreign key constraint.
Enable Index Monitoring
To Enable Index Monitoring, you alter the index as shown below:
ALTER INDEX . MONITORING USAGE;
Checking Index Usage
To check if the index has been used, will depends on the version of Oracle:
Oracle 10g/11g
SELECT * FROM V$OBJECT_USAGE;
Database Reference 10g – V$OBJECT_USAGE
Database Reference 11g – V$OBJECT_USAGE
Please Note: Only shows the indexes belonging to the login user.
To see all indexes in the database regardless of the logged on user:
select
do.owner,
t.name table_name, io.name index_name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
ou.start_monitoring,
ou.end_monitoring
from
sys.obj$ io,
sys.obj$ t,
sys.ind$ i,
sys.object_usage ou,
dba_objects do
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and i.obj# = do.object_id
order by 1, 2, 3;
DISCLAIMER: As accessing data dictionary objects directly, this query is not guaranteed to work.
Oracle 12cR1
“The V$OBJECT_USAGE view is deprecated in Oracle Database 12c Release 1 (12.1) and maintained for backward compatibility. Support for this view may be removed in a future release. Oracle recommends that you use the USER_OBJECT_USAGE view instead of the V$OBJECT_USAGE view.”
Database Reference 12cR1 – V$OBJECT_USAGE
For the current logon user:
SELECT * FROM USER_OBJECT_USAGE;
Database Reference 12cR1 – USER_OBJECT_USAGE
For all users:
SELECT * FROM DBA_OBJECT_USAGE;
Database Reference 12cR1 – DBA_OBJECT_USAGE
Dis-enable Index Monitoring
To Dis-enable Index Monitoring, you alter the index as shown below:
ALTER INDEX . NOMONITORING USAGE;
Demo of Index Monitoring
The content of the index_monitoring_demo.sql is below:
-------------------------------------------------------------
--
-- Index Monitoring Demo
--
-- File Name: index_monitoring_demo.sql (blog.zeddba.com)
-- Created: Zahid Anwar (ZedDBA)
--
-- Date: 16/07/2018
-- Version: 1.0
--
-- History
--
--1.0 16/07/2018 ZA Initial Script
--
-------------------------------------------------------------
set echo on
set pages 999 lines 400
CREATE TABLE "EMP"
( "ID" NUMBER,
"FIRST_NAME" VARCHAR2(50),
"LAST_NAME" VARCHAR2(50)
);
pause Press Enter
INSERT INTO "EMP" VALUES (1, 'Zahid', 'Anwar');
INSERT INTO "EMP" VALUES (2, 'Scott', 'Tiger');
INSERT INTO "EMP" VALUES (3, 'Joe', 'Bloggs');
commit;
pause Press Enter
set autotrace on
col id format 99
col first_name format a50
col last_Name format a50
select * from emp;
pause Press Enter
create index emp_id on emp (id);
pause Press Enter
select * from emp where id = 1;
pause Press Enter
set autotrace off
col INDEX_NAME format a20
col TABLE_NAME format a20
col MONITORING format a15
col USED format a10
select * from USER_OBJECT_USAGE;
pause Press Enter
alter index emp_id monitoring usage;
pause Press Enter
select * from USER_OBJECT_USAGE;
pause Press Enter
set autotrace on
select * from emp;
pause Press Enter
set autotrace off
select * from USER_OBJECT_USAGE;
pause Press Enter
set autotrace on
select * from emp where id = 1;
pause Press Enter
set autotrace off
select * from USER_OBJECT_USAGE;
pause Press Enter
alter index emp_id nomonitoring usage;
pause Press Enter
select * from USER_OBJECT_USAGE;
pause Press Enter
DROP TABLE "EMP" PURGE;
Below is the output of the script index_monitoring_demo.sql:
SQL> @index_monitoring_demo.sql
SQL> set pages 999 lines 400
SQL>
SQL> CREATE TABLE "EMP"
2 ( "ID" NUMBER,
3 "FIRST_NAME" VARCHAR2(50),
4 "LAST_NAME" VARCHAR2(50)
5 );
Table created.
SQL>
SQL> pause Press Enter
Press Enter
Table EMP is created in my schema ZEDDBA, please ensure you have appropriate schema with correct privileges. Next we insert 3 rows into the table EMP:
SQL> INSERT INTO "EMP" VALUES (1, 'Zahid', 'Anwar');
1 row created.
SQL> INSERT INTO "EMP" VALUES (2, 'Scott', 'Tiger');
1 row created.
SQL> INSERT INTO "EMP" VALUES (3, 'Joe', 'Bloggs');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> pause Press Enter
Press Enter
With the 3 rows inserted and committed, we select from the EMP table with auto trace on, so we can see the execution plan:
SQL> set autotrace on
SQL> col id format 99
SQL> col first_name format a50
SQL> col last_Name format a50
SQL>
SQL> select * from emp;
ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
2 Scott Tiger
3 Joe Bloggs
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| EMP | 3 | 201 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
784 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> pause Press Enter
Press Enter
Please Note: This database is an Exadata Machine, hence the “TABLE ACCESS STORAGE FULL“, otherwise would be “TABLE ACCESS FULL“.
As we can see the Execution Plan was a Full Table Scan (FTS) as this was the only access method available. Next, we create an index on the emp_id:
SQL> create index emp_id on emp (id);
Index created.
SQL>
SQL> pause Press Enter
Press Enter
Next, we query the table with a predicate on emp_id:
SQL> select * from emp where id = 1;
ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
Execution Plan
----------------------------------------------------------
Plan hash value: 458854468
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 67 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> pause Press Enter
Press Enter
Now, we can see the Execution Plan used the newly created index. Next, we check if the index was used:
SQL> set autotrace off
SQL>
SQL> col INDEX_NAME format a20
SQL> col TABLE_NAME format a20
SQL> col MONITORING format a15
SQL> col USED format a10
SQL> select * from USER_OBJECT_USAGE;
no rows selected
SQL>
SQL> pause Press Enter
Press Enter
We get no rows back as we didn’t turn on index monitoring, which isn’t on by default. So next, we enable index monitoring:
SQL> alter index emp_id monitoring usage;
Index altered.
SQL>
SQL> pause Press Enter
Press Enter
Next, we check the index usage and we can see it monitoring but not used:
SQL> select * from USER_OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID EMP YES NO 07/16/2018 17:53:59
SQL>
SQL> pause Press Enter
Press Enter
Next, we query the table with no predicate to do a FTS:
SQL> set autotrace on
SQL>
SQL> select * from emp;
ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
2 Scott Tiger
3 Joe Bloggs
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| EMP | 3 | 201 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
51 recursive calls
0 db block gets
54 consistent gets
0 physical reads
132 redo size
784 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> pause Press Enter
Press Enter
Next, we check if the index monitoring reflects the usage:
SQL> set autotrace off
SQL>
SQL> select * from USER_OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID EMP YES NO 07/16/2018 17:53:59
SQL>
SQL> pause Press Enter
Press Enter
Next, we query the table with a predicate to do Index Range Scan:
SQL> set autotrace on
SQL>
SQL> select * from emp where id = 1;
ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
Execution Plan
----------------------------------------------------------
Plan hash value: 458854468
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 67 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
3 db block gets
24 consistent gets
0 physical reads
876 redo size
695 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> pause Press Enter
Press Enter
Next, we check if the index monitoring reflects the usage:
SQL> set autotrace off
SQL>
SQL> select * from USER_OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID EMP YES YES 07/16/2018 17:53:59
SQL>
SQL> pause Press Enter
Press Enter
Next, we now turn off index monitoring, as we know it’s been used:
SQL> alter index emp_id nomonitoring usage;
Index altered.
SQL>
SQL> pause Press Enter
Press Enter
Next, we check to see if monitoring is now disabled:
SQL> select * from USER_OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID EMP NO YES 07/16/2018 17:53:59 07/16/2018 17:54:09
SQL>
SQL> pause Press Enter
Press Enter
Finally, we drop the demo EMP table:
SQL> DROP TABLE "EMP" PURGE;
Table dropped.
SQL>
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
Zed DBA's Oracle Blog 