Mittwoch, November 28, 2012

Statistiktransfer von PROD nach DEV

Maria Colgan liefert im Oracle Optimizer Blog ein sehr kompaktes Beispiel für den Transfer von Optimizer-Statistiken aus einem Produktiv-System in ein Entwicklungs-System. Die Schritte dabei sind:
  • Anlage einer Hilfstabelle in PROD mit dbms_stats.create_stat_table, in der die PROD-Statistiken gespeichert werden können
  • Übertragen der PROD-Statistiken in die Hilfstabelle mit dbms_stats.export_schema_stats
  • Anlage eines Directories in PROD (falls nicht schon eines vorhanden ist)
  • Export der Hilfstabelle via expdp
  • Transfer des Dumps nach DEV
  • Import der Hilfstabelle aus dem Dump in die DEV-DB
  • Kopieren der Statistiken der Hilfstabelle ins dictionary via dbms_stats.import_schema_stats
Dass das Übertragen möglich ist, war mir bekannt, aber dass es so einfach ist, hatte ich offenbar vergessen (oder nie gewusst).

Eine (geringfügig komplexere) Variante für den Transfer der für eine einzelne Query relevanten Statistiken hat übrigens gerade Yury Velikanov im Pythian Blog erläutert.

Freitag, November 23, 2012

Sonderfälle der Plan-Interpretation

Jonathan Lewis hat dieser Tage zwei Fälle geschildert, in denen die Standard-Regeln der Plan-Interpretation nicht gelten:
  • Plan timing: skalare Subqueries, die in einer SELECT-Liste verwendet werden, erscheinen im Plan oberhalb des Query Blocks, der sie aufruft. Eigentlich geht es im Artikel um das Verständnis der Zeitangaben in den erweiterten (rowsource execution) Plan-Statistiken, aber die klären sich, wenn die Verabeitungsreihenfolge deutlich wird. Der Artikel enthält auch noch einen Verweis auf das Phänomen des scalar subquery caching, das mir zuletzt häufiger begegnet ist, und das dafür sorgt, dass eine skalare Subquery nicht für jeden Satz, sondern nur für jeden distinkten Wert der Ergebnismenge aufgerufen wird.
  • Plan Order: der Index-Zugriff einer konstanten Subquery erscheint im Plan unterhalb eines HASH JOINs, aber die rowsource execution statistics zeigen deutlich, dass die Ausführung nach der ergebnislosen Ausführung der Subquery abbricht (Starts = 0 für alle folgenden Schritte). Mit den rowsource execution statistics und dem sqlmonitor lassen sich solche Effekte inzwischen relativ leicht bestimmen.

Status einer Materialized View

Ein kleiner Test zur Semantik der Status-Angaben für Materialized Views in den relevanten Dictionary-Tabellen. Dabei geht es zunächst nur um die einfachsten Fälle (kein Query Rewrite, kein Fast Refresh):

-- 11.1.0.7
-- Aufbau Datenbasis
drop table test_mpr;
drop materialized view test_mv_mpr;

create table test_mpr 
as 
select rownum id
     , mod(rownum, 10) col1 
  from dual 
connect by level <= 1000;

create materialized view test_mv_mpr 
as 
select col1
     , count(*) row_count
  from test_mpr
 group by col1;

-- Analyse-Queries 
select object_name
     , object_type
     , status
  from dba_objects
 where object_name = 'TEST_MV_MPR';
 
select mview_name
     , invalid
     , known_stale 
     , unusable
  from dba_mview_analysis 
 where mview_name = 'TEST_MV_MPR';
 
select mview_name
     , staleness
     , compile_state
  from dba_mviews
 where mview_name = 'TEST_MV_MPR'; 

Dazu liefern die befragten Dictionary-Tabellen zunächst folgende Angaben:

-- dba_objects
OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
TEST_MV_MPR     MATERIALIZED VIEW   VALID
TEST_MV_MPR     TABLE               VALID

-- dba_mview_analysis 
MVIEW_NAME      INVALID    KNOWN_STALE UNUSABLE
--------------- ---------- ----------- ----------
TEST_MV_MPR     N          N           N

-- dba_mviews
MVIEW_NAME      STALENESS           COMPILE_STATE
--------------- ------------------- -------------
TEST_MV_MPR     FRESH               VALID

So weit keine Überraschungen: die MV ist frisch aufgebaut und alle Status-Angaben sind folglich im grünen Bereich. Was passiert, wenn ich die Basistabelle lösche:

drop table test_mpr;

-- dba_objects
OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
TEST_MV_MPR     MATERIALIZED VIEW   INVALID
TEST_MV_MPR     TABLE               VALID

-- dba_mview_analysis 
MVIEW_NAME      INVALID    KNOWN_STALE UNUSABLE
--------------- ---------- ----------- ----------
TEST_MV_MPR     Y          N           N

-- dba_mviews
MVIEW_NAME      STALENESS           COMPILE_STATE
--------------- ------------------- -------------
TEST_MV_MPR     NEEDS_COMPILE       NEEDS_COMPILE

Auch diese Angaben erscheinen mir völlig nachvollziehbar: nach der Löschung der Basistabelle ist die MV tatsächlich in einer unglücklichen Situation, ein Refresh ist nicht mehr möglich, und der Status INVALID beschreibt das zutreffend. Nun ein weniger massiver Eingriff: ich füge in der Basis-Tabelle ein paar neue Datensätze ein, ändere an den Strukturen aber nichts:

insert into test_mpr
select rownum id
     , mod(rownum, 10) col1
  from dual
connect by level <= 1000;

commit;

-- dba_objects
OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
TEST_MV_MPR     MATERIALIZED VIEW   INVALID
TEST_MV_MPR     TABLE               VALID

-- dba_mview_analysis
MVIEW_NAME      INVALID    KNOWN_STALE UNUSABLE
--------------- ---------- ----------- ----------
TEST_MV_MPR     Y          N           N

-- dba_mviews
MVIEW_NAME      STALENESS           COMPILE_STATE
--------------- ------------------- -------------
TEST_MV_MPR     NEEDS_COMPILE       NEEDS_COMPILE

Die Status-Angaben sind in diesem Fall die gleichen wie im Fall der Löschung der Basis-Tabelle - und das finde ich nicht völlig plausibel, denn eigentlich würde ich erwarten, dass hier eine Unterscheidung möglich sein sollte. Die Einführung von weiteren Zustandsangaben wäre aus meiner Sicht kein Luxus gewesen.

Aus Gründen der Vollständigkeit hier noch die zugehörigenDefinitionen der Dokumentation:
  • DBA_OBJECTS
    • STATUS: Status of the object VALID, INVALID, N/A
  • DBA_MVIEW_ANALYSIS
    • INVALID: "Indicates whether this materialized view is in an invalid state (inconsistent metadata)"
    • KNOWN_STALE: "Indicates whether the data contained in the materialized view is known to be inconsistent with the master table data because that has been updated since the last successful refresh"
    • UNUSABLE: "Indicates whether this materialized view is UNUSABLE (inconsistent data) [...]. A materialized view can be UNUSABLE if a system failure occurs during a full refresh"
  • DBA_MVIEWS:
    • STALENESS: "Relationship between the contents of the materialized view and the contents of the materialized view's masters" Es folgen 5 Zustandsangaben, unter denen NEEDS_COMPILE allerdings nicht aufgeführt ist.
    • COMPILE_STATE: "Validity of the materialized view with respect to the objects upon which it depends". Dazu gibt's 3 Zustände. Zu NEEDS_COMPILE heisst es: " Some object upon which the materialized view depends has changed. An ALTER MATERIALIZED VIEW...COMPILE statement is required to validate this materialized view"

Mittwoch, November 21, 2012

Parallelisierung (Randolf Geist) - Teil 1

In der Reihe der OTN-Artikel zum Thema Database Performance & Availability wurde zuletzt eine zweiteilige Serie Understanding Parallel Execution von Randolf Geist veröffentlicht, die einen sehr guten Überblick zu den Voraussetzungen und Leistungen paralleler Operationen liefert (wobei die Aussagen für Exadata, aber auch für "normale" Datenbanken gelten).

Mein Exzerpt erhebt dabei mal wieder keinen Anspruch auf Vollständigkeit, sondern soll mir in erster Linie als Erinnerungshilfe dienen. Grundsätzlich würde ich ohnehin jedem, der sich mit Parallelisierung beschäftigt, die komplette Lektüre der beiden OTN-Artikel empfehlen. Außerdem ist das Thema mal wieder eines, bei dem ich am Übersetzen der technischen Begriffe scheitere, so dass kein wirklich konsistenter Text daraus wird:

Im ersten Artikel erklärt der Autor die Voraussetzungen für einen sinnvollen Einsatz paralleler Operationen:
  • wenn der serielle Plan nichts taugt (falsche Join Reihenfolge, ungeeignete Zugriffsverfahren), wird auch der parallele Plan keine Wunder bewirken
  • wenn PL/SQL-Funktionen eingesetzt werden, die nicht explizit als parallelisierbar definiert wurden, kann es vorkommen, dass im Plan ein Schritt PX COORDINATOR FORCED SERIAL erscheint, der bedeutet, dass der Plan letztlich seriell ausgeführt wird, obwohl PX-Operationen darin erscheinen (es gibt offenbar neben den Funktionen noch andere Gründe für dieses Verhalten). Da aber das Costing die Parallelisierung berücksichtigt, kann dieser Effekt zu massiven Fehlkalkulationen führen.
  • durch das verwendete Consumer/Producer-Modell kommt es vor, dass beide Gruppen paralleler Slave-Prozesse beschäftigt sind, wenn im Plan eigentlich eine parallele Weiterverarbeitung vorgesehen ist. In solchen Fällen treten blocking operations auf, die im Plan als BUFFERED oder BUFFER SORT ausgewiesen sind (wobei BUFFER SORT in seriellen Plänen eine andere Semantik hat). Dieses Abwarten ist inhaltlich nicht immer nachvollziehbar (der Autor zeigt das Problem am Beispiel eines HASH JOINs), aber anscheinend unvermeidlich: "It looks like that the generic implementation always generates a Parallel execution plan under the assumption for the final step that there is potentially another Parallel Slave Set active that needs to consume the data via re-distribution. This is a pity as it quite often implies unnecessary blocking operations as shown above."
  • die Verabeitungsreihenfolge für parallele Operationen entspricht nicht unbedingt der Reihenfolge, die für serielle Operationen gilt (und wo üblicherweise zuerst der im Plan am weitesten oben aufgeführte  Step ausgeführt wird, zu dem keine untergeordneten Steps existieren: also der erste Leaf-Step), da sich auch hier die Begrenzung auf zwei aktive parallel slave Gruppen auswirkt.
  • die BUFFER-Operationen aufgrund von blocking operations können zur Auslagerung auf die Platte führen, was natürlich der Performance schadet; auch ohne Auslagerung kann der Memory-Bedarf hoch sein.
  • Parallel Distribution Methods: Für den HASH JOIN (das übliche Join-Verfahren bei Parallelisierung) gibt es drei Verarbeitungs-Varianten, die in der Spalte "PQ Distrib" im Plan erscheinen:
    • Hash Distribution: die beiden Quelldatenmengen (row sources) werden über den Join-Key Hash-verteilt, was zwei aktive Slave-Gruppen erfordert, und der eigentliche Join wird wiederum von einer Slave-Gruppe durchgeführt, so dass sich (in der Regel) eine Buffered Operation ergibt
    • Broadcast Distribution: der Join (bzw. sein Probe Phase) wird zusammen mit einer der row source Operationen durchgeführt. Da keine Verteilung der Daten auf den Join-Key erfolgte, müssen die Ergebnisse der zweiten row source an alle Slaves, die den Join durchführen, weitergereicht werden (Broadcast). Dies führt zu einer Vervielfachung der intern verarbeiteten Datenmengen. Effizient ist das Verfahren, wenn die erste row source relativ klein ist.
    • Partition Distribution: wenn beide row sources in gleicher Weise partitioniert sind, ist ein partition-wise-Join möglich, der keine hash distribution der Daten erfordert, und deshalb von einer einzigen Slave-Gruppe ausgeführt werden kann und keine blocking operation hervorruft. Der partition-wise-Join ist damit das effizienteste der erwähnten Verfahren. Auch ohne Parallelisierung ist der partion-wise-Join sehr nützlich, da er die Größe der Join-Operationen reduziert.
  • MERGE JOIN und NESTED LOOPS sind ebenfalls parallelisierbar, kommen aber sehr viel seltener vor.
  • Für den partition-wise-Join sollte der DOP höchstens der Anzahl der Partitionen entsprechen.
  • mit Hilfe des Hints PQ_DISTRIBUTE lässt sich das Verhalten beeinflussen. Dabei lassen sich die Syntax-Details aus den OUTLINE-Informationen von DBMS_XPLAN entnehmen.
  • Der Abschnitt "Distribution of load operations" beschäftigt sich mit der Beeinflussung interner Sortierungen (z.B. zum Zweck einer möglichst effizienten Komprimierung)
  • "Plans Including Multiple Data Flow Operations (DFOs)" erläutert Fragen des geeigneten DOP und der Effekte einer Verknüpfung mehrerer Operationen mit unterschiedlichem DOP.

IOTs, CTAS und Sortierungen

Connor McDonald (auf dessen Blog Jonathan Lewis vor kurzem hingewiesen hatte - und dessen PL/SQL-Buch immer noch an meinem Arbeitsplatz steht) hat dieser Tage in seinem Blog ein paar interessante Effekte aus dem Kontext der IOTs angesprochen:
  • um LOGGING beim Aufbau einer IOT zu vermeiden, muss man CTAS verwenden. Bei Verwendung von INSERT /*+ APPEND */ wird auch für eine als NOLOGGING definierte Tabelle massiv redo erzeugt.
  • Der Execution Plan beim IOT-Aufbau über CTAS taugt nicht viel. Im gegebenen Beispiel zeigt der Plan einen INDEX FULL SCAN ohne Sortierungen, aber tatsächlich erfolgen für den zugehörigen Indes-Aufbau massive Sortier-Operationen.
Nachtrag 28.11.2012: Jonathan Lewis hat inzwischen auch noch einen Artikel zum Thema geschrieben und zeigt darin, wie das Logging durch spooling der Quelldaten in eine Datei und Einfügen ins Ziel per SQL-Loader vermieden werden kann.

Freitag, November 16, 2012

SQL Performance Explained von Markus Winand

Dass ich gerne mal ein Buch über Indizes von Richard Foote hätte, habe ich wahrscheinlich gelegentlich schon mal erwähnt, aber leider scheint damit auch weiterhin nicht zu rechnen zu sein - zumal die Herr Foote niemals versprochen hat, etwas Derartiges zu veröffentlichen. Stattdessen habe ich dieser Tage den im Sommer 2012 erschienenen Band SQL Performance Explained von Markus Winand gelesen, auf dessen interessante Seite Use The Index, Luke! ich hier auch schon verwiesen habe. Im ersten Moment ist es etwas ungewohnt, über Indizes zu lesen, ohne regelmäßigen Referenzen auf das Werk David Bowies zu begegnen, aber daran gewöhnt man sich ziemlich schnell ... 

Um es vorweg zu nehmen: das Buch ist aus meiner Sicht eine ausgesprochen empfehlenswerte Lektüre und liefert einen sehr zugänglichen Einstieg ins Thema SQL-Performance-Optimierung. Dabei wendet sich der relativ schmale Band (196 S.) in erster Linie an die Entwickler, die der Autor als die Gruppe betrachtet, die aufgrund ihrer Kenntnis der Applikationen (und - hoffentlich auch - der Daten) am besten dazu in der Lage ist, eine sinnvolle Indizierung durchzuführen, während DBAs und externen Beratern dieses Wissen in der Regel fehlt. Ich will an dieser Stelle nicht massiv widersprechen, denke aber, dass man viele SQL-Zugriffsprobleme auch ohne Kenntnis der Applikationslogik bestimmen kann (jedenfalls in Oracle und im SQL Server, da für diese RDBMS gilt, dass das data dictionary und die dynamischen Performance-Views sehr viele relevante Informationen liefern). Dass die Entwickler ein gutes Verständnis der Arbeitsweise von Indizes haben sollten, stimmt aber in jedem Fall. 

Das Thema des Buches sind B*Tree-Indizes und ihre Rolle in OLTP-Systemen. Diese starke Fokussierung auf eine zentrale - und beschränkte - Fragestellung und eine klare Strukturierung der Erklärungen sorgen dafür, dass die Darstellung sich nicht in Details verliert. Diese Struktur leidet auch nicht darunter, dass die Erläuterungen nicht auf ein einziges RDBMS beschränkt sind - neben Oracle und SQL Server werden MySQL und PostgreSQL untersucht -, im Gegenteil: durch den Vergleich der Systeme wird deutlich, wie viele Übereinstimmungen es in den grundsätzlichen Verfahrensweisen der Datenbanken im Bereich der Indizierung und der SQL-Optimierung gibt. Das Buch gliedert sich in acht Kapitel:
  • Anatomy of an Index: erläutert die Struktur von B*Tree-Indizes.
  • The Where Clause: erklärt die Rolle unterschiedlicher Operatoren (Equality, Range), Funktionen (und FBIs), NULL-Werten, Datentypen, Statistiken, Bindewerten und liefert dabei zahlreiche Antworten auf die klassische Frage, warum ein Index nicht verwendet wird. Einer der wichtigsten Punkte ist aus meiner Sicht die prägnante Erklärung von access und filter Prädikaten. Nützlich sind auch die Hinweise auf das unterschiedliche Verhalten unterschiedlicher RDBMS (z.B. Oracles fragwürdige Behandlung von Leerstrings als NULL).
  • Performance und Scalability: zeigt den Einfluss von Datenvolumen und Contention auf die Performance.
  • The Join Operation: behandelt die drei Join-Verfahren (Nested Loops, Hash Join, Merge Join) und ihre Nutzung von Indizes. Dabei wird auch das Thema der Code-Generierung von ORM-Tools angesprochen und vorgeführt, wie man deren traurige Leistungen in bestimmten Fällen korrigieren kann.
  • Clustering Data: erklärt den clustering factor und die Leistungsfähigkeit von index-only scans (covering indexes; "the second power of indexing"); außerdem wird die Struktur von IOTs (bzw. clustered indexes) erläutert. 
  • Sorting and Grouping: erklärt die Möglichkeiten zur Vermeidung von Sortierungen bei ORDER BY und GROUP BY Operationen durch die Nutzung geeigneter Indizes ("the third power of indexing", wobei die Verarbeitung "pipelined" erfolgt: der nächste Verarbeitungsschritt muss also nicht das Ende der Sortierung abwarten). Außerdem werden die Sortierreihenfolge (ASC, DESC) und die Position von NULL-Werten (FIRST, LAST) beim Sortieren thematisiert.
  • Partial Results: zeigt effiziente Verfahren zur Ausgabe paginierter Ergebnisse und geht (knapp) auf analytische Funktionen ein.
  • Modifiying Data: erklärt die Wirkung von Indizes auf DML-Operationen.
  • Appendix A: mit Hinweisen zur Darstellung und Interpretation von Ausführungsplänen in den behandelten RDBMS.
Ohne jeden Zweifel kennt der Autor seine Materie sehr genau - und ist dazu in der Lage, sie zu vermitteln.  Dabei bleibt die Darstellung nicht bei Behauptungen, sondern führt die angesprochenen Effekte immer wieder an praktischen Beispielen vor (sehr häufig sind das Ausführungspläne). In einigen Fällen dienen übersichtliche Grafiken zur Visualisierung von Zusammenhängen (Struktur von Branch- und Leaf-Knoten). Ein häufiges Phänomen bei meiner Lektüre war der Gedanke: da fehlt aber noch der Hinweis auf Effekt xyz (z.B. bind peeking, adaptive cursor sharing), der dann mit schöner Regelmäßigkeit wenige Seiten später erschien: aus didaktischer Sicht ist das wahrscheinlich günstig: zuerst wird das grundlegende Phänomen dargestellt, die Spezialfälle kommen dann mit einem gewissen Abstand. Ein anderer Punkt, der mir gut gefällt, ist der Hinweis auf einige klassische Mythen der Indizierung, z.B. auf die "unbalanced trees", die man durch regelmäßigen Rebuild bei Laune halten muss (aus Gründen der Deutlichkeit: es gibt keine "unbalanced trees" in b(alanced)*Tree-Indizes; und ein Index-Rebuild ist nur in sehr wenigen - und klar bestimmbaren - Fällen nützlich, auch wenn auf gewissen Seiten, die bei der Google-Suche häufig ganz oben erscheinen, etwas anderes behauptet wird oder wurde). Zu den Qualitäten des Buchs gehört auch die sprachliche Klarheit und pointierte Darstellung (wichtige Punkte werden als Merksätze grafisch hervorgehoben), wobei ich die englische Version gelesen habe, aber keinen Grund habe anzunehmen, dass Gleiches nicht auch für die deutsche Version gilt.

Gut gefällt mir wohl auch, dass die Einschätzungen des Autors in nahezu allen wichtigen Punkten mit den meinen übereinstimmen. Ein Punkt, den ich vielleicht anders akzentuieren würde, ist die Rolle von Bindewerten: natürlich sind sie in OLTP-Systemen zur Vermeidung von contention extrem wichtig, aber andererseits nehmen sie dem Optimizer relevante Informationen. Da ich mich aber auch eher mit ETL-Fragen im DWH-Kontext beschäftige, lässt sich dieser Aspekt vermutlich ziemlich schnell abhaken (ich glaube, das ist ein Punkt in dem auch die Propheten Kyte und Lewis leicht abweichende Positionen einnehmen). Ein paar kleinere Details habe ich in den Ausführungen vermisst (z.B. den INDEX SKIP SCAN, obwohl, so richtig vermisse ich den eigentlich nicht; den FIRST_ROWS_n-Modus für den CBO; den rowid-guess in IOTs und deren Overflow-Segment), aber das Erstaunliche ist viel mehr, was hier alles auf weniger als 200 Seiten angesprochen wird. Eine Frage, die mich noch interessieren würde, wäre, wo um alles in der Welt man Indizes mit einer tree depth von 6 findet? (mehr als 4 habe ich auch auf relativ großen Tabellen mit mehreren Milliarden Sätzen noch nicht gesehen, aber vielleicht ist das jenseits der Oracle-Welt anders)

Ich denke, dass SQL Performance Explained ein ungeheuer nützliches Buch für jeden ist, der beginnt, sich ernsthaft mit Fragen der SQL-Optimierung auseinander zu setzen - und das sollte aus meiner Sicht eigentlich jeder Entwickler, der SQL-Code schreibt. Im Bereich der SQL-Zugriffe lassen sich Laufzeiten häufig um Größenordnungen reduzieren, wenn man den richtigen Index benutzt (bzw. im DWH-Kontext eher: nicht benutzt, denn dort sind es mir schöner Regelmäßigkeit die Index-getriebenen NL-Joins, die zu Problemen führen) - um solche Verbesserungen in anderen Teilen des Codes zu erreichen, muss man sich schon sehr viel einfallen lassen. Selbst, wenn man sich schon länger mit Fragen der SQL-Optimierung beschäftigt, wird man hier noch allerlei nützliche Hinweise finden: für mich waren das vor allem die Erläuterungen zum Verhalten anderer RDBMS, mit denen ich seltener zu tun habe (SQL Server), bzw. fast nie (MySQL, PostgreSQL). Auch habe ich mir noch nie ernsthaft darüber Gedanken gemacht, dass Indizes auf einer SQL Server-Tabelle mit clustered index notwendigerweise die gleichen Probleme haben wie sekundäre Indizes auf IOTs. Ich kenne kein anderes Buch, dass die Grundlagen der SQL-Performance-Optimierung ähnlich gut erläutern würde (vielleicht am ehesten Christian Antogninis Troubleshooting Oracle Performance, das allerdings ein größeres Vorwissen voraussetzt und auch Aspekte anspricht, die eher in den DBA-Bereich fallen). Würde ich in diesem Blog Kaufempfehlungen aussprechen, dann wäre SQL Performance Explained ein Kandidat für eine solche.

Freitag, November 09, 2012

Hekaton

Microsoft hat dieser Tage eine neue in-memory Technologie vorgestellt, die in der nächsten Version des SQL Servers verfügbar sein soll, und den schönen Namen Hekaton trägt:
Hekaton is from the Greek word ἑκατόν for “hundred”. Our design goal for the Hekaton original proof of concept prototype was to achieve 100x speedup for certain TP operations.
Das wird sicher eine interessante technische Neuerung, der Trend zur Verlagerung von I/O-Operationen in den Speicher setzt sich fort - aber irgendwie ruft die Benamung bei mir die falschen Assoziationen auf:
Als Hekatombe [...] bezeichnete man im antiken Griechenland ursprünglich ein Opfer von 100 Rindern. Der Begriff entwickelte sich aber schon frühzeitig zur allgemeinen Bezeichnung für jedes reichere, aus Tieren bestehende Opfer, das sich in Bezug auf die Zahl und Art derselben nach den Vermögensumständen der Opfernden richtete sowie nach der Gottheit, der man es weihte, und nach dem Ort oder Fest, an dem es dargebracht wurde. [...] Im übertragenen Sinn spricht man auch bei einer erschütternd großen Zahl von Menschen, die einem Unglück zum Opfer gefallen sind, von einer Hekatombe.
Nun ja, vielleicht ist meine Sensibilität an dieser Stelle übertrieben, aber ich habe im Jahr 2004 für eine Firma gearbeitet, die damals ihr (internes) Projekt Tsunami umbenennen musste...

Notizen zur Performance von DELETE-Operationen

Gute Erklärungen für die folgenden Beobachtungen könnte ich mit hoher Wahrscheinlichkeit aus der einschlägigen Literatur bekommen, aber bekanntlich ist solide Recherche der Tod der Kreativität. Daher notiere ich einfach mal ein paar Punkte, die mir dieser Tage aufgefallen sind.

Seit Jahren warne ich die Entwickler in meiner Umgebung regelmäßig vor DELETE- und UPDATE-Operationen auf großen Datenmengen, da man daran in aller Regel keine Freude hat. Sofern es möglich ist, wandele ich solche Operationen gerne in INSERTs in neue Tabellen um, die ich dann anschließend gegen die Ursprungsobjekte austausche. Aber erst in den letzten Tagen ist mir klar geworden, wie viel Arbeit DELETE tatsächlich hervorruft. Dazu ein kleines Beispiel (11.1.0.7, ASSM, 8K Blockgröße, Noarchivelog auf einem nicht ganz neuen Windows-Desktop-PC):

drop table test_delete;
create table test_delete
as
select rownum id
  from dual
connect by level <= 1000000;

--> Abgelaufen: 00:00:00.89

exec dbms_stats.gather_table_stats(user, 'TEST_DELETE')

alter system flush buffer_cache;

-- Statistikerfassung über Snapshots aus v$sesstat 
-- vor und nach dem delete (erzeugt in einer anderen Session)
delete from test_delete;
--> Abgelaufen: 00:00:12.51

Während die Anlage der einspaltigen Tabelle mit 1M rows also weniger als 1 sec. benötigt, läuft das DELETE-Kommando über 12 sec. Ein Blick auf die Statistiken (bzw. die Deltas) in v$sesstat zeigt dabei ein paar interessante Werte, zu denen ich im Folgenden behutsame Interpretationen anschließe und dabei die Erläuterungen der Dokumentation berücksichtige:

-- Zeitangaben
NAME                                               DIFF
-------------------------------------------- ----------
DB time                                            1250
CPU used by this session                            761
redo log space wait time                            383
change write time                                   165
user I/O wait time                                   61

Demnach ist der eigentliche I/O-Anteil an der Laufzeit sehr bescheiden. Hoch sind hingegen die CPU-Nutzung und die Wartezeiten auf Platz im Log-Buffer (in meiner Test-Datenbank sind die Größe von log buffer und online redo logs nicht optimiert). Dass hier Wartezeiten entstehen, liegt nicht zuletzt an den Datenmengen, die in diesem Zusammenhang bewegt werden:

NAME                                               DIFF
-------------------------------------------- ----------
redo size                                     237244384 -- ca. 226 MB
undo change vector size                       103994836 -- ca.  99 MB
physical read bytes                            12804096 -- ca.  12 MB

Dabei entsprechen die 12 MB physical read bytes nahezu exakt der tatsächlichen Größe des Segments, das laut USER_TABLES 1557 Blocks umfasst (1557 * 8192 = 12754944). Das Löschen einer Tabelle von 12 MB führt demnach zur Erzeugung von über 200 MB redo und fast 100 MB undo! Dabei ist das Verhältnis von redo zu undo erst einmal keine besondere Überraschung, da auch zu jedem undo record redo-Informationen erzeugt werden müssen (in einem früheren Test-Lauf hatte ich auch noch IMU(also in-memory-undo)-Angaben, die anzeigen, dass das DELETE zunächst den in-memory-undo-Mechanismus verwendete, aber beim Erreichen eines gewissen Volumens davon wieder Abstand nahm - so jedenfalls deute ich den Wert IMU undo allocation size: 63996; habe aber gerade Jonathan Lewis' Oracle Core nicht zur Hand, wo das Verhalten im Detail geschrieben wird. Ein paar grundlegende Aussagen zu diesem Thema hat Jonathan Lewis auch gelegentlich in einem OTN-Thread gemacht. Im aktuellen Test-Lauf spielte IMU dann aber keine Rolle). Aber das Verhältnis der Netto-Datenmenge in der Tabelle zu undo und redo ist doch deutlich extremer als ich es erwartet hätte. Interessant sind auch die Statistiken zu den Block-Zugriffen, hier kombiniert mit allen Angaben, deren Wert im Umkreis von 1M liegt:

NAME                                               DIFF
-------------------------------------------- ----------
db block changes                                2019636
session logical reads                           1039698
db block gets                                   1037897
db block gets from cache                        1037897
redo entries                                    1006269
HSC Heap Segment Block Changes                  1000000
table scan rows gotten                          1000000
buffer is pinned count                           998477
free buffer requested                             16246
consistent gets                                    1801
consistent gets from cache                         1801
consistent gets from cache (fastpath)              1701
no work - consistent read gets                     1658
physical reads                                     1563
physical reads cache                               1563
switch current to new buffer                       1523
table scan blocks gotten                           1523
physical reads cache prefetch                      1495
buffer is not pinned count                          271
db block gets from cache (fastpath)                 200

Keine Überraschungen sind die table scan rows gotten. Ansonsten sehe ich diverse Werte, die im Bereich der Anzahl der Tabellenblocks (1557) liegen und andere, die eher der Satzanzahl entsprechen. Eine Ausnahme sind die db block changes, die die Dokumentation folgendermaßen erklärt: "the total number of changes that were part of an update or delete operation that were made to all blocks in the SGA. Such changes generate redo log entries and hence become permanent changes to the database if the transaction is committed." Blocks in der SGA sind natürlich auch die undo Blöcke, so dass die Verdopplung des Werts gegenüber 1M verständlich wird. Festzustellen ist in jedem Fall, dass die Anzahl der db block gets ("Number of times a CURRENT block was requested") in der Nähe der Satzanzahl liegt: offenbar ist hier keine BULK-artige Zusammenfassung mehrerer Änderungen eines Blocks möglich - und tatsächlich ändern sich redo size und undo change vector size nicht, wenn man die Löschung der Daten satzweise durchführt:

-- basierend auf dem ersten Test
create index ix_test_delete on test_delete(id);

-- Fall 1:
delete from test_delete;
--> Abgelaufen: 00:00:28.60
-- die Laufzeit ist also gegenüber dem ursprünglichen Test
-- ohne Index mehr als verdoppelt

-- Fall 2:
begin
for i in 1..1000000 loop
delete from TEST_DELETE where id = i;
end loop;
end;
/
--> Abgelaufen: 00:01:23.49

In beiden Fällen kommt man auf > 400MB redo und > 200MB undo, was angesichts des zusätzlichen Index in der Relation plausibel erscheint. Davon abgesehen ist das DELETE über alle Sätze natürlich schneller als die Löschung über Einzelkommandos: unter anderem steigt die Anzahl der consistent gets (1954 -> 3002599), da für jedes einzelne DELETE noch einmal ein für den Start der Transaktion konsistenter Zustand des Blocks, der geändert werden soll, erzeugt werden muss.

Viel weiter komme ich an dieser Stelle heute nicht mehr, aber zumindest die Beobachtung, dass die Performance von DELETE-Operationen weniger vom Datenvolumen als von der Anzahl geänderter Sätze abhängt, ist etwas, das ich mir merken sollte.

Mittwoch, November 07, 2012

Partitioned Outer Join

Beim Blick auf die nützliche Zusammenfassung der SQL-Optionen für Analyse und Reporting in der Dokumentation ist mir dieser Tage der (in 10g eingeführte) Partitioned Outer Join wieder ins Bewusstsein gekommen, dem ich wohl in der Vergangenheit schon mal begegnet war, damals aber noch nicht so genau wusste, wozu man ihn denn gebrauchen sollte. Da mir das inzwischen klarer ist, hier ein Beispiel:

Gegeben sind eine Fakten-Tabelle mit den jährlichen Umsätzen von Abteilungen (DEPTNO) und eine Datums-Dimension - beide im Beispiel so schmal wie möglich:

drop table d_test;
drop table f_test;

create table d_test (
    year number
);    

insert into d_test(year) values (2010);
insert into d_test(year) values (2011);
insert into d_test(year) values (2012);
insert into d_test(year) values (2013);

create table f_test (
    deptno number
  , year number
  , turnover number
);

insert into f_test (deptno, year, turnover) values (10, 2010, 500);
insert into f_test (deptno, year, turnover) values (10, 2011, 600);
insert into f_test (deptno, year, turnover) values (10, 2012, 500);
insert into f_test (deptno, year, turnover) values (20, 2011, 500);
insert into f_test (deptno, year, turnover) values (20, 2012, 700);

select * from d_test;

      YEAR
----------
      2010
      2011
      2012
      2013

select * from f_test;

    DEPTNO       YEAR   TURNOVER
---------- ---------- ----------
        10       2010        500
        10       2011        600
        10       2012        500
        20       2011        500
        20       2012        700

Um aus diesen Daten einen Bericht zum machen, in dem für jede Abteilung und jedes Jahr ein Datensatz enthalten ist - also Sätze mit einem Turnover = 0 zu ergänzen für alle Abteilungen, zu denen in der Faktentabelle für ein Jahr kein Datensatz vorliegt -, gibt's verschiedene Möglichkeiten. Eine Variante wäre, zunächst eine Referenz als Kreuzprodukt aller Jahre und Abteilungen zu bilden und diese dann per outer join mit den Fakten zu verbinden. Das funktioniert, ist aber relativ sperrig. Nicht möglich ist in diesem Fall der einfache Outer Join:

select r.year
     , t.year
     , t.deptno
     , t.turnover
  from f_test t
 right outer join
       d_test r
    on (t.year = r.year);

      YEAR       YEAR     DEPTNO   TURNOVER
---------- ---------- ---------- ----------
      2010       2010         10        500
      2011       2011         10        600
      2012       2012         10        500
      2011       2011         20        500
      2012       2012         20        700
      2013

Damit wird zwar das in den Fakten fehlende Jahr ergänzt, aber nur einmal und ohne Bezug zu den Abteilungen. Um das gewünschte Ergebnis zu erhalten, muss das Jahr an jede einzelne Abteilung gejoint werden: und genau das leistet der Partitioned Outer Join, bei dem die (zusätzliche Partitions-) Join-Bedingung in einer PARTITION BY-Klausel eingesetzt wird - womit sich PARTITION BY in die Reihe jener Oracle-Begriffe stellt, die je nach Kontext sehr viele unterschiedliche Dinge bezeichnen können ...

select t.deptno
     , t.year
     , r.year
     , t.turnover
  from f_test t partition by (deptno)
 right outer join
       d_test r
    on (t.year = r.year);

    DEPTNO       YEAR       YEAR   TURNOVER
---------- ---------- ---------- ----------
        10       2010       2010        500
        10       2011       2011        600
        10       2012       2012        500
        10                  2013
        20                  2010
        20       2011       2011        500
        20       2012       2012        700
        20                  2013

-- mit etwas Glättung:
select t.deptno
     , r.year
     , coalesce(t.turnover, 0) turnover
  from f_test t partition by (deptno)
 right outer join
       d_test r
    on (t.year = r.year)
 order by t.deptno
        , r.year

    DEPTNO       YEAR   TURNOVER
---------- ---------- ----------
        10       2010        500
        10       2011        600
        10       2012        500
        10       2013          0
        20       2010          0
        20       2011        500
        20       2012        700
        20       2013          0

Hier bekomme ich also die gewünschten acht Ergebnissätze (2 Abteilungen * 4 Jahre). Im Plan sieht man für diesen Fall einen klaren Hinweis auf die durchgeführte Operation:

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    65 |     9  (34)| 00:00:01 |
|   1 |  VIEW                       |        |     1 |    65 |     8  (25)| 00:00:01 |
|   2 |   MERGE JOIN PARTITION OUTER|        |     1 |    52 |     8  (25)| 00:00:01 |
|   3 |    SORT JOIN                |        |     4 |    52 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL       | D_TEST |     4 |    52 |     3   (0)| 00:00:01 |
|*  5 |    SORT PARTITION JOIN      |        |     5 |   195 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL       | F_TEST |     5 |   195 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T"."YEAR"="R"."YEAR")
       filter("T"."YEAR"="R"."YEAR")

-- zum Vergleich der Plan für eine Query mit einer 
-- per cartesian join erzeugten Referenz (bei der
-- ich auch noch eine zweite Dimension ergänze und
-- feststelle, dass meine Namensgebung im Test mal
-- wieder nichts taugt ...
create table d_dept (
    deptno number
);

insert into d_dept (deptno) values (10);
insert into d_dept (deptno) values (20);

with
reference as (
select d1.deptno
     , d2.year
  from d_dept d1
     , d_test d2
)
select r.deptno
     , r.year
     , coalesce(t.turnover, 0) turnover
  from f_test t
 right outer join
       reference r
    on (t.year = r.year and t.deptno = r.deptno)
 order by t.deptno
        , r.year;

----------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     8 |   520 |    12  (17)| 00:00:01 |
|   1 |  SORT ORDER BY          |        |     8 |   520 |    12  (17)| 00:00:01 |
|*  2 |   HASH JOIN OUTER       |        |     8 |   520 |    11  (10)| 00:00:01 |
|   3 |    VIEW                 |        |     8 |   208 |     7   (0)| 00:00:01 |
|   4 |     MERGE JOIN CARTESIAN|        |     8 |   208 |     7   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL  | D_DEPT |     2 |    26 |     3   (0)| 00:00:01 |
|   6 |      BUFFER SORT        |        |     4 |    52 |     4   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL | D_TEST |     4 |    52 |     2   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL    | F_TEST |     5 |   195 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."DEPTNO"(+)="R"."DEPTNO" AND "T"."YEAR"(+)="R"."YEAR")

Offensichtlich ist, dass für den Partitioned Outer Join spezielle Schritte erscheinen (MERGE JOIN PARTITION OUTER, SORT PARTITION JOIN) und dass die Cardinality nur im Fall der Query mit Referenz korrekt (= 8) bestimmt wird. Über Hints konnte ich für den Partitioned Outer Join den MERGE JOIN auch in einen NESTED LOOPS umwandeln, allerdings habe ich es auf Anhieb nicht geschafft, einen HASH JOIN daraus zu machen. Auf der Suche nach einer Erklärung dafür, bin ich bei einem recht interessanten Thread (von 2005) in oracle-l gelandet, in dem Christian Antognini die Frage stellt, ob der Partitioned Outer Join die Verwendung der Join-Bedingung als access-Prädikat ausschließt und Lex de Haan die ANSI-Definition der Funktionalität zitiert. Die dort aufgeworfenen Fragen zeigen in jedem Fall, dass das Performance-Verhalten des Partitioned Outer Join ein Thema ist, das sich nicht ohne umfassendere Untersuchung erläutern lässt.

Außerdem habe ich dann auf der Suche nach Aussagen zur Arbeitsweise des Partitioned Outer Join auch noch einen Artikel von Adrian Billington entdeckt, der so ziemlich alles enthält, was ich hier aufgeschrieben habe, und noch ein paar zusätzliche Punkte - anders hätte ich's mir beim Herrn Billington auch nicht vorstellen können.

Dienstag, November 06, 2012

IFFS und Filterprädikate

Diesmal habe ich den Titel nicht - wie üblich - aus mangelnder Sorgfalt, sondern mit Bedacht vage gewählt, um dem Eintrag nicht seine ohnehin schon bescheidene Pointe zu nehmen... Davon abgesehen ist mir der beschriebene Effekt bestimmt schon ziemlich häufig begegnet, ohne dass ich darüber nachgedacht hätte. Aber genug der Vorrede: heute habe ich in einem Execution Plan ungefähr Folgendes gesehen:

-- 11.2.0.1

explain plan for
select *
  from test
 where c = 1;

select * from table(dbms_xplan.display);

Plan hash value: 850129961

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  1000 | 38000 |    22   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| PK_TEST |  1000 | 38000 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C"=1)

Daraufhin habe ich einen Blick auf die Index-Spalten geworfen:

select column_name
     , column_position
  from user_ind_columns
 where index_name = 'PK_TEST';

COLUMN_NAME                    COLUMN_POSITION
------------------------------ ---------------
A                                            1

Anschließend habe ich mich einen Moment lang gewundert: ein IFFS mit einem Filterkriterium, das gar nicht im Index enthalten ist? Ein Blick auf die Objekt-Definition hat die Überraschung dann beendet - und auch ich spare mir hier jetzt die dramatische Steigerung:

create table test (
    a
  , b
  , c
  , constraint pk_test primary key(a)
) organization index
as
select rownum a
     , 'xxxxxxxxxx' b
     , mod(rownum, 10) c
  from dual
connect by level <= 10000

Es handelt sich also um eine IOT und deren Auftreten wird im Execution Plan offenbar immer als IFFS des PK-Index dargestellt, was durchaus einleuchtet, da das Index-Segment im Fall meiner Test-Tabelle ohne Overflow-Segment ja das einzige physikalische Objekt ist, auf das man sich beziehen kann. Und somit ist dann auch die Filteroperation einleuchtend, da das Index-Segment natürlich alle Tabellen-Attributen enthält - nur die Spaltenangabe der user_ind_columns passt nicht ganz zu den anderen Informationen. Da aber auch nur die PK-Spalte A im Beispiel als access-Prädikat verwendet werden kann, bin ich auch mit dieser Klassifizierung zufrieden.

Freitag, November 02, 2012

Oracle System Architektur (James Morle)

James Morle hat eine Serie zum Thema System Architecture gestartet, die eine ganze Reihe von Aspekten beleuchten soll, die beim Design einer Oracle Datenbank relevant sind.Wie der Autor bin auch ich gespannt, ob er dieses Projekt abschließen wird: "Now I’m looking at the list it seems more like a multi-volume book than a handful of blog posts." Eine andere Frage ist, ob ich die Serie hier komplett verlinken (und kommentieren?) werde, aber ich will mich bemühen:
  • System Architecture Series: Introduction to the Series and Licensing: beschäftigt sich mit der Frage der erforderlichen Oracle-Lizenzen. Dabei weist der Autor auf die Rolle der CPU cores (und der gewichtenden core factors) hin. Außerdem erklärt er, dass von aktuelleren Lizenzbedingungen in aller Regel nur Oracle profitiert und man deshalb nach Möglichkeit bei den alten Lizenzen bleiben sollte. Auch zeigt sein Beispiel, wie schnell Oracle-Lizenzen furchtbar teuer werden können (im Beispiel: 16.000$ Hardware zu 1.224.000$ für die Lizenzen)

Bestimmung von Intervallüberschneidungen

Im OTN-Forum SQL und PL/SQL hat Frank Kulash eine sehr schöne Lösung für das Problem der Bestimmung der Schnittmenge mehrerer Zeitintervalle geliefert, die eine Kombination den analytischen Funktionen LEAD, COUNT und SUM verwendet. Entscheidend ist die Betrachtung der Intervallanfänge als Events, die gezählt und bewertet werden.

Ich hatte ich Thread eine sehr viel sperrigere Variante angeboten, die die Intervalle in ihre Einzelelemente splittete (in diesem Fall Sekunden) - für längere Intervalle könnte man damit ziemlich effektiv Last auf dem Server erzeugen ... (was mir schon klar war, als ich das SQL schrieb: ich wollte erst einmal sehen, ob mir überhaupt eine Lösung für das, wie ich fand, interessante Problem einfallen würde).

Data Guard

Data Guard ist auch eine Technik, mit der ich mich noch nie ernsthaft beschäftigt habe - aber in diesem Fall könnte ich das (anders als im Exadata-Fall) auch auf einem simplen Desktop-System. Wie man einen solchen Test durchführen kann, erklärt Howard Rogers in seinem Blog - und mir fällt niemand ein, der bessere Anleitungen schreibt als der Herr Rogers. Die Beantwortung der Frage, was Data Guard eigentlich ist, überlasse ich ihm dann auch gleich:
Data Guard is Oracle’s premium “high availability” database architecture. “Premium” in this context means “Enterprise Edition Only”, so it costs big dollars. Happily, you are allowed to set it up and test it out for free. “High Availability” means that, unlike RAC, it uses two (or more) databases to store identical copies of your data in different physical locations. Should the proverbial meteorite strike, one of them should still be in existence and thus access to your data remains assured. 
Data Guard is, therefore, a highly-interesting technology that is pricey but invaluable. Getting a taste of it on a desktop is therefore something budding DBAs should definitely be interested in. This article will accordingly show you how to go about setting up an 11g Release 2 physical Data Guard configuration and what’s involved in managing it to cope with disasters and other management needs.
Nachtrag 19.02.2015: wieder ein Link auf eine Webseite von Howard Rogers, der keine Dauer beschieden war...

Donnerstag, November 01, 2012

Exadata Storage Indizes

Da ich noch nicht mit Exadata gearbeitet habe - und nicht damit experimentieren kann - fällt es mir schwer, mich ohne Praxisbezug mit dem Thema zu beschäftigen. Trotzdem hier ein paar Links auf Richard Footes Erklärung der Exadata Storage Indizes:
Und noch eine Serie mit dem Vergleich von herkömmlichen und Storage-Indizes, deren Grundgedanke ist, dass Storage-Indizes nicht alles leisten können, was die traditionellen Indizes leisten:

Use cases für die model clause

Tony Hasler spricht in seinem Blog zwei Fälle an, in denen die model clause etwas leistet, das nicht genauso gut mit PL/SQL umzusetzen wäre:
  • Implementierung von Analytics, die nicht direkt durch SQL-Funktionen zur Verfügung gestellt werden
  • Parallelisierte Ausführung
Die Beispiele für Fall 1 sind die Definition eines MEDIAN über einen sich bewegenden Zeitraum (die als model wirklich sehr kompakt wird; in solchen Fällen habe ich in der Vergangenheit üblicherweise einen self join verwendet, bei dem ich die aktuelle Zeile mit dem entsprechenden range verknüpfte) und die Bestimmung eines zscores (= Abstand eines Werts vom Mittelwert in Anzahl Standardabweichungen).