Freitag, August 30, 2013

CBO: Überflüssige Prädikate

Ein recht wichtiger Hinweis von Jonathan Lewis zum Thema redundanter und nutzloser Prädikate: wenn der CBO erkennt, dass ein Prädikat notwendigerweise FALSE ist bzw. durch ein anderes Prädikat bedingt ist, dann kann er dieses Prädikat ignorieren. Das Beispiel dazu lautet:
I can only make assumptions about how the optimizer code is handling this predicate - but if we assume that it simplifies predicates when possible then that's sufficient to explain the disappearance of anything that looks like "125 is null". Consider: 
SQL> select * from t1 where n1 > 14 and n1 > 15;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2774 |   506K|    14 |
|*  1 |  TABLE ACCESS FULL| T1   |  2774 |   506K|    14 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1">15)
Has "n1 > 14" disappeared, or has the compound predicate "n1 > 14 and n1 > 15" been simplified.

Your predicate simplifies to "col = :b or FALSE" which simplifies to "col = :b".
The only time that FALSE (which newer versions of Oracle represent by "NULL IS NOT NULL") is visible as a final predicate is when it is the ONLY predicate on a table (as in select user from dual where 1 = 0).
Wie der Herr Lewis erklärt: es ist nur eine Annahme. Aber eine sehr plausible. 

Donnerstag, August 29, 2013

Fast Refresh Internals für Materialized Views (aggregate-only)

Alberto Dell'Era steigt mal wieder in die Tiefen der Algorithmen, die beim MV Fast Refresh Verwendung finden. Diesmal hat er gleich eine ganze Artikelserie daraus gemacht:
  • Fast refresh of aggregate-only materialized views – introduction: erläutert die Struktur des Materialized View Log (als Sequenz von old/new values oder delete/insert Einträgen) und zeigt, wie redundante Einträge aus der Informationsfolge gefiltert werden (was man anscheinend vermeiden kann, wenn man die Sequenz-Angabe aus dem Log entfernt!). Die refresh engine prüft anschließend, welchem Typ die im Log vorliegenden Informationen entsprechen (new-only(insert-only), old-only(delete-only) oder mixed), und wählt einen dazu passenden Refresh Algorithmus aus (und meldet gegebenenfalls "ORA-32314 REFRESH FAST unsupported after deletes/updates" for insert-only MVs). Der Artikel enthält noch diverse zusätzliche Details, deren Nacherzählung ich mir an dieser Stelle spare.
  • Fast refresh of aggregate-only materialized views with SUM – algorithm: erläutert das Vorgehen beim Fast-Refresh von MVs mit der Funktion SUM. Der Artikel untersucht die unterschiedlichen Aktualisierungsverfahren für die unterschiedlichen DML-Pattern (nur Insert, nur Delete, gemischt) und diskutiert Optimierungsmöglichkeiten (die aber eher beschränkt zu sein scheinen: man kann einen im Prozess mehrfach relevanten Index auf der MV erzeugen; grundsätzlich ist der Fast Refresh nur bei moderatem Änderungsvolumen sinnvoll; erfreulich ist, dass kein Zugriff auf die Master-Tabelle erforderlich ist, sondern alle relevanten Informationen aus der MV und dem MV Log stammen).
  • Fast refresh of aggregate-only materialized views with MAX – algorithm: erläutert das Vorgehen beim Fast-Refresh von MVs mit der Funktion MAX. Der Fall ähnelt dem Refresh für die SUM-Funktion, allerdings ist im Fall gemischter DML-Operationen (Insert + Delete) ein Zugriff auf die Master-Tabelle erforderlich, der durch einen Index unterstützt werden sollte.
Meine Zusammenfassung taugt in diesem Fall nicht besonders viel und soll tatsächlich nur als Index dienen - sprich als Erinnerung daran, dass man beim Herrn Dell'Era Details zu den Mechanismen des Fast Refresh finden kann, die man sich bei Bedarf noch mal genauer anschauen müsste.

Mittwoch, August 28, 2013

Postgres: Löschungen in CTEs

Bei der Lektüre der postgres-Dokumentation sind mir eine ganze Reihe interessanter Optionen begegnet, aber besonder gut gefällt mir Folgendes: die Möglichkeit, ein DELETE in einer CTE zu definieren und die Löschungen in der Hauptquery weiter zu verarbeiten:

postgres=# create table t1 as select * from generate_series(1, 10) id;
SELECT 10

postgres=# select * from t1;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 Zeilen)

postgres=# create table t2 as select * from t1 where 1 = 0;
SELECT 0

postgres=# select * from t2;
 id
----
(0 Zeilen)

postgres=# with moved_rows as (delete from t1 where id <= 5 returning *)
postgres-# insert into t2 select * from moved_rows;
INSERT 0 5

postgres=# select * from t1;
 id
----
  6
  7
  8
  9
 10
(5 Zeilen)

postgres=# select * from t2;
 id
----
  1
  2
  3
  4
  5
(5 Zeilen)

Damit könnten Archivierungs-Operationen sehr elegant werden.

Dienstag, August 27, 2013

Temporäres Undo

Harald van Breederode weist auf ein interessantes neues Feature in 12c hin: temporary undo. Durch den Parameter temp_undo_enabled kann man dafür sorgen, dass die Undo-Informationen für DML-Operationen auf temporären Tabellen in einen temporary tablespace geschrieben werden. So spart man sich nicht nur die redo Informationen für die eigentlichen Datenänderungen, sondern auch die (irrelevanten) redo Informationen für das zugehörige undo. Der Artikel zeigt der Verhalten anhand eines einfachen Tests mit Betrachtung der v$mystat-Statistiken zu redo entries und redo size. Außerdem untersucht er das Verhalten beim Einsatz einer physical standby database.

Sonntag, August 25, 2013

Adaptive Plans in 12c

Gestern habe ich im OTN Forum ein paar Fragen zum grundsätzlichen Verhalten der in 12c eingeführten Adaptive Plans gestellt, aber inzwischen denke ich, dass die Überlegungen hier besser aufgehoben wären. Falls der OTN Thread noch interessante Ergänzungen liefern sollte, werde ich sie auch hier hinzufügen. Aber nun zum Thema. Maria Colgan ("The Optimizer Lady" - wenn ich mich richtig erinnere, verdankt sie den Titel Doug Burns) nennt die Adaptive Plans in ihrem White Paper Optimizer with Oracle Database 12c "by far the biggest change to the optimizer in Oracle Database 12c". Zur Arbeitsweise des Features erklärt sie weiterhin:
Adaptive plans enable the optimizer to defer the final plan decision for a statement, until execution time. The optimizer instruments its chosen plan (the default plan), with statistics collectors so that at runtime, it can detect if its cardinality estimates, differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it can be automatically adapted to avoid suboptimal performance on the first execution of a SQL statement.
Der CBO entwickelt also nicht wie bisher einen vorab vollständig festgelegten Plan, den er dann zum Zeitpunkt der Ausführung durchführt, sondern er erzeugt einen Gesamtplan mit alternativen Sub-Plänen und behält sich vor, die Wahl des Sub-Plans erst zu treffen, wenn er die tatsächlichen Kardinalität der zugehörigen Input-Menge kennt. Wenn man bedenkt, wie viele suboptimale Pläne sich aus fehlerhaften Entscheidungen zwischen Nested Loops und Hash Joins ergeben, weil die Schätzung des CBO deutlich von den tatsächlichen Größen der Mengen abweichen, dann erscheint diese Strategie ausgesprochen plausibel. In meiner OTN-Frage hatte ich Frau Colgans Erläuterung zitiert, aber offenbar nicht bis zu Ende gelesen, denn der letzte Teil war mir offenbar entgangen: "on the first execution of a SQL statement". Die endgültige Entscheidung für einen Plan wird bei der ersten Ausführung der Query getroffen, wenn der statistics collector seine Zahlen liefert. Dazu ein Beispiel mit 12c auf meinem heimischen Windows 7-Rechner:

-- creation of test tables with indexes and statistics
drop table t1;
drop table t2;

create table t1
as
select rownum id
     , mod(rownum, 10) col1
     , lpad('*', 20, '*') col2
  from dual
connect by level <= 100000;

exec dbms_stats.gather_table_stats(user, 't1')

create index t1_id_idx on t1(col1, id);

create table t2
as
select mod(rownum, 100) id_t1
     , lpad('*', 20, '*') col2
     , rownum col3
  from dual
connect by level <= 100000;

exec dbms_stats.gather_table_stats(user, 't2')

create index t2_idx on t2(id_t1);

-- a significant change of the data distribution
-- without regathering of statistics
update t1 set col1 = 1000 where id > 1;
--> Update for 99999 rows
commit;

Für meine folgende Test-Query hätte die initiale Datenverteilung einen HASH JOIN nahe gelegt, aber durch das Update sollte ein NESTED LOOPS JOIN die bessere Wahl sein. Dazu zunächst die Einschätzung von Explain Plan:

explain plan for
select sum(t1.col1) sum_col3
  from t1
     , t2
 where t1.id = t2.id_t1
   and t1.col1 = 1;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 1632433607

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |    11 |    84   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE        |           |     1 |    11 |            |          |
|*  2 |   HASH JOIN            |           |   100K|  1074K|    84   (2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN    | T1_ID_IDX | 10000 | 80000 |    28   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| T2_IDX    |   100K|   292K|    55   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("T1"."ID"="T2"."ID_T1")
   3 - access("T1"."COL1"=1)

Note
-----
   - this is an adaptive plan

-- plan with adaptive parts
select * from table(dbms_xplan.display(format=>'+adaptive'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1632433607

----------------------------------------------------------------------------------------
|   Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT         |           |     1 |    11 |    84   (2)| 00:00:01 |
|     1 |  SORT AGGREGATE          |           |     1 |    11 |            |          |
|  *  2 |   HASH JOIN              |           |   100K|  1074K|    84   (2)| 00:00:01 |
|-    3 |    NESTED LOOPS          |           |   100K|  1074K|    84   (2)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR |           |       |       |            |          |
|  *  5 |      INDEX RANGE SCAN    | T1_ID_IDX | 10000 | 80000 |    28   (0)| 00:00:01 |
|- *  6 |     INDEX RANGE SCAN     | T2_IDX    |    10 |    30 |    55   (0)| 00:00:01 |
|     7 |    INDEX FAST FULL SCAN  | T2_IDX    |   100K|   292K|    55   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("T1"."ID"="T2"."ID_T1")
   5 - access("T1"."COL1"=1)
   6 - access("T1"."ID"="T2"."ID_T1")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

Der Plan ist explizit als "adaptive plan" gekennzeichnet und enthält den HASH JOIN, mit dem bei der von den Statistiken beschriebenen Datenverteilung zu rechnen ist. Dass diese Datenverteilung nach dem Update nicht mehr vorliegt, kann der Optimizer zu diesem Zeitpunkt nicht wissen, weil er im Rahmen von Explain Plan keinen Blick auf die Daten wirft. Die mit dem Format '+adaptive' erzeugte Version des Plans zeigt die Subplan-Varianten: in diesem Fall die Möglichkeit, den Hash Join durch einen NL Join zu ersetzen, wobei die inaktiven Teilen durch das führende '-' gekennzeichnet sind. Besonders lesbar ist dieser gemischte Plan nicht, aber die parallele Darstellung solcher Varianten ist auch keine dankbare Aufgabe, wie diverse Diff-Tools bestätigen können.

Nun zur tatsächlichen Ausführung der Query:

select sum(t1.col1) sum_col3
  from t1
     , t2
 where t1.id = t2.id_t1
   and t1.col1 = 1;

  SUM_COL3
----------
      1000

select * from table( dbms_xplan.display_cursor('0g3mdd8b1pstt', 0));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0g3mdd8b1pstt, child number 0
-------------------------------------
select sum(t1.col1) sum_col3   from t1      , t2  where t1.id =
t2.id_t1    and t1.col1 = 1

Plan hash value: 1261696607

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |    84 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |    11 |            |          |
|   2 |   NESTED LOOPS     |           |   100K|  1074K|    84   (2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN| T1_ID_IDX | 10000 | 80000 |    28   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| T2_IDX    |    10 |    30 |    55   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   3 - access("T1"."COL1"=1)
   4 - access("T1"."ID"="T2"."ID_T1")

Note
-----
   - this is an adaptive plan

Die tatsächliche Ausführung verwendet also den NL Join, wobei der Plan weiterhin die Cardinalities anzeigt, die aus den Tabellen- und Spalten-Statistiken des Dictionary ermittelt wurden. Dabei sind die Cost-Angaben die des HASH JOIN Planes: wenn man den ausgeführten Plan mit einem USE_NL Hint erzwingt, ergeben sich sehr viel höhere Cost-Werte, was wiederum keine Überraschung, sondern ganz folgerichtig ist.

explain plan for
select /*+ use_nl(t1 t2) */
       sum(t1.col1) sum_col3
  from t1
     , t2
 where t1.id = t2.id_t1
   and t1.col1 = 1;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1261696607

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    11 | 10081   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |    11 |            |          |
|   2 |   NESTED LOOPS     |           |   100K|  1074K| 10081   (1)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN| T1_ID_IDX | 10000 | 80000 |    28   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| T2_IDX    |    10 |    30 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   3 - access("T1"."COL1"=1)
   4 - access("T1"."ID"="T2"."ID_T1")

Letztlich steht alles, was ich hier ausführe, auch schon in Maria Colgans White Paper, und es zeigt sich wieder einmal, dass es Texte gibt, die eine gründliche Lektüre verdienen statt eines kursorischen Überfliegens. Hier noch einmal eine Zusammenfassung der Schritte:
  • im Rahmen der Optimization entwickelt der CBO einen Plan mit adaptiven Komponenten, über deren Verwendung er erst zum Zeitpunkt der ersten Ausführung eine Entscheidung treffen will.
  • während der ersten Ausführung prüft der statistics collector die tatsächliche Größe der Input-Mengen und trifft dann die endgültige Entscheidung für eine der im ursprünglichen Plan vorgesehenen potentiellen Varianten. Nachdem die Entscheidung getroffen ist, wird das Attribut IS_RESOLVED_ADAPTIVE_PLAN in v$sql auf 'Y' gesetzt (so dass ein Wert 'N' nur während der ersten Ausführung auftreten kann, wie man im White Paper erfährt)
Nützlich sind Adaptive Plans somit in Fällen, in denen die initialen Cardinality Schätzungen des CBO (aus welchen Gründen auch immer) nicht viel taugen. Für Fälle, in denen sich die Daten darüber hinaus rasch ändern, ist weiterhin dynamic sampling relevant (das in 12c das neue Label "dynamic statistics" bekommen hat).

Nachtrag 26.08.2013: ziemlich zeitgleich mit meiner Untersuchung hat Frau Colgan noch einen Artikel What's new in 12c: Adaptive joins veröffentlicht, der offenbar den Auftakt zu einer Serie zum Thema darstellt - und weitere wichtige Punkte anspricht (etwa den - im CBO Trace sichtbaren - 'inflection point', der definiert, wann eine Änderung der Join-Strategie aufgrund der Größe der ersten Input-Menge sinnvoll wird; Iordan Iotzow hat allerdings schon vor einigen Wochen darauf hingewiesen, dass die Statistiken hier immer noch relevant sind, nämlich bei der Größenbestimmung der zweiten Menge).

Nachtrag 25.04.2014: noch ein Artikel von Iordan Iotzow zum Thema: diesmal stellt er die Frage, wann adaptive Pläne verwendet werden und kommt zum Ergebnis:
  • wenn eine Menge (laut Statistiken) erheblich kleiner ist als die andere und passende Indizes vorliegen, wählt der Optimizer einen NL Join und spart sich den Aufwand, einen adaptive plan zu erzeugen.
  • wenn beide Mengen eine ähnliche Größe haben (für geeignete Werte von ähnlich), dann wählt der Optimizer den HJ.
  • wenn die eine Menge "slightly smaller" als die andere, dann wird ein adaptive plan erzeugt - wobei "slightly smaller" in der illustrierenden Grafik deutlich kleiner ist.
Die Aussage ist also recht allgemein, dafür aber vermutlich zutreffend.

Nachtrag 29.07.2014: mit Release 12.1.0.2 ist auch der SQL Monitor dazu in der Lage, adaptive plans korrekt anzuzeigen, wie man bei Franck Pachot nachlesen kann.

Nachtrag 30.07.2014: noch mal Iordan Iotzow mit Überlegungen zum Inflection-Point. Im Titel erscheint ein geklammertes "part 1", so dass von Fortsetzungen auszugehen ist.

Samstag, August 24, 2013

Eine neue MDX-Funktion ...

Chris Webb erwähnt, dass mit SSAS 2012 SP1 die neue MDX Funktion Divide verfügbar wurde und schreibt dazu:
This is the first new MDX function since… what, 2005? 2008? Five long years at least. [A loud bump is heard as Chris falls off his chair in amazement]
Tatsächlich ist MDX Divide nur ein wenig syntaktischer Zucker, um die unerfreulichen Fälle division-by-zero and division-by-null zu vermeiden - und ich erwähne diese Neuerung auch nur, weil sie mir noch mal deutlich macht, wie wenig in Sachen MOLAP dieser Tage passiert.

Freitag, August 23, 2013

Automatisches Lifecycle Management in 12c

Nur eine kurze Notiz: in 12c ist es möglich, die Storage von Daten auf unterschiedlicher Hardware und mit unterschiedlichen Eigenschaften (sprich: Komprimierung) über ILM (= Information Lifecyle Management) Optionen zu steuern. Einen einführenden Beitrag zum Thema findet man bei Martin Bach.

Montag, August 19, 2013

Asynchrone Aktualisierung globaler Indizes in 12c

Richard Foote hat sich in einer Artikelserie mit dem Thema Global Index Maintenance auseinandergesetzt. Dabei behandeln die einzelnen Artikel folgende Aspekte:
  • Global Index Maintenance – Pre 12c (Unwashed and Somewhat Slightly Dazed): erläutert die Situation in 11g: dort war die Löschung einer Tabellen-Partition eine sehr billige Operation, die allerdings alle zugehörigen globalen Indizes invalidierte (Status = 'UNUSABLE'). Alternativ konnte man dem drop partition Kommando auch die Klausel "update global indexes" zufügen, die dann aber erwartungsgemäß hohe Kosten (db block gets und redo) hervorrief.
  • 12c Asynchronous Global Index Maintenance Part I (Where Are We Now ?): in 12c ist die Verwendung der Klausel "update global indexes" sehr viel günstiger geworden: sie ruft keine unmittelbare Reorganisation hervor, sondern führt nur zu ORPHANED_ENTRIES, also Index-rowid-Verweisen ins Nirgendwo. Die erforderlichen Maintenance-Operationen erfolgen asynchron. Der Verzicht auf die unmittelbare Reorganisation bedeutet dabei allerdings, dass die gelöschten Index-Einträge von folgenden DML-Operationen nicht direkt wiederverwendet werden können, was zu einem Wachstum des Index führen kann (jedenfalls, wenn der Index nonunique ist, was in Teil 3 genauer erläutert wird).
  • 12c Asynchronous Global Index Maintenance Part II (The Space Between): untersucht das interne Vorgehen anhand eines Vergleichs von Index Block Dumps aus 11g und 12c: in 11g werden die Verweise auf die gelöschte Partition mit dem Flag D (= deleted) versehen, während im 12er Dump des Index keine Hinweise auf die Löschung zu finden sind. Das fehlen der Information in 12c macht die Wiederverwendung von Einträgen verständlicherweise unmöglich. Um eine Reorganisation der Index-Struktur hervorzurufen, gibt es verschiedene Möglichkeiten:
    • ALTER INDEX ... REBUILD PARTITION ...; ruft einen vollständigen Neuaufbau der Index-Struktur hervor, was effektiv, aber auch teuer ist.
    • ALTER INDEX ... COALESCE CLEANUP; löscht die orphaned entries aus der Index-Struktur, was billiger ist als die Rebuild-Operation.
    • PMO_DEFERRED_GIDX_MAINT_JOB - führt die Reorganisation automatisch im vorgesehenen Maintenance-Window durch.
  • 12c Asynchronous Global Index Maintenance Part III (Re-Make/Re-Model): zeigt, dass im Fall eines unique index eine Wiederverwendung der orphaned entries unmittelbar möglich ist, wenn der gleiche Wert erneut indiziert wird (was der Herr Foote gelegentlich schon mal erläutert hatte).
Möglicherweise wird die Serie noch weiter fortgesetzt, worauf ich dann vermutlich reagieren würde.

Mittwoch, August 14, 2013

Transformation von Skalaren Subqueries in 12c

Tanel Poder zeigt, dass der Optimizer in 12c in vielen Fällen dazu in der Lage ist, scalar subqueries im Rahmen der Transformationsphase zu einem Outer Join umzuwandeln, was in sehr vielen Fällen eine gute Idee sein dürfte. Im CBO Trace erscheint diese Umformung als "SU: Unnesting scalar subquery query block". Der Herr Poder erläutert auch noch mal, dass es nicht allzu viele gute Gründe für die Verwendung von scalar subqueries gibt, die sich dennoch einer gewissen Popularität erfreuen - vor allem bei SQL-agnostischen Entwicklern. Ich muss allerdings gestehen, dass ich sie in der Vergangenheit auch schon in Fällen herangezogen habe, in denen ich mir völlig sicher sein wollte, meine Ergebnismenge nicht durch einen verpfuschten Join zu vergrößern...

Dienstag, August 13, 2013

CBO: Cost für NVL-Prädikate

Jonathan Lewis zeigt, dass der Oracle Optimizer offenbar eine erstaunlich klare Vorstellung von der Wirkung von Prädikaten mit der NVL-Funktion besitzt und dabei die NULL-Werte der Spalte in seiner Kalkulation akkurat einbezieht (und das bereits seit Version 8i). Leider ist diese Einsicht des CBO offenbar auf NVL beschränkt und betrifft nicht COALESCE oder CASE, die stattdessen mit der Standard-Selektivität von 0.01 (also 1%) für Funktionen operieren. Besonders traurig ist das, weil diese beiden NVL-Alternativen im Gegensatz zu NVL eine short-circuit Optimierung verwenden, die dafür sorgt, dass überflüssige Auswertungen vermieden werden können. Schade: ich dachte, das wäre mal ein Fall, bei dem man eine eindeutige Antwort auf die Frage nach der am besten geeigneten Verfahrensweise geben könnte ...

Samstag, August 10, 2013

Partielle Indizes für partitionierte Tabellen in Oracle 12c

Richard Foote schreibt eifrig über neue Features in 12c und ich habe Mühe, bei der Lektüre schrittzuhalten. Zum Thema der Möglichkeit, Indizes nur für eine Teilmenge der Partitionen einer Tabelle anzulegen, hat er folgende Artikel veröffentlicht:
  • 12c Partial Indexes For Partitioned Tables Part I (Ignoreland): weist zunächst darauf hin, dass partielle Indizes nicht auf lokale Indizes beschränkt sind - was ich besonders erstaunlich finde -, und zeigt das Verhalten zunächst anhand eines globalen Index. Das Schlüsselwort für die Festlegung, welche Partitionen indiziert werden sollen, lautet INDEXING (was ich etwas einfallslos finde), und ein partieller Index wird über die Syntax CREATE INDEX ... INDEXING PARTIAL; angelegt. Zugriffe, die sowohl indizierte als auch nicht indizierte Partitionen betreffen, werden im Execution Plan über UNION ALL verknüpft. Durch geschickte (Sub-) Partitionierung kann man auf diese Weise die Größe (und Anzahl) von Index-Partitionen auf ein Minimum reduzieren.
  • 12c Partial Indexes For Partitioned Tables Part II (Vanishing Act): liefert ein Beispiel für das Verhalten mit lokalen Indizes. In diesem Fall werden die Index-Partitionen, für Partitionen, die mit INDEXING OFF definiert sind, als UNUSABLE erzeugt, was dafür sorgt, dass kein entsprechendes Segment angelegt wird. Aus nahe liegenden Gründen kann ein partieller Index nicht zur Unterstützung von PK- und UK-Constraints verwendet werden.
Dürfte sich als ein extrem nützliches Feature erweisen, nehme ich an.

Freitag, August 09, 2013

CBO: Cardinality-Berechnung für Werte außerhalb des bekannten Wertebereichs

Jonathan Lewis gibt eine kurze Zusammenfassung des in Cost Based Oracle umfassender erläuterten Verfahrens zur Berechnung der Cardinality von Werten die außerhalb des durch LOW_VALUE und HIGH_VALUE (in DBA_TAB_COLS und entsprechenden Views) definierten Bereichs bekannter Werte liegen. Für Gleichheitsprüfungen gilt dabei folgende Formel:
base cardinality * (total range – distance outside ) / (total range)
Die Wahrscheinlichkeit nimmt dabei jenseits der bekannten Werte sukzessive ab und die Cardinality fällt spätestens auf 1, wenn die "distance outside" die "total range" erreicht. Für Range-Pädikate wird eine andere Berechnung verwendet - in diesem Fall gilt:
For the out-of-range range-based predicate, the cardinality is simply the base cardinality of an in-range “column = unknown constant”
Die Größe des Bereichs hat folglich keinen Einfluss auf die Cardinality. Für Ranges, die teilweise außerhalb des bekannten Wertebereichs liegen, wird dabei offenbar nur die Cardinality des bekannten Teilbereichs berücksichtigt:

-- basierend auf dem Beispiel von Jonathan Lewis
-- 12.1.0.1
select * from t1 where n1 between 90 and 200;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   178 |  1958 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   178 |  1958 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

select * from t1 where n1 between 90 and 100;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   178 |  1958 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   178 |  1958 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Diese Berechnungsregeln sind in Oracle 12 offenbar unverändert.

Montag, August 05, 2013

Recovery einer einzelnen Tabelle mit RMAN in 12c

Ich hänge ja nicht so sehr an meinen Daten, aber wenn ich es täte, dann würde mir die neue (= Oracle 12) RMAN-Option der (Point-In-Time) Wiederherstellung einer einzelnen Tabelle, die Gavin Soorma in seinem Blog beschreibt, sicher viel Freude bereiten.

Histogramme in 12c

In einer kleinen Artikelserie beschäftigt sich Jonathan Lewis mit den Änderungen im Bereich der Histogramme im neuen Oracle-Release 12c:
  • 12c histograms: liefert eine Zusammenfassung zu den Veränderungen/Verbesserungen, die in 12c eingeführt wurden. Meine Zusammenfassung dieser Zusammenfassung wäre:
    • Erweiterung des bisher nur für basic column stats verfügbaren approximate NDV Mechanismus auf frequency histograms.
    • Bereitstellung eines neuen Typs von fequency histograms: "Top-N histogram" (die in vielen Fällen height-balanced histograms ersetzen).
    • Erhöhung der maximalen Bucket-Anzahl auf 2000 (der default bleibt aber 254).
    • Bereitstellung eines neuen Typs von height-balanced histograms: "hybrid histogram".
  • 12c Histograms pt.2: erklärt den neuen Mechanismus zur Erzeugung von frequency histograms und die Implementierung der Top-N frequency histograms.
    • der für die Erzeugung von frequency histograms in 12c verwendete approximate NDV Mechanismus geht folgendermaßen vor: zu jedem erzeugten hash value wird im Rahmen des Table Scans die erste rowid und die Gesamtanzahl der Vorkommen festgehalten. Wenn die Anzahl der unterschiedlichen hash values am Ende des Scans unterhalb der Anzahl vorgesehener Buckets liegt (also 254 oder 2000), dann können die zu den hash values gehörigen Werte per rowid lookup ermittelt werden.
    • wenn die Anzahl der erforderlichen Buckets (d.h. die Anzahl der ermittelten hash values) größer ist als die Anzahl der möglichen Buckets kann Oracle in 12c an Stelle eines height-balanced histograms ein Top-N frequency histogram verwenden, sofern die meisten Daten einer überschaubaren Anzahl von Werten zugeordnet sind. In diesem Fall wird jeweils ein Bucket für den minimalen und den maximalen Wert reserviert und die übrigen N-2 buckets für die am häufigsten vorkommenden Werte verwendet. Das Verfahren sollte in geeigneten Fällen deutlich bessere Dienste leisten als ein height-balanced histogram.
  • 12 Histogram fixes: mit Links auf ältere Artikel zum Verhalten in Extremfällen (mit langen Strings), das sich in 12c verändert hat, weil die Definition von ENDPOINT_ACTUAL_VALUE geändert wurde - was aber nicht alle Probleme löst.
Die Zusammenfassung weiterer Artikel werde ich bei Erscheinen - und Gelegenheit - ergänzen.