Long Parse aufgrund von Skew Detection in Hybrid Hash Distribution
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:
--#######################################
--# Small lookup table #
--#######################################
drop table asc_pids;
create table asc_pids
as
select 45e6 + rownum id
from dual
connect by level <= 13;
--#######################################
--# Big, partitioned fact table #
--#######################################
--5 mio records in each partition from P1 to P9
--only 4 records in partition P10
drop table asc_skew_part;
create table asc_skew_part
nologging
partition by range(id)
(
partition P1 values less than (5e6),
partition P2 values less than (10e6),
partition P3 values less than (15e6),
partition P4 values less than (20e6),
partition P5 values less than (25e6),
partition P6 values less than (30e6),
partition P7 values less than (35e6),
partition P8 values less than (40e6),
partition P9 values less than (45e6),
partition P10 values less than (50e6)
)
as
with a as
(
select rownum id
from dual
connect by level <= 1e5
)
select /*+ parallel(4) */rownum id,
lpad('*', 255, '*') padding
from a, a
where rownum <= 45e6
union all
select 45e6 + mod(rownum, 2) + 1 id,
lpad('*', 255, '*') padding
from dual
connect by level <= 4;
--#######################################
--# histograms on id on fact table #
--#######################################
exec dbms_stats.gather_table_stats(user, 'asc_skew_part', method_opt=>'for all columns size 1 for columns id size 255', granularity=>'ALL');
--#######################################
--# clear RC #
--#######################################
exec dbms_result_cache.flush();
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.
--####################################### --# note the long parsing time # --####################################### explain plan for select /*+ leading(b a) use_hash(a) parallel(8) pq_distribute(a HASH HASH)*/* from asc_skew_part a, asc_pids b where a.id = b.id and a.id between 45000000 and 50000000;
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 😉
Beispiel:
create table asc_skew
nologging
as
with generator as
(
select *
from dual
connect by level <= 1e4
)
select (case when mod(rownum, 2) = 0 then -1 else rownum end)
id,
rpad('*', 255, '*') padding
from generator, generator
where rownum <= 50e6;
create table asc_pids
as
select rownum id
from dual
connect by level <= 13;
exec dbms_stats.gather_table_stats(user, 'asc_skew', method_opt=>'for all columns size 1 for columns id size 255', granularity=>'ALL');
--this will parse very long
explain plan for
select /*+ leading(b a) use_hash(a) parallel(8) pq_distribute(a HASH HASH) */*
from asc_skew a, asc_pids b
where a.id = b.id
and a.id = 42;
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.
Dabei gab sich das Who-is-who der österreichischen Oracle Anwenderszene das Mikrofon in die Hand, um ihr wertvolles Know-how mit der Comunity zu teilen.
