Für einen detaillierten Überblick wie Skew Detection/Skew Handling in Hybrid Hash Distributionen funktioniert und welche Ziele es verfolgt, empfehle ich den sehr guten Blogeintrag von Randolf Geist zu lesen: https://oracle-randolf.blogspot.com/2014/05/12c-hybrid-hash-distribution-with-skew.html
Im Blog von Herrn Geist werden unter anderem sieben Vorbedingungen genannt, die nötig sind, damit dieses Feature wirksam wird.
Zumindest in Oracle 12.2 konnte ich auf Basis meiner Untersuchung eine weitere Vorbedingung finden:
Dynamic Sampling muss ebenfalls aktiviert sein (d.h auf einem Level > 0 konfiguriert sein; der Default ist 2).
Die rekursive Query die während der Optimierungsphase (=Hard Parse) ausgeführt wird, um einen Skew zu erkennen sieht nun auch ein wenig anders aus.
Zum Großteil wurden hier weitere Hints hinzugefügt.
Auf Oracle 12.2 hat sie nun folgende Form:
| –kkopqSkewInfo: Query:SELECT /* DS_SKEW */ /*+ RESULT_CACHE no_parallel dynamic_sampling(0) no_sql_tune no_monitoring */ * FROM (SELECT SYS_OP_COMBINED_HASH(„ID“), COUNT(*) CNT, TO_CHAR(„ID“) FROM „ASC_SKEW_PART“ SAMPLE(99.900000) SEED(1) GROUP BY „ID“ ORDER BY CNT DESC, „ID“) WHERE ROWNUM <= 2; |
Was hier sofort auffällt ist die Größe des gewählten Samples (99.9%).
Nach ein paar Tests mit unterschiedlichen Tabellengrößen, kam ich zum Schluss, dass das Sample so gewählt wird, um ca. 5.500 Rows aus der Tabelle zu selektieren, um auf dieser Datenbasis die Skew Erkennung durchzuführen.
Die Beschränkung auf 5.500 Rows wurde in älteren Releases schon vorgenommen, wenn Histogramme im Zuge der Statistikberechnung erzeugt wurden. Für Tabellen die weniger als 5.500 Rows haben, wird das Sample mit 99,9% gewählt.
Sehen wir uns nun an unter welchen Umständen das zu sehr langen Parse Zeiten führen kann:
| exec dbms_stats.gather_table_stats(user, ‚asc_skew_part‘, method_opt=>’for all columns size 1 for columns id size 255′, granularity=>’ALL‘); |
Ich habe eine große, partitionierte Faktentabelle erstellt. Darin befinden sich jeweils 5 Millionen Rows in den Partitionen P1 bis P9 und nur 4 Rows in der letzten Partition P10 (2 Rows pro Value, das reicht um die notwendige Vorbedingung für die Skew Erkennung zu erfüllen).
Nachdem ich Histogramme auf der Faktentabelle erzeugt und den Result Cache (aufgrund des Hints in der rekursiven Query) geflushed habe, joine ich diese Tabelle nun zur kleinen Lookup-Tabelle.
Dabei schränke ich in der where Klausel aber so ein, dass nur Datensätze der kleinen Partition P10 selektiert werden.
Die rekursive Query die im 10053 Event/Optimizer Trace ersichtlich ist, ist exakt jene die ich schon zu Beginn des Posts gezeigt habe:
| –kkopqSkewInfo: Query:SELECT /* DS_SKEW */ /*+ RESULT_CACHE no_parallel dynamic_sampling(0) no_sql_tune no_monitoring */ * FROM (SELECT SYS_OP_COMBINED_HASH(„ID“), COUNT(*) CNT, TO_CHAR(„ID“) FROM „ASC_SKEW_PART“ SAMPLE(99.900000) SEED(1) GROUP BY „ID“ ORDER BY CNT DESC, „ID“) WHERE ROWNUM <= 2; |
Hier wurde während des Parsens ein 99,9% Sample über alle Partitionen in einer Tabelle mit 45 Millionen Rows gelesen, eine Hash Funktion auf der Join Spalte angewandt und aggregiert.
Es sieht so aus als wäre die Größe des Samples nur von der kleinen Partition P10 (4 Rows) abgeleitet worden, die ich mit meiner where Klausel treffe.
Angewandt wurde das Sampling dann aber über die gesamte Tabelle, eine where Klausel um auf die entsprechende Partition zu filtern gibt es in der rekursiven Query nicht.
Im konkreten Fall ist das auf einer Produktions-DB mit Milliarden von Rows und hunderten Partitionen aufgetreten, woraufhin das bl0ße Parsen über Stunden gedauert hat.
Update:
Offensichtlich wird die Anzahl der Zeilen, die der Optimizer nach Anwendung der Filter in der where Klausel schätzt, als Basis für die Größe des Samples herangezogen.
Wenn wir eine Tabelle mit 1 Mrd. Rows hätten, dabei ein Wert besonders oft vorkommen würde (≥30% defaultmäßig) und die anderen eindeutig wären, würde ein Filter auf einen der eindeutigen Werte zu einem Sample von 99,9% in der rekursiven Query führen (da der CBO 1 Row nach dem Filter schätzen würde; 1 ≤ 5,500 deswegen → 99,9%).
Natürlich müssen alle anderen Vorbedingungen (Histogramme, Hash Join, etc.) natürlich weiterhin erfüllt sein.
Um eine Zeile aus einer Tabelle von 1 Mrd. Rows zu lesen, möchte man es wahrscheinlich vermeiden 99,9% aller Rows während des Parsens zu lesen und zu aggregieren 😉
| exec dbms_stats.gather_table_stats(user, ‚asc_skew‘, method_opt=>’for all columns size 1 for columns id size 255′, granularity=>’ALL‘); |
Fazit:
Meiner Meinung nach sollte die Auswahl des Samples angepasst werden auf die Größe des Segments vor der Anwendung der where Klausel.
Weiters sollte auf partitionierte Tabellen besonders Rücksicht genommen werden.
Eventuell wäre es auch sinnvoll bei 99,9% komplett auf die sample Klausel zu verzichten, da diese z.B auch Smart Scans auf Exadata verhindert.
