Database Administration (MOSC)

MOSC Banner

Determining whether SYSTEM STATISTICS are collected or not?

edited Mar 12, 2014 3:20PM in Database Administration (MOSC) 7 commentsAnswered ✓

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> 

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center