Revision as of 13:49, 31 March 2016

DBA / Tuning

@@ Line 1: / Line 1: @@
 =DBA / Tuning=
-==Index monitoring==
+* [[Index Monitoring]]
-Enable monitoring:
-<source lang="sql">
-select 'alter index ' || owner || '.' || index_name || ' monitoring usage;'
-  from dba_indexes
- where owner = '?'
-   and index_type not in ('LOB');
-</source>
-Validate it has been enabled on all indexes (in case some were locked):
-<source lang="sql">
-select index_name
-  from user_indexes
- where index_type not in ('LOB')
-minus
-select index_name
-   from v$object_usage;
-</source>
-Verify, make sure to connect as the owner of the objects you are monitoring:
-<source lang="sql">
-select *
-   from v$object_usage;
-</source>