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

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Tue, 4 Dec 2012 08:45:39 -0800 (PST)
Message-ID: <1354639539.84830.YahooMailNeo_at_web121605.mail.ne1.yahoo.com>

I'm not finding that to be true with 11.2.0.3 -- I haven't checked MOS  yet for bugs but I have no issues seeing updates to v$segment_statistics in my personal db.

David Fitzjarrell

---

From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Tuesday, December 4, 2012 8:44 AM
Subject: What causes an index to have no rows in v$segment_statistics?

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
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 04 2012 - 17:45:39 CET