Today’s flakey hypothesis is this:
When you use DBMS_Stats.Gather_System_Stats() (note: no parameters to be passed) then Oracle works out system stats by reading all the table segments in the first system tablespace data file, and it does it in single block reads. Or something like that.
I really have no idea if this is true, but here is the flakey work on which this hypothesis depends.
- A 10046 trace reveals that a list of datafiles is retrieved, the first of which is the (single) data file for SYSTEM.
- The procedure takes almost exactly 90 seconds.
- PerfMon shows 90 seconds of read on the disk drive that SYSTEM is on.
- Therefore I deduce that the process read about 270Mb of data.
- There are 254 Mb of table segments in SYSTEM
quod erat flakeystrandum
Pretty thin stuff, eh? It makes some sense though. The procedure as executed is recommended for empty databases, so what would you read?