Oracle Index Monitoring Status Change – Why It Changes to ‘No’?

indexmonitoringoraclestandard-edition

On one of my production Oracle database 11g Standard Edition I have a lot (a very lot of) indexes. Some of them (majority) are created by the application installer, others are created by me when I identify a request who need some dba work.
I'm pretty sure that some of them aren't used at all and I want to identify them.

For that task, I alter all my table & index setting MONITORING status.

After a while (several weeks) I watched about used status :

SELECT index_name, table_name, monitoring, used,
       start_monitoring, end_monitoring
  FROM v$object_usage
 ORDER BY index_name;

I notice that a lot of indexes have now a NOMONITORING status …

I search to understand what happened and why about this status.

Except the regular ALTER INDEX index_name NOMONITORING USAGE query, what is likely to change this? Does anyone know a process (a standard Oracle's in-dictionary process) that remove the MONITORING status?

Best Answer

One possible reason:

Bug 16311211 : REBUILDING INDEX ALTER SETTING THE MONITORING TO OFF

The bug is said to be fixed on version 12.2 and there are one-off patches for 11.2.0.3 and 11.2.0.4 as well.

Related Solutions

Oracle monitoring db status

What is 'normal' will depend on your situation. An OLTP database and a warehouse will have very different usage profiles.

If you use a connection pool, then you probably don't expect to see a wide variation in the number of connections or sessions. Such a change would indicate a problem.

Generally you are best off looking for dramatic changes in your measures.

Grab SQL Developer 3 Early Adopter edition from the Oracle website. Check out some of the system reports, and look at the underlying SQLs.

Oracle concatenation of queries and function index usage

This seems to be a limitation of the optimizer (or perhaps a bug).

The plan for this query:

SELECT /*+ USE_CONCAT */ * FROM test2 WHERE upper(field1)='FIELD1=1' OR field3=1
union all
(
SELECT * FROM test2 WHERE upper(field1)='FIELD1=1'
union all
SELECT * FROM test2 WHERE field3=1
);

explain

shows that it is not the fault of bad stats, and also demonstrates a workaround using union all (but note you need something like upper(field1)='FIELD1=1 and (field3 is null or field3<>1) for an exactly equivalent query)

(My test was on 10.2)

Best Answer

Related Solutions

Oracle monitoring db status

Oracle concatenation of queries and function index usage

Related Question