Determining whether SYSTEM STATISTICS are collected or not?
Does anyone know a conclusive method to tell whether system statistics are in use or have been set/collected in a given database?
The best I can determine is that if they're not collected or if they've been collected and then deleted, then the FLAGS column is set to "0" in SYS.AUX_STATS$. Other columns such as CPUSPEEDNW, IOSEEKTIM, and IOTFRSPEED are populated with default values even before collecting any system stats on a newly created database.
Observation:
SQL> -- Newly created DB, no system statics created SQL> SELECT pval1 FROM sys.aux_stats$ WHERE pname='FLAGS'; PVAL1 ---------- 0 SQL>