Zerlegung von Tabellen in rowid ranges

Stew Ashton ist derzeit für einen signifikanten Anteil der ungelesen in meinem Blog-Reader wartenden Artikel verantwortlich, da er gerade eine vielteilige Serie zum Thema "Chunking tables" veröffentlicht hat (veranlasst durch eine entsprechende OTN-Anfrage von Jonathan Lewis bzw. White paper Bezugnahme von Bryn Llewellyn). Die genaue Anzahl der Artikel lasse ich aus, um diese Einleitung generisch zu halten und spätere Korrekturen der Anzahl vermeiden zu können. Freundlicherweise pflegt der Autor im ersten Artikel eine Liste der veröffentlichten Artikel, so dass ich mir das an dieser Stelle sparen kann. Hier folgt jetzt eine ganz und gar subjektive und hoffnungslos unvollständige Liste von Punkten, die mir daraus erinnerungswürdig erscheinen - und der übliche Hinweis, dass eine vollständige Lektüre der Artikel weitaus lohnender wäre als die Durchsicht meiner Kommentare:

• Chunking tables 1: Genesis
• erläutert das Thema und liefert die Liste der Links zu den Artikeln (und ist oben verlinkt).
• Chunking tables 2: Requirement
  
• grundsätzlich geht es in den Artikeln darum, eine Tabelle in gleichgroße Bereiche zu zerlegen.
• interessant kann das sein, wenn man für große batch DML-Operationen einen do-it-yourself-parallelism verwenden will und "echte" Parallelisierung einen zu großen Overhead hervorruft, nicht gut funktioniert (etwa wenn db links im Spiel sind) oder einfach zu teuer ist.
• unter Umständen kann man dabei auf mit intermediate commits arbeiten (um parallele OLTP Transaktionen nicht zu lange zu blockieren), wobei allerdings sicherzustellen ist, dass die Operation nach einem Fehler auf den verbleibenden Daten neu aufsetzen kann.
• seit 11.2 wird die Strategie der intermediate commits durch das Package dbms_parallel_execute unterstützt.
• mit dbms_parallel_execute kann man eine feste Größe der Chunks definieren, nicht aber eine bestimmte Anzahl.
• Chunking tables 3: working with blocks
• erklärt, wie man die rowid mit Hilfe von dbms_rowid in die Block-Adresse umwandeln kann.
• Chunking tables 4: Histograms
• zeigt, wie man die Basisdaten aus dba_extents mit SQL-Mitteln in 12 gleichgroße Chunks mit Minimal- und Maximalwerten zerlegen kann (also Histogramme). Dabei kommen die Funktion WIDTH_BUCKET und das analytische SUM zum Einsatz - und ein paar Subqueries in CTEs. Der Herr Ashton hat sein SQL immer gut im Griff.
• eine Filterung irrelevanter Zwischenergebnisse, die ein Sonderlob von Jonathan Lewis erhalten hat, macht die Query für Tabellen mit sehr vielen Extents effizienter.
• Chunking tables 5: chunk boundaries
• die im vorangehenden Artikel erzeugten Chunks enthalten die Extents, aber noch nicht die exakten Block-Begrenzungen, die in diesem Artikel mit Hilfe relativ simpler Arithmetik bestimmt werden ("he right answer is to subtract 1 from the maximum value and then use CEIL()").
• Chunking tables 6: JOIN solution
• erläutert mit umfassenden Kommentaren, wie man die in den bisherigen Artikeln aufgeführten Informationen in einer einzigen großen (und recht komplexen) Query zusammenfasst. Die Details spare ich mir: die würde ich beim Einsatz eines solchen Vorgehens ohnehin wieder im Original nachlesen müssen - ach was: vermutlich würde ich die Lösung einfach kopieren und mit meinen Objektnamen anpassen...
• Chunking tables 7: prior sys_guid() ???
• erklärt die Grundalge einer zweite Lösung ohne Join, die eine rekursive Query und die sys_guid-Funktion verwendet - und noch mal deutlich erklärungsbedürftiger ist als der Join aus Teil 6. In 12c kann man dieses Vorgehen mit der LATERAL clause noch deutlich vereinfachen.
• Chunking tables 8: second solution
• liefert die komplette zweite Lösung mit der in Artikel Nr. 7 erläuterten Vorgehensweise.

Mag sein, dass der Herr Ashton noch weitere Artikel zum Thema ergänzt. Und mag sein, dass ich das hier dann auch tun werde. Wobei der wichtigste Punkt, den ich mir merken muss, ist, dass die Vorgehensweise hier erläutert ist und ich mir die passende ggf. ausborgen könnte.

Erläuterungen zum Nested Loops Join

Nikolay Savvinov hat zuletzt in seinem Blog eine interessante Artikelserie zum Verhalten des Nested Loops Join veröffentlicht und dankenswerterweise die Ergebnisse und die Links noch einmal in einem summary zusammengefasst. Und diese Zusammenfassung fasse ich jetzt hier zusammen:

• der Nested Loops Join (NL Join) ist der einfachste Join-Mechanismus in Oracle. Er liest die Daten einer (driving) row source und testet die Ergebnisse gegen die zweite row source (probe-Zugriff, üblicherweise via Index lookup). Da das Verfahren keine startup Kosten beinhaltet, ist es attraktiv für Zugriffe mit hoher Selektivität und für Abfragen, bei denen nur ein kleiner Teil der Daten tatsächlich benötigt wird (also etwa bei der Listendarstellung im Browser).
• das Costing für den NL Join addiert die Kosten des Zugriffs auf die driving row source mit den Kosten des einzelnen probe-Zugriffs, der mit der Anzahl der probe-Zugriffe multipliziert wird - was inhaltlich sehr einleuchtend erscheint. Durch einige überkommene Optimizer-Parameter kann man diese Kosten anpassen (optimizer_index_cost_adj und optimizer_index_caching) und leider trifft man ihre Verwendung noch immer recht häufig an.
• tatsächlich ist das physikalische I/O-Volumen beim NL-Join in der Regel niedriger, als das Modell es angibt, da dieses Join-Verfahren einen "self-caching" Effekt hervorruft: die relevanten Blöcke bleiben mit einiger Wahrscheinlichkeit im Cache, da immer wieder auf sie zugegriffen wird. Daher überschätzt der Optimizer die realen NL-Kosten häufig.
• durch das pinning von driving table blocks und den root/branch blocks des Index, der für die probe-Zugriffe verwendet wird, sinkt die Zahl der LIOs (da der Block nicht zwischenzeitlich freigegeben und dann wieder gepinnt wird).
• Zugriffe für einen non-unique index sind teurer als Zugriffe für einen unique index.
• es gibt diverse Optimierungen für die NL Join, die in verschiedenen Releases eingeführt wurden (10g: prefetching; 11g: batching; 12c: batch rowid access). Sie alle basieren darauf, dass die beim Zugriff auf die driving row source ermittelten rowids, zunächst sortiert werden, ehe der probe-Zugriff erfolgt: dadurch ist es nicht mehr notwendig, Blöcke der zweiten row source mehrmals zu besuchen.
• so lange ausreichende I/O bandwith zur Verfügung steht, ist eine höhere Anzahl von Blocks, die bei einer Leseoperation gelesen werden, nützlich. Anpassungen an den Parametern zur Bestimmung dieser Größe sind in der Regel nicht sinnvoll: viele der zugehörigen Parameter sind nicht dokumentiert und nach den Ergebnissen der Test sind die Performance-Unterschiede, die sich aus Veränderungen am multiblock read count ergeben, nicht dramatisch.
• einen größeren Einfluß auf die Performance der multiblock reads hat die Hardware und ihre Nutzung (SSDs oder HDDs; Striping; I/O Scheduler type; physical or virtual server; asynchonous I/O).
• Das Fazit des Artikels lautet: "Multiblock reads are a complete game changer for nested loop performance. However, extracting the full value from them requires properly configuring the storage, the OS and the database."

Die Analyse-Details der vorangehenden Artikel lohnen auf jeden Fall auch einen Blick, aber wenn man mir eine Zusammenfassung liefert, dann halte ich mich auch an diese...

Join mit Datums-Bereichen

Wenn man mit SQL arbeitet, dann gehört die Behandlung von Datumsintervallen zu den Themen mit eher beschränktem Unterhaltungswert. Insofern ist es sehr nützlich, dass Stew Ashton eine Artikelserie veröffentlicht hat, in der er diverse Spezialfälle genauer beleuchtet, mit denen man in diesem Kontext konfrontiert werden kann:

• Joining Temporal Tables 1: virtual date ranges: behandelt den einfachsten Fall: lückenlose und überschneidungsfreie Intervalle, die nur über ein FROM_DATE definiert sind, während das TO_DATE ermittelt wird (hier spricht der Herr Ashton von "virtual date ranges"). Die einfachste Vorgehensweise zur Zusammenfassung der FROM_DATEs ist die Verwendung von UNION ALL - und die TO_DATEs kann man sich dazu über Analytics (last_value) ergänzen. Alternativ könnte man auch MATCH_RECOGNIZE einsetzen.
• Joining Temporal Tables 2: handling nulls: zeigt, wie sich das Vorgehen verändern muss, wenn NULL-Werte ins Spiel kommen. Für die MATCH_RECOGNIZE-Lösung ist das ein vorgesehener Fall, aber bei der Verwendung von Analytics muss man unter diesen Umständen eine klarere Fallunterscheidung einführen.
• Joining Temporal Tables 3: Gaps: führt Lücken in den Intervallen ein. Erneut ist das mit MATCH_RECOGNIZE offenbar einfacher zu fassen als mit den Analytics.
• Joining Temporal Tables 4: ranges: betrachtet das Vorgehen im Fall der Existenz "echter" Ranges gegenüber den "virtual date ranges" der vorangehenden Artikel. Auch in diesem Fall spare ich mir die Details der Lösung.

Meine zentrale Beobachtung an dieser Stelle ist, dass MATCH_RECOGNIZE in solchen Fällen offenbar eine echte Alternative ist, der ich gelegentlich mehr Aufmerksamkeit widmen sollte.

Zeitmessung im Oracle Wait Interface

Frits Hoogland gehört zu den Autoren, bei denen mir die plausible Zusammenfassung ihrer Artikel große Mühe bereitet - was zunächst damit zu tun hat, dass sich diese Artikel häufig sehr intensiv mit OS-Fragestellungen beschäftigen und dorthin kann ich nur in beschränktem Umfang folgen. Diesmal aber hat der Herr Hooglands einen Artikel veröffentlicht, der sich mit der Umstellung der für die Zeitmessung im Wait Interface verwendeten Timer-Funktion beschäftigt - und da will ich mal wieder mein Glück in der Exzerpierung versuchen; oder wenigstens den Link unterbringen, um gelegentlich noch mal nachlesen zu können, was sich an dieser Stelle geändert hat. Dabei habe ich auch noch das Gefühl, dieses Thema hier schon mal in irgendeiner Form untergebracht zu haben, aber die beschränkten Suchfunktionen des Blogs haben mir an dieser Stelle nicht weiter geholfen.

Aber zum Thema: mit 11.2 wurde die Funktion gettimeofday() an vielen Stellen durch die Funktion clock_gettime() ersetzt. Dabei ist gettimeofday() "a best guess of the kernel of the wall clock time", während clock_gettime() ein monoton steigender Timer, "which means it is more precise and does not have the option to drift backward, which gettimeofday() can do in certain circumstances, like time adjustments via NTP." Im Artikel erläutert der Autor, warum er in der Kernel-Version seines Systems Schwierigkeiten hatte, diese Umstellung sichtbar zu machen, und wie das über den GNU Debugger (gdb) möglich ist. Durch dieses Vorgehen kann er auch bestimmen, dass die für clock_gettime() verwendete Verfahrensweise CLOCK_MONOTONIC ist - laut Dokumentation gäbe es da eine Reihe anderer Zeitmessungsalternativen (wie CLOCK_REALTIME, CLOCK_PROCESS_CPUTIME_ID, CLOCK_THREAD_CPUTIME_ID, CLOCK_MONOTONIC_RAW etc.). Durch die Verwendung dieser Basis ergibt sich auch, dass die Granularität des Wait Interfaces auf Mikrosekunden-Ebene liegt. Die weitere Untersuchung zeigt, dass das Wait Interface selbst im gegebenen Testfall einen Overhead von etwa 6 Mikrosekunden hervorgerufen hat. Dieser Overhead erklärt auch, wieso das Wait Interface zur Messung bestimmter Operationen nicht geeignet ist, deren Laufzeiten so niedrig sind, dass dieser Overhead die eigentlichen Ergebnisse massiv überlagern würde. Ein Beispiel sind einzelne Latch-Zugriffe, die auf OS-Ebene durchaus sinnvoll gemessen werden können, deren Laufzeiten aber deutlich niedriger sind als der Overhead des Wait Interfaces. Um genauere Informationen zur CPU-Nutzung zu erhalten ist man demnach früher oder später dazu gezwungen, auf die OS-Utilities (systemtap, perf, flame graphs etc.) zurückzugreifen, die ich mir offenbar gelegentlich genauer ansehen müsste.

OPT_ESTIMATE-Hint für Materialized View Fast Refresh

Die folgende Beobachtung verdanke ich Randolf Geist, der mir auf meine Anfrage im OTN-Forum General Database Discussions den entscheidenden Hinweis gegeben hat. Beim Fast Refresh für Materialized Views in 11g ergeben sich regelmäßig Merge-Statements der folgenden Form:

/* MV_REFRESH (MRG) */ 
MERGE INTO "TEST"."T_MV" "SNA$" 
USING (SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=1000) */

Grunsätzlich sind die zugehörigen Mechanismen bekannt - insbesondere Alberto Dell'Era hat dazu in seinem Blog umfassende Erläuterungen geliefert -, aber zum enthaltenen OPT_ESTIMATE-Hint habe ich nicht viel gefunden; die Suchmaschine meines Vertrauens hat mich in diesem Zusammenhang wieder zurück auf meine eigene Materialsammlung geführt, die ich hier vor einigen Jahren notiert hatte, aber dort steht nur, dass der Hint in diesem Zusammenhang erscheint. Mein Problem damit war, dass sich der Wert des OPT_ESTIMATE-Hints  bei unterschiedlichen Ausführungen verändert und mir bisher nicht klar war, woraus sich diese Parametrisierung ergibt. Ich hatte cardinality feedback im Verdacht und auf sql profiles, aber auch nach Deaktivierung des feedbacks bzw. in Abwesenheit von profiles änderte sich das Verhalten nicht. Nach Randolfs Hinweis, dass hier  habe ich folgenden Test durchgeführt:

-- 11.2.0.1
-- creation of table, mview log and mview
create table t
as
select rownum id
     , mod(rownum, 50) col1
     , mod(rownum, 10) col2
     , lpad('*', 50, '*') col3
  from dual
connect by level <= 1000000;

create materialized view log on t with rowid (id, col1, col2, col3) including new values;     

create materialized view t_mv
refresh fast on commit
as
select col1
     , sum(col2) sum_col2
     , count(*) cnt
     , count(col2) cnt_col2
  from t
 group by col1;

-- exec dbms_stats.set_table_stats(user, 'T_MV', numrows=>100)
-- exec dbms_stats.set_table_stats(user, 'T', numrows=>10000)
-- exec dbms_stats.set_table_stats(user, 'MLOG$_T', numrows=>10000)

select sql_id
     , substr(st.sql_text, instr(st.sql_text, 'OPT_ESTIMATE'), 40) sql_text
     , last_active_time
  from v$sql st
 where upper(st.sql_text) like '%OPT_ESTIMATE%'
   and upper(st.sql_text) like '/* MV_REFRESH (MRG) */%'
 order by last_active_time;

Dabei habe ich die Statistiken für die Anzahl der Datensätze für Tabelle, MView log und MView über dbms_stats.set_table_stats angepasst und dabei folgende Muster beobachtet:

• die Statistiken für Tabelle T und MView log MLOG$_T spielen für den OPT_ESTIMATE-Hint keine Rolle.
• ohne Statistiken für die MView T_MV wird ein default-Wert 1000 eingesetzt.
• wenn Statistiken vorliegen, dann wird der für den OPT_ESTIMATE-Hintverwendete Wert berechnet als trunc(num_rows/10)
• das Muster bleibt erhalten bis zum Wert 21474836479 (im Hint erscheint 2147483647). Mit 21474836480 scheint ein Überlauf erreicht zu sein, denn dann erscheint im Hint ein negativer Wert: -2147483648. Ich behaupte mal, dass hier 2³¹ erreicht ist.

Das Muster scheint also nicht besonders kompliziert zu sein. Ausgangspunkt meiner Suche war übrigens die Hoffnung, die Pläne der MView-Refresh-Statements über sql plan baselines stabilisieren zu können, was durch die Veränderung der Hints (bzw. der Statistiken) schwierig wird, aber dafür gibt es vermutlich noch andere Lösungen.