Freitag, Dezember 23, 2016

Neue Index-Features in 12.2

Richard Foote hat angekündigt, einige Artikel zu Index-Features, die in 12.2 ergänzt wurden, zu veröffentlichen. Ich werde versuchen, diese Artikel hier zusammenzufassen:
  • Oracle Database 12c Release 2: New Indexing Features – Long Identifiers (Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict): die Länge des Titels steht in Zusammenhang mit dem vorgestellten Feature - die Größenbegrenzung für Identifier wurde angehoben: statt 30 Zeichen kann ein Indexname jetzt 128 Zeichen umfassen. Klingt für mich eher bedrohlich, obwohl es natürlich Fälle gibt, in denen das alte Limit ein Problem darstellte.
  • 12.2 Index Advanced Compression “High” – Part I (High Hopes): die neue "high" Variante der in 12.1 eingeführten "index advanced compression" (bei der mich immer noch die Reihenfolge der Namensbestandteile stört) verspricht eine deutlich verbesserte Komprimierung und verwendet andere Algorithmen als die "low" Variante, die nur auf der Deduplizierung von Index-Einträgen im Leaf Block basierte. Bei der "high" Variante werden die Index-Einträge in compression units gespeichert, wobei das Verfahren offenbar dem der hybrid columnar compression ähnelt. Im Beispiel sind die Effekte recht eindrucksvoll: während normale index compression den Beispiel-Index (in dem 25% der Daten in einem bestimmten Teilbereich der Tabelle Duplikate sind)  größer macht als die nicht komprimierte Variante (2158 Blocks vs. 2684 Blocks), sorgt die "low" Variante für eine geringfügige Verkleinerung (2057 Blocks), aber die "high" Variante macht den Index deutlich kompakter (815 Blocks; wobei aufgrund eines Bugs eine Neuerfassung der Statistiken erforderlich ist, die zunächst 0 Blocks anzeigen).
  • 12.2 Index Advanced Compression “High” Part II (One Of My Turns): untersucht das Verhalten mit einem Index auf eindeutigen Werten ohne Wiederholungen. Für die normale index compression und die "low" Variante der index advanced compression ergibt sich jeweils der Fehler "ORA-25193: cannot use COMPRESS option for a single column key". Die "high" Variante hingegen reduziert die Indexgröße im Test wiederum signifikant (2088 Blocks vs. 985 Blocks). Auf Wunsch von Jonathan Lewis hat der Herr Foote das Beispiel mit größeren Id-Werten und einer randomisierten Verteilung wiederholt, wodurch sich der Effekt verkleinert, aber immer noch eine Größenreduzierung um 30% hervorruft. Jonathan Lewis hat dem Thema auch noch einen eigenen Artikel in seinem Scratchpad gewidmet.
  • 12.2 Index Advanced Compression “High” Part III (I Can’t Give Everything Away): zeigt, dass die Vererbung der compression-Attribute über db_index_compression_inheritance gesteuert werden kann. Außerdem kann man für einen Tablespace einen Default-Wert angeben.
Ich versuche, wie üblich, die in der Serie folgenden Artikel nachzutragen.

Donnerstag, Dezember 22, 2016

Attribut-Clustering und ein Jubiläum

Vor kurzem hat Connor McDonald eine Artikelserie zum Attribut-Clustering in 12c veröffentlicht, die im dritten Teil ein ziemlich seltsames Verhalten demonstriert. Ein Kommando:
alter table source_data add clustering by linear order(object_id);
führt zu einem erfolgreichen Clustering, während das Kommando:
alter table source_data clustering by linear order(object_id);
erfolgreich abläuft, aber anscheinend keinerlei Wirkung hat: das "add" hat also die entscheidende Wirkung. Randolf Geist hat in seinem Kommentar zum Artikel die naheliegende Frage nach dem Sinn der zweiten Variante gefragt, was Connor McDonald leider nicht beantworten konnte. Seltsam ist das auf jeden Fall.

Einen anderen Punkt will ich auch noch erwähnen: dies ist der tausendste Eintrag in diesem Blog und ich nehme das mal wieder zum Anlass, mich bei meinen Lesern zu bedanken. Darüber hinaus ergänze ich aus kalendarischen Gründen dann auch noch alle jahreszeitüblichen Grüße und Wünsche.

Montag, Dezember 19, 2016

Datenarchivierungs-Strategien in Oracle (Delete/Insert)

Jonathan Lewis hat vor kurzem zwei interessante Artikel zur Archivierung von Daten veröffentlicht, wobei die Anforderung so definiert ist, dass Daten via delete aus einer Tabelle gelöscht und die gleichen Datensätze per insert in eine zweite Tabelle eingefügt werden:
  • Delete/Insert: erläutert das grundsätzliche Problem: wie kann sichergestellt werden, dass die gleichen Daten, die aus der ersten Tabelle (t1) gelöscht wurden auch in der zweiten Tabelle (t2) eingefügt werden. Relativ einfach ist diese Anforderung mit massiven Locks zu erreichen, mit denen man konkurrierende DML-Operationen verhindert, aber in der Regel ist das keine wünschenswerte Verhaltensweise in einem multi-user System. Im Artikel werden mehrere möglich Strategien - samt ihrer jeweiligen Vor- und Nachteile - erläutert, darunter: (1) die Sammlung der vom delete betroffenen Sätze in einer collection, für die man dann ein insert in einer forall Schleife durchführt, (2) Sammlung der betroffenen rowid-s über ein select for update gefolgt von einem delete und einem insert mit den ermittelten rowid-s. In den Kommentaren zum Artikel werden weitere Optionen diskutiert (Trigger; logische Löschung mit delete Flag - unter Umständen als invisible column; Verwendung eines table lock; Verwendung einer GTT in Kombination mit einer pipelined table function).
  • Delete/Insert #2: analysiert eine in einem der Kommantare zum ersten Artikel aufgeführte Idee, die Jonathan Lewis zunächst verwarf und dann noch mal genauer untersuchte: die Verwendung einer "as of SCN" Klausel, die sich unter bestimmten Umständen ebenfalls verwenden lässt.
Sehr viel kompakter als alle Oracle-Varianten ist die postgres-Lösung zum Thema - mit Verwendung einer CTE und einer returning clause, die insert und delete zusammenfasst und die ich hier gelegentlich schon mal erwähnt hatte. Nachdem diese Syntax auch in den Kommentaren zum ersten Artikel erwähnt wurde und vom Herrn Lewis gelobt wurde ("That Postgres syntax is wonderfully compact – much more convenient than the PL/SQL of option 1 which is probably the closest you could get to it in Oracle.") habe ich eine entsprechende Idee im OTN Forum "database ideas" angelegt.

Montag, Dezember 12, 2016

Bestimmung der Verwendung von Indizes in 12.2

Bis Oracle 12.2 zum Download verfügbar ist, werde ich mich damit wohl nur theoretisch beschäftigen - also durch die Lektüre von Artikeln zum Thema. Eine der besten Quellen solcher Artikel ist derzeit der Blog von dbi Services - insbesondere, wenn der zugehörige Autor Franck Pachot heißt. Einer dieser Artikel behandelt das in 12.2 eingeführte erweiterte Monitoring zur Index-Verwendung und liefert unter anderem folgende Informationen:
  • die View dba_index_usage liefert kumulierte Nutzungsstatistiken für jeden Index.
  • in v$index_usage_info finden sich Nutzungsstatistiken seit dem letzten Flush (der alle 15 min stattfindet).
  • üblicherweise basieren die Angaben auf Sampling, können bei Bedarf auch für jede Ausführung erfasst werden (was natürlich einen Performance-Overhead hervorruft).
  • auch die Verwendung eines Index im Rahmen der gather_%_stats-Aufrufe werden erfasst, so dass die Angaben nur begrenzt Auskunft darüber geben, ob ein Index für Anwendungs-Zugriffe relevant ist.
  • die Verwendung eines Index auf einem Foreign Key zur Vermeidung eines table locks (Stichwort: deadlock-Ursache Nr. 1) wird hingegen in den Statistiken nicht erfasst.
Zusammenfassend lässt sich feststellen, dass dieses Monitoring ein Schritt in die richtige Richtung ist, aber nicht ausreicht, um die klassische Frage "kann ich diesen Index gefahrlos löschen?" zu beantworten. Zumal das Thema der Verwendung von Index-Statistiken für den Optimizer ohnehin nicht betroffen ist - aber das kann man dem Monitoring aus meiner Sicht nicht ernsthaft vorwerfen.

Montag, Dezember 05, 2016

Änderung der endpoint value Berechnung für Histogramme seit Oracle 11.2.0.4

Jonathan Lewis und vor ihm bereits Franck Pachot haben zuletzt darauf hingewiesen, dass in 11.2.0.4 eine Änderung der Berechnung der endpoint values für Histogramme von char und nchar (nicht aber varchar2 und nvarchar2) Spalten eingeführt wurde, die eine Neuerzeugung entsprechender Histogramme nach einem Upgrade von einer früheren Version erforderlich macht, da sich sonst recht bizarre costing-Effekte ergeben.

In Francks Test existiert eine Spalte mit den beiden Werten 'Y' und 'N', die massive ungleichmäßig verteilt sind (100K zu 1K). Zu erwarten wäre, dass diese Verteilung durch ein Frequency-Histogramm exakt abgebildet wird, was in 11.2.0.3 auch der Fall ist. Nach dem Upgrade auf 11.2.0.4 (ohne Neuerzugung des Histogramms) wird die Cardinality für beide Werte aber mit der Formel für nicht im frequency Histogramm vorliegende Werte berechnet: also "Häufigkeit des seltensten Werts im Histogramm" geteilt durch zwei (im Beispiel also 500). Demnach scheinen nach dem Upgrade also alle Werte unbekannt zu sein. Verantwortlich dafür ist die interne Ablage der endpoint values im Histogramm: bis 11.2.0.3 erfolgte das Padding der Werte mit spaces (ASCII 0x20), in 11.2.0.4 erfolgt es mit Nullen (ASCII 0x00) - was dem Verhalten entspricht das scon ihn älteren Releases für varchar2 verwendet wurde. Insofern ist die Neuerzeugung der Histogramme unvermeidbar.

Jonathan Lewis erwähnt in seinem Artikel auch noch mal die wichtige Tatsache, dass die Erzeugung vieler Histogrammtypen in 12c mit einem "approximate NDV" Verfahren erfolgt: die 5500 row samples, die in der Vergangenheit häufig zu Problemen geführt hatten, sind demnach in vielen Fällen nicht mehr im Spiel.

Freitag, November 18, 2016

Granularität der SCN

Ich mache es mir immer leichter mit meinen Blog-Einträgen: diesmal beschränke ich mich auf einen Link zu einem Artikel von Frits Hoogland und zitiere dann einfach die dort gelieferte Zusammenfassung:
Changes in the database are synchronised by system change numbers, also known as SCNs. SCNs are the way Oracle serialises changes to data. However, this post shows that the Oracle engine tries to maximise the number of changes inside a SCN, and as such a SCN is not an highly granular serialisation mechanism. This is not a problem, since Oracle’s transactional protection mechanisms (like TX and TM locks) do work alongside SCNs to make sure no conflicting changes can happen at the same time. The changes itself do just read and pick the current global SCN from the instance, a commit of these changes does increase the global SCN, but as little as possible. You might find multiple changes in the same SCN, and you might even find commits with the same SCNs.
Sehr kompakt und präzise. Merken werde ich mir in erster Linie, dass der Artikel erklärt, warum mehrere Commits die gleiche SCN beinhalten können.

Mittwoch, November 16, 2016

dbms_redefinition und deterministische Funktionen

Vor längerer Zeit war ich hier zum Ergebnis gekommen, dass rdbms_redefinition für komplexere Umbaumaßnahmen nicht verwendbar ist, weil man im Mapping keine komplexere Join-Logik oder Subselects unterbringen kann. Jetzt habe ich bei Connor McDonald gesehen, wie man es richtig macht: statt eines Joins  kann man im col_mapping eine deterministische Funktion unterbringen - und damit wird das Package noch mal deutlich interessanter.

Dienstag, November 08, 2016

Delta-Bestimmung mit Oracle

Dani Schnider hat vor einigen Wochen einen schönen Artikel veröffentlicht, in dem er verschiedene Möglichkeiten zur Bestimmung von Datenänderungen zwischen Quellsystem und Data Warehouse erläutert. Grundsätzlich kann in solchen Fällen ein Full (oder Left) Outer Join verwendet werden, der die Daten aus Quelle und Ziel miteinander abgleicht, und dabei gibt es drei relevante Fälle:
  • ein Datensatz existiert nur in der Quelle und nicht im Ziel: demnach wurde er neu erzeugt und muss im Ziel ergänzt werden.
  • ein Datensatz existiert nur im Ziel, nicht aber in der Quelle: offenbar wurde er in der Quelle gelöscht. In der Regel wird er nicht aus der Zieltabelle gelöscht, sondern dort als "im Quellsystem gelöscht" markiert (in der Regel mit Zeitstempel).
  • ein Datensatz existiert in Quelle und Ziel, aber einige Attribute weichen ab. In diesem Fall können unterschiedliche Versionierungsstrategien verwendet werden.
Der angesprochene Full (Left) Outer Join wäre recht trivial, wenn es nicht Fälle gäbe, in denen einzelne Attribute NULL-Werte enthalten können. Für diese Fälle gibt es folgende Abgleichs-Möglichkeiten:
  • NVL: man ersetzt NULL-Werte durch einen Wert, der in den Daten nicht erscheinen kann - wobei auf den Datentyp zu achten ist. Die Definition entsprechender Default-Werte kann relativ mühsam sein.
  • LNNVL: ist Datentypunabhängig, hat aber den Nachteil, dass der Vergleich true liefert, wenn beide Werte NULL enthalten. Daher ist das für diesen Fall erzeugte Statement relativ unübersichtlich.
  • SYS_OP_MAP_NONNULL: eine interne Funktion, die NULL durch einen internen Platzhalter ersetzt. Dadurch wird die Vergleichsquery recht übersichtlich, aber leider ist die interne Funktion noch immer nicht offiziell dokumentiert.
  • DECODE: liefert 1, wenn die Vergleichswerte identisch sind oder beide NULL, sonst aber 1. Durch Aufsummieren der Vergleichsergebnisse kann man erkennen, ob es relevante Abweichungen gibt.
  • ORA_HASH: kann aus der konkatenierten Spaltenliste einen Hash-Wert generieren. Probleme kann die Länge der verknüpften Strings mit sich bringen und theoretisch gibt es die Möglichkeit von Hash-Kollisionen.

Mittwoch, November 02, 2016

Cost Based Optimizer-Grundlagen

Randolf Geist, dessen englischsprachige Artikel ich hier gerne exzerpiere, hat für die Zeitschrift "Informatik Aktuell" einen Beitrag Cost Based Optimizer: Grundlagen – mit Update für Oracle 12c geschrieben. Dem aufmerksamen Leser ist jetzt vielleicht schon aufgefallen, dass Zeitschrift und Artikel offenbar deutschsprachig sind. Das einziges, was ich dazu ergänzen möchte, ist, dass ich den Artikel für eine der besten einführenden Zusammenfassungen zum Thema halte, die ich bisher gelesen habe - und mir fällt nichts Vergleichbares ein, das in deutscher Sprache vorläge. Insofern werde ich vermutlich in Zukunft häufiger auf diesen Artikel verweisen.

Mittwoch, Oktober 26, 2016

Angemessene Indexgröße

Für All Things Oracle hat Jonathan Lewis vor einigen Wochen einen Artikel geschrieben, in dem er erläutert, wie man die angemessene Größe eines B*Tree Index bestimmen kann. Darin geht er davon aus, dass ein stabiler B*Tree Index in der Regel etwa 70% des verfügbaren Speichers in den Leaf-Blocks auslastet, während etwa 30% freier Speicher in den Leaf-Blocks verfügbar bleibt. Unter dieser Voraussetzung ist es möglich eine grobe Abschätzung zu geben, wie groß ein Index in etwa sein sollte - und daraus kann man dann ableiten, ob unter Umständen einer der seltenen Fälle vorliegt, in denen ein Index-Rebuild tatsächlich einmal eine gute Idee sein könnte. Seine Formel zur Berechnung der angemessenen Indexgröße enthält folgende Elemente:
  • sum(user_tab_cols.avg_col_len + 1) für alle Index-Spalten
  • 6 byte für die rowid (8 byte im Fall eines globally partitioned index)
  • 1 byte, wenn der Index non-unique ist
  • 2 byte pro row
  • 2 byte pro row directory Eintrag
Der im Leaf-Block verfügbare Platz wird berechnet als: Blöckgröße - 200 Byte (für das block directory) und muss den pctfree-Wert berücksichtigen. Damit kann man dann berechnen, wie viele Index-Einträge in einen Leaf-Block passen und wie groß der Index insgesamt sein muss. Die Formel sollte für nicht komprimierte Indizes nicht mehr als 1 oder 2% von der tatsächlichen Index-Größe nach einem rebuild abweichen - und auf seiner Webseite hat der Herr Lewis auch ein entsprechendes Skript bereitgestellt.

Ein klassischer Fall, in dem ein Index von einem rebuild profitieren kann, ist ein fifo (first in first out) Processing, bei der einzelne Einträge stehen bleiben - und da ein Index-Block erst wieder verfügbar wird, wenn alle Einträge daraus gelöscht wurden, machen solche Überbleibsel Probleme. Solche Fälle kann man recht komfortabel mit dem dbms_space-Package analysieren und auch zu diesem Zweck gibt es ein Skript beim Herrn Lewis.

Ein weiteres Hilfsmittel zur Analyse von Indizes ist der Treedump den man über ein trace event erstellen lassen kann. Das Vorgehen dabei und einige grundlegende Analysetechniken (etwa die Verwendung von grep) werden im Artikel erläutert, aber in diesem Fall spare ich mir die detaillierte Nacherzählung. Festzuhalten bleibt, dass der Treedump eine genauere Analyse der Index-Struktur erlaubt und es möglich macht, ungewöhnliche Fälle exakter zu bestimmen.

Dienstag, Oktober 11, 2016

OTN Appreciation Day: Instrumentation

And now to something completely different: an entry in english. Some days ago Tim Hall suggested the introduction of an "OTN Appreciation Day" (did I miss some important letters?):
So taking that as the basis, and considering OTN is all about community, I figured it would be fun if we got as many people as possible to write a small blog post about their favourite Oracle feature and we all post them on the same day.
Today my feedly reader is flooded with articles of the naming pattern 'OTN Appreciation Day%' and so I came to the conclusion that adding some lines here should not hurt.

What is my favorite Oracle feature? There are many great things I could mention, but since I have worked frequently with other RDBMS it's quite easy to name the element which makes the main difference to me: Oracle's superior instrumentation. To make it clear: there are many very good relational database management systems available and they all have their strengths. And I would not at all claim that Oracle's database is better than any competitor in every given area (- since this would be complete nonsense...); but there is one thing that Oracle got right long before the competition: the instrumentation of database operations, the awesome wait interface and the large number of instructive trace events (Ok, maybe two things; maybe three things - and of course "fear, surprise, and ruthless efficiency...and an almost fanatical devotion to the Pope"). In Oracle there is no need to guess if switching parameter X could make something more efficient, no need to claim that in "real world systems it is always a good idea to...". Oracle's instrumentation makes it simple to measure the effects: we can build different test cases and check what differences the internal statistics show. To me this instrumentation made the difference between Oracle and many other RDBMS for many years. Nowadays the difference is not as big as it used to be, since other vendors started to add good instrumentation to their code too - but as far as I can see Oracle's advantage is still there. And in my opinion the instrumentation of RDBMS is also a big advantage in comparison to all the new shiny NoSQL solutions out there: without doubt they have their areas - but I guess they do not have the kind of instrumentation our good old legacy databases can provide.

That's it: now I promise to stop my mistreatment of the english language...

Freitag, Oktober 07, 2016

Wissenswertes über Pivot und Unpivot

Eine schöne und ausführliche Darstellung dessen, was man mit den Funktionen PIVOT und UNPIVOT anstellen kann, liefert Chris Saxon im AllThingsSQL Blog. Ich neige immer noch dazu, diese Transformationen manuell zu erzeugen, aber irgendwann werde ich mich an die Funktionen gewöhnt haben - hoffe ich ...

Mittwoch, Oktober 05, 2016

500 Byte Limit für Hints in SQL Profiles und SQL Patches

Mohamed Houri weist in einem aktuellen Artikel darauf hin, dass (Outline) Hints mit einer Länge von mehr als 500 Bytes zu Problemen bei der Erzeugung von SQL Profiles führen können, da der zu ihrer Erzeugung üblicherweise verwendete Typ sys.sqlprof_attr als "varray(2000) of varchar2(500)" definiert ist. Glücklicherweise akzeptiert die Prozedur dbms_sqltune.import_sql_profile statt sqlprof_attr Angaben auch CLOBs, so dass die Beschränkung an dieser Stelle umgangen werden kann. In einem Kommentar zum Artikel weist Jonathan Lewis darauf hin, dass die gleiche Einschränkung auch für SQL Patches gilt - allerdings in diesem Fall, ohne dass ein effektiver Workaround existierte.

Mittwoch, September 28, 2016

dbms_redefinition mit virtuellen Spalten

Nach längerer Zeit mal wieder etwas selbst Gebasteltes: im OTN Forum General Database Discussions wurde kürzlich ein Fall vorgestellt, in dem die Verschiebung einer Tabelle in einen anderen Tablespace mit Hilfe von dbms_redefinition.redef_table in einen Fehler führt, weil der vereinfachte automatisierte Prozess versucht, eine virtuelle Spalte durch ein Insert zu befüllen. Dazu ein Beispiel, das den OTN Fall noch mal reduziert:

drop table t;
drop table t_int;

create table t (
    id number
  , col_1 number
  , col_2 number
  , col_virtual number generated always as (nvl(col_1, col_2))
)
tablespace users
;

alter table t add constraint t_pk primary key (id);

insert into t (id, col_1, col_2)
values (1, 42, null);

commit;

select * from t;

        ID      COL_1      COL_2 COL_VIRTUAL
---------- ---------- ---------- -----------
         1         42                     42

select table_name, tablespace_name from user_tables where table_name = 'T';


TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              USERS


exec dbms_redefinition.redef_table (uname=>user, tname=>'t', table_part_tablespace=> 'EXAMPLE');  
*
ERROR at line 1:
ORA-42008: error occurred while instantiating the redefinition
ORA-12018: following error encountered during code generation for "C##TEST"."REDEF$_T92713"
ORA-54013: INSERT operation disallowed on virtual columns
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3385
ORA-06512: at line 1

Zur Erinnerung: rdbms_redefinition überführt die Daten der Originaltabelle in eine Interim-Tabelle und tauscht die beiden Objekte intern gegeneinander aus. Änderungen (also DML-Operationen), die sich nach dem Start der Reorganisation in der Quelltabelle ergeben, werden intern vermerkt und in der Zieltabelle vor Abschluss der Umstellung nachgezogen, so dass die Operation komplett online ablaufen kann. Die Prozedur redef_table ist dabei eine vereinfachte Variante, sie bietet ein:
single interface that integrates several redefinition steps including the CAN_REDEF_TABLE Procedure, the START_REDEF_TABLE Procedure, the COPY_TABLE_DEPENDENTS Procedure and the FINISH_REDEF_TABLE Procedure. This procedure can change data storage properties including tablespaces (for table, partition, subpartition, index, LOB column), compress type (for table, partition, subpartition, index, LOB column) and STORE_AS clause for the LOB column.
Im gegebenen Fall übersieht die Prozedur aber offenbar, dass man eine virtuelle Spalte nicht einfach mit einem Insert füllen kann, sondern ausklammern muss. Um das zu erreichen, kann man die Einzelschritte, die in redef_table zusammengefasst sind, einzeln aufrufen:

create table t_int (
    id number
  , col_1 number
  , col_2 number
  , col_virtual number generated always as (nvl(col_1, col_2))
)
tablespace example
;

declare
    l_colmap varchar(512);
begin
    l_colmap := 'id, col_1, col_2';
    dbms_redefinition.start_redef_table (  
        uname => user
      , orig_table => 't'
      , int_table => 't_int'
      , col_mapping => l_colmap );

end;
/

exec dbms_redefinition.finish_redef_table ( user, 't', 't_int' );

select table_name, tablespace_name from user_tables where table_name = 'T';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              EXAMPLE

Im Rahmen der Prozedur start_redef_table kann man die interim Tabelle t_int explizit angeben (nachdem man sie zuvor passend definiert hat) und über ein col_mapping die Liste der Spalten aufführen, die übertragen werden sollen. Das ist zwar nicht ganz so komfortabel wie der redef_table-Aufruf, führt aber zum gewünschten Ergebnis.

Montag, September 26, 2016

Histogramme für Spalten mit PK/UK

Jonathan Lewis hat dieser Tage in einer Diskussion der Oracle-L Mailing-Liste darauf hingewiesen, dass auch eine Spalte mit einem PK oder UK von einem Histogramm profitieren kann - und diese Aussage jetzt in seinem Blog erläutert und mit einem Beispiel versehen. Interessant ist ein solches Histogramm dann, wenn die Werteverteilung zwischen Minimum und Maximum sehr uneinheitlich ist, so dass sich große Bereiche ergeben, in denen fast keine Daten existieren, während in anderen Bereichen gleichen Umfangs sehr viele Ergebnisse zu finden sind. In seinem Beispiel erfolgt eine Abfrage auf einen solchen sparse-besetzten Bereich und Oracle erkennt anschließend bei der Statistikerfassung (mit Standardeinstellungen), dass hier ein Histogramm nützlich ist. Das die Anlage von Histogrammen begründende Phänomen "data skew" (sprich: Ungleichverteilung) betrifft also nicht nur das Auftreten ungleichmäßig vieler Datensätze für einen gegebene Wert, sondern auch die ungleiche Verteilung in bestimmten Wertebereichen für eindeutige Werte.

Dienstag, September 20, 2016

Parallel Hint auf Statement Ebene

Christian Antognini erläutert in seinem Blog die unterschiedlichen Ausprägungsformen, der seit 11.2 verfügbaren Variante des parallel Hints auf Statement-Ebene, die neben den Hint auf Objekt-Ebene getreten ist, der es seit 11.1 erlaubt, den dem Objekt zugeordneten DOP (degree of parallelism) zu überschreiben. Für den parallel Hint auf Statement-Ebene gibt es dabei folgende Möglichkeiten:
  • parallel (default): erzwingt die Verwendung des allgemeinen (also von den beteiligten Objekten unabhängigen) default DOP (der sich errechnet als: CPU_COUNT * PARALLEL_THREADS_PER_CPU * Anzahl, der an der Ausführung beteiligten Instanzen).
  • parallel (manual): aktiviert den manuellen DOP, wenn für mindestens eines der beteiligten Objekte ein Parallelisierungsgrad angegeben ist (default oder ein Wert > 1). Dieser Wert ist abhängig von den Parallelisierungs-Definitionen der beteiligten Objekten.
  • parallel (auto): aktiviert den automatischen DOP (nicht aber parallel statement queuing und in-memory parallel execution). Die Entscheidung über den Einsatz der Parallelisierung liegt damit bei Optimizer.
  • parallel: entspricht weitgehend parallel(auto). Der einzige Unterschied ist anscheinend, dass der Parameter PARALLEL_MIN_TIME_THRESHOLD beim Einsatz von parallel ohne (auto) nicht berücksichtigt wird.
  • parallel (n): setzt die Parallelisierung auf den vorgegebenen Wert.
Der Artikel ergänzt diese Aussagen um praktische Beispiele, bei denen die unterschiedlichen Hints für ein Test-Szenario jeweils unterschiedliche Effekte hervorrufen.

Dienstag, September 13, 2016

Template Datenbanken in Postgres

Daniel Westermann erklärt im DBI Blog die Rolle der Template-Datenbanken in postgres, über die ich mir bisher nicht allzu viele Gedanken gemacht habe. Im Artikel erläutert er folgende Punkte:
  • in einem neu installierten postgres-Cluster existieren zunächst drei Datenbanken: postgres, template0 und template1.
  • die Datenbank postgres ist der default-Einstiegspunkt für viele Applikationen (etwa den pgadmin), kann aber - wie jede andere Datenbank - gelöscht werden, wenn keine Connections zur Datenbank existieren.
  • man kann sich anschließend aber problemlos mit der Datenbank template1 verbinden und in dieser über das Kommando "create database postgres;" die Standarddatenbank neu erzeugen.
  • Eine Datenbank, die über "create database" erzeugt wird, stellt eine exakte Kopie der Datenbank template1 dar. Werden in template1 neue Objekte erzeugt, so sind sie somit auch in den danach aus dem Template erzeugten DBs vorhanden.
  • es ist möglich, an Stelle von template1 eine andere Template-Datenbank zu verwenden. Dazu dient die Syntax "create database ... template ....;"
  • template0 erlaubt keine Connections (datallowconn=f in pg_database) und dient als unveränderliches Basis-Template.
  • Änderungen an den Attributen in pg_database erfolgen als Update: damit kann man beispielsweise das Attribut datistemplate anpassen, um eine Datenbank zum Template zu machen (wodurch sie übrigens unlöschbar wird). Ich finde es zwar immer wieder unheimlich, wenn an Dictionary-Tabellen Anpassungen durch DML ausgeführt werden, aber in postgres ist das offenbar so vorgesehen.
Erstaunlich ist für mich dabei vor allem, wie wenig ich bisher über die Rolle der Template-Datenbanken nachgedacht habe...

Montag, September 05, 2016

Dictionary Metadaten in der Multitenant Infrastruktur

Franck Pachot hat seit 2014 eine Reihe interessanter Artikel zu den internen Implementierungsdetails der Dictionary-Zugriffe im Multitenant-Kontext veröffentlicht. Ich spare mir eine detaillierte Zusammenfassung der umfangreichen Inhalte und beschränke mich auf die Verweise:
Die Wahrscheinlichkeit ist hoch, dass ich damit nicht alle Artikel zum Thema erfasst habe, aber mir ging es eher darum, das Thema überhaupt einmal zu notieren, da ich bisher in Sachen Multitenant noch solide Wissenslücken habe.

    Mittwoch, August 31, 2016

    Statistikerfassung für Tabellen mit mehr als 255 Spalten mit dbms_stats

    Nur eine kurze Notiz auf einen Artikel von Randolf Geist, der erklärt, dass Oracle 12c eine deutliche Verbesserung bei der Erfassung von Statistiken für Tabellen mit sehr vielen Spalten eingeführt hat: in älteren Releases mussten die Statistiken für solche Tabellen mit mehr als 255 Spalten, deren Datensätze intern auf mehrere row pieces verteilt werden müssen, in mehreren Leseoperationen ermittelt werden (multi pass). Diese Einschränkung ist mit 12c aufgehoben: auch für Tabellen mit mehreren row pieces pro Datensatz benötigt die Statistikerfassung jetzt nur noch einen einzigen Zugriff (single pass), was die Performance der Operation deutlich verbessern kann.

    Montag, August 29, 2016

    Umwandlung von LONG in CLOB mit SYS_DBURIGEN

    Meine Standardantwort auf die Frage, wie man die Inhalte von LONG-Spalten auslesen kann, war seit vielen Jahren: ich hab's vergessen, aber Adrian Billington hat alles notiert, was man über diesen unerfreulichen Datentyp wissen muss. Diese Antwort kann ich jetzt modifizieren: der handlichste Weg, um LONGs in etwas weniger Häßliches zu verwandeln, ist die Verwendung der builtin-Funktion SYS_DBURIGEN, der von Marc Bleron (aka odi_63) in seinem Blog beschrieben wird. Intern erzeugt Oracle in diesem Zusammenhang ein DBURI Objekt und beim Aufruf der Funktion SYS_DBURIGEN(...).GETCLOB() wird eine SQL-Query generiert, die die erforderlichen Informationen abruft. Die Details des Aufrufs werde ich mir auch diesmal nicht merken, aber mit etwas Glück zumindest den Ort, an dem ich danach suchen kann.

    Zerlegung von Strings

    Stew Ashton hat in jüngerer Vergangenheit eine ganze Reihe interessanter Lösungen zum Problem der Zerlegung von Strings in einzelne Token in SQL vorgestellt. Die klassischen Lösungen dafür sind die (in der Regel auf Tom Kyte zurückgeführte) Kombination von instr und substr (die kurz vor 400 Bytes an ihre Grenzen stösst), sowie der Einsatz von regexp_substr, der - wie alle regexp-Operationen recht kostspielig im Hinblick auf die Nutzung der CPU-Ressourcen ist:
    • New, Improved IN Lists!: zeigt ein - auf Grundlage einer Präsentation von Connor McDonald - entwickeltes Vorgehen, das eine Kombination des row-Generators (connect by level) mit der instr- und der analytischen lead-Funktion verwendet. Das Ergebnis ist bereits recht kompakt und lesbar.
    • Splitting Strings: Surprise!: liefert einige zusätzliche Alternativen durch die Verwendung von XMLTABLE und insbesondere durch die zugehörige ora:tokenize Funktion.
    • Splitting Strings: Proof!: zeigt, dass ora:tokenize nicht nur eine kompakte Lösung darstellt, sondern auch deutlich schneller arbeitet als regexp_substr (erwartungsgemäß sehr langsam) und substr+instr (deutlich langsamer als ora:tokenize, wenn die Anzahl der Substrings, die zerteilt werden sollen, relativ groß ist).
    • Splitting Strings: PL/SQL: zeigt, dass die String-Zerlegung ein Fall ist, in dem PL/SQL schneller ist als SQL. Das kommt nicht besonders oft vor, ist aber auch nicht völlig unüblich.
    Ich würde mich nicht wundern, wenn der der Herr Ashton weitere Artikel ergänzen würde - und mein Bestes versuchen, sie hier gegebenenfalls einzufügen.

    Dienstag, August 16, 2016

    Physical I/O-Optimierung für Nested Joops Joins

    Vor einiger Zeit hatte ich hier eine Zusammenfassung der Zusammenfassung einer Artikelserie von Nikolay Savvinov untergebracht, die sich mit den physical IO Optimierungen für Nested Loops Joins beschäftigt. Nun hat Randolf Geist einen Artikel veröffentlicht, der - ausgehend auf Nikolays Ausführungen - den Versuch unternimmt, die in 12c vorkommenden Nested Loops Plan-Varianten mit den I/O Optimierungen zusammenzuführen.
    1. Nested Loops Join Batching: seit 11g die häufigste Variante. Im Plan erscheinen zwei Nested Loops steps: zunächst werden die rowids ermittelt und dann erfolgt der Tabellenzugriff über die rowid. Diese Plan-Form kann batched I/O ermöglichen ("db file parallel read" oder aynchronous I/O), aber die Entscheidung darüber, ob diese Optimierung verwendet wird, liegt bei der runtime engine (die auch auf die konventionellen "db file sequential read" Zugriffe zurückgreifen kann). Unter bestimmten Umständen können statt der "db file parallel read" Operationen (die mehrere I/O requests in einem einzelnen I/O submit call zusammenfassen) auch "db file scattered read" Zugriffe auftauchen - also multibock reads, die üblicherweise beim Full Table Scan (oder dem verwandten Index Fast Full Scan) auftreten; dies ergibt sich vor allem, wenn ein "cache warmup prefetching" verwendet wird (das aus unerfindlichen Gründen bei Verwendung von SQL Trace oder der Planerzeugung mit rowsource statistics deaktiviert wird; diese Plan-Form macht in 12c offenbar auch dem SQL Monitoring Probleme, das bei der Zählung von Iterationen durcheinander kommen kann).
    2. Nested Loop Join Prefetch (mit batched rowid Zugriff - in 12c): Seit 12c tritt das in 9i eingeführte Nested Loop prefetching in neuer Form auf und enthält nun nach dem TABLE ACCESS BY INDEX ROWID das zusätzliche Schlüsselwort BATCHED. Für den Tabellenzugriff erfolgt dabei eine sehr intensive Zusammenfassung von Zugriffen in den "db file parallel read" Operationen, während der Index-Zugriff offenbar nicht zusammengefasst wird, sondern via single block calls erfolgt (also "db file sequential read") - zumindest kommen Randolf und Nikolay in ihren Test-Setups zu diesem Ergebnis. Der Plan tritt in der freien Wildbahn normalerweise nicht auf, kann aber durch (im Artikel aufgeführte) Hints oder durch Deaktivierung des Nested Loops Join Batching erzwungen werden.
    3. Nested Loop Join Prefetch (seit 9i): auch dieser Plan tritt in aktuellen Releases nur auf, wenn man das Nested Loops Join Batching deaktiviert. Er verhält sich ähnlich wie die 12c Variante, verwendet aber ein weniger agressives prefetching: die Anzahl der in einem "db file parallel read" zusammengefassten requests scheint auf 39 beschränkt zu sein.
    4. Klassischer Nested Loops Plan (mit batched rowid Zugriff - in 12c): in Randolfs Tests werden nur die Zugriffe einer Loop-Iteration zusammengefasst: "db file parallel read" Zugriffe treten also nur auf, wenn das Clustering der Tabellendaten in Hinsicht auf den Index nicht besonders gut ist. Für den Index-Zugriff im Index Range Scan erfolgen anscheinend keine Optimierungen (wie schon bei den Prefetch Varianten). Auch dieser Plan tritt in 12c nur unter bestimmten Umständen auf: hauptsächlich, wenn mehrere aufeinander folgende Nested Loops Operationen aufeinander folgen. Mit (den im Artikel aufgeführten) Hints kann man den Plan natürlich auch erzwingen.
    5. Klassischer Nested Loops Plan: anders als die 12c-Variante mit dem batched rowid Zugriff erlaubt dieser Plan auch ein ein Batching über mehrere Loop-Iterationen hinweg (und ähnelt insofern recht stark der Implementierung des in 9i eingeführten "Nested Loop Join Prefetch" (aus 3.), als die Zusammenfassung von requests wiederum auf 39 beschränkt ist und kein Batching für die Index Range Scan Operation erfolgt.
      Nachtrag 21.08.2016: In seinem Kommentar hat mich Randolf darauf hingewiesen, dass ich an dieser Stelle falsch interpretiert habe: auch die pre-12c-Implementierung des klassischen NL-Plans unterstützt kein Batching über Loop-Grenzen hinweg. Die Symmetrie zu den vorherigen Beispielen ("Nested Loop Join Prefetch" in 2. und 3.) liegt darin, dass auch hier die ältere (pre-12c) Plan-Variante ohne das "BATCHED ROWID" ein weniger agressives Batching unterstützt: Fall 5 verhält sich demnach zu Fall 4. wie Fall 3. zu Fall 2.
    Bei der Verknüpfung mehrerer verschachtelter Nested Loop Operationen tritt das Batching übrigens nur für die äußerste Schleife auf, was den Effekt der Optimierung reduziert, da NL Joins ja oft in Scharen auftreten. Da sich die unterschiedlichen Plan Varianten nicht durch das Costing unterscheiden, könnte die manuelle Beeinflussung der Join Reihenfolge für solche Fälle einen signifikanten Performance-Unterschied hervorrufen.

    Montag, August 01, 2016

    Spaltenvergleichen mit NULL-Werten

    Randolf Geist hat vor kurzem einen interessanten Artikel zu einem Thema veröffentlicht, mit dem man sich beim Schreiben komplexerer SQL-Queries regelmäßig herumschlagen muss: dem Vergleichen von Spalten, in denen NULL-Werte auftauchen können. Für die Prüfung der Gleichheit von Werten bedarf die korrekte Behandlung von NULL-Werten bereits eines recht sperrigen Ausdrucks:
    column1 = column2 or (column1 is null and column2 is null)
    Und noch unhandlicher wird der Ausdruck, wenn man die Ungleichheit von Werten prüfen möchte:
    column1 != column2 or (column1 is null and column2 is not null) or (column1 is not null and column2 is null)
    Um solche Konstrukte vermeiden zu können, wird bisweilen ein NVL um die Vergleichswerte gesetzt, um den möglichen NULL-Wert durch eine Alternative zu ersetzen, von der man sicher ist, dass sie außerhalb des Wertebereichs der Spalte liegt - aber wann kann man sich in einem solchen Punkt wirklich sicher sein?

    Eine andere beliebte Variante dazu ist die Verwendung der lange Zeit nicht dokumentierten Funktion SYS_OP_MAP_NONNULL, die in 12c schließlich in der Doku erscheint, aber immer noch nicht im SQL language manual. Diese Funktion hat allerdings einen Nachteil: sie ergänzt ein Byte zu jedem Input-Wert, was dazu führt, dass sie bei Verwendung von Spalten mit der maximalen Größe für den verwendeten Datentyp einen Fehler hervorruft (nämlich "ORA-01706: user function result value was too large"). Als Alternative dazu wurde gelegentlich die Verwendung von decode vorgeschlagen - etwa von Stew Ashton, dessen zugehörigen Artikel ich hier gelegentlich erwähnt hatte. Da decode NULL-Werte als vergleichbar ansieht, werden die erforderlichen Prüfungen deutlich übersichtlicher - für den Fall der Gleichheit:
    decode(column1, column2, 0, 1) = 0
    Und für die Ungleichheitsprüfung:
    decode(column1, column2, 0, 1) = 1
    Hier kommt aber seit Version 11.2.0.2 eine problematische Optimierung ins Spiel: der Fall der Gleichheitsprüfung (aber nicht der der Ungleichheitsprüfung) wird intern auf die Verwendung von SYS_OP_MAP_NONNULL umgeschrieben, was dann wiederum die Probleme mit den Maximallängen hervorruft. Zusätzlich kommt noch hinzu, dass die SYS_OP_MAP_NONNULL-Funktion in Randolfs Test langsamer ist als das nicht umgeschriebene decode und langsamer als die verbose Standard-Variante. Insofern sollte man SYS_OP_MAP_NONNULL und die implizite Umschreibung von decode seit 11.2.0.2 unter Umständen besser vermeiden und fix control 8551880 verwenden, um die implizite Umwandlung zu vermeiden. Zu hoffen ist, dass Oracle gelegentlich eine solidere Lösung für dieses Problem zur Verfügung stellen kann.

    Mittwoch, Juli 27, 2016

    Bloom Pruning im Ausführungsplan

    Maria Colgan hat mal wieder einen Artikel geschrieben, der sich nicht unmittelbar mit ihrem aktuellen Tätigkeitsfeld In-Memory beschäftigt, sondern ihr altes Thema betrifft: die Strategien des Optimizers. Im Artikel zeigt sie einen Plan, der in der Name-Spalte zwei Bloom Filter Erzeugungen aufführt (JOIN FILTER CREATE, PART JOIN FILTER CREATE), aber nur die Verwendung eines der beiden Filter. Der zweite Bloom Filter erscheint in den Spalten Pstart und Pstop und wird zum Partition Pruning verwendet (also zum Überspringen nicht benötigter Partitionen) - und dieses Pruning mit dem Bloom Filter nennt man dann aus naheliegenden Gründen Bloom Pruning.

    Donnerstag, Juli 21, 2016

    Partial Indexes für partitionierte Tabellen

    Dani Schnider hat zuletzt eine dreiteilige Serie zur Verwendung partieller Indizes veröffentlicht. Dabei stellt sich als erstes die Frage: was ist ein Partial Index überhaupt? Die Antwort lautet: Partielle Indizes werden nur auf einer Teilmenge der Partitionen einer partitionierten Tabelle erzeugt: entweder, um die Ladeprozesse für aktive Partitionen nicht zu beeinträchtigen - in diesem Fall verzichtet man auf eine Indizierung der aktuellen Daten; oder um umgekehrt die Zugriffe auf die aktuellen Daten durch Indizes zu unterstützen, während das für historische Daten vermeidbar ist:
    • Partial Indexes Trilogy – Part 1: Local Partial Indexes: erläutert, dass die partielle Indizierung über die Schlüsselwörter INDEXING ON/OFF gesteuert wird, die bestimmen, ob für eine Partition lokale Indizes angelegt werden. In dba_tab_partitions zeigt die Spalte INDEXING an, welche Definition für eine Partition gewählt wurde. Bei der Anlage eines lokalen Index kann man nun die Option INDEXING PARTIAL angeben, die dafür sorgt, dass für alle Partitionen mit indexing=off Index-Partitionen im Zustand UNUSABLE erzeugt werden, zu denen keine physikalischen Segmente angelegt werden. Ohne die INDEXING PARTIAL Option wird der Index in allen Partitionen erzeugt, die indexing Definition ist dann nicht wirksam, so dass man die partielle Indizierung Index-spezifisch einrichten kann. Ein Index rebuild für lokale Indizes ist nur auf Partitionsebene möglich, aber damit kann man dann auch die initial als unusable definierten Partitionen über einen rebuild usable machen - und damit den Index von einem partiellen in einen ganz normalen lokalen Index umwandeln. Eigentlich sind partielle Indizes erst in 12c verfügbar geworden, aber in 11g kann man das Verhalten leicht nachbauen, wenn man einen lokalen Index initial als unusable definiert und dann nur die Partitionen über rebuild verfügbar macht, für die man den Index tatsächlich verwenden möchte.
    • Partial Indexes Trilogy – Part 2: Global Partial Indexes: während sich das Verhalten partieller lokaler Indizes in 11g manuell nachbilden lässt, ist das im Fall globaler Indizes nicht der Fall. Bei diesen Indizes werden nur die rowids indiziert, die auf Partitionen verweisen, für die indexing=on gewählt wurde. Die häufigste Ursache dafür, dass man überhaupt einen global Index definiert, ist die Notwendigkeit, einen unique index zu erzeugen, der den partition key nicht enthält - und das ist dann mit einem partial index aus naheliegenden Gründen nicht möglich: wenn nicht alle Datensätze indiziert sind, lässt sich die Eindeutigkeit eindeutig nicht garantieren. Mit einem Befehl ALTER TABLE ... MODIFY PARTITION ... INDEXING ON; kann man zusätzliche Partitionen in den global partial index aufnehmen, was aber natürlich massive Index-Maintenance nach sich ziehen kann. Umgekehrt ist die Umstellung auf INDEXING OFF zunächst eine Metadaten-Operation: die Einträge werden nicht sofort aus der Index-Struktur gelöscht, stattdessen werden in dba_indizes orphaned_entries angezeigt, die darauf hin deuten, dass die Index-Struktur temporär nutzlose Elemente enthält. Die Beseitgung dieser Einträge erfolgt schließlich durch "asynchronous global index maintenance" und in diesem Zusammenhang verweist der Herrn Schnider auf einige Artikel von Richard Foote, die ich hier sicher auch schon mal verlinkt habe. Ein anderer interessanter Aspekt ist, dass die Löschung einer Partition mit indexing=off dazu führt, dass der global partial index unusable wird, sofern dabei nicht die UPDATE INDEXES Klausel verwendet wird.
    • Partial Indexes Trilogy – Part 3: Queries on Partial Indexes: erläutert, wie sich die Zugriffe über partial indexes verhalten. Im Fall lokaler partial indexes wird der Zugriff über UNION ALL verknüpft: für die indizierten Partitionen erscheint der Index-Zugriff, für die übrigen Partitionen ein Full Table Scan. Abhängig davon, ob indizierte und nicht-indizierte Partitionen oder nur die einen oder die anderen abgefragt werden, ergeben sich unterschiedliche Plan-Varianten, wobei das UNION ALL in allen Fällen erscheint und die irrelvanten Teile des Plans dann ggf. nicht ausgeführt werden müssen. Ähnlich sieht es für die global partial indexes aus, wobei sich weitere Varianten in der Plandarstellung ergeben. Auch im Rahmen der Star Transformation im Data Warehouse können partielle Indizes verwendet werden.

    Montag, Juli 18, 2016

    Hint-Verwendung zur Bestimmung der Join-Reihenfolge

    Brendan Furey erläuert in seinem Blog die Möglichkeiten, die Oracle bietet, die Join-Reihenfolge über Hints zu beeinflussen. Darin weist er zunächst darauf hin, dass der USE_HASH Hint eigentlich nur einen Parameter benötigt und nicht die Angabe der beiden Aliase der Tabellen, die man miteinander verknüpfen will: bei der Angabe von zwei Aliasen betrachtet Oracle dies als zwei Hints und wird folglich einen der beiden übergehen. Welcher der Hints nicht berücksichtigt wird, hängt von der Reihenfolge ab, in der die Tabellen gejoint werden, und in diesem Zusammenhang wird auf einen klassischen "Quiz Night" Artikel von Jonathan Lewis verwiesen, der erläutert, dass im Fall eines 4-Tabellen-Joins, bei dem die Join-Reihenfolge über LEADING Hints und die Join Methode über USE_HASH Hints bestimmt ist, immer noch acht unterschiedliche Pläne entstehen können, weil dadurch noch nicht festgelegt ist, welche rowsource als "build table" und welche als "probe table" zu betrachten ist, was über die Hints SWAP_JOIN_INPUTS und NO_SWAP_JOIN_INPUTS gesteuert werden kann. An dieser Stelle ergänzt der Herr Furey seine Überlegung, dass in diesem Fall eigentlich zwei Ebenen von Sortierungen zu berücksichtigen sind, da man den gleichen Plan mit mehreren unterschiedlichen Hint-Kombinationen hervorrufen kann: eine Outer-level join order (die die Reihenfolge angibt, in der die Tabellen in einem komplexen Join verknüpft werden) und eine Inner-level join order (die anzeigt, auf welcher Seite eine Joins eine Tabelle bei der Verknüpfung mit einer anderen rowsource eingesetzt wird: also im Hash Join etwa als build oder probe table). Nach dieser Definition hat der LEADING Hint unterschiedliche Auswirkungen, abhängig davon, ob ein Hash Join oder ein anderer Join Typ im Spiel ist. Ich denke, das ist eine interessante Art, den Sachverhalt zu betrachten, obwohl ich mir noch nicht ganz sicher bin, ob diese Unterscheidung massive Vorteile bei der Beschreibung der Situation bringt.

    Montag, Juli 11, 2016

    Staleness IMPORT für Materialized Views in all_mviews nach data pump Import

    Eine kurze Notiz, da ich bei meiner Recherche im Netz nicht arg viel zum Thema gefunden habe: durch den Transport einer Materialized View über data pump (expdp - impdp) werden zwar die Daten des Segments transportiert, aber die MView wird nicht mehr für Query Rewrite verwendet und auch ein Fast Refresh ist nicht mehr möglich und scheitert an einem Fehler ORA-12034:
    ORA-12034: materialized view log on "XXX"."YYY" younger than last refresh
    An dieser Stelle ist daher ein complete refresh erforderlich. Insofern ist es vermutlich sinnvoller, auf den Transfer der MView-Daten komplett zu verzichten und sie lieber gleich im Zielsystem neu aufzubauen. Besonders überraschend ist das nicht - auch wenn sich dafür vermutlich eine Lösung bei der Implementierung hätte finden lassen, wenn man sich darum bemüht hätte -, aber falls ich es schon mal wusste, habe ich es vergessen. Überraschender ist vielleicht der Status IMPORT, der in diesem Fall für die MView in der Spalte STALENESS in DBA_MVIEWS auftaucht - obwohl die Dokumentation diesen Status nicht erwähnt; dort findet man nur den Hinweis auf die ergänzende Spalte UNKNOWN_IMPORT: "Indicates whether the materialized view is imported (Y) or not (N)". Gänzlich undokumentiert ist dieser Staleness-Status aber nicht: man findet ihn im MOS-Dokument "How to Perform an EXPDP/IMPDP for a SCHEMA with a MATERIALIZED VIEW. Highlighting the IMPORT Staleness and UNKNOWN_IMPORT column in DBA_MVIEWS (Doc ID 1579866.1)" und dort wird das Verhalten auch noch mal anhand eines Beispiels dargestellt.

    Montag, Juli 04, 2016

    Default für Lead und Lag Funktionen

    Eine Lösung für ein kleines Problem, dem ich mit den analytischen Funktionen LEAD und LAG schon häufiger begegnet bin, findet man bei Connor McDonald: an den Grenzen eines durch die analytischen Funktionen definierten Fensters erhält man üblicherweise einen NULL-Wert - also etwa dann, wenn man nach dem Vorgängerwert für den ersten Satz eines Ergebnisses sucht. Diesen Fall kann man mit einer NVL-Funktion abfangen, aber eleganter und kürzer ist die Verwendung eines default-Wertes, den die analytische Funktion als dritten Parameter (nach value_expr und offset) annimmt:

    create table t
    as
    select rownum id
      from dual
    connect by level <= 10;
    
    select id
         , lag(id) over(order by id) lag_id1
         , nvl(lag(id) over(order by id), 0) lag_id2
         , lag(id, 1, 0) over(order by id) lag_id3
      from t;
    
    ID    LAG_ID1    LAG_ID2    LAG_ID3
    -- ---------- ---------- ----------
     1                     0          0
     2          1          1          1
     3          2          2          2
     4          3          3          3
     5          4          4          4
     6          5          5          5
     7          6          6          6
     8          7          7          7
     9          8          8          8
    10          9          9          9
    

    Ich vergesse auch die offset-Option gerne und komme mal wieder zum Ergebnis, dass es gelegentlich noch mal nützlich wäre, die Dokumentation gründlich zu lesen.

    Dienstag, Juni 28, 2016

    Index Sanity Check für Cardinality Schätzungen

    Auf das Thema weise ich in den OTN-Foren recht häufig hin und da ist es gut, dass Jonathan Lewis einen Artikel dazu geschrieben hat, auf den man verweisen kann: die Löschung eines Index kann den Ausführungsplan einer Query auch dann ändern, wenn der Index gar nicht im Execution Plan erscheint. Die Erklärung dafür sind Sanity Checks die auf der Basis der Index-Statistiken für mehrspaltige Indizes operieren. Im Artikel stellt Jonathan Lewis ein kleines Beispiel vor, das diesen Sachverhalt deutlich macht: darin wird ein Index gelöscht, der im Ausführungsplan keine Rolle spielt. Dieser Index ist auf einer Kombination von drei Spalten angelegt, von denen die erste 10 distinkte Werte enthält, die zweite nur einen und die dritte wiederum 20 distinkte Werte. Die Kombination der Selektivitäten würde - da der Oracle Optimizer bekanntlich von unabhängigen Wahrscheinlichkeiten ausgeht, also keine Abhängigkeiten annimmt - daraus 10 * 1 * 20 = 200 machen. In den Index-Statistiken ist aber festgehalten, dass die Zahl der tatsächlich auftretenden Kombinationen nur bei 20 liegt und dieser Wert wird dann auch vom Optimizer berücksichtigt. Die Löschung des Index führt dann natürlich zum Verlust der Information und zu einer weniger akkuraten Schätzung. Seit 11g kann man in einem solchen Fall den Informationsverlust durch Anlage von Extended Statistics auf der Spaltengruppe vermeiden, aber grundsätzlich zeigt das Beispiel, dass man beim Löschen "unbenutzter" Indizes vorsichtig sein sollte.

    Montag, Juni 20, 2016

    Typ-Konvertierung und cardinality-Schätzung

    Jonathan Lewis weit in seinem Scratchpad darauf hin, dass Funktionsaufrufe für Spalten im Fall einer Bedingung "function(column) = constant" üblicherweise zu einer Schätzung von 1% führen. Dies gilt aber nicht für einfache Typ-Umwandlungen: eine Bedingung in der eine Charakter-Spalte mit einem numerischen Wert verglichen wird, verwendet eine Variante der Standardformel zur Berechnung gleichverteilter Werte, nämlich (Anzahl Werte)/(Anzahl distinkter Werte) - wobei die Klassifizierung als Variante damit zusammenhängt, dass die Anzahl distinkter Werte in einer Charakter-Spalte nicht zwingend der Anzahl der in ihr repräsentierter numerischer Werte entspricht, da 9 als '9' und '09' und beliebig viele andere Strings dargestellt werden kann. Für eine String-Spalte v1 liefert demnach folgende Bedingung eine cardinality auf Basis der Standardformel:
    where to_number(v1) = 9
    Wobei das to_number keine Rolle spielt, denn "where v1 = 9" wird genauso behandelt. Will man den Optimizer in einem solchen Fall weiter verwirren und die 1%-Schätzung für "function(column) = constant" zurückbekommen, dann kann man eine weitere Funktion ins Spiel bringen. Mit:
    where sign(v1) = 1
    bekommt man diese 1%-Schätzung, da der Optimizer in diesem Fall keine Vorstellung davon hat, was die sign-Funktion eigentlich tut. Ob man das irgendwo praktisch nutzen kann, sei mal dahingestellt - aber es zu wissen, kann sicher nicht schaden.

    Natürlich funktionieren alle Beispiele nur so lange, wie v1 tatsächlich nur Repräsentationen numerische Werte enthält - und dann wäre es vermutlich naheliegend die Spalte gleich mit dem richtigen Datentyp auszustatten.

    Montag, Juni 06, 2016

    Merge Operationen und überflüssige Spalten.

    Vor ein paar Jahren hatte ich hier einen Artikel von Alexander Anokhin verlinkt, in dem erläutert wurde, dass die in der USING clause eines Merge verwendeten Spalten selbst dann in die workarea aufgenommen werden müssen, wenn sie weder im ON noch in der UPDATE-Spaltenliste erscheinen - obwohl es eigentlich durch eine semantische Analyse möglich sein sollte zu bestimmen, dass man diese Spalten gefahrlos ausklammern könnte. Jetzt hat Jonathan Lewis auf einen naheliegenden anderen Aspekt dieses Effekts hingewiesen: dadurch, dass die Spalten in der Projection berücksichtigt werden, ist auch die Verwendung eines Index Fast Full Scans anstelle eines Full Table Scans nicht möglich, wenn zwischenzeitlich Spalten benötigt werden, die für die funktionale Ausführung der Operation eigentlich keine Rolle spielen.

    Sonntag, Juni 05, 2016

    SQL Server Wait Typen und Latch Klassen

    Zwei großartige Hilfsmittel hat Paul Randal vor wenigen Wochen in seinem Blog bekannt gemacht:
    • SQL Server Wait Types Library: eine Liste mit den seit Version 2005 im SQL Server vorkommenden Wait Typen und Erklärungen zu ihrer Bedeutung, ihrer Verfügbarkeit, ihrer Beziehung zu des Extendes Events, sowie Informationen zum Troubleshooting.
    • SQL Server Latch Classes Library: mit entsprechenden Informationen zu den Latch Klassen.
    Diese Links könnten mir allerlei Suchoperationen in Books Online ersparen.

    Mittwoch, Juni 01, 2016

    Datentypauswahl für Datumsangaben und ihre Wirkung auf den CBO

    In Cost Based Oracle hat Jonathan Lewis dieses Thema bereits umfassend erläutert: Datumswerte sollten mit dem Datentyp DATE gespeichert werden, da der Optimizer nur für diesen Datentyp das Wissen besitzt, dass es zwischen den Monats- und Jahreswechseln keine großen Lücken gibt - dass also auf to_date('31.12.2016', 'dd.mm.yyyy') als nächster Tag to_date('01.01.2017', 'dd.mm.yyyy') folgt; und nicht 20161232 nach 20161231. Insofern enthält die aktuelle Artikelserie von Richard Foote zum gleichen Thema keine grundsätzlich neuen Einsichten, aber lesenswert sind die Artikel des Herrn Foote immer und man kann bei Bedarf gut darauf verlinken, so dass ich sie hier einfach aufliste:

      Freitag, Mai 27, 2016

      CBO Transformationen für count distinct Operationen

      Anfang 2014 hatte ich hier einen Artikel angesprochen, der im High-Performance Blog von Persicope erschienen war und versprach, postgres Queries mit einer count distinct Operation um den Faktor 50 zu beschleunigen. Ich hatte damals selbst ein paar Tests mit Oracle durchgeführt, die zeigten, dass die Umformulierung auch dort nützlich ist, aber weitaus weniger dramatische Effekte hervorruft als bei postgres (was kurz darauf in einem weiteren Artikel bei Periscope ebenfalls angemerkt wurde. Außerdem hatte ich einen Artikel von Hubert Lubacewski verknüpft, in dem dieser zeigte, dass man durch geschicktere Umformulierung in postgres noch sehr viel mehr erreichen kann als mit den periscope-Varianten.

      Jetzt hat Jonathan Lewis im Scratchpad auf den Artikel verwiesen und dabei gezeigt, dass der CBO für einfachere Beispiele durchaus dazu in der Lage ist, diese Umformung selbständig durchzuführen. Verwendet werden dabei folgende Optimizer-Transformationen:
      • place group by: verfügbar ab 11.1.0.6 (2007)
      • transform distinct aggregation: verfügbar seit 11.2.0.1 (2009)
      Wie üblich ist der Optimizer aber nicht in jedem Fall dazu in der Lage, die richtige Strategie auszuwählen, so dass man ihn manchmal durch Hints auf die richtige Spur bringen muss (hier etwa: no_transform_distinct_agg).

      Freitag, Mai 20, 2016

      Optimizer-Features unterschiedlicher Oracle-Versionen vergleichen

      Nigel Bayliss stellt im Blog der Oracle Optimizer Entwicklung ein nützliches Skript vor, mit dessen Hilfe man die Optimizer Features unterschiedlicher Oracle Releases vergleichen kann. Das Skript legt diverse Hilfstabellen an und greift auf v$session_fix_control, sys.x$ksppi und sys.x$ksppcv zu, für die man demnach Lesezugriff benötigt. Kann man natürlich auch von Hand machen, aber ein passendes Skript macht dergleichen komfortabler.

      Mittwoch, Mai 11, 2016

      Locks zur Sicherstellung referentieller Integrität

      Jonathan Lewis wiederholt in seinem Blog zur Zeit allerlei Erläuterungen, die er schon häufiger gegeben hat, und ich wiederhole hier dann noch mal die Punkte, die es bisher trotz Wiederholung noch nicht bis in meinen aktiven Wissensbestand geschafft hatten (sondern nur eine vage Erinnerung aufrufen). Einer dieser Punkte betrifft das Verhalten der Locks zur Gewährleistung referentieller Integrität, die als RI Locks bezeichnet werden. Dabei skizziert der Autor folgendes Szenario:
      • ein Datensatz wird in eine Parent-Tabelle eingefügt, aber nicht über commit festgeschrieben.
      • in einer zweiten Session soll in eine child-Tabelle ein Datensatz eingefügt werden, der sich auf den neuen Parent-Datensatz bezieht.
      • man könnte annehmen, dass die zweite Session unmittelbar einen Fehler erhält, der darauf hinweist, dass kein parent key gefunden wurde, aber das ist nicht der Fall: tatsächlich wartet die zweite Session darauf, dass in der ersten Session ein Commit oder ein Rollback erfolgt.
      • obwohl das isolation level READ COMMITTED dafür sorgt, dass eine Session nur die Daten sehen kann, die in einer anderen Session bereits per commit festgeschrieben wurden, kann der zugehörige interne Prozess durchaus die Arbeit anderer Sessions wahrnehmen. 
      • ein Blick in v$lock zeigt, dass die zweite Session ein TX Lock im Mode 6 hält (also ein exklusives Transaktionslock, was bedeutet, dass die Session undo und redo erzeugt); außerdem wird ein weiteres Transaktionslock in Modus 4 (share) angefordert, und diese Anforderung wird von der ersten Session blockiert.
      • nach einem rollback in Session 1 wird in Session 2 dann der erwartete Hinweis auf den fehlenden parent key geliefert (ORA-02291). Nach einem commit in Session 1 kann Session 2 problemlos weiterarbeiten.
      • im Fall einer multi-statement-Transaktion in Session 2 würde nur das insert in die child-Tabelle zurückgerollt werden, nicht aber alle Statements der Transaktion.
      • das Verhalten ist das gleiche im Fall von update und delete.
      • aus dem Verhalten können sich auch deadlocks ergeben.
      • wenn in einem deadlock-Graphen ein TX wait des Typs S (share, mode 4) erscheint, sind mit hoher Wahrscheinlichkeit Indizes im Spiel (verursacht durch referentielle Integritätsregeln oder auch PK-Werte-Kollisionen oder Werte-Kollisionen in einer IOT).
      Ob ich mir das jetzt besser merken kann, bleibt abzuwarten.

      Mittwoch, Mai 04, 2016

      Wann ist ein Full Table Scan billiger als ein Index Fast Full Scan?

      Und seit wann verwende ich in meinen Überschriften Fragezeichen? Fragen über Fragen. Aber eigentlich ist der Fall, den Jonathan Lewis in seinem aktuellen Blog-Artikel beschreibt, ebenso überschaubar wie erinnerungswürdig. Seine Fragen darin lauten: wieso werden die Kosten eines Full Table Scans auf einer Tabelle vom Optimizer als niedriger berechnet als die Kosten für den Index Fast Full Scan auf einem Index der Tabelle und warum ist der Index Fast Full Scan trotzdem effizienter in Hinblick auf die Laufzeit der Ausführung. Auf die Frage nach den Kosten gibt es eine wahrscheinliche und eine eher exotische Möglichkeit:
      • die eher exotische Variante wäre: Index und Tabelle wurden mit unterschiedlichen Blockgrößen angelegt. Da eigentlich niemand (außer vielleicht dem Herrn Burleson) ohne gute Gründe (z.B. den Einsatz von Transportablen Tablespaces) auf die Idee kommt, mehrere Blockgrößen in einer Datenbank zu verwenden, tritt der Fall wohl eher selten auf.
      • die wahrscheinlichere Erklärung ist: die Tabelle ist kleiner als der Index. Da ein Index Fast Full Scan (IFFS) tatsächlich ein Full Table Scan (FTS) des Index-Segments ist, basiert das Costing auf der Anzahl der Blocks im Segment. Wenn also die Tabelle kleiner ist als der Index, dann sind auch die Kosten des FTS niedriger als die des IFFS.
      Im dem Artikel zugrunde liegenden OTN-Fall war tatsächlich die (aus meiner Sicht) wahrscheinlichere Variante Nr. 2 im Spiel: die Tabelle war kleiner als der Index. Grundsätzlich kann das leicht passieren, wenn eine Tabelle sehr schmal ist, da der Index ja immer noch zusätzlich zu den indizierten Spalten die rowid des Datensatzes enthält, aber im gegebenen Fall deuteten die Autotrace-Statistiken (consistent gets) nicht darauf hin, dass der Index größer war als die Tabelle. Die Ursache dafür war Compression, die für die Tabelle eingerichtet war, während der Index nicht komprimiert wurde - wobei natürlich auch noch zu berücksichtigen ist, dass die Index Compression technisch anders funktioniert als die Table Compression und unter jeweils anderen Voraussetzungen effektiv ist. Die Verwendung der Table Compression ist dann auch der Grund dafür, dass das Costing des Optimizers in diesem Fall den weniger effizienten Plan favorisiert: die Tabelle ist zwar geringfügig kleiner als der Index, aber das Entpacken der komprimierten Daten erfordert zusätzlichen CPU-Einsatz, so dass die Ausführung mit dem IFFS ein wenig schneller abläuft als die mit dem FTS. Interessant ist im Artikel auch, wie der Herr Lewis das Vorliegen der Table Compression und das Fehlen der Index Compression aus den Zahlen des zugehörigen CBO-Traces ermittelt, wobei das keine höhere Mathematik ist.

      Samstag, April 30, 2016

      Materialized View Fast Refresh und Data Clustering

      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.

      Freitag, April 22, 2016

      Extended Statistics und CHAR Spalten

      Vor Problemen bei der Verwendung von Extended Statistics - also Statistiken für mehrere (in der Regel korrelierte) Spalten, die intern über eine virtuelle Spalte abgebildet werden - warnt Jonathan Lewis in seinem jüngsten Sratchpad-Artikel. Der Hintergrund des Problems ist folgender: für eine einzelne CHAR-Spalte (in diesem Fall CHAR(2)) mit Histogram ist der Optimizer klug genug zu erkennen, das Blanks, die am Ende eines zum Vergleich herangezogenen Literals angehängt werden, keinen Einfluß auf die Selektivität des Prädikats haben: col = 'X' und col = 'X ' liefern demnach die gleiche Kardinalität im Ausführungsplan. Wiederholt man diesen Versuch mit Ergänzung einer zweiten Spalte im WHERE, wobei über beide Spalten der Bedingung Extended Statistics erzeugt wurden, dann liefert nur die Variante mit col = 'X ' das richtige Ergebnis. Jonathan Lewis vermutet, dass der Optimizer in diesem Fall "vergisst", dass ein CHAR-Wert im Spiel ist, wenn die Funktion sys_op_combined_hash() aufgerufen wird, mit der die Werte der virtuellen Spalte der Column Group erzeugt werden, so dass der HASH-Vergleich kein Ergebnis liefert. Laut Aussage des Oracle Supports (in den Kommentaren) handelt es sich um einen Bug. Da Column Groups in 12c unter entsprechenden Voraussetzungen automatisch erzeugt werden, sollte man sich dieses Problems bewusst sein. Allerdings mag ich CHAR ohnehin nicht besonders, so dass der Typ in meinen Tabellen nicht auftaucht.

      Mittwoch, April 13, 2016

      Mustererkennung mit MATCH_RECOGNIZE in 12c

      Keith Laker hat im Data Warehouse Insider Blog eine Artikelserie begonnen, die sich mit der Verwendung des MATCH_RECOGNIZE Features in 12c beschäftigt. Ich will jetzt nicht behaupten, dass ich die Artikel umfassend wiedergeben würde (ja nicht einmal, dass ich sie komplett gelesen hätte), aber sie wären sicherlich ein sehr nützlicher Einstieg, sollte ich gelegentlich auf die Idee kommen, das Feature irgendwo einzusetzen. In erster Näherung greife ich hier aber nur die Zusammanfassungs-Abschnitte am jeweiligen Artikelende ab:
      Sollten noch weitere Artikel folgen, würde ich sie ergänzen. Selten habe ich eine armseligere Kommentierung von Links erstellt...

        Montag, April 11, 2016

        Neue Transformation "Group-by and Aggregation Elimination" in 12.1.0.2

        Im Blog der Optimizer-Entwickler hat Nigel Bayliss eine Optimizer-Transformation vorgestellt, die in 12.1.0.2 eingeführt wurde und den Namen "Group-by and Aggregation Elimination" trägt. Was die Transformation leistet, ist Folgendes: wenn eine Query mit Gruppierung und aggregierenden Funktionen auf einer Inline-View basiert, die ihrerseits wieder eine Gruppierung und aggregierende Funktionen enthält, dann kann der Optimizer die beiden Gruppierungsschritte zusammenfassen. Das klingt so simpel, wie es in den Beispielen des Artikels auch aussieht: in den Plänen erscheint dann nur noch eine (HASH) GROUP BY Operation (an Stelle von zwei Operationen des Typs). Obwohl die Transformation sehr harmlos aussieht, ist die zugehörige Logik allerdings sehr komplex, was zum Auftreten von Bugs führte (die Mike Dietrich in seinem Blog gelegentlich erwähnte), die nzwischen aber durch einen Patch behoben sein sollten.

        Sonntag, April 10, 2016

        STRING_SPLIT im SQL Server 2016

        Vor einiger Zeit habe ich in der Sektion database ideas bei OTN folgenden Wunsch geäußert: a string splitting function like SPLIT_PART in postgres. SPLIT_PART erhält als Argumente einen String und einen Delimiter, zerlegt den String an den Positionen der Delimiter-Zeichen in Substrings und liefert den n-ten Teilstring:

        SELECT SPLIT_PART('A;B;C;D', ';', 2);
        split_part
        -----------
        B

        Das ist sicherlich keine höhere Magie und kann in SQL auf verschiedenen Wegen erreicht werden (etwa durch den Einsatz regulärer Ausdrücke), aber ich war in der Vergangenheit oft genug in Situationen, in denen mir eine solche built-in-Funktion geholfen hätte, dass ich ihre Ergänzung in Oracle für wünschenswert halte.  Für den Vorschlag gab es 21 positive und 4 negative Stimmen (was bei OTN schon eine recht rege Beteiligung ist) und ich vermute mal, dass die Resonanz vielleicht noch positiver ausgefallen wäre, wenn ich im Titel auf "postgres" verzichtet hätte.

        Heute habe ich dann im Blog von Ozar Unlimited einen Artikel von Erik Darling gelesen, der auf die Funktion STRING_SPLIT hinweist, die im SQL Server 2016 zur Verfügung steht und die folgendermaßen beschreiben wird: "splits input character expression by specified separator and outputs result as a table." Dort gibt's das jetzt also auch schon.

        Mittwoch, April 06, 2016

        Massendatenlöschungen

        Ich werde nachlässig: auf der Suche nach meiner Zusammenfassung der ersten beiden Teile von Jonathan Lewis AllThingsOracle-Serie zu Massendatenlöschungen ist mir aufgefallen, dass ich dazu keine Zusammenfassungen geschrieben habe. Aber das lässt sich ja ändern: 

        Massive Deletes – Part 1: beschäftigt sich zunächst mit strategischen und taktischen Fragen bei der Löschung von Massendaten. Auf der strategischen Ebene liegen Fragen nach der Zielsetzung der Löschung: was verspricht man sich davon und lohnt sich der Aufwand überhaupt und schafft die Löschung nicht vielleicht neue Probleme. Auf taktischer Ebene liegen Fragen der Verfügbarkeit von Ressourcen, der Möglichkeit einer Downtime zu Wartungszwecken etc. Dazu liefert der Autor zwei Szenarien in denen Partitioninierung - bzw. ihr Fehlen - und globale Indizes eine Rolle spielen. Ausgehend davon werden zwei übliche Gründe für Löschungen und drei Standard-Muster aufgeführt. Die Gründe sind:
        • Verbesserung der Perfromance
        • Reduzierung der Storage-Verwendung
        Bei den Pattern werden folgende Fälle aufgeführt:
        • Löschung von Daten auf Basis einer Eingangszeit
        • Löschung von Daten auf Basis des Endzeitpunkts der Bearbeitung
        • Löschung der Daten einer bestimmten Kategorie
        In Zusammenhang mit diesen Mustern steht die Frage, wie Oracle die Daten auf Blockebene speichert. Dabei führt die "Löschung von Daten auf Basis einer Eingangszeit" dazu, dass komplette Blöcke in den ersten Extents einer Tabelle frei werden, die dann wieder von (erneut gut geclusterten) neuen Daten belegt werden. Bei den beiden anderen Mustern kann sich die physikalische Clusterung der Daten deutlich verändern, da die "Löschung der Daten einer bestimmten Kategorie" vermutlich aus allen Blöcken etwa den gleichen Anteil entfernt, während bei der "Löschung von Daten auf Basis des Endzeitpunkts der Bearbeitung" eine gewisse Korrelation zur zeitlichen Entwicklung vorliegt,die aber weniger stark ist als bei den Eingangszeitpunkten. Diese physikalische Clusterung der Daten kann sich dann wieder massiv auf die Effektivität von Indizes auswirken (Stichwort: clustering factor). Außerdem spielt die Menge der Indizes natürlich eine große Rolle bei der Löschung selbst, da diese Indizes während der Löschung verwaltet werden müssen.

        Massive Deletes – Part 2: beginnt mit der Beobachtung, dass die Frage, wo massive Datenlöschungen beginnen, angesichts sehr unterschiedlicher Hardware schwer zu definieren ist. Im Beispiel wird eine Tabelle von 1,6G erzeugt, die 10M rows für 10 Jahre enthält (1M pro Jahr). Auf dieser Basis werden die drei im ersten Teil skizzierten Szenarien durchgespielt. Die Effekte der Löschung werden mit einer Query überprüft, die über die Anzahl der rows pro Block gruppiert. Für die "Löschung von Daten auf Basis einer Eingangszeit" ergeben sich auch nach der Löschung sehr dicht gepackte Blöcke. Für die Variante "Löschung von Daten auf Basis des Endzeitpunkts der Bearbeitung" bleiben relativ viele Blöcke mit einem deutlich niedrigeren Füllgrad stehen, von denen allerdings die überwiegende Mehrheit nicht für neue Inserts verwendet werden würden. Auch für die Indizes ergibt sich ein ähnliches Bild - allerdings ist die zugehörige Analyse-Query deutlich komplexer: jedenfalls wird der Füllgrad der Indizes der Indizes reduziert. Abhängig von der Verteilung der Einträge kann die Maintenance bei der Löschung sehr teuer werden, so dass es unter Umständen sinnvoller sein könnte, den Index vor der delete Operation als unusable zu markieren (oder zu löschen) und anschließend neu aufzubauen.

        Massive Deletes – Part 3: erläutert die Bereiche, in denen eine Löschung - wie jede andere DML-Operation - einen Overhead hervorruft, und da der Herr Lewis das sehr präzise zusammenfasst, zitiere ich hier extensiv:
        • redo: "every change to a data block generates redo – a description of the new information that has been written to the block plus an overhead of a few tens of bytes: and if you’ve got four indexes on the table then you may be changing 5 blocks for every row you delete."
        • undo: "Every time you delete a row you “preserve” the row by writing into the undo segment a description of how to re-insert that row, and how to “undelete” the index entries. Each undo entry has an overhead of a few tens of bytes and each undo entry IS a change to a data block so (redo second thoughts) generates even more redo. Combining the two sources of redo, a single row delete with four affected indexes could produce around 1KB of redo, plus the size of the row and index entries deleted."
        • I/O effects: hier kürze ich ab: Blöcke müssen von der Platte und in den Cache gelesen werden - und umgekehrt wieder geschrieben.
        • Concurrency: auch hier versuche ich's mit Verkürzung: aufgrund Oracles MVCC-Verfahrens muss mit zusätzlichen redo und undo Effekten gerechnet werden.
        Weiterhin wird erklärt, dass die Optimizer-Kosten eines delete denen eines entsprechenden select rowid from ... entsprechen. Für das delete hat der Optimizer im gegebenen Fall die Auswahl zwischen drei Strategien: FULL TABLE SCAN, INDEX FAST FULL SCAN (der aber erst seit 12c ohne Hint-Verwendung berücksichtigt wird) und INDEX RANGE SCAN. In zwei Beispielen wird nun ausgeführt, wie die Ressourcen-Nutzung in Hinsicht auf redo und undo für verschiedene Zugriffs-Varianten aussieht (abhängig von der Anzahl der vorhandenen Indizes) - einmal für eine Löschung gut geclusterter Daten und einmal für Daten, die stark verteilt sind. Interessant ist dabei vor allem folgender Punkt:
        It’s not a well-known phenomenon, but Oracle uses a completely different strategy for deleting through an index range/full scan from the strategy it uses when delete through a tablescan or index fast full scan. For the tablescan/index fast full scan Oracle deletes a row from the table then updates each index in turn, before moving on to delete the next row.  For the index range scan / full scan Oracle deletes a table row but records the rowid and the key values for each of the indexes that will need to be updated – then carries on to the next row without updating any indexes. When the table delete is complete Oracle sorts all the data that it has accumulated by index, by key, and uses bulk updates to do delayed maintenance on the indexes. Since bulk updates result in one undo record and one redo change vector per block updated (rather than one per row updated) the number of redo entries can drop dramatically with a corresponding drop in the redo and undo size.
        Insofern kann die Löschung über die den Index hinsichtlich der Index-Maintenance also grundsätzlich effizienter sein als die Verwendung von FTS (bzw. dem eng verwandten IFFS). Allerdings erfordert der Index Range Scan (bzw. der verwandte Index Full Scan) zusätzliche Sortierungen. Für die unterschiedlichen Pattern ergibt sich dabei jeweils eine sehr unterschiedliche Effektivität der Verfahren: während für die gut geclusterten Daten der Index Range Scan sehr performant erfolgt, ist bei den in der Tabelle stark verteilten Daten der FTS die bessere Wahl. Dabei ist die Arbeit zur Änderung der Tabelle unter Umständen im einen Fall höher, während die Index-Maintenance in diesem Fall höher wird und umgekehrt. Weniger gut schneidet in vielen Fällen der Index Fast Full Scan, der in 12c unter Umständen sehr häufig ausgewählt werden kann, weil er für select rowid from ... Queries eine sehr günstige Wahl ist.

        Da Jonathan Lewis im dritten Artikel bereits weitere Beiträge angekündigt hat, werde ich hier vermutlich später noch Ergänzungen vornehmen.