MV-Refresh mit out_of_place Parameter in 12c

Und wieder verweise ich auf Jonathan Lewis, der diesmal eine interessante neue (12c) Refresh-Option für Materialized Views vorstellt, die durch den Parameter out_of_place aufgerufen wird. Die Idee dabei ist sehr einfach: im Rahmen des Refreshs wird eine zusätzliche Hilfstabelle erzeugt und gefüllt, die dann am Ende der Operation gegen das bisher zur MV gehörende Segment ausgetauscht wird (über eine interne Folge von Rename-Operationen). Das Verfahren entspricht grundsätzlich einem Workaround, den der Herr Lewis in einem älteren Artikel vorgestellt hatte, in dem er die MV als Partitionierte Tabelle mit einer einzigen Partition anlegte und den Refresh als Partition Exchange Operation ausführte. Mir gefällt dieser Workaround tatsächlich immer noch besser als die neue out-of-place Refresh-Variante, was vor allem an folgenden Einschränkungen der Option liegt:

• die Insert-Operation wird als konventionelle durchgeführt, also ohne Append-Hint.
• es ist nicht möglich die Option einzusetzen, um eine MV zu aktualisieren, die als Subset einer einzelnen Basistabelle definiert ist (also Spalten oder Zeilen ausschließt).

Für beide Beschränkungen fehlt mir zunächst eine einleuchtende Erklärung - aber vielleicht gibt es auch keine und die Einschränkungen werden in folgenden Releases aufgehoben.

Frequenz des ASH/AWR Samplings

Noch einmal Jonathan Lewis, diesmal mit einer Erläuterung des ASH- und AWR-Samplings von Session-Informationen:

• einmal in jeder Sekunde werden die Informationen zu aktiven Sessions (state = 'ACTIVE') aus v$session nach v$active_session_history kopiert.
• diese Snapshots werden als Sample bezeichnet.
• ein Zehntel dieser Datensätze wird in der entsprechenden AWR-Tabelle dba_hist_active_sess_history persistiert.
• Es handelt sich aber nicht um jeden zehnten Datensatz, sondern um die Datensätze der zehnten Sekunde. Im Ergebnis sieht die enthält die AWR-Tabelle also einen konsistenten Zustand von v$session auf Basis eines Samplings im Abstand von zehn Sekunden. Daher können hier für einzelne Sessions auch größere Lücken als zehn Sekunden auftreten (wenn die Session zum entsprechenden Zeitpunkt inaktiv war).
• der Abstand von zehn Sekunden ist zeitlich nicht immer völlig akkurat und verschiebt sich allmählich.
• in der ASH-View werden die in die AWR-Tabelle kopierten Sätze mit dem Flag is_awr_sample gekennzeichnet.
• die Kontrolle der Sampling-Frequenz und -Zeitabstände erfolgt über die Underscore-Parameter _ash_sampling_interval (Defalt: 1000 ms) und _ash_disk_filter_ratio (Default: 10). Eine Anpassung dürfte aber nur in Ausnahmefällen sinnvoll sein.

ANSI Join Syntax und das 1000-Spalten-Limit

Mein Titel klingt mal wieder wie der Name einer eher lieblos fortgeschriebenen Krimi-Serie, aber wahrscheinlich gelingt es mir eher, den Eintrag über eine solche Überschrift wiederzufinden, als anhand des Titels ANSI expansion, den Jonathan Lewis seinem Artikel gegeben hat, den ich hier abkürzend nacherzähle. Worum es geht ist Folgendes: im OTN-Forum wurde ein gut beschriebener Testfall vorgelegt, in dem ein ANSI-Join (mit recht breiten Views) in 12c (aber nicht in 11g) einen Fehler "ORA-01792: maximum number of columns in a table or view is 1000" hervorruft - was insofern verwundert, als in der Select-Liste der Query nur auf die Spalten einer deutlich schmaleren Tabelle zugegriffen wird. Ersetzt man das "Select *" durch eine explizite Liste der zugehörigen Spalten, so tritt das Problem nicht auf, und auch die Umformung des ANSI-Joins in einen traditionellen Join (*) führt zu einer problemlosen Verarbeitung. Im Artikel erklärt der Herr Lewis die Hintergründe des Verhaltens, wobei er die neue Routine dbms_utility.expand_sql_text verwendet, um die in der Query verwendeten Views auf die zugrunde liegenden Objekte zurückzuführen. Dabei wird deutlich, dass das 1000-Spalten-Limit im Select-*-Fall beim Aufbau zwischengeschalteter Sub-Queries überschritten wird, die sämtliche Spalten der verwendeten Views enthalten, obwohl im Test auf oberster Ebene nur zwei Spalten relevant sind. Bei einer expliziten Adressierung der relevanten Spalten tritt der Effekt nicht auf. Dieses Problem hat dabei anscheinend keine Auswirkungen auf die internen Transformationen der Query: im CBO-Trace erscheint für alle Fälle ein simpler Join in klassischem Stil. Angesichts der Häufigkeit, mit der ANSI-Probleme im OTN-Forum erscheinen, komme ich allmählich zum Schluss, die traditionelle Join-Syntax wieder zu präferieren - obwohl die neue ANSI-Syntax manche Query deutlich lesbarer macht.

(*) Ich gelobe, mir in Zukunft die Hinweise darauf zu sparen, dass letztlich beide Join-Syntax-Varianten in (unterschiedlichen) ANSI-Standards enthalten sind.

Function Based Index mit Trunc-Funktion für Date-Spalten

Franck Pachot erwähnt eine interessante Verbesserung in der Behandlung von Indizes in jüngeren Oracle-Versionen: seit 11.2.0.2 können funktionsbasierte Indizes, die die Trunc-Funktion für ein Datum beinhalten, auch verwendet werden, um eine Query zu unterstützen, in der auf das Datum ohne die Funktion eingeschränkt wird (also z.B. über einen Datums-Range). Das ist in der Beschreibung einmal mehr unhandlich, sollte aber durch ein kleines Beispiel leicht zu verdeutlichen sein:

drop table t;

create table t
as
select sysdate + interval '3' hour ts
  from dual
connect by level <= 1000;

create index t_idx_ts_trunc on t(trunc(ts));

explain plan for
select *
  from t
 where ts < sysdate - 10;

------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |     8 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T              |     1 |     8 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T_IDX_TS_TRUNC |     9 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   1 - filter("TS"<SYSDATE@!-10)
   2 - access(TRUNC(INTERNAL_FUNCTION("TS"))<=TRUNC(SYSDATE@!-10))

In älteren Oracle-Releases wäre der Zugriff in dieser Query nicht über den FBI möglich gewesen, da die Einschränkung für die Spalte TS nicht der Index-Definition mit der Trunc-Funktion entspricht. Da der Index die Uhrzeitangaben des Zeitstempels nicht enthält, ist hier allerdings noch ein table access notwendig, den man sich sparen könnte, wenn man den Index noch um die TS-Spalte erweitert, also: 

create index t_idx_ts on t(trunc(ts), ts);

Im gegebenen Beispiel ist das natürlich recht zweckfrei (weil der Index dadurch größer wird als die Tabelle), aber Franck Pachots Artikel liefert da Plausibleres - und auch umfassendere Erklärungen zum Verhalten.

Nachtrag 31.03.2015: in einem Folgeartikel hat Franck Pachot darauf hingewiesen, dass ähnliche Optimierungen auch für FBI-s mit der substr-Funktion existieren - allerdings werden LIKE-Prädikate in diesem Zusammenhang nicht unterstützt.

Hybrid Hash Distribution für Parallele Operationen in 12c

Randolf Geist hat in jüngerer Vergangenheit einige Artikel zu den in 12c eingeführten Features mit Bezug zur Parallel Execution geschrieben. Darunter findet man:

• 12c Parallel Execution New Features: Hybrid Hash Distribution - Part 1: die Adaptive Broadcast Distribution erlaubt für kleine "Left row sources" die Auswahl zwischen Broadcast- und Hash- (bzw. Round-Robin- und Hash-) Distribution zum Zeitpunkt der Ausführung abhängig von den Datenmengen durchzuführen - es handelt sich also um einen Korrektur-Mechanismus, der fehlerhafte Mengenabschätzungen behandelt. Anders als etwa die in 12c eingeführten adaptive joins, wird das Verfahren bei jeder folgenden Ausführung den Datenvolumina angepasst (also nicht bei der zweiten Ausführung festgeschrieben). Zum allgemeinen Verhalten und zu seinen Problemen gibt es ausführliche Beispiele. Interessant sind auch die Kommentare zum Artikel, in denen sich Yasin Baskan, der Produkt Manager für Parallel Execution bei Oracle, zum Thema äußert.
• 12c Parallel Execution New Features: Hybrid Hash Distribution - Part 2: die Hybrid Distribution wurde eingeführt, um Join-Bedingungen mit starker Ungleichverteilung zu optimieren (was der Autor in älteren Artikeln ausführlich erläutert hat). Allerdings erfordert das Verfahren eine wiederholte Redistribution, selbst dann, wenn ein folgender Join mit den gleichen Join-Bedingungen operiert. Das bedeutet einerseits einen zusätzlichen Overhead und führt darüber hinaus unter bestimmten Umständen dazu, dass Operationen als BUFFERED durchgeführt werden (und somit blockieren), die ohne die Optimierung nicht blockiert hätten. Auch dazu gibt es natürlich wieder aussagekräftige Beispiele.

Einmal mehr ist der Sinn dieser Auflistung in erster Linie die Verlinkung der Artikel - die Details muss ich ohnehin immer wieder nachlesen, wenn ich entsprechenden Phänomenen begegne.

Komprimierung in Oracle Versionen und Varianten

Noch mal Franck Pachot - diesmal mit einer schönen Darstellung der Verfügbarkeit unterschiedlicher Komprimierungs-Features in den Oracle-Releases und Editionen:

• NOCOMPRESS: die einzige Option in der Standard-Edition.
• BASIC COMPRESSION for bulk load: gibt es in der Enterprise Edition seit 9i und erfordert nicht die Lizenzierung zusätzlicher Optionen. In 12c lautet die offizielle Syntax zur Verwendung: ROW STORE COMPRESS BASIC; das schlichte Schlüsselwort COMPRESS funktioniert aber auch noch.
• ADVANCED COMPRESSION for OLTP: seit 11g verfügbar; seit 12c ist das "FOR OLTP" nicht mehr Namensbestandteil. Die Syntax zur Anlage in 12c lautet: ROW STORE COMPRESS ADVANCED. Die Nutzung des Features erfordert die zusätzliche Option Advanced Compression.
• EXADATA COMPRESSION: für Exadata wurde die Hybrid Columnar Compression eingeführt, die für diverse (elaboriertere) Storage Varianten verfügbar ist: "Exadata database machine, Oracle SuperCluster, ZFS storage appliances, Pillar axiom array. But not in ODA." Dazu gibt es folgende Algorithmen:
• LZO algorithm: COMPRESS FOR QUERY LOW.
• ZLIB algorithm: COMPRESS FOR QUERY HIGH.
• BZIP2 algorithm: COLUMN STORE COMPRESS FOR ARCHIVE HIGH.
• Information Lifecycle Management: in 12c eingeführt wurde die ADO (Automatic Data Optimization), die dazu dient, historische Daten automatisch zu komprimieren, wenn sie nicht mehr Ziel größerer DML-Operationen sind. Zur Verwendung muss wiederum die Advanced Compression Option lizenziert werden.

Der Artikel enthält auch noch ein paar Details zur Implementierung, die ich an dieser Stelle unterschlage, weil ich dazu gelegentlich schon allerlei geschrieben und verlinkt habe.

Optimierung mit mehreren Unbekannten

Franck Pachot hat im dbi Blog eine kleine Serie von Artikeln begonnen, in denen er eines der klassischen Anti-Pattern der SQL Entwicklung untersucht - generische Queries mit mehreren AND-verknüpften Bedingungen der Form:

column_value = NVL(:bindewert, column_value)

Diese Konstrukte dienen dazu, dem Anwender die Möglichkeit zu geben, eine Ergebnismenge durch eine Eingabe einzuschränken, wobei der Verzicht auf eine Eingabe keine Einschränkung nach sich ziehen soll. Von der Anwendungslogik her ist das durchaus plausibel, aber der Optimizer kann solche Queries nicht sinnvoll optimieren, da er zum Zeitpunkt der Planerstellung nicht wissen kann, welche Prädikate im Rahmen der Ausführung tatsächlich eingesetzt werden. Zur Serie gehören bislang folgende Artikel:

• Generic query for multicriteria search - part I: USE_CONCAT (OR Expansion): zeigt das grundsätzlich Problem anhand einer Beispielquery, in der vier derartige indizierte Attribute erscheinen. Der Optimizer erzeugt dazu einen - mehr oder minder plausiblen - Plan mit OR-Expansion. Für einige Parameter-Kombinationen ist dieser Plan geeignet, für andere eher nicht.
  
• Generic query for multicriteria search - part II: BIND_AWARE (Adaptive Cursor Sharing): untersucht das Verhalten der im ersten Artikel vorgestellten Query mit einem zusätzlichen BIND_AWARE Hint. Dabei werden (erwartungsgemäß) mehrere Cursor erzeugt - allerdings sind die Pläne alle weniger geeignet als es entsprechende Varianten für dynamisches SQL mit den tatsächlich vorgenommenen Einschränkungen wären. Auf Twitter hat Lothar Flatz in diesem Zusammenhang auf seine OTN-Idee eines Reoptimierungs-Hints hingewiesen, die ich für durchaus plausibel halte.

Der Autor hat weitere Artikel angedeutet, die ich wahrscheinlich hier ergänzen werde.

Datensätze generieren

Das Thema ist nicht neu, aber bei der Gestaltung komplexerer SQL-Logik immer wieder von Interesse: wie generiere ich effizient eine größere Menge von Datensätzen mit einer eindeutigen id? Dazu hat Natalka Roshak eine kleine Serie gestartet, die bislang zwei Artikel umfasst, und in der zunächst vier verschiedene Verfahren vorgestellt werden:

-- rekursive Variante mit connect by level
select level id 
  from dual 
connect by level <= 10;

-- rekursive Variante mit subquery factoring
with 
generator (id) as ( 
select 1 as id from dual
 union all
select id + 1 
  from generator
 where id < 10
)
select id 
  from generator;

-- XML table
select column_value as id 
  from xmltable ('1 to 10'); 

-- group by cube
select rownum id
  from (select 1 
          from dual 
   group by cube( 1, 1, 1, 1) 
        ) 
where rownum <= 10;

Mir persönlich war die group by cube Variante unbekannt, aber sie ist auch nicht unbedingt etwas, das man sich merken müsste, da ihre Performance erbärmlich ist. Nicht viel besser schneidet die XML table Lösung ab, so dass man letztlich doch bei den klassischen Varianten bleiben kann - meine erste Wahl ist dabei das kompakte connect by level Verfahren.

Alternativ könnte man noch über ein paar Varianten nachdenken, die Adrian Billington vor einigen Jahren beschrieben hat:

• Verwendung einer pipelined table function
• Verwendung der model clause

Beide Varianten sind offenbar recht flott, aber auch das bringt mich nicht von connect by level ab, dessen einzigen (mir bekannten) größeren Nachteil Tanel Poder gelegentlich erwähnt hat: seine Memory-Nutzung wächst aufgrund des rekursiven Verfahrens mit der Anzahl der generierten Sätze. Daher kann es sinnvoll sein, die Generierung mit mehreren Subqueries (oder CTEs) durchzuführen, die man dann via Cross-Join verbindet.