We’re running Oracle 19c. I’ve been running a query against the v$segment_statistics view to check for any activity against a table we’re planning to delete (believing it to be obsolete).
My understanding is that following the DB instance being restarted; the DB statistics will continue to accumulate, until such time as the DB is restarted again (at which point the stats will zero, and the accumulation will begin again).
However, yesterday, whilst my v$segment_statistics query reported READ activity against the database table in question – when I run the same query again today, there are no statistics reported…
I’ve double-checked that the DB instance hasn't been restarted overnight using:
select startup_time from v$instance;
Is it my misunderstanding, and the statistics don’t in fact accumulate daily? Or is there an explanation (some settings in the DB perhaps?) for why these stats appear to be clearing down each day that I can discuss with my DBA?
The v$ views expose memory structures maintained by each instance. They do not employ persistent storage (disk). Therefore, as soon as an instance is bounced, the data in all v$ views is zero'd out and starts afresh. If you are sampling it yourself, you have to recognize when the numbers suddenly show up much lower than previous and recognize an instance bounce has occurred, avoiding computing deltas across the bounce.
Since 10g, however, AWR (active workload repository) has been storing regular snapshots (e.g. hourly, though it is configurable) the contents of certain v$ views in physical tables under the view names dba_hist_****. But you won't find everything there - only certain things. For example, there's no DBA_HIST option for v$sesstat (so if you want a history of that, you have to write your own capture engine). Fortunately for you, there is an AWR capture for v$segstat:
DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT_OBJ
v$segstat_name
You'd need all three of these to reassemble what you see in v$segment_statistics.
However, I find this info to be of limited value for the specific purpose you have. Various background jobs, backups, DBA tools, etc... can scan tables that aren't actually being used by anybody, and that activity would show up as non-zero metrics in most of those statistics. For more useful information, a home-grown capture system watching sys.mon_mods_all$ for row modifications (frequently enough to catch it before a stats gathering clears it) and/or joining dba_hist_active_sess_history to dba_hist_sql_plan to get the objects being hit by SQL, by whom (which unfortunately, beyond machine, username and program would require that you capture v$session yourself, since AWR doesn't do it), and whether its select vs. DML, etc... is much more useful than raw segment read counts and such.
Lastly, the most sure-fire way (and simplest, though it can be costly in terms of storage) of capturing table usage is to enable auditing.