Database Reference
In-Depth Information
SQL> CONNECT / AS SYSDBA
SQL> GRANT SELECT ON obj$ TO site_sys WITH GRANT OPTION;
SQL> GRANT SELECT ON ind$ TO site_sys WITH GRANT OPTION;
SQL> GRANT SELECT ON object_usage TO site_sys WITH GRANT OPTION;
SQL> GRANT SELECT ON user$ TO site_sys WITH GRANT OPTION;
SQL> CREATE OR REPLACE VIEW site_sys.index_usage
(owner,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING)
AS
SELECT u.name, io.name index_name, t.name table_name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
FROM sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.user$ u, sys.object_usage ou
WHERE io.owner# = t.owner#
AND io.owner# = u.user#
AND i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#;
-- have to grant to public, to allow non DBAs access to the view
-- used by function MONITOR_SCHEMA_INDEXES, which runs with AUTHID CURRENT_USER
GRANT SELECT ON site_sys.index_usage TO PUBLIC;
Still connected as
SYS
or any other user with the required privileges, we may now retrieve
index usage information on any foreign schema such as HR.
SQL> SELECT owner, table_name, index_name, monitoring, used
FROM site_sys.index_usage
WHERE owner='HR'
AND ((monitoring='YES' AND used='YES')
OR (used='YES' AND end_monitoring IS NOT NULL));
OWNER TABLE_NAME INDEX_NAME MONITORING USED
----- ---------- ----------------- ---------- ----
HR EMPLOYEES EMP_DEPARTMENT_IX NO YES
Lessons Learned
This wraps up the case study on index monitoring. With the function
MONITOR_SCHEMA_INDEXES
and the view
INDEX_USAGE
, a DBA has the required tools to monitor index usage in any schema.
Indexes that were not used over a period of time that covers the complete code paths of
applications using a database may be dropped to reduce index maintenance costs. The issue
here lies with complete code path. You may never be certain that the entire code path of an
