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.