What causes an index to have no rows in v$segment_statistics?
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-lReceived on Tue Dec 04 2012 - 16:44:08 CET