What causes an index to have no rows in v$segment_statistics?
- From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 04 Dec 2012 16:44:08 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
