Auswahl  

Lange Laufzeiten bei Zugriff auf DBA_FREE_SPACE oder DBA_EXTENTS verbessern 

Oracle
DBA
RDBMS 12.x:RDBMS 18.1:RDBMS 18.3:RDBMS 19.3
19.10.20 (MP)
18.01.21
X$KTFBUE, DBA_FREE_SPACE, X$KTFBUE

Passende Schulungen zum Thema

Wir hatten jüngst eine (vermeintlich) einfache Query, die aber 5 Min lief. Sie lautete:
 
SELECT sum("8_Bis_64"), sum("65_Bis_256"), sum("257_Bis_512")
  FROM (SELECT
           CASE WHEN BLOCKS between 8 and 64 THEN '1' END as "8_Bis_64",
           CASE WHEN BLOCKS between 65 and 256 THEN '1' END as "65_Bis_256",
           CASE WHEN BLOCKS between 257 and 512 THEN '1' END as "257_Bis_512"
         FROM dba_free_space);

Warum lief sie so lange?
Wenn man sich den Ausführungsplan ansieht, stellt man fest, dass eine der internen Tabellen keine Statistiken besitzt.

Tipp 1:
Die Tabelle X$KTFBUE hat keine Statistiken und bekommt auch keine, weil es sehr lange dauern könnte diese zu berechnen (ist also ein Feature und kein Bug)
Übrings, bekommt die Tabelle auch keine Statistiken durch den Aufruf:
 
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

Achtung, es sind schon Fälle beobachtet worden, da lief die Statistikerzeugung über 8 Stunden !
Also bitte nicht einfach nur:
 
EXEC dbms_stats.gather_table_stats('SYS','X$KTFBUE');

sondern zuerst:
 
alter session set "_optimizer_cartesian_enabled" = false;
-- und danach:
EXEC dbms_stats.gather_table_stats('SYS', 'X$KTFBUE');

Laufzeit bei uns: 14.8s.
Oder als abgeschätzte Statistik (5%):
 
exec dbms_stats.gather_table_stats('SYS', 'X$KTFBUE', estimate_percent=>5);

Laufzeit bei uns: 6.1s.
Oder als abgeschätzte, parallelisierte Statistik:
 
exec dbms_stats.gather_table_stats('SYS', 'X$KTFBUE', estimate_percent=>5, degree=>4);

Laufzeit bei uns: 6.0s (bringt also bei unserer Hardware nichts, evtl. haben Sie ja eine bessere Maschine :-) )
P.S.: Auch mit noch kleineren Sample-Sizes (1%, 0.1% wurde es bei uns nicht schneller)

Wer auf die Berechnung der Statistik verzichten möchte und sich gleich traut, einfach selbst den Statistik-Wert einzutragen (traue keiner Statistik, ....)
BEGIN
dbms_stats.set_table_stats (
ownname=>'SYS',
tabname=>'X$KTFBUE',
numrows=>50000,
avgrlen=>47,
statown=>'SYS');
END;

Bei uns gab es keine signifikanten Unterschiede in der Laufzeit, auch wenn wir Faktor 10 die Statistik zu hoch eingesetzt hatten, aber das ist hier jetzt unsupported ...


Oder noch besser zuerst:
Tipp 2: Leeren Sie den Mülleimer:
 
purge dba_recyclebin;

und erzeugen Sie neue Statistiken für den Mülleimer:
 exec dbms_stats.gather_table_stats('SYS','RECYCLEBIN$')

Am Besten schaltet man dem Mülleeimer sowieso ganz aus (sagt auch Oracle):
alter system set recyclebin=off scope=spfile;



Behind the scenes:
Wer wissen möchte, wieviele Zeilen nun in der Tabelle X$KTFBUE enthalten sind:
 
SELECT ft.name, ts.analyzetime, ts.rowcnt, ts.samplesize
  FROM tab_stats$ ts, v$fixed_table ft
 WHERE ts.obj#=ft.object_id
   AND ft.name='X$KTFBUE';
NAME     ANALYZETIME         ROWCNT SAMPLESIZE
-------- ------------------- ------ ----------
X$KTFBUE 19.10.2020 16:05:51  18640       4895

Laufzeit der Query nach den Tipps:
0.031 Sekunden, das kann sich doch sehen lassen, oder ?

Mike Dietrich von Oracle hat das Thema Fixed Table Stats in einem seiner Blogs auch schon mal thematisiert.