Details zu dbms_space.space_usage

Man lernt eine Menge, wenn man versucht, anderen Anwendern die Arbeitsweise interner Oracle-Mechanismen zu erklären. Noch mehr lernt man, wenn Jonathan Lewis die Erläuterungen redigiert, die man beigesteuert hat.

Im OTN-Forum wurde dieser Tage die Frage gestellt, auf welcher Datengrundlage die Prozedur dbms_space.space_usage ihre Aussagen zum Füllgrad von Tabellen-Blocks generiert. Da ich darauf nicht unmittelbar eine Antwort wusste und eine halbherzige Google-Suche keine Ergebnisse brachte, habe ich ein SQL Trace (Event 10046) erstellt und darin eine große Zahl von Einzelblock-Zugriffen gefunden (db file sequential read), was mich zunächst an Sampling denken ließ - allerdings würde Sampling keine globalen Aussagen gestatten. Daher habe ich mir umfangreichere Interpretationen gespart und stattdessen nur meine Beobachtungen mitgeteilt. Den Rest hat dann der Herr Lewis beigesteuert:

It's reading the bitmap space management blocks for objects in ASSM tablespace: 

fs1 -> blocks with 0 to 25% free space

fs2 -> blocks with 25 to 50% free space

fs3 -> blocks with 50 - 75% free space

fs4 -> blocks with 75 - 100% free space

FULL -> filled past the limit set by PCTFREE (and you might have set pctfree to something big like 60%, which means a block would be flagged full when it was actually 40% used) 

For index blocks the indicators are different - they're either FULL (in use in the index, so locked in place) or fs2 (available for relocation).

Die Unklarheiten bei der Semantik für Index-Blocks hatte Jonathan Lewis vor einiger Zeit in seinem Blog angesprochen. Mir war jedenfalls nicht mehr(?) bewusst, dass die space_usage-Prozedur nur für ASSM-Objekte ein Ergebnis liefert, für MSSM-Objekte hingegen "ORA-10614: Operation not allowed on this segment" - wobei die Dokumentation an dieser Stelle durchaus eindeutig ist ("This procedure can only be used on tablespaces that are created with auto segment space management"). Um die Ergebnisse der Prozedur zu formatieren, kann man übrigens ein Code-Stück von Yong Huangs Webseite (die inhaltlich so interessant wie in der Darstellung spartanisch ist) ausborgen.