What causes an index to have no rows in v$segment_statistics?

From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Tue, 04 Dec 2012 16:44:08 +0100
Message-ID: <50BE1A48.5050107_at_mgm-tp.com>

Hello all,

we are trying to monitor the usage of certain indexes by checking v$segment_statistics on a regular basis.

But unfortunately not all of them have an entry in the view, even though they *have* been used:

select max(SEQUENCENUMBER)
from FOOBAR;

uses the following execution plan:

---

| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT           |             |     1 |     3 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE            |             |     1 |     3 |            |          |

|   2 |   INDEX FULL SCAN (MIN/MAX)| SEQNR_20332 |     1 |     3 |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------
  

So the segment should show some activity after running that statement several times, but no rows show up in v$segment_statistics. I also checked v$segstat for the object# of the index, with the same (empty) result.

The index is visible and usable, the following query:

     SELECT index_name,
            logging,
            status,
            user_stats,
            global_stats,
            visibility,
            segment_created
     FROM all_indexes
     WHERE index_name = 'SEQNR_20332'

returns:

INDEX_NAME | LOGGING | STATUS | USER_STATS | GLOBAL_STATS | VISIBILITY | SEGMENT_CREATED

------------+---------+--------+------------+--------------+------------+----------------
SEQNR_20332 | YES     | VALID  | NO         | YES          | VISIBLE    | YES

After dropping and re-creating the index, it does appear in v$segment_statistics.

This is not the only index that misses segment statistics.  From the ~1700 indexes in our schema, ~1000 do not have entries in v$segment_statistics.

Does this mean v$segment_statistics (or v$segstat) is not recording every segment activity? Or is there maybe some threshold that the segment usage needs to exceed in order to be recorded in v$segment_statistics (or v$segstat)? Is there anything we can do about? Any option to enable?

Environment information:

Oracle 11.2.0.2.0 running on CentOS

SELECT statistics_name,

        session_status,
        system_status,
        activation_level,
        statistics_view_name

FROM v$statistics_level
WHERE statistics_name = 'Segment Level Statistics'

returns:

STATISTICS_NAME | SESSION_STATUS | SYSTEM_STATUS | ACTIVATION_LEVEL | STATISTICS_VIEW_NAME

-------------------------+----------------+---------------+------------------+---------------------
Segment Level Statistics | ENABLED        | ENABLED       | TYPICAL          | V$SEGSTAT

Kind Regards
Thomas Kellerer

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 04 2012 - 16:44:08 CET