Zur Abwechslung kann ich hier mal wieder eine selbst erlebte Geschichte erzählen: am letzten Wochenende habe ich ältere historischen Daten aus den Aggregationstabellen eines Data Warehouses (11.2) entfernt, was den Neuaufbau diverser Materialized Views erforderlich machte. Diese Materialized Views sind auf verschiedenen Ebenen der Zeitachse definiert: für Wochen, Monate und Jahre, wobei die beiden ersten Gruppen range partitioniert sind, während für die Jahres-Ebene keine Partitionierung eingerichtet wurde: vermutlich, weil in der Regel nur wenige Jahre in den Aggregationen vorgehalten werden sollen. Meine Annahme war, dass sich die Löschungen moderat positiv auf die Performance folgender Fast Refresh Operationen auswirken sollten, da das Datenvolumen insgesamt merklich reduziert wurde. Tatsächlich liefen die Fast Refresh Operationen aber nach dem Neuaufbau deutlich langsamer als zuvor - obwohl die Ausführungspläne strukturell unverändert blieben. Dazu zunächst der Ausführungsplan und ein paar Zahlen:
Plan hash value: 956726641
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 648 (100)| |
| 1 | MERGE | MV_YEARLY_XXX | | | | |
| 2 | VIEW | | | | | |
| 3 | NESTED LOOPS OUTER | | 119 | 17731 | 648 (1)| 00:00:08 |
| 4 | VIEW | | 119 | 12376 | 291 (1)| 00:00:04 |
| 5 | SORT GROUP BY | | 119 | 11067 | 291 (1)| 00:00:04 |
| 6 | TABLE ACCESS FULL | MLOG$_MV_MONTHLY_XXX | 119 | 11067 | 290 (0)| 00:00:04 |
| 7 | MAT_VIEW ACCESS BY INDEX ROWID| MV_YEARLY_XXX | 1 | 45 | 3 (0)| 00:00:01 |
| 8 | INDEX UNIQUE SCAN | I_SNAP$_MV_YEARLY_XXX | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
with
statistics as (
select /* exclude */
st.sql_id
, st.parsing_schema_name
, st.module
, trunc(sn.begin_interval_time) snap_date
, count(*) no_snapshots
, round(sum(st.elapsed_time_delta/1000000)) ela_sec_total
, sum(st.executions_delta) execs_total
, sum(st.rows_processed_delta) rows_processed_total
, sum(st.buffer_gets_delta) buffer_gets
from dba_hist_sqlstat st
join dba_hist_snapshot sn
on st.snap_id = sn.snap_id and st.instance_number = sn.instance_number
where sn.begin_interval_time >= trunc(sysdate) - 14
group by st.parsing_schema_name
, st.module
, st.sql_id
, trunc(sn.begin_interval_time)
)
,
basedata as (
select /*+ materialize */ st.sql_id
from dba_hist_sqltext st
where upper(st.SQL_TEXT) like '%&sql_pattern%'
and st.sql_text not like '%/* exclude */%'
)
select st.snap_date
, max(st.sql_id) keep (dense_rank last order by snap_date) last_sql_id
, sum(ela_sec_total) ela_sec_total
, sum(execs_total) execs_total
, sum(rows_processed_total) rows_processed_total
, round(sum(rows_processed_total)/case when sum(execs_total) > 0 then sum(execs_total) else null end, 0) rows_per_exec
, round(sum(buffer_gets)/case when sum(execs_total) > 0 then sum(execs_total) else null end, 0) buffer_gets_per_exec
, round(sum(ela_sec_total)/case when sum(execs_total) > 0 then sum(execs_total) else null end, 2) ela_sec_per_exec
from basedata bd
join statistics st
on bd.sql_id = st.sql_id
group by st.snap_date
order by st.snap_date;
SNAP_DATE LAST_SQL_ID LAST_PLAN_HASH_VALUE ELA_SEC_TOTAL EXECS_TOTAL ROWS_PROCESSED_TOTAL ROWS_PER_EXEC BUFFER_GETS_PER_EXEC ELA_SEC_PER_EXEC
------------------- ------------- -------------------- ------------- ----------- -------------------- ------------- -------------------- ----------------
16.04.2016 00:00:00 3gqhk0g658b7c 956726641 1075 153 1052262 6878 32921 7.03
18.04.2016 00:00:00 3gqhk0g658b7c 956726641 176 26 251987 9692 45211 6.77
19.04.2016 00:00:00 03zqa2r6v9hxp 956726641 1268 294 2355310 8011 36819 4.31
20.04.2016 00:00:00 03zqa2r6v9hxp 956726641 848 83 715340 8619 42348 10.22
21.04.2016 00:00:00 03zqa2r6v9hxp 956726641 864 133 771265 5799 28422 6.50
22.04.2016 00:00:00 03zqa2r6v9hxp 956726641 700 84 665256 7920 38072 8.33
23.04.2016 00:00:00 avavqbxkq8qpr 956726641 10026 171 1361158 7960 32848 58.63
24.04.2016 00:00:00 avavqbxkq8qpr 956726641 1030 31 214286 6912 29321 33.23
26.04.2016 00:00:00 avavqbxkq8qpr 956726641 9473 321 2763663 8610 35883 29.51
27.04.2016 00:00:00 avavqbxkq8qpr 956726641 3402 183 1153780 6305 27824 18.59
Hier sind mehrere Punkte, die einer Erklärung bedürfen:
- der Execution Plan ist meiner Einschätzung nach plausibel: bei durchschnittlich weniger als 10K geänderten Datensätzen in einer Tabelle mit 160M rows ist der NL Join mit dem Zugriff über den eindeutigen I_SNAP$-Index eine sinnvolle Wahl.
- die unterschiedlichen sql_id-s für die haben damit zu tun, dass die intern im Rahmen des Fast Refresh für Materialized Views verwendeten Statements einen OPT_ESTIMATE-Hint enthalten, der von den aktuellen Statistiken der Materialized View abhängt, was ich gelegentlich hier vermerkt hatte.
- der Neuaufbau der MViews erfolgte am 23.04. und erhöhte die durchschnittlichen Laufzeiten von unter 10 sec. auf Werte von 20-30 sec., was angesichts der Häufigkeit der Refresh-Operationen recht massive Auswirkungen hatte - insbesondere, weil hier mehrere ähnliche MViews betroffen waren.
- die dabei zu erledigende Arbeit ist weitgehend unverändert, jedenfalls, wenn man nach den Buffer Gets und der Anzahl der Datensätze geht.
Also ein Fall von "die Query läuft langsamer, obwohl sich nichts verändert hat"? Nun, das nicht gerade: immerhin wurden die Objekte komplett neu aufgebaut, was auf jeden Fall eine Änderung der physikalischen Struktur des Segments mit sich brachte. Leider hatte ich mir die Daten vor dem Neuaufbau nicht genau angesehen (sondern nur die DDL dazu) und konnte daher nichts über die ursprüngliche Organisation der Daten sagen. Nach dem Neuaufbau war jedenfalls zu erkennen, dass die Sortierung nach dem Jahr erfolgt war, die weitere Anordnung aber nicht der Reihenfolge der Spalten des I_SNAP$-Index entsprach (so dass dieser auch einen sehr schlechten Clustering Factor besaß). Für die Refresh Operationen wiederum nahm ich an, dass sie auf jeden Fall nach der zweiten Spalte des Index gruppiert sein müssten: nämlich einer Filialnummer. Daraus ergab sich dann die Überlegung, die Materialized Views der Jahres-Ebene nochmals neu aufzubauen: diesmal aber mit einem ORDER BY in der Definition. Dieses ORDER BY spielt nur eine Rolle bei der initialen Anlage der MView und sorgt für ein entsprechendes physikalisches Clustering in den Datenblöcken, das im Lauf der Zeit durch folgende DML-Operationen allmählich nachlassen wird (und hat natürlich auch keinen verlässlichen Einfluss auf die Sortierung von Abfrageergebnissen). Die Idee dabei war, dass sich auf diese Weise die Zahl der physical reads reduzieren lassen könnte, weil die Wahrscheinlichkeit steigt, dass mehrfach bearbeitete Blöcke noch im Buffer Cache vorliegen, wenn sie benötigt werden.
Nach dem Neuaufbau ergaben sich dann folgende Werte (gegenüber der innitialen Analysequery ergänzt um die phsical reads:
SNAP_DATE LAST_SQL_ID HASH_VALUE ELA_SEC_TOTAL EXECS_TOTAL ROWS_PROCESSED_TOTAL ROWS_PER_EXEC BUFFER_GETS_PER_EXEC DISK_READS_PER_EXEC ELA_SEC_PER_EXEC
---------- ------------- ---------- ------------- ----------- -------------------- ------------- -------------------- ------------------- ----------------
16.04.2016 3gqhk0g658b7c 956726641 1075 153 1052262 6878 32921 1286 7.03
18.04.2016 3gqhk0g658b7c 956726641 176 26 251987 9692 45211 1132 6.77
19.04.2016 03zqa2r6v9hxp 956726641 1268 294 2355310 8011 36819 715 4.31
20.04.2016 03zqa2r6v9hxp 956726641 848 83 715340 8619 42348 1887 10.22
21.04.2016 03zqa2r6v9hxp 956726641 864 133 771265 5799 28422 1171 6.50
22.04.2016 03zqa2r6v9hxp 956726641 700 84 665256 7920 38072 1578 8.33
23.04.2016 avavqbxkq8qpr 956726641 10026 171 1361158 7960 32848 6048 58.63
24.04.2016 avavqbxkq8qpr 956726641 1030 31 214286 6912 29321 5662 33.23
26.04.2016 avavqbxkq8qpr 956726641 9473 321 2763663 8610 35883 3783 29.51
27.04.2016 avavqbxkq8qpr 956726641 3402 183 1153780 6305 27824 3948 18.59
28.04.2016 f1za0dqt3r803 956726641 134 50 433547 8671 37853 1606 2.68
29.04.2016 f1za0dqt3r803 956726641 433 204 1395873 6843 30404 1009 2.12
30.04.2016 f1za0dqt3r803 956726641 99 28 299080 10681 48064 1850 3.54
Sichtbar ist hier vor allem Folgendes:
- der Plan ist immer noch der gleiche und auch die Werte für die Buffer Gets und verarbeiteten Datensätze bleiben im gleichen Bereich.
- die durchschnittliche Anzahl der disk reads sinkt wieder von 4-6K auf unter 2K.
- die durchschnittlichen Laufzeiten liegen jetzt zwischen 2-4 sec: also sogar deutlich niedriger als vor dem ersten Neuaufbau der Mviews
Das ist zunächst sehr zufriedenstellend. Aber was kann man daraus lernen? Vielleicht Folgendes: Das physikalische Clustering von Daten kann - insbesondere in Data Warehouse Umgebungen - einen ziemlich entscheidenden Einfluss auf die Performance von Abfragen und DML Operationen haben. Manchmal lohnt es sich hier durchaus, über eine physikalische Reorganisation nachzudenken, um die Anzahl kostspieliger physikalischer Zugriffe zu reduzieren. Dabei stellt sich dann natürlich die Frage nach der Sortierung für dieses Clustering, die von den jeweiligen Zugriffen abhängt. Außerdem ist zu überlegen, ob sich die Mühe lohnt, da die Sortierung sich im Lauf der Zeit wieder ändern wird, sofern man nicht mit read-omly Daten zu tun hat.
