Dienstag, Februar 09, 2016

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.

    Montag, Februar 08, 2016

    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.

    Donnerstag, Februar 04, 2016

    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 231 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.

      Samstag, Januar 30, 2016

      Zur Semantik der Statistik "table scans (long tables)"

      Der aktuelle Beitrag im Scratchpad von Jonathan Lewis hat ein wenig an seine alten Quiz-Night Artikel angeschlossen und mir eine Erwähnung eingetragen:
      Martin Preiss (see comments) has been working hard to investigate this, and managed to produce a couple more variations in statistics for 'the same' problem.
      Ganz so hart war die Arbeit nicht, aber überraschend war das Ergebnis für mich allemal. Interessant ist zunächst, dass sich zwar Angaben für die Statistiken "table scans (short tables)" und "table scans (long tables)" wechselseitig ausschließen (soll heißen: ein full table scan kann nicht gleichzeitig in beiden Kategorien erscheinen), dass das aber nicht für die übrigen "table scans"-Angaben gilt: also für "table scans (rowid ranges)", "table scans (cache partitions)" und "table scans (direct read)". Dort auftretende Ergebnisse erscheinen zusätzlich auch unter "table scans (short tables)" oder "table scans (long tables)" - sie sind also eher ergänzende Typ-Informationen. Interessant ist darüber hinaus, dass die Semantik der Angaben in "table scans (short tables)" und "table scans (long tables)" auch deutlich von dem abweicht, was zumindest ich mir bisher darunter vorgestellt hatte: für einen parallelisierten Zugriff erscheint hier nicht etwa ein Wert 1 und auch nicht die Anzahl der parallelen Slaves, sondern "Parallelisierungsgrad" * 13, wobei die 13 dem Parameter _px_min_granules_per_slave entspricht und die Anazhl der Chunks angibt, in die die Tabelle zerlegt wurde. Die entsprechende Angabe wiederholt sich in "table scans (rowid ranges)", und die zugehörige Aussage der Dokumentation hatte mich erst auf die Idee gebracht, die Frage der Parallelisierung zu prüfen: "During parallel query, the number of table scans conducted with specified ROWID ranges". Der Wert "Parallelisierungsgrad" * 13 erscheint übrigens auch im sql monitor Ergebnis unter "execs". Unschön daran ist, dass dadurch der Aussagewert von "table scans (long tables)" dadurch ziemlich überschaubar wird, sofern man sich nicht die Mühe macht, die Parallelisierung von Queries einzurechnen. Interessant ist weiterhin der Hinweis auf eine Option, die mir entweder entgangen oder entfallen war: parallele Slaves können in 11g den Buffer Cache verwenden, so dass sie nicht unter "table scans (direct read)" erscheinen müssen - während mir der umgekehrte Fall der serial full table scans mit direct path Nutzung schon häufiger begegnet ist, hatte ich diese Variante bisher nicht im Blick; fast vielleicht damit zusammen hängt, dass mich parallele Zugriffe regelmäßig in Erstaunen versetzen.

      Donnerstag, Januar 28, 2016

      Erforderliche Leseoperationen bei der Anlage eines Primary Keys

      Ein recht überraschendes Verhalten bei der Constraint-Aktivierung spricht Jonathan Lewis in einem aktuellen Artikel an: wenn man einen mehrspaltigen Primary Key auf einer Tabelle anlegt, auf der zuvor für die betroffenen Spalten noch keine NOT NULL Constraints existierten, dann muss Oracle für jede einzelne Spalte einen Full Table Scan durchführen, um zu prüfen, dass die Spalten tatsächlich keine NULL-Werte enthalten. Anschließend ist ein weiterer Full Table Scan erforderlich, um die Daten für die Index-Anlage zu beschaffen. Sichtbar ist die Ressourcen-Nutzung über die Session-Statistics. Der Eindruck entsteht, dass hier ein effizienteres Vorgehen möglich wäre - aber die Implementierung ist an dieser Stelle offenbar seit vielen Oracle-Versionen die gleiche geblieben.