MP Oracle Blog

Fortschrittsangaben in v$session_longops

2012-02-15T01:56:00.000-08:00

Heute ist mir zum ersten Mal aufgefallen, dass in v$session_longops nicht unbedingt immer plausible Angaben erscheinen:

   SOFAR  TOTALWORK MESSAGE                                             SQL_PLAN_OPERATION  SQL_PLAN_OPTIONS 
-------- ---------- --------------------------------------------------- ------------------- -----------------
 1138348     861414 Sort Output:  : 1138348 out of 861414 Blocks done   WINDOW              SORT

Entsprechend zeigt auch DBMS_SQLTUNE.REPORT_SQL_MONITOR einen Progress von deutlich über 100%. Es handelt sich dabei um eine WINDOW SORT Operation einer analytischen Funktion, also nicht um einen Wert der mit dbms_application_info.set_session_longops eingefügt wurde, sondern um interne Operationsangaben.

Davon abgesehen deutet sich mal wieder an, dass man mit Analytics auf großen Datenmengen vorsichtig sein sollte, da die WINDOW SORT Operation nicht ganz unproblematisch ist - wozu Jonathan Lewis Grundsätzliches gesagt hat; zwei Randbemerkungen zum Thema habe ich hier und hier gegeben. In solchen Fällen gehe ich unter Umständen auf die Verwendung eines Joins der Basistabelle mit einem aggregierten Zwischenergebnis zurück.

DBMS_SCHEDULER-Visualisierung mit SQL Developer 3.1

2012-02-12T08:00:00.000-08:00

Tim Hall, der vor ein paar Jahren ein ganzes Buch zu DBMS_JOBS und DBMS_SCHEDULER geschrieben hat, zeigt in seinem Blog, was der aktuelle SQL Developer 3.1 zu Scheduler-Jobs und Job-Ketten anzubieten hat und liefert dabei auch noch eine Reihe von Links auf weitere Artikel zum Thema Scheduler. Sein Fazit lautet: "I would currently define all job chains using the PL/SQL API, so I could save my creation script in source control, and just use the SQL Developer interface to view the job chains I've created. With a bit of luck, this situation will change in subsequent versions."

I/O-Tests

2012-02-09T12:14:00.000-08:00

Kevin Closson überfordert mich häufig bereits mit den Titeln seiner Blog-Artikel - und nicht selten auch mit den Inhalten - aber diesmal verstehe ich, dass er sein bewährtes SLOB-Tool (The Silly Little Oracle Benchmark) vorstellt, das offenbar deutlich mehr kann als das Orion-Tool (das dort auch verlinkt ist).

Plan-Interpretation

2012-02-07T12:30:00.000-08:00

Christian Antogninis Erläuterungen zur Interpretation von Execution Plans, die man in Kapitel 6 seines großartigen Buches Troubleshooting Oracle Performance und - in knapper Form - auch im Web findet, gehören für mich zu den wichtigsten Grundlagen für die Deutung von SQL-Zugriffen. Im Blog-Eintrag Index Scan with Filter Predicate Based on a Subquery zeigt der Autor jetzt einen jener Fälle, in denen die allgemeinen Interpretationsregeln nicht gelten. Sein Fazit lautet: "In summary, be careful when you see an index scan with a filter predicate applying a subquery. The execution plan might not be carried out as you expect at first sight. It is also essential to point out that in such a case the predicate information is essential to fully understand what’s going on." Auf die Prädikat-Informationen würde ich aber bei der Plandeutung ohnehin selten verzichten wollen, da sie für die Überprüfung von Cardinality-Schätzungen entscheidend sind.

Interval Partitioninig und das Ende aller Tage

2012-02-07T11:05:00.000-08:00

Dass das Interval Partitioning leider noch ein paar unerfreuliche Bugs enthält, habe ich gelegentlich schon mal erwähnt. Heute ist mir aufgefallen, dass die Verwendung des sehr beliebten Datums-Default-Werts '31.12.9999' bei einer nach einer Datumsangabe intervall partitionierten Tabelle nicht ganz das gewünschte Ergebnis bringt:

create table test_interval_max_default ( mydate date)
partition by range (mydate)
interval (NUMTOYMINTERVAL(1,'MONTH'))
(partition test_p1 values less than (to_date('20120201', 'yyyymmdd')));

Tabelle wurde erstellt.

insert into test_interval_max_default (mydate) values (to_date('30.11.9999', 'dd.mm.yyyy'));

1 Zeile wurde erstellt.

insert into test_interval_max_default (mydate) values (to_date('01.12.9999', 'dd.mm.yyyy'));

insert into test_interval_max_default (mydate) values (to_date('01.12.9999', 'dd.mm.yyyy'))
            *
FEHLER in Zeile 1:
ORA-01841: (Volles) Jahr muss zwischen -4713 und +9999 liegen und darf nicht 0 sein

Nun ist '31.12.9999' sicher ein ziemlich unglücklicher Default-Wert - nicht zuletzt, weil er den CBO, der in Abwesenheit von Histogrammen eine Gleichverteilung der Werte zwischen Minimum und Maximum annimmt, zur massiven Fehleinschätzung von Kardinalitäten führen kann -, aber dass man ihn einfach nicht einfügen kann, erscheint doch etwas drastisch. In diesem Fall handelt es sich aber nicht um einen Bug, sondern um ein dokumentiertes Verhalten:

Note, however, that using a date where the high or low bound of the partition would be out of the range set for storage causes an error. For example, TO_DATE('9999-12-01', 'YYYY-MM-DD') causes the high bound to be 10000-01-01, which would not be storable if 10000 is out of the legal range.

Es bleibt also nur die Verwendung eines anderen Default-Werts für "das Ende aller Tage" oder der Verzicht auf Interval Partitioning.

Nachtrag 09.02.2012: Der Fall ist tatsächlich sogar noch unerfreulicher: bereits eine Query mit einer entsprechenden Bedingung ruft ora-01841 hervor:

SQL> select * from test_interval_max_default where mydate = to_date('31.12.9999', 'dd.mm.yyyy');
select * from test_interval_max_default where mydate = to_date('31.12.9999', 'dd.mm.yyyy')
              *
FEHLER in Zeile 1:
ORA-01841: (Volles) Jahr muss zwischen -4713 und +9999 liegen und darf nicht 0 sein

Angesichts dieses Verhaltens schwindet meine Zuneigung zum Interval Partitioning allmählich - obwohl es eigentlich eine so schöne Idee ist. Nun ja, vielleicht funktioniert es mit Version 12 oder 14.

Fehlerhaftes Costing für Index Hash Joins

2012-02-05T07:50:00.000-08:00

Jonathan Lewis zeigt in seinem Blog, dass die Kostenberechnung für Index Hash Joins in allen aktuellen Oracle-Releases massive Fehler enthält, was zur Folge haben kann, dass diese - in manchen Fällen sehr effiziente - Zugriffsoption nicht in Betracht gezogen wird: "This means the optimizer may be missing opportunities where the index hash join is a good execution path. Keep an eye open for this, you may want to hint some of your SQL (and then switch the hints into SQL Baselines, if you’re running 11g)." Ein Aspekt dabei ist, dass Indizes anhand ihrer Benamung ausgewählt werden, ein anderer, dass die Kosten der Einzelschritte nicht sinnvoll summiert werden; außerdem werden für den Index Join zusätzlich zu den erwarteten Kosten eines Index Fast Full Scan noch die Kosten eines Index Full Scan aufgeschlagen, was inhaltlich nicht einleuchtet (und offenbar erst im 10053er Trace sichtbar wird).

Ich wollte mir noch eine passende Begriffsbestimmung für den Index Hash Join ausdenken, aber die findet man natürlich auch schon beim Herrn Lewis:

One of the less well known access paths available to the optimizer is the “index join” also known as the “index hash join” path. It’s an access path that can be used when the optimizer decides that it doesn’t need to visit a table to supply the select list because there are indexes on the table that, between them, hold all the required columns.

Und der Vollständigkeit halber dann auch noch ein dritter Link zum Scratchpad, diesmal auf ein Fallbeispiel, in dem eine Tabelle mehrfach referenziert wird, um den cbo auf die Idee zu bringen, für jeden Fall den geeigneten Index zu verwenden.

Hier angekommen erinnere ich mich daran, dass ich schon mal auf einen der Index-Join-Artikel verwiesen hatte - und da das dann bereits Teil 4 einer Serie war, ist es vermutlich einfacher gleich auf die entsprechende Kategorie im Scratchpad zu verweisen, die aktuell fünf Artikel enthält.

SQL-Monitor-Darstellungsprobleme im RAC

2012-02-03T09:27:00.000-08:00

Nur eine kurze Notiz, zu der ich vielleicht gelegentlich die Pointe nachliefern kann: in RAC-Systemen liefert dbms_sqltune.report_sql_monitor - natürlich - nur dann die gewünschten Ergebnisse, wenn man einen sinnvollen Wert für den Parameter INST_ID setzt:

INST_ID: Only considers statements started on the specified instance. Use -1 (the default) to target the login instance. NULL will target all instances.

Die NULL-Variante fällt mir erst jetzt auf (und wird dann in der nächsten Woche getestet), aber wenn man den Aufruf für einen anderen Knoten als den, auf dem man sich gerade befindet, ausführt, dann liefert die table function (zumindest in dem System mit dem ich dieser Tage arbeite) einen relativ unformatierten Plan, in dem die Einrückungen fehlen. Um das zu vermeiden, verbinde ich mich in solchen Fällen direkt mit dem entsprechenden Knoten, aber die Ursache des Verhaltens ist mir unklar - und ich kann ohne größeren Aufwand kein 10046er Trace auf den Maschinen durchführen, um die Unterschiede der beiden Varianten zu bestimmen. Falls NULL hier Abhilfe schafft oder falls mir eine Erklärung über den Weg läuft, trage ich das noch nach.

Bug Fix Links

2012-01-31T11:31:00.000-08:00

Wenn Jonathan Lewis eine Liste von Links erstellt, dann sind diese mit hoher Wahrscheinlichkeit relevant. Im gegebenen Fall geht es um Bug Fixes für aktuelle Oracle Releases.

Nachtrag 02.02.2012: Interessant ist auch der Hinweis eines weiteren Artikels, dass in 11.2.0.3 diverse Bugs aus dem CTE-Umkreis behoben sind. CTEs sind ein großartiges Mittel um Abfragen übersichtlicher zu gestalten und wenn sie jetzt auch noch robust werden, werde ich sie mit noch größerer Begeisterung einsetzen.

Materialisierung von CTEs

2012-01-25T22:47:00.000-08:00

Dom Brooks liefert eine Reihe interessanter Beobachtungen zur Materialisierung von CTEs, die entweder durch den Hint MATERIALIZE oder durch die mehrfache Referenzierung einer solchen Subquery hervorgerufen wird. Unter anderem zeigt er im 10046er Trace die DDL der erzeugten GTT (Global Temporary Table) und erklärt, dass diese Struktur für folgende Ausführungen des Cursors nicht neu erzeugt werden muss (und dass sie sogar für andere Sessions erreichbar ist). Außerdem weist er auf einige zugehörige Bugs im Zusammenhang mit Distributed Transactions hin.

Execution Plans für DAX

2012-01-25T07:18:00.000-08:00

Seitdem ich mich mit MDX beschäftige, hoffe ich darauf, über das Stadium der "Optimierung durch Ausprobieren" hinauszukommen. Voraussetzung dafür wären Execution Plans - aber die sind anscheinend ein Geheimnis der SSAS-Entwickler. Insofern wundere ich mich darüber, dass der SQL Server Profiler für Microsofts neue DAX-Sprache ein Event DAX Query Plan liefert. Eine Einführung dazu liefert Jeffrey Wang - und falls ich mal ernsthaft mit DAX arbeiten sollte, würde mir das vermutlich helfen.

Fixed Object Statistics für X$-Objekte

2012-01-21T06:26:00.000-08:00

Maria Colgan schreibt im Blog der CBO-Entwickler über die Bedeutung der Erhebung von Statistiken für Fixed Objects (als X$-Tabellen und -Indizes) mit Hilfe von:

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.

Wichtig sind die Statistiken, da der CBO für die X$-Objekte kein dynamic sampling durchführt, sondern Defaults verwendet, die natürlich in vielen Fällen unpassend sind. Gespeichert werden grundsätzlich die gleichen Statistiken, die auch für normale Tabellen erfasst werden, obwohl die X$-Objekte nur im Memory existieren (daher wird Blocks auf 0 gesetzt). Frau Colgan empfiehlt, die Statistiken unter repräsentativer Workload zu erzeugen. In den Kommentaren beantwortet sie auch noch die Frage, warum diese Statistikerfassung noch nicht automatisiert wurde (die Antwort lautet: sie arbeiten dran, aber der passende Zeitpunkt für die Erfassung ist nicht leicht zu bestimmen). Gelegentlich ergänze ich hier vielleicht noch den Link auf einen Eintrag von Charles Hooper, der das Thema auch schon mal genauer beleuchtet hatte.

SCN

2012-01-21T05:52:00.000-08:00

Zuletzt haben sich Martin Decker und Riyaj Shamsudeen ausführlicher zum Thema System Change Number (SCN) geäußert:

Martin Decker weist auf einen häßlichen Bug hin, der sich ergibt, wenn die SCN ihren Maximalwert erreicht - was unter bestimmten Umständen sehr viel schneller passieren kann, als man angesichts des gigantischen Werteraums, der für sie zur Verfügung steht, erwarten würde.
Bei Riyaj Shamsudeen erfährt man Grundsätzliches zum Thema, beispielsweise zum gerade angesprochenen Wertebereich: "SCN is a huge number with two components to it: Base and wrap. Wrap is a 16 bit number and base is a 32 bit number. It is of the format wrap.base. When the base exceeds 4 billion, then the wrap is incremented by 1. Essentially, wrap counts the number of times base wrapped around 4 billion." Außerdem wird erläutert, wo die SCN verwendet und wann sie erhöht wird, und auch die Bugs, die der Herr Decker anspricht, werden beleuchtet.

Cost für Scalar Subqueries

2012-01-19T12:45:00.000-08:00

Dom Brooks weist in seinem Blog darauf hin, dass Oracle auch in aktuellen Releases die Kosten von scalar subqueries (also Subqueries in der SELECT-Liste, die für jeden einzelnen Ergebnissatz ausgeführt werden) nicht in in die Gesamtkosten einer Query einrechnet (was Jonathan Lewis in einem verlinkten Artikel damit begründet, dass der cbo nicht schätzen möchte, wie oft diese Unterabfrage ausgeführt wird - obwohl er ja die Menge der Ergebnissätze der Haupt-Query durchaus prognostiziert). Außer auf den Herrn Lewis wird auch noch auf Randolf Geist (der grundsätzlich darauf hinweist, dass Oracle "obviously treats work that has to be performed as part of the projection differently than work that has to be performed as part of the selection part"; der Fall ist demnach verwandt mit den problematischen Kostenschätzungen für user-defined functions, zu denen der Herr Geist in jüngerer Vergangenheit einiges geschrieben hat) und Gary Myers verwiesen, die sich auch schon gelegentlich zum Thema geäußert haben.

Ein Hinweis also, dass man scalar subqueries mit Vorsicht zu verwenden hat - aber das sollte man ohnehin, weil sie ab einer gewissen Größe der Ergebnismenge unheimlich kostspielig werden.

Bei nochmaliger Durchsicht des Absatzes wird mir klar, dass ich mir nicht nur über den Einsatz von scalar subqueries, sondern auch über den von Parenthesen Gedanken machen sollte.

Richard Foote über IOTs

2012-01-18T08:43:00.000-08:00

Nachdem Martin Widlake im vergangenen Jahr eine interessante Serie über Index Organized Tables veröffentlichte, hat jetzt auch Richard Foote begonnen, das Thema genauer zu erläutern. Hier eine Liste der bisher veröffentlichten Artikel:

Index Organized Tables – An Introduction Of Sorts (Pyramid Song): zeigt - unter anderem anhand von Index Block Dumps - inwiefern eine IOT einem entsprechenden Index überlegen ist (soll heißen: kompakter ist), der alle für eine Query relevanten Spalten enthält (also einem "fat" oder "covering" index): der zentrale Unterschied liegt darin, dass in der IOT keine rowid gespeichert werden muss, da der Verweis auf das zugehörige Tabellensegment entfallen kann (da es kein solches Segment gibt).
Index Organized Tables – Overflow Segment (Shadow Man): erklärt den Sinn des Overflow-Segments, in das Spalten abgeschoben werden können, auf die eher selten zugegriffen wird. Solche Spalten würden die Index-Struktur unnötig aufblähen. Stattdessen kann man sie ins Overflow-Segment auslagern, das mehr oder minder einer Heap-Tabelle entspricht, aber nicht alle Spalten enthält (sondern nur die nutzlosen). Der erforderliche Pointer in der Index-Struktur nennt sich im Dump "consisting of a 6 byte relative block address and row directory number". Während der Zugriff auf die Spalten im Index-Segment dadurch beschleunigt wird, erhöhen sich (natürlich) die Kosten für den Zugriff auf die ausgelagerten Spalten gegenüber einer entsprechenden IOT ohne Overflow-Segment.
Index Organized Tables – Overflow Segment Part II (The Loneliest Guy): hier erklärt Herr Foote, dass er (wie vermutlich die meisten Leute) PK-Spalten gerne an den Anfang der Spaltenliste einer Heap-Tabelle stellt, obwohl es durchaus auch Gründe dafür gäbe, sie nicht dort zu positionieren (da man ohnehin häufig über den PK zugreift und dann erst einmal über die bereits bekannten PK-Infos drüberlesen muss). Für IOTs ist die Platzierung der PK-Spalten an führende Stelle entscheidend. Tatsächlich ignoriert Oracle jede abweichende Definition und ordnet die PK-Spalten intern an den Anfang der Tabelle (was in der SEGMENT_COLUMN_ID in DBA_TAB_COLS abzulesen ist). Eine solche Umordnung kann dann unerfreuliche Wirkungen darauf haben, welche Spalten im Overflow-Segment landen, was man vor kurzem auch schon mal bei Jonathan Lewis lesen konnte.

Da es kaum jemanden gibt, der komplexe Sachverhalte in Oracle so klar darstellt wie der Herr Foote - und wahrscheinlich so gut wie niemanden, der sich mit Indizes besser auskennt -, lohnt es sich auf jeden Fall, die Artikel im Original zu lesen.

Inkrementelle Statistiken

2012-01-17T10:09:00.000-08:00

Randolf Geist beschäftigt sich in seinem Blog mit den Eigenschaften der in 11g eingeführten inkrementellen Partitions-Statistiken: "The most important point to understand is that Incremental Partition Statistics are not "cost-free", so anyone who is telling you that you can gather statistics on the lowest level (partition or sub-partition in case of composite partitioning) without any noticeable overhead in comparison to non-incremental statistics (on the lowest level) is not telling you the truth." Die angesprochenen Kosten liegen im Bereich der Laufzeiten und beim Speicherplatzbedarf in SYSAUX. Außerdem werden ein paar wichtige Detailbeobachtungen aufgeführt (z.B. wird bei Verwendung von INCREMENTAL die ESTIMATE_PERCENT-Angabe ignoriert).

Ein paar (geringfügig) ältere Stellungnahmen zum Feature hatte ich vor kurzem hier verlinkt.

Nachtrag 21.01.2011: Doug Burns, der dem Thema der Statistiken für Partitioned Tables im Jahr 2010 eine ganze Artikelserie gewidmet hat, gibt in seinem Blog mit Bezug auf Randolfs Aussagen noch zwei Kommentare zu den inkremetellen Statistiken:

dass Oracle beim initialen Aufbau von inkrementellen Statistiken die komplette Tabelle lesen muss, um die Synopsis aufzubauen, erscheint selbstverständlich: diese lange Laufzeit ist unvermeidbar
"Incrementals are a replacement for GRANULARITY=>'GLOBAL AND PARTITION' and not 'PARTITION'! Expecting an option which gathers Partition stats and then goes around updating synposes to perform as well as a simple partition gather is unrealistic*. Any performance improvement needs to be measured against both gathering the Partition stats and maintaining the Global stats. Incrementals will almost definitely be quicker than that!"

Der Vollständigkeit halber noch der Hinweis, dass diese Aussagen keinen Widerspruch zu den Geist'schen Ausführungen darstellen.

Löschung eines verwendeten DB-Links

2012-01-11T05:12:00.000-08:00

Was passiert, wenn man einen DB-Link löscht, der gerade in einer anderen Session verwendet wird? Die Antwort lautet: der Link wird gelöscht, ohne dass die zweite Session sich daran stört.

-- Session 1
-- Anlage eines DB-Links 
-- (in diesem Fall ein loopback Link auf die gleiche Instanz,
-- was aber wohl keine Rolle spielt)
create database link loopback using 'TestDB';

-- Session 2
select count(*) from t_remote@loopback;

-- Session 1
drop database link loopback;

-- Session 2
--> liefert das Ergebnis der Abfrage

  COUNT(*)
----------
  24800000

-- erneute Ausführung
select count(*) from t_remote@loopback
                              *
FEHLER in Zeile 1:
ORA-02019: Verbindungsbeschreibung für Fern-Datenbank nicht gefunden

Somit bleibt die Verbindung also für die Dauer der Query bestehen. Wenn ich darüber nachdenke, ist das Verhalten auch ganz plausibel: selbst die Löschung einer Tabelle führt ja nicht (notwendigerweise) zum Abbruch einer Query.

Index-Größenschätzung durch Explain Plan

2012-01-06T05:22:00.000-08:00

Heute ist mir zum ersten Mal aufgefallen, dass Explain Plan für ein CREATE INDEX eine Größenschätzung liefert:

explain plan for
create index idx_t1 on t1(a);

select * from table(dbms_xplan.display);

Plan hash value: 2186317495

---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |        |   257K|  3264K|  1001   (1)| 00:00:13 |
|   1 |  INDEX BUILD NON UNIQUE| IDX_T1 |       |       |            |          |
|   2 |   SORT CREATE INDEX    |        |   257K|  3264K|            |          |
|   3 |    TABLE ACCESS FULL   | T1     |   257K|  3264K|   858   (1)| 00:00:11 |
---------------------------------------------------------------------------------

Note
-----
   - estimated index size: 7340K bytes

Bisher war ich nie auf die Idee gekommen, dass Explain Plan für einen Index interessant sein könnte. Der Versuch herauszufinden, seit wann diese Option existiert, hat mich dann mal wieder zu Randolf Geists Blog geführt, wo sich ein Artikel EXPLAIN PLAN on DDLs findet, der diese und manch andere Frage beantwortet:

Starting with Oracle 10.2 you'll get an indication of the size of the index based on the dictionary statistics in the "Notes" section, so the estimate is only as good as your statistics allow for, in addition above points apply regarding the accuracy of the estimate in case of null values or function-based indexes. The size estimate is obviously based on the average column length recorded in the statistics.

Insgesamt scheint die Aussagekraft der Schätzung allerdings recht beschränkt zu sein, da sie diverse relevant Punkte ignoriert (Anzahl NULLs, implizite Tablespace-Zuordnung, Kompression, FBI, keine Unterscheidung zwischen B*Tree und Bitmap), wie Randolf anhand praktischer Tests nachweist.

Nachtrag 08.01.2012: kaum schreibe ich darüber, da sehe ich, dass Jonathan Lewis gerade gezeigt hat, dass die Schätzung einen recht massiven Bug enthält (zumindest bis inklusive 11.2.0.2)

Oracle Core von Jonathan Lewis

2012-01-04T08:54:00.000-08:00

Kurz nach Weihnachten hat mir Amazon das bei Apress erschienene Buch Oracle Core. Essential Internals for DBAs and Developers von Jonathan Lewis zugeschickt. Bei Amazon gibt's dazu aktuell fünf Customer Reviews, die dem Buch jeweils fünf Sterne zusprechen - die erste und ausführlichste hat (natürlich) Charles Hooper geliefert (und man findet sie - natürlich - auch in seinem Blog). Wenn man berücksichtigt, dass ich hier vermutlich auf keinen anderen Blog so viele Links setze, wie auf das Lewis'sche Scratchpad und dass ich kein anderes Buch so oft erwähne wie Cost-Based Oracle, überrascht's vermutlich nicht allzu sehr, dass ich jetzt in die allgemeinen Lobeshymnen einstimmen werde: der Herr Lewis hat da - zum dritten Mal (nach dem CBO-Buch und dem selbst heute noch relevanten Practical Oracle 8i aus dem Jahr 2000) - ein grundlegendes Buch zum Verständnis des Oracle Servers vorgelegt. Tanel Poder, der offizielle Technical Reviewer des Bandes (was ein weiteres Qualitätssiegel wäre, wenn man noch eines brauchte), schreibt in seinem Amazon-Kommentar: "It will probably become THE technical source for Oracle internals information for the next 10 years, just like Steve Adams'es Oracle Internal Services book was in the previous decade." Und damit hat er vermutlich recht.

Was der Herr Lewis in seinem Buch leistet, ist das, was Tom Kyte in Expert Oracle Database Architecture explizit verweigert: "Time and time again, I get questions regarding the exact bits and bytes of redo and undo. People seem to want to have a very detailed specification of exactly, precisly, what is in there. I never answer those questions." (S. 299) Der Herr Kyte hat dafür durchaus gute Gründe: er will die grundsätzlichen Konzepte erläutern und erklären, wozu redo und undo gedacht sind und wie sie zusammenarbeiten. Möglicherweise sieht er auch ähnliche Problem wie Tanel Poder, dessen (in Oracle Core zitierte) Erklärung, warum er selbst kein Buch über Oracle Internals schreibe, lautet: "I think the subject just changes too fast." (S. XIV) Dass Jonathan Lewis trotzdem ein Buch geschrieben hat, dessen Halbwertszeit - meiner Einschätzung nach - recht hoch sein wird, liegt daran, dass er in erster Linie über die grundlegendsten Mechanismen des Oracle Servers schreibt, und diese Mechanismen sind letztlich doch recht stabil. Vieles von dem, was man in Oracle Core findet, konnte man auch schon anderswo lesen: vor allem in den zahlreichen hochklassigen Blogs der Oracle-Community. Aber die konsistente Darstellung dieser Einzelbeobachtungen im Zusammenhang ist die große Leistung des Herrn Lewis, der meiner Meinung nach ohnehin einer der besten Stilisten unter den Oracle-Autoren ist.

Es folgt eine kurze Zusammenfassung der Kapitel des relativ schmalen Bandes (266 Seiten), aber die wird hier als work-in-progress erst allmählich entstehen, weil mich die nachvollziehende Lektüre sehr viel Zeit kosten wird:

Kapitel 1: Getting Started: liefert auf vier Seiten eine ganz knappe Übersicht zu den Prozessen und Speicherstukturen in einer Oracle Instanz.

Kapitel 2: Redo and Undo: erläutert das Zusammenspiel von Redo und Undo bei einer Blockänderung: zu jeder Änderung werden eine redo-Information und eine undo-Information erzeugt, wobei auch für die undo-Information noch einmal eine redo-Information geschrieben werden muss. Erst dann kann die Änderung im data block erfolgen. Die Reihenfolge ist dabei:

change vector für undo record erzeugen
change vector für data block erzeugen
change vektoren kombinieren und redo in den log buffer schreiben
undo record in den undo block schreiben
Änderung des data blocks durchführen

In älteren Releases wurde für jede Änderung einer Session ein redo record in den log buffer eingetragen, aber seit Oracle 10 werden mehrere Änderungen in einem privaten Speicherbereich (private strand) gesammelt und dann zusammengefasst gespeichert, was eine Reduzierung der erforderlichen Latches (redo copy, redo allocation) herbeiführt (allerdings kommen neue In memory undo Latches hinzu), die dadurch noch signifikanter wird, dass es mehrere child latches für die In memory undo Latches gibt, während die Latches für den zentralen Log Buffer notwenigerweise eine knappe Ressource waren.

Wie die folgenden Kapitel basiert auch dieses auf der detaillierten Auswertung von Block Dumps, die alle relevanten Informationen zu den Zusammenhängen enthalten - man muss sie nur interpretieren können ...

SSD für Oracle-Datenbanken

2012-01-01T03:49:00.000-08:00

Bei Gwen Shapira findet man ein paar grundlegende Informationen zum Einsatz von SSDs in Oracle-Datenbanken - interessant sind dabei auch die zahlreichen Kommentare zum Artikel. Zu den wichtigsten Hinweisen gehören die Erläuterungen zur Performance bei Lese- und Schreiboperationen unterschiedlichen Typs. Ohne den Artikel komplett exzerpieren zu wollen hier ein paar der wichtigsten Punkte:

SSD ist nur interessant, wenn die Hauptlast einer DB im I/O-Bereich liegt (was bei den meisten DBs, die ich sehe, der Fall ist).
wenn man es bezahlen kann, sollte man alle data files auf SSD legen.
für die redo logs sind SSDs nicht geeignet, da sie bei sequentiellem Schreiben keine Vorteile gegenüber spinning disks haben.
man kann SSDs über die Option Database Smart Flash Cache als sekundären Cache der SGA verwenden - dort können dann Blöcke zwischengespeichert werden, die aus dem Buffer Cache heraus gefallen sind, was das erneute Laden natürlich beschleunigt.
Für Exadata sieht alles noch mal ganz anders aus, aber Exadata ist dieser Tage nicht mein Thema.

CBO Verbesserungen in 11g

2012-01-01T03:00:00.000-08:00

Christian Antognini hat die Powerpoint-Folien zu seiner Präsentation Challenges and Chances of the 11g Query Optimizer auf seiner Webseite verfügbar gemacht - und liefert damit eine sehr kompakte Zusammenfassung der in Version 11 ergänzten Features des CBO und der Statistikerfassung. Hier ein paar der aufgeführten Punkte:

Indizes

invisible indexes
Verwendung von linguistic indexes bei LIKE
Statistik Historie bleibt nach Index Rebuild erhalten (erst in 11.2)

FULL OUTER JOIN wird intern nicht mehr durch UNION ALL abgebildet
Join-Filter Pruning (ist mir noch nicht völlig klar)
Table Expansion: unterschiedliche Teile einer partitionierten Tabelle können über unterschiedliche Zugriffswege abgearbeitet werden (FTS + Index-Zugriff)
Join Factorization: ein für mehrere Teile eines UNION ALL gemeinsamer Zugriff kann heraus faktoriert und nur einmal ausgeführt werden (erst in 11.2)
OR Expansion funktioniert auch mit FBI
Join Elimination: ein überflüssiger Join kann vermieden werden, wenn entsprechende Constraints existieren
Subquery Unnesting: NOT IN und NOT EXISTS werden in Joins umgewandelt, mit denen der CBO besser zurecht kommt

Statistiken

Bugs für System Statistics beseitigt
Präferenzen für Objekt-Statistiken definierbar
Auto Sample Size funktioniert jetzt richtig
Pending Statistics: werden nicht unmittelbar nach der Anlage veröffentlicht
Incremental Statistics für partitionierte Tabellen (dazu gibt's hier ein paar weiterführende Anmerkungen)
Extended Statistics für Expressions und Spaltengruppen (in 11.2.0.2 kann man automatisch bestimmen lassen, für welche Spaltengruppen solche Korrelationsstatistiken angelegt werden sollten)
Funktionen zum Vergleich von Statistiken

Plan Stabilität

cursor_sharing = similar ist deprecated; es wurde gelegentlich als Krücke für Applikationen, die keine Bindevariablen verwenden, vorgeschlagen
Einführung von SPM
Adaptive Cursor Sharing
Cardinality Feedback

Kategorisierung von Hints

2011-12-30T06:10:00.000-08:00

Bei Dom Brooks findet man den Versuch einer Taxonomie der unterschiedlichen Typen von Hints, also z.B.:

Optimizer-Mode
Direct-Path
Anpassungen von Cardinality-Schätzungen
Bug-Handhabung
Cursor-Sharing
Parallelisierung
Caching
...

Eine Liste verfügbarer Hints liefert V$SQL_HINT.

Ergänzungen zu display_cursor

2011-12-23T03:51:00.000-08:00

Randolf Geist hat ein hübsches Weihnachtsgeschenk abgeliefert: eine um eine Reihe sehr nützlicher Zusatzinformationen erweiterte Version zur selbst schon extrem nützlichen dbms_xplan.display_cursor-Routine. Das Script kombiniert einige Ideen, die Adrian Billington und Kyle Hailey gelegentlich geäußert haben, mit zusätzlichen Ergänzungen, und macht es deutlich einfacher, den Ablauf der Operationen im execution plan korrekt zu deuten. Für besonders nützlich halte ich den TCF Graph zur Visualisierung von Fehleinschätzungen bei den Cardinalities und die klarere Verteilung der I/O-Operationen auf die Einzelschritte (in den %SELF-Spalten und in den Graphen). Eine schöne Alternative für alle, die keine Lizenz für das Real-Time SQL Monitoring besitzen - dafür aber einen breiten Monitor.

ix_sel_with_filters

2011-12-22T04:42:00.000-08:00

In Cost-Based Oracle erläutert Jonathan Lewis im vierten Kapitel die Grundlagen des Index Costing und verweist dabei auf die von Wolfgang Breitling im Jahr 2002 veröffentlichte Formel:

cost = blevel
+ ceiling(leaf_blocks + effective index selectivity)
+ ceiling(clustering_factor * effective table selectivity)

Völlig unproblematisch sind in dieser Rechnung die Faktoren blevel, leaf_blocks und clustering_factor, die man z.B. in der View user_indexes findet. Die beiden anderen Elemente effective index selectivity und effective table selectivity werden im CBO-Buch ebenfalls erläutert, aber ich hatte mir bisher nie die Mühe gemacht, zu prüfen, ob ich diese Erklärungen tatsächlich verstanden hatte. Das soll hiermit geändert werden.

Dabei scheint die effective index selectivity auch noch recht harmlos zu sein: sie ergibt sich aus der Kombination der Selektivitäten der für den Zugriff verwendeten Index-Spalten. leaf_blocks * effective index selectivity repräsentiert dabei die Kosten des Zugriffs auf die Index-Struktur, von der ein durch die effective index selectivity bestimmter Anteil gelesen werden muss.

Zur effective table selectivity schreibt Jonathan Lewis, sie solle "be based only on those predicates that can be evaluated in the index, before you reach the table." (S. 67) Da mir dieser Punkt noch immer nicht völlig klar ist dazu ein kleiner Test (mit 11.2.0.1):

-- Anlage einer Test-Tabelle mit einem zusammengesetzten Index
drop table test_ind_selectivity;

create table test_ind_selectivity tablespace test_ts
as
select rownum id
     , mod(rownum, 10) col1 -- 10 unterschiedliche Werte -> Selektivität: 0,1
     , mod(rownum, 100) col2 -- 100 unterschiedliche Werte -> Selektivität: 0,01 
     , mod(rownum, 1000) col3 -- 1000 unterschiedliche Werte -> Selektivität: 0,001  
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'TEST_IND_SELECTIVITY', method_opt=>'FOR ALL COLUMNS SIZE 1')

create index test_ind_selectivity_ix1 on test_ind_selectivity(col1, col2, col3);

Für den Index ergeben sich folgende Statistiken:

select index_name
     , blevel
     , leaf_blocks
     , clustering_factor
  from user_indexes
 where table_name = upper('test_ind_selectivity');

INDEX_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
TEST_IND_SELECTIVITY_IX1                2        2894           1000000

Ich könnte jetzt die tatsächlichen Kosten von Queries ermitteln und versuchen, passende Werte für die Selektivitäten einzusetzen, aber ich mache mir die Sache ganz einfach und erzeuge ein 10053er Trace:

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

select /*+ index(test_ind_selectivity) test1 */ count(id) from test_ind_selectivity where col1 = 1;

select /*+ index(test_ind_selectivity) test2 */ count(id) from test_ind_selectivity where col1 = 1 and col2 = 1;

select /*+ index(test_ind_selectivity) test3 */ count(id) from test_ind_selectivity where col1 = 1 and col2 = 1 and col3 = 1;

select /*+ index(test_ind_selectivity) test4 */ count(id) from test_ind_selectivity where col1 = 1 and col3 = 1;

ALTER SESSION SET EVENTS '10053 trace name context OFF';

Für die Queries ergeben sich folgende Trace-Inhalte:

Fall 1: where col1 = 1

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    #Rows: 1000000  #Blks:  16907  AvgRowLen:  116.00
Index Stats::
  Index: TEST_IND_SELECTIVITY_IX1  Col#: 2 3 4
    LVLS: 2  #LB: 2894  #DK: 1000  LB/K: 2.00  DB/K: 1000.00  CLUF: 1000000.00
    User hint to use this index
Access path analysis for TEST_IND_SELECTIVITY
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_IND_SELECTIVITY[TEST_IND_SELECTIVITY] 
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    Card: Original: 1000000.000000  Rounded: 100000  Computed: 100000.00  Non Adjusted: 100000.00


  Access Path: index (RangeScan)
    Index: TEST_IND_SELECTIVITY_IX1
    resc_io: 100292.00  resc_cpu: 751223460
    ix_sel: 0.100000  ix_sel_with_filters: 0.100000 
    Cost: 100292.15  Resp: 100292.15  Degree: 1
  Best:: AccessPath: IndexRange
  Index: TEST_IND_SELECTIVITY_IX1
         Cost: 100292.15  Degree: 1  Resp: 100292.15  Card: 100000.00  Bytes: 0

***************************************

Die ix_sel repräsentiert im 10053er Trace die effective index selectivity, während die ix_sel_with_filters für die effective table selectivity steht. Beide Werte sind für die erste Query 0,1 - und entsprechen damit der Selektivität der Einzel-Spalte. Setzt man die Werte in die Formel ein, ergibt sich:

blevel + ceiling(leaf_blocks + effective index selectivity) + ceiling(clustering_factor * effective table selectivity)
2 + ceil(2894 * 0,1) + ceil(1000000 * 0,1)
2 + 290 + 100000 = 100292

Das passt dann schon mal zur resc_io, die offenbar den I/O-Anteil der Kosten darstellt. Die zusätzlichen 0,15 in den Kosten sind dann vermutlich der CPU-Anteil.

Fall 2: where col1 = 1 and col2 = 1

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    #Rows: 1000000  #Blks:  16907  AvgRowLen:  116.00
Index Stats::
  Index: TEST_IND_SELECTIVITY_IX1  Col#: 2 3 4
    LVLS: 2  #LB: 2894  #DK: 1000  LB/K: 2.00  DB/K: 1000.00  CLUF: 1000000.00
    User hint to use this index
Access path analysis for TEST_IND_SELECTIVITY
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_IND_SELECTIVITY[TEST_IND_SELECTIVITY] 
  ColGroup (#1, Index) TEST_IND_SELECTIVITY_IX1
    Col#: 2 3 4    CorStregth: -1.00
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: 
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    Card: Original: 1000000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: 
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: 
  Access Path: index (RangeScan)
    Index: TEST_IND_SELECTIVITY_IX1
    resc_io: 1005.00  resc_cpu: 7547047
    ix_sel: 0.001000  ix_sel_with_filters: 0.001000 
    Cost: 1005.00  Resp: 1005.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: TEST_IND_SELECTIVITY_IX1
         Cost: 1005.00  Degree: 1  Resp: 1005.00  Card: 1000.00  Bytes: 0

***************************************

In diesem Fall sind beide Selektivitäten auf 0,001 (also das Produkt der Selektivitäten der Einzelspalten 0,1 * 0,01) gesetzt, was auch wieder den Erwartungen entspricht. In der Formel ergibt sich für die Komponenten 2 + 3 + 1000.

Fall 3: where col1 = 1 and col2 = 1 and col3 = 1

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    #Rows: 1000000  #Blks:  16907  AvgRowLen:  116.00
Index Stats::
  Index: TEST_IND_SELECTIVITY_IX1  Col#: 2 3 4
    LVLS: 2  #LB: 2894  #DK: 1000  LB/K: 2.00  DB/K: 1000.00  CLUF: 1000000.00
    User hint to use this index
Access path analysis for TEST_IND_SELECTIVITY
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_IND_SELECTIVITY[TEST_IND_SELECTIVITY] 
  ColGroup (#1, Index) TEST_IND_SELECTIVITY_IX1
    Col#: 2 3 4    CorStregth: 1000.00
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0010
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    Card: Original: 1000000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0010
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0010
  Access Path: index (AllEqRange)
    Index: TEST_IND_SELECTIVITY_IX1
    resc_io: 1005.00  resc_cpu: 7567047
    ix_sel: 0.001000  ix_sel_with_filters: 0.001000 
    Cost: 1005.00  Resp: 1005.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: TEST_IND_SELECTIVITY_IX1
         Cost: 1005.00  Degree: 1  Resp: 1005.00  Card: 1000.00  Bytes: 0

***************************************

Die Werte für ix_sel und ix_sel_with_filters sind mit denen in Fall 2 identisch. Ein offensichtlicher Unterschied sind die Angaben unter Partial und CorStregth (wo vielleicht ein n fehlt) - zu diesen Einträgen im CBO-Trace hat Randolf Geist im Rahmen einer Untersuchung von Korrelationseffekten und extended statistics (die wiederum auf einen Artikel von Riyaj Shamsudeen Bezug nimmt) gelegentlich ein paar Erläuterungen gegeben, die ich so interpretiere, dass die Angaben etwas über die Korrelation der Spaltenwerte aussagen und durch extended statistics und Histogramme beeinflusst werden können. Die Werte in Fall 3 scheinen anzudeuten, dass der CBO die Korrelation der Spalten erkennt, aber wie er das macht, ist mir unklar, da keine extended statistics erzeugt wurden (und auch keine Histogramme: method_opt=>'FOR ALL COLUMNS SIZE 1'). In jedem Fall entsprechen die Kosten exakt denen von Fall 2.

Nachtrag 24.12.2011: Die Antwort auf meine Frage liefert Randolf Geists Kommentar: im gegebenen Fall eines Zugriffs mit Einschränkung auf alle Index-Spalten kann der CBO die Index-Statistiken verwenden, statt sich mit der indirekten Bestimmung über die Tabellenspalten-Statistiken behelfen zu müssen.

Fall 4: where col1 = 1 and col3 = 1

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    #Rows: 1000000  #Blks:  16907  AvgRowLen:  116.00
Index Stats::
  Index: TEST_IND_SELECTIVITY_IX1  Col#: 2 3 4
    LVLS: 2  #LB: 2894  #DK: 1000  LB/K: 2.00  DB/K: 1000.00  CLUF: 1000000.00
    User hint to use this index
Access path analysis for TEST_IND_SELECTIVITY
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_IND_SELECTIVITY[TEST_IND_SELECTIVITY] 
  ColGroup (#1, Index) TEST_IND_SELECTIVITY_IX1
    Col#: 2 3 4    CorStregth: -1.00
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: #1 (2 4 )  Sel: 0.0010
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    Card: Original: 1000000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
kkofmx: index filter:"TEST_IND_SELECTIVITY"."COL3"=1


  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: #1 (2 4 )  Sel: 0.0010
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: #1 (2 4 )  Sel: 0.0010
  Access Path: index (skip-scan)
    SS sel: 0.001000  ANDV (#skips): 100.000000
    SS io: 300.000000 vs. index scan io: 8455.000000
    Skip Scan rejected
  Access Path: index (RangeScan)
    Index: TEST_IND_SELECTIVITY_IX1
    resc_io: 1292.00  resc_cpu: 29410900
    ix_sel: 0.100000  ix_sel_with_filters: 0.001000 
 ***** Logdef predicate Adjustment ****** 
 Final IO cst 0.00 , CPU cst 50.00
 ***** End Logdef Adjustment ****** 
    Cost: 1292.01  Resp: 1292.01  Degree: 1
  Best:: AccessPath: IndexRange
  Index: TEST_IND_SELECTIVITY_IX1
         Cost: 1292.01  Degree: 1  Resp: 1292.01  Card: 1000.00  Bytes: 0

***************************************

In diesem Fall erscheinen nur die führende und die letzte Spalte des Index in der WHERE-Bedingung. Einleuchtend ist deshalb die ix_sel, die nur die Selektivität der führenden Spalte berücksichtigt: aufgrund der Einschränkung müssen 10% der Index-Struktur durchsucht werden, um alle Sätze mit col1 = 1 zu ermitteln, ehe dann die Filterung auf col3 = 1 erfolgen kann. Aber die ix_sel_with_filters ist mir nicht ganz klar, denn sie ergibt sich nicht aus der Selektivität der beiden Spalten (0,1 * 0,001 wären 0,0001). Möglicherweise ist auch hier eine Korrelationsbestimmung im Spiel, denn die ix_sel_with_filters = 0.001000 entspricht der Angabe in: Partial: #1 (2 4 ) Sel: 0.0010. Auch in diesem Fall ist mir unklar, wie der CBO die Korrelation erkennen kann.

Interessant ist darüber hinaus noch der Plan für die vierte Query, der sich im CBO-Trace ebenfalls findet:

============
Plan Table
============
----------------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name                    | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |                         |       |       |  1292 |           |
| 1   |  SORT AGGREGATE               |                         |     1 |    12 |       |           |
| 2   |   TABLE ACCESS BY INDEX ROWID | TEST_IND_SELECTIVITY    |  1000 |   12K |  1292 |  00:00:07 |
| 3   |    INDEX RANGE SCAN           | TEST_IND_SELECTIVITY_IX1|  1000 |       |   292 |  00:00:02 |
----------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("COL1"=1 AND "COL3"=1)
3 - filter("COL3"=1)

Hier erscheinen col1 und col3 als access-Prädikate und col3 dann noch einmal als Filter-Prädikat. Ich hatte erwartet, dass nur col1 als access-Prädikat verwendbar wäre und deshalb 100000 Sätze über den Index gelesen werden müssten, ehe die zusätzliche Filterung auf col3 = 1 erfolgen könnte, aber das ist offenbar nicht der Fall, wie die Ausführungsinformationen belegen:

select plan_table_output
  from table( dbms_xplan.display_cursor ( NULL, NULL, 'allstats'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  gj6fuawjzpr51, child number 0
-------------------------------------
select /*+ index(test_ind_selectivity) gather_plan_statistics */
count(id) from test_ind_selectivity where col1 = 1 and col3 = 1

Plan hash value: 779399104

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |      1 |        |      1 |00:00:00.03 |    1296 |
|   1 |  SORT AGGREGATE              |                          |      1 |      1 |      1 |00:00:00.03 |    1296 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_IND_SELECTIVITY     |      1 |   1000 |   1000 |00:00:00.01 |    1296 |
|*  3 |    INDEX RANGE SCAN          | TEST_IND_SELECTIVITY_IX1 |      1 |   1000 |   1000 |00:00:00.01 |     296 |
-------------------------------------------------------------------------------------------------------------------

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

   3 - access("COL1"=1 AND "COL3"=1)
       filter("COL3"=1)

Tatsächlich liefert der Index-Zugriff also nur die 1000 relevanten Sätze, kann also die fehlende Spalte col2 überspringen - was durchaus plausibel ist, aber nicht unbedingt das, was ich erwartet hatte.

Nachtrag 24.12.2011: auch dazu liefert Randolf Geist in seinem Kommentar die Erklärung: der ACCESS liefert 296 von 1296 Blocks (und 100.000 Sätze) und anschließend erfolgt die FILTERung auf 1.000 Sätze. Das Erscheinen von col3 als ACCESS-Prädikat dient dabei offenbar nur zur Verwirrung... Hilfreich wär's, wenn die Informationen zum ACCESS auch noch bei den A-Rows auftauchen würden, wie Randolf unten anmerkt. Eine schöne Erklärung zur Interpretation von FILTER-Prädikaten in Index-Scan-Steps (und überhaupt zur Rolle von ACCESS- und FILTER-Prädikaten) findet man hier und hier bei Markus Winand.

Einmal mehr bin ich nicht unbedingt dort angelangt, wo ich hin wollte - nämlich bei einem klaren Verständnis der ix_sel_with_filters, aber für heute genügt's mir.

RBO-Nutzung in Version 11

2011-12-20T07:30:00.000-08:00

Maria Colgan erläutert im Blog der CBO-Entwickler, welche Objekt- bzw. Query-Eigenschaften die Verwendung des CBO erzwingen. Grundsätzlich gilt, dass alle Features neueren Datums dazu führen, dass der RBO aus dem Spiel ist.

Ehrenrettung für Trigger

2011-12-14T02:44:00.000-08:00

Ich bin bei Tom Kyte (und anderswo) so oft vor dem Einsatz von Triggern gewarnt worden und habe selbst so viele Unerfreuliches mit ihnen erlebt, dass ich sehr gespannt darauf bin, was Toon Koppelaars in seinem neuen Blog Triggers Considered Harmful, Considered Harmful alles aufführen wird, um den schlechten Ruf dieses Features zu korrigieren.

Hier eine kurze Liste der bisher veröffentlichten Artikel (die ich vermutlich nicht bis zum Ende des Blogs ergänzen werde):

Starting this blog: mit einem Blick zurück ins Jahr 1994 und einigen Tom-Kyte-Zitaten zum Thema Trigger.
So what triggers are we talking about? mit einer kurzen Klassifizierung nach Trigger-Typen und einer Erläuterung zu row-level- und statement-level-triggers.
Some preliminaries: erklärt, dass in einem Trigger keine DDL-Operation aufgerufen werden darf (wegen des impliziten Commit; wenn man den problematischen Code in eine autonome Transaktion packt, kann diese die Änderungen der laufenden DML-Operation dann - natürlich - nicht sehen). Dann wird erklärt, dass der Fehlschlag eines Triggers ein Statement-Rollback für die betroffene DML-Operation hervorruft.Außerdem wird das mutating table Problem erläutert: "row level triggers are not allowed to read what's called the mutating table. If your row level trigger code queries the table that's being affected by the triggering DML statement, then Oracle will throw an ORA-04091 at you and cause the above mentioned statement level rollback."
"Workarounds" for ORA-04091: nennt zwei beliebte Workarounds für das Mutating-Table-Problem: die Verwendung einer autonomen Transaktion und die (weit weniger verbreitete) Verwendung eines loopback db-link. Ein wichtiger Unterschied der beiden Varianten ist, dass der db-link-Zugriff die Änderungen der noch nicht festgeschriebenen Transaktion sehen kann (da er zur gleichen dirstibuted transaction gehört), während die autonomous transaction das (natürlich) nicht kann.
The mutating table error prevents non-deterministic behavior of your code: erklärt, warum die beiden oben angeführten Workarounds grundsätzlich problematisch sind - und wieso der Mutating-Table-Error eine sinnvolle Sicherheitseinrichtung ist: im Fall des Inserts mehrerer Sätze entscheidet die Reihenfolge der rows darüber, ob ein row Trigger mit loopback db_link, der die Beziehung von Tabelleninhalten überprüft, einen Fehler liefert oder nicht - das Verhalten ist also nicht deterministisch. Ein row Trigger mit dem Pragma autonomous transaction würde in einem solchen Fall immer einen Fehler liefern, wenn die neu eingefügten Sätze nur in ihrer Kombination die Logik des Triggers erfüllen.
Look mom: a mutating table error without a trigger!: zeigt dass ORA-04091 nicht nur für Trigger relevant ist, sondern auch für Funktionen - und auch dort einen wichtigen Sicherheitsmechanismus darstellt..

RAC-Test-Installation mit Oracle VM

2011-12-09T08:43:00.001-08:00

Sollte ich mal wieder auf die seltsame Idee kommen, zu Test-Zwecken ein RAC-System aufzubauen, würde ich am besten noch mal einen Blick auf Gavin Soormas detaillierte Anleitung werfen.

Eine kurze Geschichte der Datenverarbeitung

2011-12-09T05:37:00.001-08:00

Thomas Kejser hat in seinem Blog eine kleine Geschichte der Datenverarbeitung in den letzten 40 Jahren aufgeschrieben. Der Historiker in mir freut sich über solche Überblicke.

Join-Cardinality

2011-12-08T12:32:00.001-08:00

Randolf Geist hat eine interessante Erläuterung zum Thema Table Functions And Join Cardinality Estimates veröffentlicht. Zunächst erklärt er darin (unter Ausklammerung von Spezialeffekten mit NULL, Histogrammen etc.), wie der CBO die Cardinality eines Joins grundsätzlich einschätzt:

Join Selectivity = 1 / greater(num_distinct(t1.c1), num_distinct(t2.c2))
low and high values of the join columns: If the join columns do not overlap at all the join cardinality will be calculated as 1.
Finally this join selectivity will be multiplied by the (filtered) cardinality of the two row sources to arrive at the join cardinality: Join Cardinality = Join Selectivity * cardinality t1 * cardinality t2

Dazu gibt's dann (natürlich) allerlei Beispiele. Im Hauptteil des Artikels erläutert der Herr Geist dann, warum der CBO im Fall von Table Functions auch bei Verwendung von dynamic sampling nicht zu den erwarteten Angaben kommt, aber diesen Teil des Artikels sollte man lieber im Original lesen, so dass ich mir hier das Exzerpieren spare.

Nachtrag 15.02.2012: im Sinne einer doppelt verketteten Liste hier noch der Verweis auf einen älteren Eintrag zum gleichen Thema (und mit nahezu gleichem Titel).

Satzgenerierung mit Pipelined Function

2011-12-08T12:16:00.001-08:00

In einem Kommentar im Oracle Scratchpad zeigt Valentin Nikotin die Verwendung einer Piplined Function als performanter Alternative zum beliebten connect-by-level-Verfahren:

create function generator (n pls_integer) return sys.odcinumberlist pipelined 
is

begin

  for i in 1 .. n loop
    pipe row (i);
  end loop;

end;
/

select count(*) from table(generator(1e7));

Lesenswert ist der vorangehende Artikel des Herrn Lewis natürlich auch, aber dessen Grundgedanke war mir schon bekannt.

ORA-08176 mit Merge und Interval Partitioning

2011-12-08T11:08:00.001-08:00

Interval Partitioning ist eine sehr praktische Vereinfachung für die Partitionierung in Version 11. Leider weist die Option aber noch einige recht unerfreuliche Bugs auf. Darüber, dass man den High_Value der initialen Partition mit Bedacht wählen sollte, habe ich gelegentlich geschrieben. Heute ist mir dann Bug 9638090 begegnet: "ORA-8176 from DML on an INTERVAL Partitioned Table OR table with deferred segment creation". Die Grundaussage des Fehlers ora-08176: consistent read failure; rollback data not available erläutert Mark Bobak in einem Kommentar im Oracle Scratchpad: "1555 means the rollback has been overwritten, 8176 means the required rollback never existed." In MOS werden die Voraussetzungen für das Auftreten des Bugs wie folgt beschrieben:

(a) A session is performing DML on an interval partitioned table or a table with deferred segment creation.
(b) The DML creates one or more interval / deferred partitions, and
(c) The same table is involved as the 'source' for the DML, and this involves an index scan (hence causing a consistent read of the index). For eg., update, merge, insert-select statements.

Ein schönes Fallbeispiel zum Thema findet man auch in einem zugehörigen OTN-Thread. Als harmloser Workaround kommt eine prophylaktische Erzeugung der Partition vor dem Merge in Frage.

Nachtrag 09.12.2011: In der MOS-Beschreibung des Bugs wird nur ein Patch in 11.2.0.2 erwähnt; tatsächlich gibt es aber auch Patches für 11.1.0.7 und andere Versionen (wie schon Dom Brooks im OTN-Thread erwähnt; mir waren diese Patches zunächst entgangen)

Cardinality-Schätzungen für Remote-Zugriffe

2011-12-07T01:46:00.001-08:00

Dieser Tage ist mir folgendes Phänomen begegnet, auf das ich mir noch keinen ganz klaren Reim machen kann: Ausgangspunkt war eine etwas unglücklich eingerichtete Reporting-Lösung, die grundsätzlich auf ein Star-Schema in einer Datenbank A (10.2.0.4) zugreift, aber zusätzlich auf die Daten einer Dimensionstabelle in einer Datenbank B (11.1.0.7) angewiesen ist, die über DB-Link (und Synonyme) angesprochen wird. Vor einiger Zeit ergaben sich für die Queries dieser Applikation Performance-Probleme, die zum Teil mit falschen Statistiken in Datenbank A zusammenhingen, aber z.T. auch auf fehlerhaften Einschätzungen der Cardinalities für die Dimensionstabelle in der Datenbank B beruhten. Meine erste Vermutung war, dass die falsche Cardinality durch die Korrelation von Spalteninhalten hervorgerufen wurde (in diesem Fall Produktgruppen und Lieferanten, die eben in vielen Fällen nicht unabhängig voneinander sind), weshalb ich extended statistics (und Histogramme) für die fragliche Spaltenkombination anlegte. Dadurch wurden die Cardinality-Schätzungen beim Zugriff in Datenbank B besser (wenn auch nicht völlig akkurat) und auch beim Remote-Zugriff von Datenbank A aus auf die einzelne Tabelle erhielt man die gleichen Angaben:

select /*+ full(t) */ count(*)
  from dim_table t
 where t.col1 = 421220
   AND t.col2 = 20

  COUNT(*)
----------
      2841

-------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|                 |     1 |     9 | 60397   (1)|
|   1 |  SORT AGGREGATE        |                 |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL    | DIM_TABLE       |   147 |  1323 | 60397   (1)|
-------------------------------------------------------------------------------

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

   2 - filter("A1"."COL1"=421220 AND "A1"."COL2"=20)

Note
-----
   - fully remote statement

Für das "fully remote statement" erwartet der CBO demnach 147 Sätze, was zwar von den tatsächlichen 2841 rows recht weit entfernt ist, aber immerhin mehr als die 1 (oder 2), die hier ohne die extended statistics erschien.

Für die komplexeren Queries der Applikation blieb es aber trotzdem bei einer deutlichen Unterschätzung der Cardinality für den Zugriff auf DIM_TABLE. Dazu hier ein minimales Beispiel:

select count(*)
  from fact_table f
     , dim_table d
 where f.col0 = d.col0
   and d.col1 = 421220
   AND d.col2 = 20
   and f.col3 = '01.12.2011'

  COUNT(*)
----------
     13562

----------------------------------------------------------------------------------------
| Id  | Operation                | Name                   | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                        |     1 |    54 |     8   (0)|
|   1 |  SORT AGGREGATE          |                        |     1 |    54 |            |
|   2 |   NESTED LOOPS           |                        |    24 |  1296 |     8   (0)|
|   3 |    REMOTE                | DIM_TABLE              |     2 |    78 |     4   (0)|
|   4 |    PARTITION RANGE SINGLE|                        |    12 |   180 |     2   (0)|
|*  5 |     INDEX RANGE SCAN     | IDX_FACT_TABLE         |    12 |   180 |     2   (0)|
----------------------------------------------------------------------------------------

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

   5 - access("F"."COL0"="A"."COL0" AND "F"."COL3"=TO_DATE(' 2011-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "COL0","COL1","COL2" FROM "DIM_TABLE" "A" WHERE "COL1"=421220 AND "COL2"=20
       (accessing 'DBL_XXX' )

Statt der Cardinality-Schätzung 147, die sich bei der einzelnen Ausführung des Remote SQL ergibt, erscheint hier eine 2, was im gegebenen Fall wahrscheinlich kein Problem ist, da der NL hier angemessen erscheint - aber manchmal einen geeigneteren HASH JOIN verhindern könnte. Meine Vermutung ist, dass die Extended Statistics nur beim "fully remote statement" herangezogen werden können, während das Join-Statement auf die Statistiken der Einzelspalten zurückgreift - möglicherweise, weil es in Datenbank A ausgeführt wird, also in 10.2.0.4, wo extended statistics noch gar nicht bekannt sind. Zur Prüfung dieser Annahme ein Versuch mit Ausführung in Datenbank B - erzwungen durch den DRIVING_SITE-Hint, zu dem Jonathan Lewis gelegentlich ein paar Hinweise gegeben hat.

select /*+ DRIVING_SITE(a) */ count(*)
  from fact_table f
     , dim_table d
 where f.col0 = d.col0
   and d.col1 = 421220
   AND d.col2 = 20
   and f.col3 = '01.12.2011'

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE       |                              |     1 |    38 |   395   (1)|
|   1 |  SORT AGGREGATE               |                              |     1 |    38 |            |
|   2 |   NESTED LOOPS                |                              |  1703 | 64714 |   395   (1)|
|   3 |    TABLE ACCESS BY INDEX ROWID| DIM_TABLE                    |   147 |  2352 |   100   (0)|
|*  4 |     INDEX RANGE SCAN          | IDX_DIM_TABLE                |   147 |       |     3   (0)|
|   5 |    REMOTE                     | FACT_TABLE                   |    12 |   264 |     2   (0)|
---------------------------------------------------------------------------------------------------

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

   4 - access("A1"."COL2"=20 AND "A1"."COL1"=421220)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   5 - SELECT "COL0","COL3" FROM "XXX"."FACT_TABLE" "A2" WHERE "COL3"=TO_DATE(' 2011-12-01
       00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "COL0"=:1 (accessing '!' )

Damit ist man dann wieder bei der 147, was meine Vermutung zu bestätigen scheint. Und damit ist das Problem in diesem Fall vermutlich nicht einmal durch einen Umzug der DIM_TABLE in Datenbank A lösbar, sondern erfordert eine grundsätzlichere Behandlung (oder einen kompletten Umzug der Applikation nach B).

Optimizer Trace für Queries im Cache

2011-12-04T23:34:00.001-08:00

Obwohl ich nur recht selten das Event 10053 (aka Optimzer Trace) verwende, um die Entscheidungen des CBO genauer nachvollziehen zu können, finde ich Maria Colgans Hinweis darauf, dass man ein solches Tracing für ein im Cache vorliegendes Statement in 11.2 einfach mit Hilfe von DBMS_SQLDIAG.DUMP_TRACE erzeugen kann, sehr interessant. Vor einigen Monaten hatte Greg Rahn dazu schon mal etwas geschrieben, aber ich hatte die Möglichkeit schon wieder völlig vergessen, obwohl ich sie hier verlinkt hatte ...

Die einzige ausführlichere Erläuterung zu Event 10053, die mir bekannt ist, findet sich (natürlich) in Jonathan Lewis' CBO-Buch.

MERGE und Transitive Closure

2011-12-03T05:04:00.001-08:00

Eigentlich hatte ich im folgenden Zusammenhang vermutet, einem Problem des CBO auf der Spur zu sein; es ist aber wohl doch eher ein Problem des vorliegenden ETL-Prozesses...

Dass der Optimizer dazu in der Lage ist, über transitive closure (also die Transitive Hülle) zusätzliche Prädikate zu ergänzen, kann man unter anderem in Jonathan Lewis' CBO-Buch nachlesen. Kurz zusammengefasst geht es darum, dass der CBO indirekte Abhängigkeiten erkennen kann:

select ...
  from a
     , b
 where a.id = b.id
   and a.id = 10

In einem solchen Fall erkennt der CBO, dass indirekt gilt:

and b.id = 10

Bei Jonathan Lewis erfährt man auch, dass die transitive closure in manchen Fällen unerwünschte Effekte hervorruft, da sie die Arithmetik des CBO durcheinander bringen kann, aber in den meisten Fällen ist sie recht nützlich. Man kann zu diesem Thema noch sehr viel mehr sagen, aber ich verweise in diesem Fall auf die üblichen Verdächtigen (neben dem Herrn Lewis wäre da noch an Randolf Geist zu denken), in deren Blogs, Büchern und Präsentationen man dazu allerlei Material finden kann.

Mein eigentliches Thema ist ein anderes: ich habe dieser Tage zum wiederholten Mal gesehen, dass die Prädikat-Weitergabe über transitive closure im Fall von MERGE-Statements nicht immer zu greifen scheint. Im fraglichen Fall (unter 10.2.0.4) wurde in der USING-clause des MERGEs auf einen Datums-Range eingeschränkt und diese Einschränkung wäre nützlich gewesen, um in der Zieltabelle eine Partition Elimination durchzuführen. Die zugehörige Query sah ungefähr folgendermaßen aus (hier anonymisiert und vereinfacht):

MERGE /*+ APPEND */ INTO FACT 
USING (SELECT A_DATE
            , ...
         FROM (SELECT DISTINCT 
                      A_DATE
                    , ...
                 FROM BASE
                WHERE A_DATE BETWEEN :B2 AND :B1 ) 
       ) SOURCE
    ON (     some_conditions
         AND FACT.A_DATE = SOURCE.A_DATE ) 
  WHEN MATCHED THEN UPDATE SET ...
  WHEN NOT MATCHED THEN INSERT ...

Die Query rief eine extreme Laufzeit hervor und der zugehörige Plan war:

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                |          |       |       |       | 57703 (100)|          |       |       |        |      |            |
|   1 |  MERGE                         | FACT     |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR               |          |       |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10001 | 41430 |  8981K|       | 57703   (7)| 00:13:04 |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     VIEW                       |          |       |       |       |            |          |       |       |  Q1,01 | PCWP |            |
|   5 |      HASH JOIN OUTER           |          | 41430 |  8981K|       | 57703   (7)| 00:13:04 |       |       |  Q1,01 | PCWP |            |
|   6 |       BUFFER SORT              |          |       |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|   7 |        PX RECEIVE              |          | 41430 |  5502K|       | 17408   (6)| 00:03:57 |       |       |  Q1,01 | PCWP |            |
|   8 |         PX SEND PARTITION (KEY)| :TQ10000 | 41430 |  5502K|       | 17408   (6)| 00:03:57 |       |       |        | S->P | PART (KEY) |
|   9 |          VIEW                  |          | 41430 |  5502K|       | 17408   (6)| 00:03:57 |       |       |        |      |            |
|  10 |           SORT UNIQUE          |          | 41430 |  2022K|  6520K| 17408   (6)| 00:03:57 |       |       |        |      |            |
|  11 |            FILTER              |          |       |       |       |            |          |       |       |        |      |            |
|  12 |             TABLE ACCESS FULL  | BASE     | 41430 |  2022K|       | 16966   (6)| 00:03:51 |       |       |        |      |            |
|  13 |       PX PARTITION RANGE ALL   |          |   895M|    71G|       | 39789   (7)| 00:09:01 |     1 |   362 |  Q1,01 | PCWC |            |
|  14 |        TABLE ACCESS FULL       | FACT     |   895M|    71G|       | 39789   (7)| 00:09:01 |     1 |   362 |  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------------

Demnach wurden also alle FACT-Partitionen von 1 bis 362 gelesen, obwohl der Partition-Key A_DATE als Join-Bedingung verwendet wurde und im USING auf einen Range eingeschränkt wurde. Die Einschränkung betraf übrigens genau einen Monat, was der Partitionierungs-Strategie der FACT-Tabelle entspricht. Eine Ersetzung der Bindewerte durch Literale änderte das Verhalten nicht.

An einem der folgenden Tage habe ich dann die semantisch redundante Sub-Query im Using herausgenommen und erhielt einen Plan, bei dem die transitive closure wieder zu funktionieren schien - und damit dann auch die Partition Elimination; so jedenfalls deutete ich die KEY-KEY-Einschränkungen (der zeitliche Abstand erklärt dann auch die veränderten Cardinalities):

MERGE /*+ APPEND */ INTO FACT 
USING (SELECT DISTINCT 
              A_DATE
            , ...
         FROM BASE
        WHERE A_DATE BETWEEN :B2 AND :B1 ) 
       ) SOURCE
    ON (     some_conditions
         AND FACT.A_DATE = SOURCE.A_DATE ) 
  WHEN MATCHED THEN UPDATE SET ...
  WHEN NOT MATCHED THEN INSERT ...

Liefert folgenden Plan:

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                         |           | 19294 |  5916K|       | 43473   (1)|       |       |        |      |            |
|   1 |  MERGE                                  | FACT      |       |       |       |            |       |       |        |      |            |
|   2 |   PX COORDINATOR                        |           |       |       |       |            |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)                  | :TQ10001  | 19294 |  4182K|       | 43473   (1)|       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     VIEW                                |           |       |       |       |            |       |       |  Q1,01 | PCWP |            |
|   5 |      NESTED LOOPS OUTER                 |           | 19294 |  4182K|       | 43473   (1)|       |       |  Q1,01 | PCWP |            |
|   6 |       BUFFER SORT                       |           |       |       |       |            |       |       |  Q1,01 | PCWC |            |
|   7 |        PX RECEIVE                       |           |       |       |       |            |       |       |  Q1,01 | PCWP |            |
|   8 |         PX SEND ROUND-ROBIN             | :TQ10000  |       |       |       |            |       |       |        | S->P | RND-ROBIN  |
|   9 |          VIEW                           |           | 19294 |  2562K|       |  4881   (9)|       |       |        |      |            |
|  10 |           SORT UNIQUE                   |           | 19294 |   942K|  3048K|  4881   (9)|       |       |        |      |            |
|  11 |            FILTER                       |           |       |       |       |            |       |       |        |      |            |
|  12 |             TABLE ACCESS FULL           | BASE      | 19294 |   942K|       |  4674   (9)|       |       |        |      |            |
|  13 |       PARTITION RANGE ITERATOR          |           |     1 |    86 |       |     0   (0)|   KEY |   KEY |  Q1,01 | PCWP |            |
|  14 |        TABLE ACCESS BY LOCAL INDEX ROWID| FACT      |     1 |    86 |       |     0   (0)|   KEY |   KEY |  Q1,01 | PCWP |            |
|  15 |         INDEX RANGE SCAN                | PK_FACT   |     1 |       |       |     0   (0)|   KEY |   KEY |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------

Allerdings hätte ich einen FTS-Zugriff auf FACT vorgezogen. Mit einem FULL-Hint für FACT bekam ich dann aber wieder den ursprünglichen Plan mit dem Zugriff auf alle Partitionen. Die Planänderung hatte also nichts mit meiner Umformulierung zu tun, sondern ergab sich aus den veränderten Statistiken. Die Elimination funktionierte im FTS-Fall erst wieder, als ich das DISTINCT entfernte, das vermutlich eingebaut werden musste, um "ORA-01427: single-row subquery returns more than one row" zu vermeiden (da die BASE-Tabelle offenbar tatsächlich Duplikate enthalten kann). Ohne DISTINCT und mit FULL-Hint ergibt sich:

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT               |          | 19294 |  5916K| 33548   (9)|       |       |        |      |            |
|   1 |  MERGE                        | FACT     |       |       |            |       |       |        |      |            |
|   2 |   PX COORDINATOR              |          |       |       |            |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)        | :TQ10002 | 19294 |  2562K| 33548   (9)|       |       |  Q1,02 | P->S | QC (RAND)  |
|   4 |     VIEW                      |          |       |       |            |       |       |  Q1,02 | PCWP |            |
|   5 |      FILTER                   |          |       |       |            |       |       |  Q1,02 | PCWC |            |
|   6 |       HASH JOIN OUTER BUFFERED|          | 19294 |  2562K| 33548   (9)|       |       |  Q1,02 | PCWP |            |
|   7 |        BUFFER SORT            |          |       |       |            |       |       |  Q1,02 | PCWC |            |
|   8 |         PX RECEIVE            |          | 19294 |   942K|  4674   (9)|       |       |  Q1,02 | PCWP |            |
|   9 |          PX SEND HASH         | :TQ10000 | 19294 |   942K|  4674   (9)|       |       |        | S->P | HASH       |
|  10 |           TABLE ACCESS FULL   | BASE     | 19294 |   942K|  4674   (9)|       |       |        |      |            |
|  11 |        PX RECEIVE             |          |  2237K|   183M| 28872   (9)|       |       |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH          | :TQ10001 |  2237K|   183M| 28872   (9)|       |       |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR    |          |  2237K|   183M| 28872   (9)|   KEY |   KEY |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL   | FACT     |  2237K|   183M| 28872   (9)|   KEY |   KEY |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

Demnach verhindert das DISTINCT die Weitergabe der Datumseinschränkung über transitive closure und das Partition Pruning (jedenfalls im FTS-Fall). Offenbar kann der CBO nicht erkennen, dass das DISTINCT keinen Einfluß auf die angegebenen Datums-Ranges haben kann. Die KEY-KEY-Einschränkungen im Fall des Index-Zugriffs sind übrigens das Ergebnis des NL-Joins, der ein dynamisches Pruning ermöglicht, wie Randolf Geist in seinem Kommentar erklärt. Um das Lesen der überflüssigen SOURCE-Partitionen zu vermeiden, hätte man demnach folgende Möglichkeiten:

Umbau der ETL-Logik zur Vermeidung von Duplikaten in der Tabelle BASE - dann wäre das DISTINCT verzichtbar.
Ergänzung einer zusätzlichen Zeiteinschränkung in der ON-clause (WHERE FACT.A_DATE BETWEEN :B2 AND :B1), so dass man sich nicht mehr auf die transitive closure verlassen muss.

Nachtrag 05.12.2011: ich habe den Eintrag heute Morgen noch mal überarbeitet, nachdem mir klar wurde, dass meine Schlußfolgerungen nicht wirklich schlüssig waren - erst im Anschluß daran habe ich gesehen, dass Randolf Geist bereits einen Kommentar zum Thema abgegeben hatte, der ebenfalls auf diese Fehleinschätzungen hinwies (und der dadurch vielleicht nicht mehr ganz zum Eintrag zu passen scheint).

Locks und Latches im SQL Server

2011-11-30T07:26:00.001-08:00

Bei Thomas Kejser gibt's eine sehr schöne Erläuterung zum Verhalten von Locks und Latches im SQL Server und zu den zugehörigen Analysemöglichkeiten, die die DMVs liefern.

Recyclebin

2011-11-29T01:23:00.001-08:00

Über den Recyclebin haben zuletzt Charles Hooper und Timur Akhmadeev geschrieben:

Hooper zeigt, wie es dazu kommen kann, dass in Zugriffsplänen Indizes mit BIN%-Namen erscheinen: diese Namen ergeben sich, wenn eine Tabelle über flashback wiederhergestellt wird - die Tabelle erhält dann wieder ihren urspünglichen Namen, aber die zugehörigen Indizes behalten ihren Recyclebin-Namen (den man natürlich auch wieder ändern kann). Wie üblich liefert der Autor alle erdenklichen Beispiele und Links zum Thema.
Ahkmadeev erwähnt einen Fall, in dem ein INSERT INTO ... VALUES-Statement für einen einzelnen Satz über eine Stunde lang hing und CPU verbrannte, weil es mit einem rekursiven DELETE FROM RECYCLEBIN$ kollidierte, das (vermutlich in einem Loop) mehr als eine Million mal ausgeführt wurde.

Beschreibende Index-Hints

2011-11-27T01:31:00.001-08:00

Jonathan Lewis erläutert in seinem Blog beschreibende Index-Hints, die seit Version 10 verfügbar sind. Dabei wird nicht der Name eines Index angegeben, sondern die Tabelle, der zugehörige Query-Block und die Reihenfolge der relevanten Spalten.

Details zu DBMS_METADATA

2011-11-26T11:47:00.001-08:00

Zwei interessante Notizen zu DBMS_METADATA:

Neil Johnson zeigt, dass für die Verwendung von DBMS_METADATA die SELECT_CATALOG_ROLE benötigt wird.
Gary Myers führt vor, wie man mit Hilfe von DBMS_METADATA auf die Hash-Version von Benutzer-Paßwörtern zugreifen kann (was in 11g über DBA_USERS nicht mehr möglich ist)

DROP-Operationen verhindern

2011-11-25T12:38:00.001-08:00

Carsten Czarski zeigt in seinem Blog, wie man das versehentliche Löschen von Datenbankobjekten mit Hilfe von Triggern erschweren kann.

LISTAGG

2011-11-24T12:19:00.001-08:00

Da ich heute - wahrscheinlich zum wiederholten Mal - die LISTAGG-Funktion in Releases unterhalb von 11.2 verwenden wollte, ehe mir einfiel, dass sie dort noch nicht existiert, hier ein Hinweis auf Tim Halls Erläuterung diverser Methoden zur Aggregation von Strings, die unter anderem auch die handliche WM_CONCAT-Funktion enthält. Weitere Hinweise auch zur Performance verschiedener Verfahren findet man bei Adrian Billington (bei dem ich dieser Tage andauernd lande), der auch einen Link auf Tom Kytes klassische STRAGG-Funktion liefert.

CBO-Schätzungen für PL/SQL

2011-11-22T11:44:00.001-08:00

Randolf Geist stellt in seinem Blog eine Liste von Artikeln zusammen, auf die er in seinen DOAG-Unconference-Sessions (die ich gerne gesehen hätte) Bezug genommen hat - vor allem auf Adrian Billingtons Beiträge zum Thema und auf Joze Senegacniks grundlegendes Kapitel im Band Expert Oracle Practices.

Index Organized Tables

2011-11-22T06:02:00.001-08:00

Jonathan Lewis hat ein paar Links zu Index Organized Tables (IOT) aufgelistet: vor allem führt er Martin Widlakes Artikelserie zum Thema auf.

Nachtrag 13.12.2011: Noch ein Hinweis vom Herrn Lewis: die including clause stellt nicht notwendig sicher, dass eine Spalte im Index-Segment landet (und nicht im Overflow-Segment), da Oracle die Spalten unter Umständen intern umstellt.

Cardinality Probleme bei Star Transformation

2011-11-19T12:38:00.001-08:00

Randolf Geist erläutert in seinem Blog, dass der CBO bei der Star Transformation zu falschen Cardinality-Schätzungen kommt, wenn mehrere Dimensionen in einer einzigen Tabelle zusammengefasst sind.

Instabile Pläne

2011-11-18T07:54:00.001-08:00

Kerry Osborne hat vor einiger Zeit ein extrem nützliches Script zur Bestimmung von Queries veröffentlicht, deren Ressourcen-Nutzung nach einer Planänderung dramatisch verändert ist. Hintergrundinformationen zum Thema (und weitere Analysescripte) findet man in seinem Blog. Ich habe das Script behutsam erweitert (und weniger behutsam formatiert) und meiner eigenen Sammlung hinzugefügt:

Basierend auf dem Ergebnis kann man dann in DBA_HIST_SQLSTAT prüfen, ob die Änderung eine Verbesserung oder eine Verschlechterung darstellte. Und anschließend kann mann dann mit Hilfe von DBMS_XPLAN.DISPLAY_AWR die im Automatic Workload Repository vorliegenden Pläne ermitteln.

Statistik-Erfassung für große partitionierte Tabellen

2011-11-16T02:26:00.001-08:00

Eine kurze Notiz zur Frage: wie sollten Statistiken für große partitionierte Tabellen aktualisiert werden? Darauf gibt es viele Anworten, zwei davon findet man im Blog der cbo-Entwickler. Darin erläutert Maria Colgan die Optionen für 10.2.0.4 und 11:

In Version 10.2.0.4 kann man mit Hilfe von DBMS_STATS.COPY_TABLE_STATS die Statistiken einer Partition in eine andere Partition kopieren, wobei die Statistiken der partition key Spalte sinnvoll angepasst werden; so wie ich's verstehe, bleiben die übrigen Spalten-Statistiken identisch, was aber in der Regel kein Problem sein sollte, da die meisten Spalten der für das Feature in Frage kommenden großen historisch partitionierten Faktentabellen pro Partition ähnliche Eigenschaften haben sollten

In Version 11 kann man die globale Statistik-Erhebung für große partitionierte Objekte auf INCREMENTAL umstellen, was bedeutet, dass nur noch die Deltas analysiert werden müssen und distinkte Anzahlen über eine Synopsis bestimmt werden. Damit sollte das Thema in Version 11 deutlich unproblematischer sein. Weitere Details zum Thema findet man bei Greg Rahn, der auch noch mal auf ein weiterführendes White-Paper verweist (das auch bei Frau Colgan erwähnt wird).

Doug Burns hat zum Thema der Statistiken für partitionierte Tabellen eine ganze Serie von Blog-Artikeln geschrieben, die neben weiteren relevanten Artikeln von Randolf Geist und Kerry Osborne im Oracle Sratchpad verlinkt sind. Dort stellt Jonathan Lewis fest: "It’s possible to spend ages talking about the best ways of collecting, or creating, statistics on partitioned tables." Und da hat er offenbar mal wieder Recht.

Nachtrag 18.01.2012: inzwischen hat Randolf Geist ein paar wichtige Hinweise zur inkrementellen Erhebung von Partitionsstatistiken veröffentlicht - und vor allem auch ein paar potentielle Probleme des Features aufgeführt.

Evil Events

2011-11-13T10:58:00.001-08:00

Tanel Poder erwähnt in seinem Blog ein paar Events, die Oracle offenbar moralisch bewertet, nämlich Inject Evil Literals und Inject Evil Identifiers. Nahe liegend ist dann die von Oracle vorgeschlagene Action zu den Fehlermeldungen: "never set this event".

TKPROF mit External Table anzeigen

2011-11-12T13:34:00.001-08:00

Adrian Billington erläutert, wie man TKPROF-Ausgabe-Dateien mit Hilfe des External Table Preprocessors in sqlplus verfügbar machen kann:

Using the simple techniques described in this article, we are now able to find a trace file, read it using the TRACEFILE_XT read-only external table, profile it with preprocessor external tables over TKProf or OraSRP and read the reports without leaving our IDE once. We can even read HTML reports in a browser window generated from within SQL*Plus without any additional keystrokes. For readers who work regularly with trace files, these are good productivity improvements!

Logging in Error-Tabelle

2011-11-11T02:47:00.001-08:00

Dieser Tage wurde ich an die Möglichkeit erinnert, Fehler bei DML-Operationen in einer DML-Error-Logging-Tabelle zu protokollieren, was ich mal wieder als Option betrachte, an PL/SQL vorbei zu kommen...

Der Database Data Warehousing Guide erläutert diese Option grundsätzlich folgendermaßen:

DML error logging extends existing DML functionality by enabling you to specify the name of an error logging table into which Oracle Database should record errors encountered during DML operations. This enables you to complete the DML operation in spite of any errors, and to take corrective action on the erroneous rows at a later time.

This DML error logging table consists of several mandatory control columns and a set of user-defined columns that represent either all or a subset of the columns of the target table of the DML operation using a data type that is capable of storing potential errors for the target column.

Ein detailliertes Beispiel für das Vorgehen gibt's bei Tim Hall; für den eiligen Nutzer hier eine kleinere Demonstration:

-- Anlage einer Tabelle TEST
create table test
( id number
, name varchar2(10)
, description varchar2(32)
, some_data number(8));

-- Anlage einer passenden ERROR-Table zur Tabelle TEST
-- mit Hilfe des dbms_errorlog-Packages
exec dbms_errlog.create_error_log (dml_table_name => 'test');

-- Die erzeugte ERROR-Tabelle besitzt das Präfix ERR$_
-- und enthält alle Spalten der Zieltabelle als VARCHAR2(4000)
-- und zusätzlich diverse Spalten zur Speicherung von Fehler-
-- Informationen
desc ERR$_TEST

Name                                      Null?    Typ
----------------------------------------- -------- -------------------
ORA_ERR_NUMBER$                                    NUMBER
ORA_ERR_MESG$                                      VARCHAR2(2000)
ORA_ERR_ROWID$                                     ROWID
ORA_ERR_OPTYP$                                     VARCHAR2(2)
ORA_ERR_TAG$                                       VARCHAR2(2000)
ID                                                 VARCHAR2(4000)
NAME                                               VARCHAR2(4000)
DESCRIPTION                                        VARCHAR2(4000)
SOME_DATA                                          VARCHAR2(4000)

Details zum Format der ERROR-Tabelle liefert der Administrator's Guide:

ORA_ERR_NUMBER$: Oracle error number
ORA_ERR_MESG$: Oracle error message text
ORA_ERR_ROWID$: Rowid of the row in error (for update and delete)
ORA_ERR_OPTYP$: Type of operation: insert (I), update (U), delete (D) Note: Errors from the update clause and insert clause of a MERGE operation are distinguished by the U and I values.
ORA_ERR_TAG$: Value of the tag supplied by the user in the error logging clause

Mit Hilfe der Klausel LOG ERRORS INTO ... kann man nun eine DML-Operation zum erfolgreichen Abschluss bringen, die sonst auf Fehler gelaufen wäre:

-- mein Insert klappt erst mal nicht
insert into test
select rownum
     , 'bla'
     , 'irgendwas'
     , rownum * 1000000
  from dual
connect by level < 10000;

     , rownum * 1000000
              *
FEHLER in Zeile 5:
ORA-01438: Wert größer als die angegebene Gesamststellenzahl, die für diese Spalte zulässig ist

-- aber mit der LOG ERRORS INTO Klausel geht's durch
insert into test
select rownum
     , 'bla'
     , 'irgendwas'
     , rownum * 1000000
  from dual
connect by level < 10000
log errors into err$_test ('insert') reject limit unlimited;

99 Zeilen wurden erstellt.
--> die übrigen 9900 Sätze landen dabei in ERR$_TEST

Offenbar wird dabei nur der erste auftretende Fehler protokolliert:

-- Löschung der Daten der ERROR-Tabelle:
truncate table err$_test;

-- dazu ein INSERT, das für mehrere Spalten unverdauliche Daten enthält:
insert into test 
values ( 1
       , 'blaaaaaaaaaaaaaaaaaaaaaa'
       , 'ssssssssssssssssssssssssssspppppppppppppppppppaaaaaaaaaaaaaaaaaaaaaaaaammmmmmmmmmmmmmmmm'
       , 123456789000000000000)
log errors into err$_test ('insert') reject limit unlimited;

0 Zeilen wurden erstellt.

--> was für die Spalten 2 - 4 nicht klappen kann

-- Die Metadaten zum Fehler sind dann:

select ora_err_number$
     , ora_err_mesg$
     , ora_err_rowid$
     , ora_err_optyp$
     , ora_err_tag$
  from err$_test;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                  ORA_ERR_ROWID$  ORA_ERR_OPTYP$  ORA_ERR_TAG$
--------------- ------------------------------ --------------- --------------- ---------------
          12899 ORA-12899: Wert zu groß für Sp                 I               insert
                alte "DBADMIN"."TEST"."NAME" (
                aktuell: 24, maximal: 10)

-- dann folgen in err$_test die Werte des gescheiterten INSERTs.

Die Dokumentation (in diesem Fall die SQL Language Reference) nennt noch folgende Fälle, in denen der Mechanismus nicht verwendbar ist - also ein Fehler auftritt und ein Rollback durchgeführt wird:

Verletzung von deferred constraints.
Direct-Path-Operationen, die eine unique constraint oder index violation hervorrufen.
Update oder Merge Operationen, die eine unique constraint oder index violation hervorrufen.

Für LONG, LOB und Objekt-Typen gelten spezielle Bedingungen, deren Aufzählung ich mir hier aber mit Verweis auf die Doku spare.

Auto DOP in 11.2

2011-11-09T03:04:00.000-08:00

Dieser Tage haben sich Uwe Hesse, Randolf Geist und Gwen Shapira mit dem neuen Auto Degree Of Parallelism (DOP) Feature in 11.2 beschäftigt.

Eine grundlegende Diskussion des Features liefert Frau Shapira in ihrem Blog:

at least in the white papers and presentations it sounds like a very attractive solution. There are two levels to Auto DOP:
Limited – when accessing tables and indexes that have been declared with parallel clause, Oracle will decide on the degree of parallelism based on the query and system resources.
Auto – Oracle will decide on degree of parallelism for every query. In addition two exciting new features are enabled: parallel statement queuing and in-memory parallel execution.

Der Herr Hesse erläutert die Wirkung des Parameters parallel_degree_policy und die daraus resultierende Wirksamkeit von Hints:

parallel_degree_policy=LIMITED will give you the DOP you request and compute an appropriate DOP only with a parallel degree of DEFAULT as an attribute of the table. Use this parameter if you trust that your applications/designers know why they use a certain DOP. parallel_degree_policy=AUTO will overrule any specific DOP you gave – except the new 11g parallel (n) Hint – and consider to do things in parallel for all tables even without a Hint or Degree.

Beim Herrn Geist geht's um die Beobachtung, dass ein parallel_degree_policy=auto die Verwendung von direct path inserts hervorrufen kann, was zwar in gewisser Weise folgerichtig ist, aber die üblichen Nebeneffekte des direct path hervorruft (INSERT oberhalb der HWM und "ORA-12838: cannot read/modify an object after modifying it in parallel"), wobei besonders ORA-12838 ungünstige Wirkungen haben kann:

An existing application logic might break because it attempts to re-access the object after the now direct-path insert within the the same transaction which will end up with an "ORA-12838: cannot read/modify an object after modifying it in parallel".

Bei den Herren Hesse und Geist gibt's die dort üblichen Testfällen, mit deren Hilfe die Effekte nachvollzogen und überprüft werden können.

Nachtrag 12.11.2011: In seinem Kommentar weist Randolf Geist darauf hin, dass Uwe Hesses Beobachtung der Ausschaltung von Parallel-Hints durch parallel_degree_policy=AUTO wahrscheinlich auf Bug 10628995 beruht. In seinem Kommentar zu Uwe Hesses Blog kommt Greg Rahn zur gleichen Einschätzung.

SQL Sentry Plan Explorer

2011-11-07T11:06:00.000-08:00

Dieser Tage bin ich auf den kostenlosen SQL Sentry Plan Explorer gestossen, ein Tool, mit dessen Hilfe sich Execution Plans im SQL Server deutlich klarer visualisieren lassen als mit den Bordmitteln im SSMS (die selbst aber schon ganz brauchbar sind). Das Tool lässt sich als Add-In zum SSMS einrichten und erfordert eine Installation von .Net 4.0. Zu den Features des Tools gehören das Plan Diagram mit einer klaren Hervorhebung der kostspieligsten Abschnitte eines Plans, der Join Tree mit einer Visualisierung der Tabellenverknüpfungen und nicht zuletzt der Plan Tree, der den Zugriffsplan so darstellt, wie man es aus Oracle z.B. vom dbms_display-Package kennt. Nett und - wie gesagt - kostenlos.

Mutex Waits

2011-11-04T03:29:00.000-07:00

Andrey Nikolaev hat mal wieder einen interessanten Artikel über das Verhalten von Mutexes veröffentlicht. Dieses Verhalten hat sich offenbar in 11.2 deutlich verändert, so dass Mutex Waits nun ziemlich gut analysierbar und damit auch optimierbar sind. Für das Verhalten vor 11.2 galt dabei Folgendes (die Aufzählungen sind dabei jeweils Zitate aus der Quelle):

“Cursor: pin S” was pure wait for CPU. Long “cursor: pin S” waits indicated CPU starvation.
Mutex contention was almost invisible to Oracle Wait Interface.
Spin time to acquire mutex was accounted as CPU time. It was service time, not waiting time.

In 11.2 gilt:

Oracle 11.2 Wait Interface computes “wait time” as a duration between the first spin and successful mutex acquisition. As a result we observe only one wait in v$system_event.
Oracle 11.2 mutex wait includes both spinning and waiting.
the 11.2.0.2 the “cursor: pin S” wait event no more starve CPU. This reduces probability of CPU thrashing due to mutex waits.

Es folgen noch allerlei Informationen zu den (underscore) Parametern, die das Verhalten der Mutexe steuern, aber die Details will ich hier nicht wiederholen

OWB und ODI

2011-11-02T09:18:00.000-07:00

Peter Scott von Rittman Mead Consulting verspricht eine Artikelserie, in der er die Unterschiede zwischen Oracle Warehouse Builder (OWB) und Oracle Data Integrator (ODI) erläutern will und liefert zunächst eine Zusammenfassung der konzeptionellen Gemeinsamkeiten und Unterschiede:

"Both ODI and OWB have a similar (I am being very simplistic here) three-component design of: a metadata repository, a development environment where the developer defines the processes and data flows and a runtime component that executes the code and flows."
Speicherung des Repositories:

OWB: Repository ist vorinstalliert in einer Oracle-DB
das ODI Repository wird mit Hilfe des Oracle Fusion Middleware’s Repository Creation Utility in einer unterstützten DB angelegt (nicht notwendig Oracle)

Standard-Operationen:

"with OWB the key parts of the IDE are those for the development of MAPPINGS and (optionally) the design of process flows to orchestrate mappings."
"In the ODI world think INTERFACES for mappings and PACKAGES for process flows. This is simplistic though as ODI also has PROCEDURES (code developed in one of the ODI supported languages) and LOAD PLANS (multiple packages orchestrated to execute in serial or parallel)"

Umsetzung der Operationen:

OWB mappings require the developer to include all of the components needed to facilitate the mapping – we connect source columns to target columns through a logic flow of joiners, filters, expressions, aggregates and a whole palette of other activities. Typically, this would generate a single, but large, SQL statement with much use of in-line views." (immer PL/SQL)
"ODI interfaces are simply about connecting source columns to target columns in a logical relationship (we also create expressions, joins and filters here) and allowing the physical implementation to be supplied by a knowledge module." (kann auch einfach SQL sein)

Meine Erfahrungen mit dem OWB sind sehr beschränkt und mit dem ODI habe ich noch nie ernsthaft gearbeitet, aber aus meiner Sicht sind die gravierendsten Einschränkungen des OWB (abgesehen von seiner fehlenden Zukunft), dass er oft recht suboptimales SQL (in PL/SQL-Verpackung) erzeugt und viele neuere Datenbankfeatures kalt lächeln ignoriert - und das scheint im Fall des ODI dann mit Hilfe des knowledge modules stärker beeinflußbar zu sein. Dass grafische ETL-Tools bei der Definition relativ einfacher Transformationen oft sehr umständliche GUI-Darstellungen erzeugen, ist vermutlich eher ein allgemeines Problem solcher Werkzeuge. Und dass der PL/SQL-Code des OWB in nahezu jedem Fall ausufernd groß wird, ist vermutlich nur für Kommandozeilen-Fetischisten wie mich ein Problem.

DBMS_SCHEDULER Grundlagen

2011-10-28T00:25:00.000-07:00

Dieser Tage habe ich mich zum ersten Mal ein wenig mit dem DBMS_SCHEDULER beschäftigt und nehme das zum Anlass, ein paar grundlegende Dinge aufzuschreiben und einige Links zu notieren. Grundsätzlich dient der Scheduler zur Definition Zeit- oder Event-gesteuerter Aktionen und bietet dabei deutlich umfangreichere Funktionen als DBMS_JOB - z.B. ist es möglich komplexe Zeitpläne und Ausführungs-Windows zu definieren, Abhängigkeiten anzugeben und ein detailliertes Monitoring der Ausführungen zu erzeugen. Hier nur ein ganz einfaches Beispiel:

-- Loeschung des Jobs (wenn vorhanden)
exec dbms_scheduler.drop_job (job_name => 'my_scheduler_test_job');

-- Job-Definition
begin
  dbms_scheduler.create_job (
    job_name        => 'my_scheduler_test_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN TEST_PROC; END;',
    start_date      => systimestamp,
    repeat_interval => 'FREQ=DAILY;BYHOUR=09;BYMINUTE=15',
    end_date        => null,
    enabled         => true,
    comments        => 'belangloser Test-Job zur Ausfuehrung einer belanglosen Test-Prozedur.');

end;
/

-- sofortiger Start des Jobs unabhaengig vom Zeitplan
exec dbms_scheduler.run_job('my_scheduler_test_job')

In der Job-Definition kann man die Aktion und den Zeitplan direkt angeben (wie im Beispiel), man kann aber auch zuvor definierte Schedules und Programme verwenden. Eine umfangreiche Darstellung der Möglichkeiten bietet Tim Halls unten verlinkte Artikelserie, während die beiden einführenden Artikel eher die Grundlagen der Verwendung des Schedulers beleuchten (allerdings deutlich heller, als ich das hier gemacht habe).

Oracle-Scheduler, Eine Alternative zu "professionellen" Schedulern? Sven Vetter erläutert in diesem Trivadis-Dokument die Verbesserungen des Schedulers in 10.2 und führt aus, was ihm noch fehlt
Jobsteuerung mit dbms_scheduler: einführender Artikel von Kathrin Hammerschmidt bei Ordix
Job-Steuerung in Oracle Datenbanken: einführender Artikel auf der Website von Held Informatik
Tim Halls Serie zu den Features des Schedulers in aktuellen Oracle-Versionen

Abhängigkeitsanalysen mit UTL_XML.PARSEQUERY

2011-10-27T05:31:00.000-07:00

Philipp Salvisberg erläutert in seinem Blog, wie man UTL_XML.PARSEQUERY als Hilfsmittel zum Parsen verwenden kann, um damit alle View-Spalten zu finden, denen eine bestimmte Spalte einer Basistabelle zugrunde liegt.

SQL_EXEC_ID

2011-10-25T06:52:00.000-07:00

Tanel Poder hat sich etwas intensiver mit den technischen Details der SQL_EXEC_ID beschäftigt und dabei - natürlich - ein paar recht interessante Beobachtungen gemacht; unter anderem liefert er eine Erklärung für den seltsamen Wert 16777216, über den ich mich auch gelegentlich schon gewundert hatte.

Planüberschreibung mit DBMS_SPM

2011-10-25T04:56:00.000-07:00

Dom Brooks zeigt in seinem Blog ein sehr handliches Beispiel dafür, wie man SQL Plan Baselines einsetzen kann, um den Zugriffsplan einer Query zu ändern, ohne das Statement anpassen zu müssen. Alles was man dafür benötigt, sind der (exakte) SQL_TEXT der ursprünglichen Query, sowie SQL_ID und PLAN_HASH_VALUE der optimierten Version. Interessant sind auch die ergänzenden Informationen des Folgeartikels Quick overview of loading plans into a baseline.

In diesem Zusammenhang interessant ist auch noch der Hinweis von Maria Colgan, dass für die Verwendung von SQL Plan Management (SPM) und den in DBMS_SPM enthaltenen Prozeduren keine zusätzliche Lizenzierung erforderlich ist. SPM ist Bestandteil der Enterprise Edition.

Florenz

2011-10-24T11:49:00.000-07:00

Von Fiesole aus gesehen.

Technical Papers des cbo Teams

2011-10-21T02:33:00.000-07:00

Das Team der cbo-Entwickler hat ein paar Links zu technischen Papers bereitgestellt, die sicher alle eine genauere Lektüre verdienen.

block_sample für gather_table_stats

2011-10-21T00:54:00.000-07:00

Dass man die Sample-Größe für die dbms_stats-Prozeduren seit Release 11 in aller Regel nicht mehr anpassen muss, da die AUTO_SAMPLE_SIZE sehr gute Resultate liefert, hat Greg Rahn schon vor einigen Jahren festgestellt:

Overall the 11g DBMS_STATS has been enhanced to gather stats in less time, but in my opinion the significant enhancement is to AUTO_SAMPLE_SIZE which yields near 100% sample accuracy in 10% sample time.

Trotzdem hatte ich mir zuletzt Gedanken darüber gemacht, ob man mit der Option block_sample die Laufzeit der Statistik-Erstellung signifikant reduzieren kann. Die Dokumentation erklärt diese Option folgendermaßen:

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

Ein kleiner Test zeigt, dass die Option tatsächlich einen deutlichen Einfluss auf die Leseoperationen der Statistikerfassung hat:

-- Anlage einer relativ großen Testtabelle
drop table big_t;

create table big_t
as
with
base_data
as
(
select rownum id1
  from dual
connect by level <= 1000000
)
,
mult
as
(
select rownum id2
  from dual
connect by level <= 30
)
select rownum id
     , 'aaa' col2
  from mult
     , base_data;

-- Statistikerhebung
exec dbms_stats.gather_table_stats(user, 'BIG_T')
 
exec dbms_stats.gather_table_stats(user, 'BIG_T', estimate_percent=>1)

exec dbms_stats.gather_table_stats(user, 'BIG_T', estimate_percent=>1, block_sample=>TRUE)

Die Ergebnisse für die angesprochenen Versionen sehen folgendermaßen aus:

Version	Parameter	Laufzeit
10.2.0.4	ohne	33.86 sec.
10.2.0.4	estimate_percent: 1	12.31 sec.
10.2.0.4	estimate_percent: 1; block_sampling	1.01 sec.
11.1.0.7	ohne	13.07 sec.
11.1.0.7	estimate_percent: 1	2.40 sec.
11.1.0.7	estimate_percent: 1; block_sampling	1.78 sec.

Dazu noch ein paar Kommentare:

die deutlichen Laufzeitunterschiede ergeben sich aus der unterschiedlichen I/O-Performance der beiden Testsysteme (sehr langsame Platten für 10.2.0.4)
ein 10046er Trace zeigt, dass die zugehörigen Queries mit der Klausel SAMPLE bzw. SAMPLE BLOCK ausgeführt werden.
mit SAMPLE BLOCK sinkt die Anzahl der erforderlichen LIOs und PIOs in beiden Versionen; Für die Queries mit SAMPLE werden alle Tabellenblocks gelesen, während SAMPLE BLOCK tatsächlich nur auf einen gewissen Prozentsatz liest, der ungefähr dem geforderten Wert entspricht (ermittelt mit AUTOTRACE in 11.1.0.7):

FTS ohne Sampling: 30812 consistent gets
FTS mit sample(1): 30812 consistent gets
FTS mit sample block (1): 327 consistent gets
FTS mit sample block (10): 3399 consistent gets

Für die Erstellung von Statistiken für sehr große Tabellen könnte die block_sample-Option demnach auch in Versionen >= 11 immer noch interessant sein, da sie die erforderlichen Leseoperationen dramatisch reduzieren kann. Das gilt aber natürlich nur dann, wenn sichergestellt ist, dass die Blocks ähnliche Verteilungsmuster besitzen wie die gesamte Tabelle. Die Qualität der mit Hilfe von Sampling erzeugten Statistiken wäre aber ohnehin noch mal ein anderes Thema.

Dynamic Sampling

2011-10-20T06:57:00.000-07:00

Heute wollte ich einer Query einen dynamic_sampling Hint mitgeben und fand bei Jonathan Lewis günstigerweise den Hinweis, dass man diesen Hint nicht pro Tabelle setzen muss, sondern auf Query-Ebene definieren kann.

Eine Liste mit einer Beschreibung der Sampling-Levels (und weitere Informationen zum Thema) findet man bei Maria Colgan.

Optimizer Statistics Präsentation bei Open World 2011

2011-10-19T09:37:00.000-07:00

Das Team der cbo Entwickler (bzw. Maria Colgan) hat bei der Oracle Open World 2011 offenbar eine sehr schöne Präsentation zur Erstellung und Rolle der Statistiken gehalten, und die zugehörigen Folien ins Netz gestellt. Hier eine kurze Liste mit Punkten, die mir erinnerungswürdig erscheinen:

dbms_stats

üblicherweise sollte man nur die ersten Parameter der GATHER_%_STATS-Prozeduren benötigen (Schema Name, Table Name, Partition Name)

Weitere Parameter:

Inkrementelle Statistiken: "Ability to accurate generate global statistics from partition level statistics"
Concurrent Statistics Gathering: "Ability to gather statistics on multiple objects concurrently under a GATHER_SCHEMA_STATS command"

Default-Werte der Prozeduren können auf verschiedenen Ebenen gesetzt werden
Sample Size: in 11g sollte man immer die default AUTO_SAMPLE_SIZE verwenden, da sie die Performance eines 10% Samples mit der Genauigkeit eines 100% Samples verbindet (dazu hatte Greg Rahn vor längerer Zeit gebloggt)

Index-Statistiken

"Index statistics are automatically gathered during creation and maintained by GATHER_TABLE_STATS"

Histogramme

"Default create histogram on any column that has been used in the WHERE clause or GROUP BY of a statement AND has a data skew" ("Relies on column usage information gathered at compilation time and stored in SYS.COL_USAGE$")
Cardinality-Berechnung:

für popular values ("Popular value means values that are the endpoint for two or more buckets") -> (Anzahl Endpoint Buckets/Gesamtanzahl Buckets)/Anzahl rows in der Tabelle
für non popular values ("Non-popular value means values that are the endpoint for only one bucket or are not an endpoint at all") -> DENSITY X * Anzahl rows in der Tabelle ("Density from 10.2.0.4 is calculated on the fly based on histogram information and is not the value show in USER_HISTOGRAMS")

Probleme:

Bind Peeking (in 11.2 durch Adaptive Cursor Sharing kein Problem mehr)
Nearly popular values ("Nearly popular value means the value is classified as
non-popular but the density calculation is not accurate for them"): werden unterschätzt; behebbar durch dynamic sampling hint im Statement)

Extended Statistics

für column groups: zur Darstellung von Korrelationen
für expressions (virtual columns)
automatische Erzeugung mit Hilfe von dbms_stats.seed_col_usage + Monitoring von Workload + dbms_stats.create_extended_stats

Performance der Statistik-Erstellung durch Parallelisierung

Parallelisierung für ein Objekt über Degree-Parameter ("Default is to use parallel degree specified on object"
paralleler Zugriff auf mehrere Objekte: gesteuert über Parameter CONCURRENT
Kombination beider Optionen möglich

Inkrementelle Erstellung globaler Statistken

"Typically gathering statistics after a bulk loading data into one partition would causes a full scan of all partitions to gather global table statistics" -> "Extremely time consuming"
Inkremental sorgt dafür, dass nur Partitionen gelsen werden, deren Daten geändert wurden

keine Statistiken für

Volatile Tables: Tabellen mit stark veränderlichen Daten (Queue-Tabellen)
GTT
Intermediate work tables: ETL-Hilfstabellen

Tabellentransfer mit dbms_datapump und DB-Link

2011-10-19T03:11:00.000-07:00

Laurent Schneider hat vor kurzem eine kleine Demonstration dazu geliefert, wie man einzelne Tabellen mit Hilfe von dbms_datapump transferieren kann, und auf diese Weise auch die abhängigen Elemente (Indizes, Constraints) befördert, die ein CTAS über DB-Link verlieren würde.

Beim Durchspielen des Beispiels gab's bei mir noch folgende Effekte:

von 10.2.0.4 nach 11.1.0.7 funktionierte der Transfer problemlos: da der Transfer-Job asynchron läuft, wird die Tabelle zwar sofort angelegt, aber der Datentransfer benötigt anschließend noch einige Zeit, so dass ein SELECT COUNT(*) erst einmal 0 Sätze liefert.
der umgekehrte Weg (von 11.1.0.7 nach 10.2.0.4) lieferte keinen Fehler, aber die Tabelle kam nicht an. Möglicherweise ist da noch ein Kompatiblitätsproblem im Spiel.

Auf jeden Fall ein interessantes Feature, das Transfer-Tasks deutlich handhabbarer machen könnte.

Dynamic Sampling für stark veränderliche Daten

2011-10-19T01:22:00.000-07:00

Randolf Geist erläutert in seinem Blog, in welchen Fällen dynamic sampling für "highly volatile data" (z.B. in einer global temporary table) geeigneter ist als eine grundsätzliche Erhebung der Statistiken, und vor allem unter welchen Umständen dynamic sampling nicht ohne Weiteres weiterhilft:

However, there is still a potential problem even when resorting to Dynamic Sampling. If the cursors based on Dynamic Sampling get shared between sessions then they won't be re-optimized even if a GTT in one session is completely different from the one of the session that created the shared cursor previously.

Eine mögliche Lösung solcher Probleme liefert die Verwendung von Row Level Security (RLS) durch Ergänzung redundanter Prädikate, die dafür sorgen, dass Queries sich textuell unterscheinden (ergänzt wird 1=1; 2=2 oder dergleichen).

Darüber hinaus werden auch noch die Effekte von Adaptive Cursor Sharing und Cardinality Feedback diskuttiert und - wie beim Herrn Geist üblich - anhand zahlreicher ausführlicher Testfälle überprüft.

SSAS 2008 R2 Performance-Guide

2011-10-17T01:40:00.000-07:00

Der SSAS-Performance Guide für Version 2008 R2 ist seit einigen Tagen verfügbar. Chris Webb fasst die Bedeutung des Guides prägnant zusammen: "if you are serious about SSAS you need to read it".

Hier noch eine Sammlung von Punkten, die mir erinnerungswürdig erscheinen (ohne jeden Anspruch auf Vollständigkeit):

Performance-Vorteile geeigneter Attribute Relationships (S. 11)

"Cross products between levels in the hierarchy do not need to go through the key attribute. This saves CPU time during queries."
"Aggregations built on attributes can be reused for queries on related attributes. This saves resources during processing and for queries."
"Auto-Exist can more efficiently eliminate attribute combinations that do not exist in the data."

Flexible vs. Rigid Relationships (S.12)

"When a change is detected during process in a flexible relationship, all indexes for partitions referencing the affected dimension (including the indexes for attribute that are not affected) must be invalidated. This is an expensive operation and may cause Process Update operations to take a very long time. Indexes invalidated by changes in flexible relationships must be rebuilt after a Process Update operation with a Process Index on the affected partitions; this adds even more time to cube processing."

Reference Dimensions (S. 14)

können materialisiert werden
Probleme beim ProcessUpdate: "if you run a process update on the intermediate dimension, any changes in the relationships between the outer dimension and the reference will not be reflected in the cube. Instead, the original relationship between the outer dimension and the measure group is retained"

Large Dimensions (S. 18)

"If a dimension contains attributes where the total size of the string values (this includes translations) exceeds 4 GB, you will get an error during processing." Für Denali soll das Limit beseitigt werden.

Partition Slicing (S. 20)

Auto Slice: "when Analysis Services reads the data during processing, it keeps track of the minimum and maximum attribute DataID reads. These values are used to set the slice when the indexes are built on the partition." + "auto slice typically works best if the data contained in the partition maps to a single attribute value. When that is the case, the maximum and minimum DataID contained in the slice will be equal and the slice will work efficiently."

Partition Sizing (S. 22)

"partition size alone does not have a substantial impact on query speeds"
sollte sich an administrativen Gesichtspunkten orientieren
die BIDS-Warnung vor Partitionen mit mehr als 20M rows ist bedeutungslos

Relational Data Source Design (S.26ff.)

"Use a Star Schema for Best Performance"
"Consider Moving Calculations to the Relational Engine"
"Use Views"

Query Processor Cache (S. 35)

Query Context, Session, Context, Global Context: "Even though a query references all three contexts, it will typically use the cache of a single context. This means that on a per-query basis, the query processor must select which cache to use. The query processor always attempts to use the broadly applicable cache depending on whether or not it detects the presence of calculations at a narrower context. If the query processor encounters calculations created at query time, it always uses the query context, even if a query also references calculations from the global context (there is an exception to this –
queries with query calculated members of the form Aggregate() do share the session cache)."
offenbar gibt es sehr viele Fälle, in denen der Global Cache nicht verwendet werden kann (MDX functions that are locale-dependent; "use of cell security; functions such as UserName, StrToSet, StrToMember, and StrToTuple; or LookupCube functions in the MDX script or in the dimension or cell security definition disable the global cache. That is, just one expression that uses any of these functions or features disables global caching for the entire cube."; visual totals; "Queries that use the subselect syntax (SELECT FROM SELECT) or are based on a session subcube (CREATE SUBCUBE)"; Arbitrary shapes); es bietet sich an, mit dem SQL Profiler zu prüfen, ob Standard-Excel-Zugriffe den Global Cache verwenden können, oder ob einer der Problemfälle dagegen spricht.

Data Retrieval (S.38)

Storage Engine (SE) sucht nach Daten in folgender Reihenfolge:

SE Cache
Aggregationen (I/O from disk oder OS-Cache)
Partitionsdaten (I/O from disk oder OS-Cache)

"storage engine cache is also known as the data cache registry because it is composed of the dimension and measure group caches that are the same structurally"

vorhandene Inhalte können aggregiert oder gesplittet werden
bei Memory-Engpässen wird der Cache geleert

Aggressive Data Scanning (S. 39): SSAS führt manchmal recht massive Prefetch-Operationen durch (in der Annahme, dass der Anwender die Daten schon noch brauchen wird). Deaktivieren kann man das Prefetching mit "Disable Prefetch = 1" im Connection-String.
Subspace Computation (S.40ff.): erläutert die Unterschiede zwischen Cell-by-cell evaluation und subspace computation.

Cell-by-cell evaluation: "The 10 cells for [2005, All Products] are each evaluated in turn. For each, the previous year is located, and then the sales value is obtained and then added to the sales for the current year." (bei sparse data wird viel unnötige Arbeit geleistet; außerdem werden Zugriffe unnötig wiederholt)
"In subspace computation, the engine works its way down an execution tree determining what spaces need to be filled."

Expensive vs. Inexpensive Query Plans (S. 42): ein Plan ist expensive, wenn er Cell-by-cell evaluation enthält: IIf, CASE, and IF functions können expensive query plans hervorrufen. Eine Liste mit Gründen für eine Cell-by-cell evaluation folgt aus Seite 49ff.

Optimizing MDX (S. 45ff.): liefert diverse Hinweise zur Query-Optimierung (Cache-Effekte ausschalten; SQL Profiler nutzen; Unterscheidung zwischen SE und FE Events; binäre Vereinfachung von Queries etc.) und auch einige wichtige Links. Hier nur die Punkte, diemir halbwegs neu waren:

Avoid Assigning Nonnull Values to Otherwise Empty Cells (S. 50): leuchtet ein, denn SSAS "is very efficient at using sparsity of the data to improve performance. Adding calculations with nonempty values replacing empty values does not allow Analysis Services to eliminate these rows."
Sparse/Dense Considerations with “expr1 * expr2” Expressions (S. 51): die Expression mit der höheren Anzahl von NULLs sollte vorne stehen: entspricht dem Vorgehen bei Nested Loops Joins, wo ebenfalls die kleinere Menge die Schleife treiben sollte.
IIF-Optimierung mit den Hints EAGER und STRICT (S. 53).
NON_EMPTY_BEHAVIOR: in 2008 in vielen Fällen nicht mehr nützlich (= wird oft ignoriert), und manchmal sogar ein Problem ("Eliminate it from the MDX script and add it back after performance testing demonstrates improvement")

Aggregations (S. 60ff.)

unnötige Aggregationen schädigen den Cache: "adding unnecessary aggregations can worsen query performance because the rare hits move the aggregation into the file cache at the cost of moving something else out"; außerdem: "there is a direct correlation between the number of aggregations and the duration for the Analysis Services storage engine to parse them"
Aggregations-Zugriffe werden im Profiler durch das "Get Data From Aggregation event" ausgewiesen

Scale-Out (S. 69): Verteilung auf mehrere Server; z.B. zur Vermeidung des Blockierens durch langlaufende Queries.
Tuning Processing Performance (S. 70ff.)

Dimension Processing

ProcessUpdate (S. 76)

erfordert Neuaufbau von flexible aggregations: "Note that ProcessUpdate drops invalid aggregations and indexes, requiring you to take action to rebuild the aggregations in order to maintain query performance. However, flexible aggregations are dropped only if a change is detected."
kann sehr teuer sein: "When ProcessUpdate runs, it must walk through the partitions that depend on the dimension. For each partition, all indexes and aggregation must be checked to see whether they require updating. On a cube with many partitions, indexes, and aggregates, this can take a very long time. Because this dependency walk is expensive, ProcessUpdate is often the most expensive of all processing operations on a well-tuned system, dwarfing even large partition processing commands."

ProcessAdd (S. 77):

"typically runs much faster than ProcessUpdate."
mit Default Konfiguration gibt's Fehlermeldungen duplicate key beim Processing ("caused by the addition of non-key properties that already exist in the dimension": Anpassung der Fehlerkonfiguration erforderlich)

Tuning

Reduzierung der Dimensionsattribute
Verzicht auf Bitmap Indexes
ByTable Processing: liefert notwendigerweise duplicate key Fehler

Splitting Processing Index and Process Data (S.84): wird immer noch empfohlen

Die Zukunft des OWB

2011-10-17T01:06:00.000-07:00

Dani Snider von Trivadis gibt eine kurze Zusammenfassung zur aktuellen strategischen Planung der Oracle-ETL-Landschaft. Offenbar hat der Oracle Warehouse Builder (OWB) keine Zukunft mehr, was sich allerdings schon seit einiger Zeit andeutete:

OWB 11g Release 2 will be the final release of Oracle Warehouse Builder. No major enhancements are planned for this tool. Support, patches and bug fixes will still be delivered for the next years. OWB 11.2 will be supported for the whole lifecycle of Oracle Database 12c, so there is no hurry to switch to ODI as soon as possible. But the strategic ETL product of Oracle is ODI, and new customers are recommended to use this tool.

Dani Snider sieht in diesem Zusammenhang vor allem zwei offene Punkte:

Eine kurzfrsitige Migration in Richtung ODI ist mangels Toolunterstützung offenbar nicht angeraten.
Abgesehen davon sind die Lizenzkosten für den ODI relativ hoch, was ein Anlaß zur Verwendung von ETL-Tools anderer Anbieter sein könnte ("There are several good open source ETL products on the market.")

Partition Exchange + Indizierung

2011-09-28T01:48:00.000-07:00

Über Partition Exchange hatte ich hier vor einiger Zeit etwas geschrieben, musste mich dieser Tage aber noch mal etwas ernsthafter mit dem Thema beschäftigen, und kam dabei zu folgenden Ergebnissen:

Wenn man im "ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ..." keine Klausel "INCLUDING INDEXES" angibt, dann werden alle auf der Austauschtabelle aufgebauten Indizes beim Exchange UNUSABLE.
Beim Aufbau von Indizes muss man den Grad der COMPRESSION berücksichtigen, da man sonst auf "ORA-28665: Tabelle und Partition müssen dasselbe Komprimierungsattribut haben" trifft. Der Fehlertext ist somit also etwas unpräzise, da auch Indizes berücksichtigt werden müssen.

Und noch eine harmlose Ergänzung: zum Monitoring des Stands der Index-Erzeugung kann man neben dem SQL-Monitor, v$session_longopsund den workare-Views auch noch dba_segments heranziehen:

select *
  from dba_segments
 where segment_type = 'TEMPORARY';

liefert Informationen zur Größe des im Aufbau befindlichen Index, der zunächst als temporäres Objekt mit einer technischen Id erzeugt wird, ehe daraus dann das permanente Segment wird.

Iggy Fernandez über DB-Design und Features

2011-09-28T01:35:00.000-07:00

Die Überschriften der Einträge hier werden allmählich unübersichtlicher ...

Iggy Fernandez hat zuletzt einige interessante Artikel geschrieben, die ich hier summarisch verlinke:

Lesson 1 of 40: Physical Database Design for Oracle Databases: Necessity and Definition mit einer recht knappen Unterscheidung von logischem und phsyikalischem DB-Design ("Logical database design is the process of designing normalized database tables." vs. "Physical database design is the process of designing indexes, partitions, clusters, and materialized views.")
Lesson 2 of 40: Physical Database Design for Oracle Databases: Non-Necessity? behandelt die Frage, ob man sich angesichts niedriger Hardwarepreise die Mühe der Zugriffsoptimierung sparen kann. Die These wurde dieser Tage von Bert Scalzo vertreten (“So, with such cheap hardware, it might be a wiser business bet to throw hardware at some solutions sooner than was done in the past.”), wobei Jonathan Lewis bereits darauf hinwies, dass das wichtigste Wort in diesem Satz das "sooner" ist. Die meisten extremen Performance-Probleme, denen ich begegne, haben erst mal recht wenig mit der Hardware-Leistung zu tun und sehr viel mit dem Design.
Unconventional Wisdom: Do we probably need RAC, Exadata, Oracle Database 12c, MySQL, certification, and Oracle user groups? verweist auf Mogens Norgaards älteren Artikel zur Frage, wer eigentlich RAC brauche, die dort mit einem klaren "(fast) niemand" beantwortet wurde - und beschäftigt sich im Anschluß daran mit weiteren technischen und Marketing-Themen, deren praktische Relevanz begutachtet wird.

Wenn der Herr Fernandez seine Drohung wahr macht und tatsächlich 40 Lessons liefert, werde ich mir die Links aber vermutlich irgendwann sparen ...

EXISTS im SELECT

2011-09-26T12:18:00.000-07:00

Rob van Wijk, dessen Blog ich - wie ich vielleicht schon mal erwähnt habe - sehr schätze, hat mal wieder etwas Interessantes angesprochen: man kann EXISTS nicht nur im WHERE, sondern in einer searched case expression auch im SELECT verwenden, was in bestimmten Fällen effizienter sein kann als ein entsprechender OUTER JOIN. Wie immer beim Herrn van Wijk gibt's aussagekräftige Beispiele und eine einleuchtende Erklärung des Verhaltens.

Index Rebuilds und der Clustering Factor

2011-09-25T08:23:00.000-07:00

Nächstes Quiz des Herrn Foote, diesmal zur Frage, wann ein Index Rebuild den Clustering Factor ändern kann. Im Grunde ist die Antwort: nie, aber es gibt doch ein paar Spezialfälle, die man als Ausnahmen von der Regel betrachten kann. Grundsätzlich gilt:

So for an index rebuild to actual have an impact on the CF on an index, means either the rows in the table needs to change or the order of the index entries needs to change.
However, when we typically rebuild an index, it has no impact at all on the table and so can’t possibly change the order of the rows there. Additionally, no matter how fragmented or inefficient the index structure might be, an index rebuild doesn’t change the order of the index entries either as they’re always sorted within the index in the order of the indexed columns.

Die Ausnahmen der Regel sind Indizes, die von REVERSE auf NOREVERSE oder umgekehrt umgestellt werden - was aus meiner Sicht aber eigentlich etwas Anderes ist als ein einfacher Neuaufbau. Ein anderer Spezialfall wäre die Definitionsänderung eines FBI - aber auch das ist kein einfaches Rebuild. Damit kann man für alle praktischen Fälle wohl doch bei der Regel bleiben, dass nur ein Neuaufbau der Tabelle den Clustering Factor eines Index ändern kann.

Oracle 11.2.0.3

2011-09-23T09:28:00.000-07:00

Greg Rahn weist darauf hin, dass das Patchset 11.2.0.3 für diverse Platformen veröffentlicht wurde. Laut New Feature Guide liegen die Verbesserungen vor allem in den Bereichen ACFS, XML und OWB.

Recovery Area

2011-09-23T05:56:00.000-07:00

Uwe Hesse erläutert in seinem Blog sehr ausführlich, warum eine Recovery Area empfehlenswert ist. Wenn ich mich mal wieder als DBA ausgebe, wäre das ein Thema, das intensivere Beschäftigung verdiente.

Rebuild Partitioned Index

2011-09-23T01:22:00.000-07:00

Da ich die Syntax mit schöner Regelmäßigkeit anderswo suche, schreibe ich sie mal hier auf: um eine Index-Partition UNUSABLE zu setzen bzw. wieder neu aufzubauen, kann man folgende Kommandos verwenden:

alter index ... modify partition ... unusable;
alter index ... rebuild partition ...;

Alternativ kann man auch alle lokalen Indizes einer Partition auf einmal auf UNUSABLE setzen bzw. neu aufbauen:

alter index ... modify partition ... unusable local indexes;
alter index ... modify partition ... rebuild unusable local indexes;

Ein kleines Beispiel zum Thema liefert Tom Kyte.

Leider kann man einen als UNIQUE definierten Index vor einem Massendaten-Ladevorgang nicht über diesen Weg deaktivieren, um ihn später wieder aufzubauen, da das INSERT dann gegen einen ORA-01502 ("index 'string.string' or partition of such index is in unusable state") läuft - Details zu diesem Problem finden sich hier.

Lebenszeit von Tablespace-Quotas

2011-09-22T11:56:00.000-07:00

In Stefan Oehrlis Blog findet sich ein interessanter Hinweis: wenn man einen Tablespace löscht und später wieder einen gleichnamigen TS anlegt, dann werden Quotas, die für den alten TS galten auch wieder für den neuen wirksam. Klingt für mich wie eine fragwürdige Konstruktion.

dbms_xplan.display_cursor und plan statistics

2011-09-21T08:36:00.000-07:00

Kyle Haileys Blog ist auch eine dieser Ressourcen, aus denen man eigentlich jeden Beitrag verlinken kann... Diesmal liefert der Herr Hailey eine detaillierte Beschreibung der display_cursor-Routine aus dbms_xplan, erläutert den Hint gather_plan_statistics und weitere Möglichkeiten, die erweiterten row source execution stats zu aktivieren, und liefert schließlich eine Query, mit der man aus v$sql_plan_statistics_all die Hinweise auf massive Abweichungen zwischen E-Rows und A-Rows komfortable visualisieren kann.

SSAS Maestros

2011-09-21T04:17:00.000-07:00

Microsoft hat eine erste Gruppe von SSAS-Spezailisten mit dem Adelsprädikat eines SSAS-Maestros ausgezeichnet. Bei der Titelgebung solcher Auszeichnungen scheint Kreativität gefragt zu sein - mal sehen, wer als erster seine Erzmagier, Diakone und Kardinäle benennt. Immerhin liefert mir der Beitrag ein oder zwei neue Blogs für meinen Reader.

Abhängigkeit von Features vom STATISTICS_LEVEL

2011-09-19T12:58:00.000-07:00

Christian Antognini weist in seinem Blog auf die Abhängigkeit der Erhebung diverser elaborierter Statistiken vom STATISTICS_LEVEL hin. Neben den in V$STATISTICS_LEVEL aufgeführten Statistiken sind seinen Beoachtungen nach auch Cardinality Feedback und Adaptive Cursor Sharing vom STATISTICS_LEVEL abhängig.

Deutschsprachige Oracle-Webseiten

2011-09-17T14:32:00.000-07:00

Hier mal der Versuch, jene Liste von deutschsprachigen Oracle-Ressourcen zu erstellen, die ich schon häufiger anderswo gesucht habe:

Blogs

Blogs

SQL und PL/SQL in Oracle: Carsten Czarskis lesenswertes Blog mit z.T. sehr ausführlichen technischen Erläuterungen zu weniger bekannten DB-Features.
oraconsult Oracle Blog: Blog von Felix Castillo Sanchez zu Performance-Themen.
Oraculix: Blog von Uwe Küchler.
Oracle SQL und PL/SQL: Blog von Thomas Uhren mit mathematischem und statistischem Schwerpunkt.
Dani Rey's Oracle Stuff: Blog von Dani Rey (die Mehrzahl der Artikel ist englischsprachig - dabei aber sehr interessant).
My Technical Blog: von Christian Klisch
Alexander Kornbrust Oracle Security Blog dt.: deutschsprachiger Blog von Alexander Kornbrust zu Security-Themen. Die Inhalte weichen von denen des englischsprachigen Blogs des Herrn Kornbrust ab.
Oracle TEXT: Tipps, Tricks, Best Practice: Blog zu Oracle-Text-Themen von Carsten Czarski und Ulrike Schwinn.
Geodaten für Alle: Oracle Spatial: noch ein Blog, an dem der Herr Czarski mitarbeitet - diesmal zum Thema Oracle Spatial.
Rund um Oracle APEX: Apex-Blog von Bernhard Fischer-Wasels.
Friedhold's Oracle World: Blog von Friedhold Matz mit Schwerpunkt Oracle Maps.
Björn's Weblog: Blog des Oracle Ace Director Björn Bröhl (aktuell Trivadis, früher Opitz).
Streetkiter: Blog von Johannes Ahrends.
mpaege: Blog von Michael Paege (Opitz).
ORACLE Datenbank: Blog von Christian Trieb (Vizepräsident der DOAG) mit sehr vielen Veranstaltungs-Hinweisen und DOAG-Informationen.
Sven's Technik-Blog: Blog von Sven Vetter (Trivadis).
Talk2Gerd: Blog von Gerd Volberg.
DOAG BLOG Aggregator: ein Aggregator, der diverse der oben genannten Blogs zusammenfasst.
German Oracle Blog Aggregator: ein Blog-Aggregator von Norbert Henz, der ebenfalls einige der bereits genannten Blogs - nun ja: aggregiert.
Trivadis: Gruppen-Blog der Trivadis Mitarbeiter zu Oracle, SQL Server, Sharepoint und vielen anderen Themen.

Foren

Webseiten

DOAG:die Webseite der Deutschen ORACLE-Anwendergruppe.
MuniQSoft: Tipps und Tricks der Münchener Beratungsfirma der Herren Wesnitzer und Patzwahl. Vor einigen Jahren gab's hier auch ein recht lebendiges Oracle-Forum.
Opitz Consulting: Eine Sammlung von Whitepapers.
Held Informatik: Tipps und Tricks.
Tipps und Tricks für Oracle Datenbanken: Tipps und Tricks von Ralf Durben.
Communities der Business Unit ST-PCM (Server Technology - Platforms Competition Modernization): Eine Liste von Communities, die von Mitarbeitern von Oracle-Deutschland geleitet werden.

Oracle DBA Community: technische Artikel von diversen Mitarbeitern von Oracle Deutschland.
Oracle APEX Community: Neuigkeiten, Tipps und Links zu APEX.
Oracle DWH Community: über die Download-Seite kommt man zu diversen Vorträgen und Artikeln.

Oracle SQL Tutorial: ein einfaches SQL-Tutorial von Taktum Software.
PL/SQL Tutorial: ein recht umfangreiches PL/SQL Tutorial von Martin Bulinski. Die Webseite enthält auch noch weitere Artikel zu PL/SQL, SQL und zur DB-Administration.
Use the Index, Luke!: Markus Winands kenntnisreiche "Gratis-Anleitung zu SQL Indizierung und SQL Tuning für Entwickler" (deutsch und englisch).

Die Liste bleibt relativ kurz, denn die meisten renommierten deutschsprachigen Oracle-Spezialisten schreiben englischsprachige Blogs. Extrem alte Webseiten, die offensichtlich nicht mehr aktualisiert werden, habe ich nicht berücksichtigt.

Bisher ist die Liste eher eine Draft-Version, die sich sicher noch erweitern lässt - wenn jemand andere Seiten kennt, die hier fehlen, würde ich mich über entsprechende Kommentare freuen.

Nachtrag 19.09.2011: ergänzt um vier Links aus dem Kommentar von Andreas und um den Hinweis auf die Oracle Communities aus Claus Jandauschs Kommentar.

Data Warehouse Architektur

2011-09-17T13:14:00.000-07:00

Thomas Kejser formuliert in seinem Blog einen interessanten Architekturvorschlag für Data Warehouses, der für mich wie eine behutsame Weiterentwicklung der Kimballschen Überlegungen aussieht. Man findet dort auch noch einen Link zum Kimball Forum, wo die Frage von Normalisierung/Denormalisierung im DWH-Kontext offenbar immer noch als Glaubenskrieg diskutiert wird.

Ebenfalls aus dem Microsoft-BI-Kosmos stammen die Überlegungen, die der Data Warehouse Junkie Marcel Franke in seinem Blog entwickelt.

ORADEBUG als Sicherheitsrisiko

2011-09-17T12:49:00.000-07:00

Alexander Kornbrust erläutert in seinem Blog, welche - im Hinblick auf Security-Fragen gefährlichen - Optionen ORADEBUG zur Verfügung stellt.

Row Cache Lock

2011-09-16T07:08:00.000-07:00

Dieser Tage ist mir folgendes Phänomen begegnet: zu einer großen Tabelle existierte ein Index für die Spalten des Primary Keys, wobei der Primary Key selbst disabled war. Ohne intensiver darüber nachzudenken, hatte ich angenommen, dass die Aktivierung des PKs sich darauf beschränken würde, zu prüfen, ob ein passender Index exisitiert - aber offenbar ist anschließend noch mehr zu tun. Meine Vermutung war, dass ein weiterer FTS stattfinden müsste, aber ein Blick in v$session_longops konnte das nicht bestätigen. Genauer gesagt konnte v$session_longops gar nichts bestätigen oder widerlegen, da die Abfrage von der Session, in der die PK-Aktivierung lief, geblockt wurde. Das Verhalten lässt sich mit einem einfachen Test stabil reproduzieren (hier in 11.1.0.7):

-- Löschung und Neuanlage einer Testtabellen mit 10M rows
drop table big_t purge;

create table big_t
as
with
base_data
as
(
select rownum id1
  from dual
connect by level <= 1000000
)
,
mult
as
(
select rownum id2
  from dual
connect by level <= 10
)
select rownum id
     , 'aaaaaaaaa' col2
  from mult
     , base_data;

alter table big_t modify id not null;

create index big_t_idx on big_t(id);

alter table big_t add constraint big_t_pk primary key (id) disable;

alter table big_t enable constraint big_t_pk;

Tabelle wurde geändert.

Abgelaufen: 00:00:17.26

Ein gleichzeitiger Zugriff einer zweiten Session auf v$session_longops (nicht aber auf viele andere v$-Views) wird blockiert, und in einer dritten Session kann man dazu in v$session_wait folgende Informationen finden:

SID       SEQ# EVENT            
--- ---------- -----------------
140        110 row cache lock

In Dion Chos (auch sonst sehr lesenswertem) Blog findet man eine Erläuterung dazu, wie man den holder eines row cache locks mit Hilfe von v$rowcache_parent ermitteln kann, aber in meinem Fall genügte ein Blick in v$session, um die blocking_session zu bestimmen. In der Dokumentation habe ich dafür zwar die Kurzbeschreibung "The session is trying to get a data dictionary lock" gefunden, aber keine grundsätzlichere Erklärung (kann auch sein, dass mein Suche nicht besonders systematisch war). Offenbar handelt es sich um ein Lock im dictionary cache, aber wieso es für den v$session_longops-Zugriff relevant ist aber nicht für Zugriffe auf ähnliche Objekte, kann ich nicht sagen.

Was ich immerhin herausgefunden habe, ist, dass die PK-Aktivierung ohne Verzögerung erfolgt, wenn der Index zur PK-Unterstützung als UNIQUE definiert ist, also:

create unique index big_t_idx on big_t(id);

Das ist durchaus einleuchtend - offenbar muss die Eindeutigkeit im Fall des non-unique Index erst explizit geprüft werden -, erklärt die Wirkung des "row cache lock" aber noch nicht. Wenn jemand mehr darüber weiß, würde ich mich über entsprechende Hinweise freuen.

Nachtrag 19.09.2011: Das row cache lock tritt offenbar auch bei der nachträglichen Definition eines NOT NULL Constraints auf.

DBMS_STATS.DISPLAY - Outline Hints und Aliase

2011-09-12T11:51:00.000-07:00

Da ich es sonst immer wieder anderswo nachschlagen muss, schreib ich endlich mal auf, wie man sich die Outline Hints und Subquery-Aliase einer Query mit Hilfe von DBMS_STATS.DISPLAY anzeigen lassen kann. Die Syntax dafür lautet einfach:

select *
  from table(dbms_xplan.display('', '', 'ALIAS OUTLINE'));

In den Aliasen findet man dann mit etwas Glück auch die Labels wieder, die man einem Query Block mit Hilfe des QB_NAME hints geben kann, der (einmal mehr) bei Jonathan Lewis ausführlicher beschrieben wurde.

System Statistic Bug in 11.2

2011-09-12T11:29:00.000-07:00

Jonathan Lewis hat in seinem Blog einige der einschlägigen Hinweise auf den Bug 9842771 gesammelt, der sich darin äußert, dass in AUX_STAT$ abwegig hohe Werte für SREADTIM and MREADTIM erscheinen.

Descending Indexes

2011-09-09T06:44:00.000-07:00

Die nächste Quizfrage des Herrn Foote, diesmal zum Thema Descending Indexes. Ohne die Details hier alle noch mal nacherzählen zu wollen, ein paar wichtige Punkte:

ein DESC-Index ist vom Typ her FUNCTION-BASED NORMAL
beim Überlauf von Index-Blocks erfolgt ein 50/50 Spilt, was den Index weniger kompakt macht als einen ASC-Index, beim dem die Block-Splits 90/10 erfolgen (also eigentlich eher 99/1)
der RBO ignoriert neumodischen Kram wie FBIs

Nachtrag 14.09.2011: in den Kommentaren zum Artikel haben sich auch noch die Herren Geist und Lewis zu Wort gemeldet und noch ein paar erinneringswürdige Fakten beigesteuert:

descending indexes werden nicht für dynamic sampling in Betracht gezogen: "The reason for not using dynamic sampling on the descending index is also interesting: The dynamic sampling code uses the original predicates (between 42 and 84) and not the actual ones that are then applied to the descending index (the SYS_OP… stuff), so it obviously doesn’t consider the index as candidate for sampling." Randolf Geist
descending indexes sind pro row ein byte größer als entsprechend acs indexes: "the descending column is a one’s-bit complement of the original value, stored in ascending order, with a 0xff appended. This means the index will be one byte larger for each row for each descending column – and that’s a pretty good reason for not making every column in an index descending. (Plus there are cases – possibly fixed in 11.2 – where descending indexes won’t be used for plans which would be considered for ascending indexes.)" Jonathan Lewis
ein dritter Kommentar zeigt, dass ein ALTER TABLE SHRINK SPACE für Tabellen mit einem DESC Index nicht funktioniert (und einen "ORA-10631: SHRINK clause should not be specified for this object" liefert)

Partitionierte Tabellen und direct path FTS

2011-09-08T12:27:00.000-07:00

Über das in 11g eingeführte Feature der direct path reads für serial table scans hatte ich schon mehrfach gelesen, ohne mir viel dabei zu denken. Vor kurzem habe ich dann in Tanel Poders Hacking Session Video den Hinweis bekommen, dass die für die Entscheidung der run time engine, ob eine Tabelle für den direct path Zugriff in Betracht gezogen wird, relevante _small_table_threshold pro Segment definiert ist - also auch für die Partitionen einer partitionierten Tabelle. Trotzdem war ich heute zunächst überrascht, als ein einfaches SELECT COUNT(*) auf einer Heap-Tabelle mit 5.000.000 Sätzen (etwa 1 GB groß) in ca. 20 sec. ablief, während der Zugriff auf eine identisch gefüllte partitionierte Tabelle mehr als 40 sec. benötigte. Ein Vergleich der Statistiken in v$sesstat half meinem Gedächtnis dann auf die Sprünge:

NAME	TABLE_NO_PART	TABLE_PART
consistent gets	72298	74445
consistent gets - examination	0	819
consistent gets direct	72286	0
consistent gets from cache	12	74445
consistent gets from cache (fastpath)	12	71726
DB time	1699	5046
free buffer inspected	0	73410
free buffer requested	0	72568
physical reads	72286	72568
physical reads cache	0	72568
physical reads cache prefetch	0	70674
physical reads direct	72286	0
table scan blocks gotten	72286	72444
table scan rows gotten	5637076	5637107
table scans (direct read)	1	0
table scans (long tables)	1	0
table scans (short tables)	0	86

Die 86 table scans (short tables) gehören zu den Partitionen, während für die nicht partitionierte Tabelle ein table scan (long tables) erscheint - und dieser ruft dann consistent gets direct hervor, während die kleineren Partitionen über den Buffer Cache gelesen werden, was zumindest in diesem Fall deutlich langsamer ist. Die Entscheidung für den direct path Zugriff kann übrigens mit Hilfe des Parameters _serial_direct_read beeinflusst werden, der wie alle hidden parameter natürlich nur nach Rücksprache mit dem zuständigen Arzt oder Apotheker verwendet werden sollte.

Einzelsatzzugriff

2011-09-05T12:50:00.000-07:00

Und schon wieder eine Quizfrage des Herrn Foote:

Assume you have a tiny little table with just 42 rows (naturally) that all fit in one table block. Order the following options in order of “efficiency” (most efficient option first) when accessing just one of these rows:
1) Full Table Scan of Heap Table
2) PK access of an Index Organised Table
3) Index access of Heap Table via a Unique Index
4) Index access of Heap Table via a Non-Unique Index

In den Kommentaren wurden gleich ein paar berechtigte Nachfragen gestellt, aber ich tu mal so, als sei die Frage völlig harmlos:

1. FTS

create table t42 tablespace test_ts
as
select rownum id
     , 'Ziggy' col2
  from dual
connect by level <= 42;

exec dbms_stats.gather_table_stats(user, 'T42')

select *
  from t42
 where id = 1;

   ID COL2
----- -----
    1 Ziggy

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T42  |     1 |     9 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=1)


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2. non-unique Index

create index t42_idx on t42(id);

--> Query

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

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

   2 - access("ID"=1)


Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        603  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

3. Unique Index

create unique index t42_uidx on t42(id);

--> Query

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |     9 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T42      |     1 |     9 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T42_UIDX |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("ID"=1)


Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        467  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

4. IOT-Zugriff

create table t42 (id, col2, constraint t42pk primary key(id)) organization index tablespace test_ts
as
select rownum id
     , 'Ziggy' col2
  from dual
connect by level <= 42

exec dbms_stats.gather_table_stats(user, 'T42')

--> Query

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     9 |     0   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| T42PK |     1 |     9 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("ID"=1)


Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

5. Single Table Hash Cluster

Nicht in der Fragestellung enthalten, aber halbwegs zum Thema passend...

create cluster t_cluster
(id number(2,0))
tablespace test_ts
hash is id hashkeys 64;

create table t42
( id number(2,0)
, col2 varchar2(10) )
cluster t_cluster (id);

insert into t42
select rownum id
     , 'Ziggy' col2
  from dual
connect by level <= 42;

--> Query

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     0   (0)|
|*  1 |  TABLE ACCESS HASH| T42  |     1 |     9 |            |
---------------------------------------------------------------

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

   1 - access("ID"=1)


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Also keine Überraschungen bei den Platzierungen:

IOT (und HashCluster) mit 1 consistent get für den Zugriff auf einen einzelnen Datensatz (+ 1 Latch)
unique Index Zugriff mit 2 consistent gets (+ 2 Latches; nur 2 - also 1 pro LIO -, da es sich um consistent get - examinations)
non-unique Index Zugriff mit 3 consistent gets (+ 6 Latches)
FTS mit 4 consistent get (+ 8 Latches)

Warum das so ist, hat der Herr Foote übrigens gelegentlich schon mal in seinem Blog erläutert:

beim non-unique Index sind zwei Fetches erforderlich: einer zum Lesen des Ergebnisses und einer, um sicher zu gehen, dass nicht noch ein Satz für den gegebenen Wert folgt
beim unique Index kann sich Oracle die zweite Fetch-Operation sparen, da nicht mehr als ein Satz zum Schlüssel vorliegen kann
auch zum FTS findet sich dort ein Artikel - und wahrscheinlich wären die beiden übrigen Artikel der fraglichen Serie Indexes And Small Tables auch noch verlinkungswürdig - falls das ein Wort sein sollte...

Nachtrag 07.09.2011: Inzwischen hat Richard Foote die Antwort auf seine Fragen gegeben und dabei auch die angesprochenen älteren Artikel verlinkt. Interessant sind auch noch die dort angegebenen Latch-Zahlen, die ich oben im Ergebnis ergänzt habe.

used_urec

2011-09-04T06:12:00.000-07:00

Jonathan Lewis erläutert in seinem Blog, wie die Spalte used_urec in V$TRANSACTION gedeutet werden kann:

when you insert a row, you typically generate one undo record (used_urec) for the row, but when you insert many rows using an array insert Oracle has an optimisation mechanism on undo record creation that allows it to create one used_urec to cover all the changes you have made simultaneously to an individual block - so used_urec could be much smaller than the number of rows processed.
However, if you have indexes in place and are doing normal index maintenance on import, then each table row would require each index to be updated, so you would go back to one used_urec per table row plus one used_urec per index maintained per table row.
So, when you look at the “big picture” there’s no obvious correlation between rows inserted and undo generated — until you look at the fine detail of exactly what you’re doing, and whether any optimisations apply. (The details of the optimisation strategies available vary with the chosen insert mechanisms and with version of Oracle)

Außerdem verweist er auf einen älteren Artikel zum Thema Session Undo und darin dann wiederum auf ein Script von Steve Adams, mit dessen Hilfe sich die Restlaufzeit eines Rollback-Vorgangs bestimmen lässt (sofern man Zugriff auf die erforderlichen v$- und x$-Objekte besitzt).

Automatic Diagnostic Repository Command Interpreter (ADRCI)

2011-09-04T05:58:00.000-07:00

Trotz des anspruchsvoll klingenden Titels nur ein Link auf Uwe Hesses einführenden Artikel zu diesem 11er Tool zur Administration von Trace-Informationen.

Frequency-Histogramme und die 254 Bucket-Grenze

2011-09-02T10:42:00.000-07:00

Und gleich die nächste interessante Quizfrage im Blog des Herrn Foote. Bei der letzten Frage war mir die Antwort schon vor der Auflösung klar, aber diesmal hatte ich absolut keine Erklärung für das beschriebene Verhalten: für eine Spalte existierte ein Frequency-Histogramm so lange darin 254 unterschiedliche - und gleichverteilte - Werte vorlagen. Das Hinzufügen eines einzelnen Satzes mit einem neuen und weit außerhalb des bisherigen Ranges liegenden Wert (also Nr. 255) führte dann dazu, dass bei erneuter Statsitikerzeugung mit der METHOD_OPT SIZE AUTO Option kein Histogram mehr erzeugt wurde - obwohl ein solches mit der neuen Datenverteilung sehr viel sinnvoller gewesen wäre als für den Fall vor der Ergänzung (für den ohnehin eine Gleichverteilung galt, die der cbo in Abwesenheit von Histogrammen von vornherein angenommen hätte). Die Erklärung des Verhaltens lautet:

When using METHOD_OPT SIZE AUTO, every column with 254 or less distinct values that has been referenced within a predicate, will have a Frequency-based histogram. Each and every one of them, regardless of whether the data is actually skewed or not.
[...]
If a column has more than 254 distinct values, whether it then has a Height-Based histogram depends on how the data is skewed.

Für die Spalte mit dem neuen und von den bisherigen Werten massiv abweichenden Wert gilt:

Having inserted the outlier value, it now has 255 distinct values and so no longer qualifies for an automatic frequency based histogram. However, if all its values are evenly distributed, then it won’t qualify for a height based histogram either and Column 3 only has just the one outlier value, all other values are evenly distributed values. Unfortunately, Oracle doesn’t pick up on rare outlier values (even if you collect 100% statistics and it’s one of the low/high points of the column) and so will not generate a height-based histogram.

Der Artikel führt auch noch vor, dass das Fehlen des Histogramms dann recht extreme Fehleinschätzungen des cbo nach sich zieht, da dieser nun wieder eine Gleichverteilung annimmt. Vorgestern Abend bin ich an dieser Quizfrage fast 1,5 h hängengeblieben - es ist einfach großartig, was man bei den kompetentesten Oracle-Bloggern alles lernen kann.

Nachtrag 03.09.2011: Randolf Geist hat in einem Kommentar zu Richard Footes Artikel noch einige wichtige Details zur Kostenberechnung des CBO mit Frequency Histograms ausgeführt.

MIN/MAX Abfragen und Index-Verwendung

2011-08-31T07:57:00.000-07:00

Dieser Tage hat Richard Foote in seinem Blog eine Quizfrage zur Abfrage der minimalen und maximalen Werte einer indizierten Spalte gestellt und inzwischen dazu auf eine ausführliche Antwort geliefert. Kurz zusammengefasst geht es darum, dass eine Query:

select min(col_ind)

     , max(col_ind)

  from ...

den bestehenden Index nur zum INDEX FAST FULL SCAN verwenden kann, während der wünschenswerte INDEX FULL SCAN (MIN/MAX) nur nutzbar ist, wenn man daraus zwei Zugriffe macht, was auf verschiedenen Wegen möglich ist.

Herr Foote und die Kommentatoren sprechen eher über B*Tree-Indizes, aber das Verhalten von Bitmap-Indizes ist identisch.

External Tables und ORA-29913

2011-08-31T00:40:00.000-07:00

Bei der Analyse von Zugriffsproblemen auf External Tables bzw. die darunter liegenden Directories und enthaltene Dateien habe ich bei Jonathan Lewis einen sehr nützlichen Artikel zu den Oracle-Fehlern ORA-29913 und ORA-29400 gefunden.

Tanel Poder über Full Scans und Direct Path Reads

2011-08-27T13:44:00.000-07:00

Wieder ein Video in Spielfilmlänge von Tanel Poder. Details dazu vielleicht, wenn ich es komplett gesehen habe ...

Nach Betrachtung der ersten 45 min des Videos ein paar erinnerungswürdige Punkte:

Der Segment Header Block enthält Pointer auf die Extents des Segments.
In Oracle 7 war die Anzahl der Extents eines Segments begrenzt, da abhängig von der Blockgröße nur eine bestimmte Anzahl von Pointern untergebracht werden konnte. Aus diesem Grund musste man Segmente von Zeit zu Zeit reorganisieren, um die Anzahl der Extents zu reduzieren.
Seit Oracle 8 gibt es zusätzliche extent map blocks, auf die im Segment Header verwiesen wird.
In dictionary managed tablespaces wurde die Verwendung von Extents noch explizit in Tabellen des data dictionary gespeichert (SYS.UET$: used extents; SYS.FET$: free extents). In aktuellen Releases (ohne dictionary managed tablespaces) sind diese Tabellen leer.
Ein Block Dump des Segment Header Blocks liefert weitere Details:

Der Dump enthält Informationen zur HWM, vor allem die Angabe #blocks below.
diese Angabe ist die Grundlage für die Entscheidung, ob die Tabelle (bzw. eigentlich das Segment) als long betrachtet wird, worauf wiederum die Entscheidung beruht, ob ein FTS über den Buffer Cache oder über direct path durchgeführt wird.
Im Header Block folgt dann die Extent Map mit den Hex-Angaben der Blockadressen (die man mit Tanel Poders Script "DBA" auflösen kann).
Dann folgt die Auxillary Map mit Verweisen auf die zusätzlichen Map Blocks.

Für jeden Segment-Zugriff wird zunächst ein sequential read durchgeführt, um den Segment Header Block zu lesen (sofern dieser nicht bereits im Buffer Cache vorliegt; deshalb wurde in der Demo zunächst ein Flush des Buffer Caches durchgeführt).
Die Idee der direct path FTS ist, den sinnvoll gefüllten Buffer Cache nicht durch massive Lese-Operationen für große FTS durcheinander zu bringen.
im 10046er Trace folgt auf den Header-Zugriff ein Event async descriptor resize über das Tanel Poder schon mal geschreiben hat: "This KCBL module [= direct path loader] tries to dynamically scale up the number of asynch IO descriptors (AIO descriptors are the OS kernel structures, which keep track of asynch IO requests) to match the number of direct path IO slots a process uses. In other words, if the PGA workarea and/or spilled-over hash area in temp tablespace gets larger, Oracle also scales up the number of direct IO slots. Direct IO slots are PGA memory structures helping to do direct IO between files and PGA."
die Entscheidung für die Verwendung von direct path FTS (oder IFFS) trifft nicht der CBO, sondern die run time engine und die Entscheidung wird für jedes Segment einzeln getroffen (also können die Partitionen eines partitionierten Objekts unterschiedlich behandelt werden).

Teil 2 - bis zur 90. Minute:

SYS.SEG$ ist die Tabelle, auf der DBA_SEGMENTS basiert.
Der Root-Block eines Index-Segments ist immer der erste Block nach dem Header.
In SEG$ finden sich die Positionen aller Segmente - aber wie wird SEG$ lokalisiert?
Dazu gibt es eine Tabelle SYS.BOOTSTRAP$, die DDL-Informationen zu diversen internen Tabellen enthält (wobei diese DDL nicht ausgeführt wird). In diesen DDL-Informationen sind File- und Blockangaben enthalten:

select *
  from sys.bootstrap$
 order by 1

LINE#       OBJ# SQL_TEXT
----- ---------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   -1         -1 8.0.0.0.0
    0          0 CREATE ROLLBACK SEGMENT SYSTEM STORAGE (  INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))
    2          2 CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 136K NEXT 200K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 144)) SIZE 800
    3          3 CREATE INDEX I_OBJ# ON CLUSTER C_OBJ# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 3 EXTENTS (FILE 1 BLOCK 168))
    4          4 CREATE TABLE TAB$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"BOBJ#" NUMBER,"TAB#" NUMBER,"COLS" NUMBER NOT NULL,"CLUCOLS" NUMBER,"PCTFREE$"
    5          5 CREATE TABLE CLU$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"COLS" NUMBER NOT NULL,"PCTFREE$" NUMBER NOT NULL,"PCTUSED$" NUMBER NOT NULL,"I
    6          6 CREATE CLUSTER C_TS#("TS#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 6 EXTENTS (FILE 1 BLOCK 176))
    7          7 CREATE INDEX I_TS# ON CLUSTER C_TS# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 7 EXTENTS (FILE 1 BLOCK 184))
    8          8 CREATE CLUSTER C_FILE#_BLOCK#("TS#" NUMBER,"SEGFILE#" NUMBER,"SEGBLOCK#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 24K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
    9          9 CREATE INDEX I_FILE#_BLOCK# ON CLUSTER C_FILE#_BLOCK# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 9 EXTENTS (FILE 1 BLOCK 200))
   10         10 CREATE CLUSTER C_USER#("USER#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 10 EXTENTS (FILE 1 BLOCK 208)) SIZE
   11         11 CREATE INDEX I_USER# ON CLUSTER C_USER# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 11 EXTENTS (FILE 1 BLOCK 216))
   12         12 CREATE TABLE FET$("TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"LENGTH" NUMBER NOT NULL) STORAGE (  OBJNO 12 TABNO 1) CLUSTER C_TS#(TS#)
   13         13 CREATE TABLE UET$("SEGFILE#" NUMBER NOT NULL,"SEGBLOCK#" NUMBER NOT NULL,"EXT#" NUMBER NOT NULL,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"LENGTH" NUMBER NOT NULL) STORAGE (  OBJNO
   14         14 CREATE TABLE SEG$("FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"TYPE#" NUMBER NOT NULL,"TS#" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"EXTENTS" NUMBER NOT NULL,"INIEXTS" NUMBER NOT NULL,"MINEXTS" NUMBER NO
   15         15 CREATE TABLE UNDO$("US#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"USER#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"SCNBAS" NUMBER,"SCNWRP" NUMBER,"XACTSQN" NUMBER,"UNDOSQN" NUMBER,
   16         16 CREATE TABLE TS$("TS#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"OWNER#" NUMBER NOT NULL,"ONLINE$" NUMBER NOT NULL,"CONTENTS$" NUMBER NOT NULL,"UNDOFILE#" NUMBER,"UNDOBLOCK#" NUMBER,"BLOCKSIZE" NUMBER NOT
   17         17 CREATE TABLE FILE$("FILE#" NUMBER NOT NULL,"STATUS$" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"TS#" NUMBER,"RELFILE#" NUMBER,"MAXEXTEND" NUMBER,"INC" NUMBER,"CRSCNWRP" NUMBER,"CRSCNBAS" NUMBER,"OWNERINSTANCE"
   18         18 CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"OWNER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"NAMESPACE" NUMBER NOT NULL,"SUBNAME" VARCHAR2(30),"TYPE#" NUMBER NOT NULL,"CTIME" DATE NOT NULL
   19         19 CREATE TABLE IND$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"BO#" NUMBER NOT NULL,"INDMETHOD#" NUMBER NOT NULL,"COLS" NUMBER NOT NULL,"PCTF
   20         20 CREATE TABLE ICOL$("OBJ#" NUMBER NOT NULL,"BO#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"POS#" NUMBER NOT NULL,"SEGCOL#" NUMBER NOT NULL,"SEGCOLLENGTH" NUMBER NOT NULL,"OFFSET" NUMBER NOT NULL,"INTCOL#" NUMBER
   21         21 CREATE TABLE COL$("OBJ#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"SEGCOL#" NUMBER NOT NULL,"SEGCOLLENGTH" NUMBER NOT NULL,"OFFSET" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"TYPE#" NUMBER NOT NULL,"LENGTH" N
   22         22 CREATE TABLE USER$("USER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"TYPE#" NUMBER NOT NULL,"PASSWORD" VARCHAR2(30),"DATATS#" NUMBER NOT NULL,"TEMPTS#" NUMBER NOT NULL,"CTIME" DATE NOT NULL,"PTIME" DATE,"E
   23         23 CREATE TABLE PROXY_DATA$("CLIENT#" NUMBER NOT NULL,"PROXY#" NUMBER NOT NULL,"CREDENTIAL_TYPE#" NUMBER NOT NULL,"CREDENTIAL_VERSION#" NUMBER NOT NULL,"CREDENTIAL_MINOR#" NUMBER NOT NULL,"FLAGS" NUMBER NOT NULL)
   24         24 CREATE UNIQUE INDEX I_PROXY_DATA$ ON PROXY_DATA$(CLIENT#,PROXY#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 24 EXTENTS (FILE 1 B
   25         25 CREATE TABLE PROXY_ROLE_DATA$("CLIENT#" NUMBER NOT NULL,"PROXY#" NUMBER NOT NULL,"ROLE#" NUMBER NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
   26         26 CREATE INDEX I_PROXY_ROLE_DATA$_1 ON PROXY_ROLE_DATA$(CLIENT#,PROXY#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 26 EXTENTS (FIL
   27         27 CREATE UNIQUE INDEX I_PROXY_ROLE_DATA$_2 ON PROXY_ROLE_DATA$(CLIENT#,PROXY#,ROLE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 27
   28         28 CREATE TABLE CON$("OWNER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"CON#" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCT
   29         29 CREATE CLUSTER C_COBJ#("OBJ#" NUMBER) PCTFREE 0 PCTUSED 50 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 56K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 29 EXTENTS (FILE 1 BLOCK 296)) SIZE 3
   30         30 CREATE INDEX I_COBJ# ON CLUSTER C_COBJ# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 30 EXTENTS (FILE 1 BLOCK 304))
   31         31 CREATE TABLE CDEF$("CON#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"COLS" NUMBER,"TYPE#" NUMBER NOT NULL,"ROBJ#" NUMBER,"RCON#" NUMBER,"RRULES" VARCHAR2(3),"MATCH#" NUMBER,"REFACT" NUMBER,"ENABLED" NUMBER,"CONDL
   32         32 CREATE TABLE CCOL$("CON#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"POS#" NUMBER,"INTCOL#" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5"
   33         33 CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 33 EXTENTS (FILE 1 BLOCK 312))
   34         34 CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 34 EXTENTS (FILE 1 BLOCK 320))
   35         35 CREATE INDEX I_UNDO2 ON UNDO$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 35 EXTENTS (FILE 1 BLOCK 328))
   36         36 CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 336))
   37         37 CREATE UNIQUE INDEX I_OBJ2 ON OBJ$(OWNER#,NAME,NAMESPACE,REMOTEOWNER,LINKNAME,SUBNAME,TYPE#,SPARE3,OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 16K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 P
   38         38 CREATE INDEX I_OBJ3 ON OBJ$(OID$) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 38 EXTENTS (FILE 1 BLOCK 352))
   39         39 CREATE INDEX I_OBJ4 ON OBJ$(DATAOBJ#,TYPE#,OWNER#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 39 EXTENTS (FILE 1 BLOCK 360))
   40         40 CREATE UNIQUE INDEX I_OBJ5 ON OBJ$(SPARE3,NAME,NAMESPACE,TYPE#,OWNER#,REMOTEOWNER,LINKNAME,SUBNAME,OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
   41         41 CREATE UNIQUE INDEX I_IND1 ON IND$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 41 EXTENTS (FILE 1 BLOCK 376))
   42         42 CREATE INDEX I_ICOL1 ON ICOL$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 42 EXTENTS (FILE 1 BLOCK 384))
   43         43 CREATE UNIQUE INDEX I_FILE1 ON FILE$(FILE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 43 EXTENTS (FILE 1 BLOCK 392))
   44         44 CREATE UNIQUE INDEX I_FILE2 ON FILE$(TS#,RELFILE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 44 EXTENTS (FILE 1 BLOCK 400))
   45         45 CREATE UNIQUE INDEX I_TS1 ON TS$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 45 EXTENTS (FILE 1 BLOCK 408))
   46         46 CREATE UNIQUE INDEX I_USER1 ON USER$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 46 EXTENTS (FILE 1 BLOCK 416))
   47         47 CREATE UNIQUE INDEX I_USER2 ON USER$(USER#,TYPE#,SPARE1,SPARE2) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 47 EXTENTS (FILE 1 BL
   48         48 CREATE UNIQUE INDEX I_COL1 ON COL$(OBJ#,NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 32K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 48 EXTENTS (FILE 1 BLOCK 432))
   49         49 CREATE INDEX I_COL2 ON COL$(OBJ#,COL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 32K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 49 EXTENTS (FILE 1 BLOCK 440))
   50         50 CREATE UNIQUE INDEX I_COL3 ON COL$(OBJ#,INTCOL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 32K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 50 EXTENTS (FILE 1 BLOCK 448))
   51         51 CREATE UNIQUE INDEX I_CON1 ON CON$(OWNER#,NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 51 EXTENTS (FILE 1 BLOCK 456))
   52         52 CREATE UNIQUE INDEX I_CON2 ON CON$(CON#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 52 EXTENTS (FILE 1 BLOCK 464))
   53         53 CREATE UNIQUE INDEX I_CDEF1 ON CDEF$(CON#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 53 EXTENTS (FILE 1 BLOCK 472))
   54         54 CREATE INDEX I_CDEF2 ON CDEF$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 54 EXTENTS (FILE 1 BLOCK 480))
   55         55 CREATE INDEX I_CDEF3 ON CDEF$(ROBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 55 EXTENTS (FILE 1 BLOCK 488))
   56         56 CREATE INDEX I_CDEF4 ON CDEF$(ENABLED) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 56 EXTENTS (FILE 1 BLOCK 496))
   57         57 CREATE INDEX I_CCOL1 ON CCOL$(CON#,COL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 57 EXTENTS (FILE 1 BLOCK 504))
   58         58 CREATE UNIQUE INDEX I_CCOL2 ON CCOL$(CON#,INTCOL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 58 EXTENTS (FILE 1 BLOCK 512))
   59         59 CREATE TABLE BOOTSTRAP$("LINE#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"SQL_TEXT" VARCHAR2(4000) NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 56K NEXT 1024K MINEXTENTS 1 MAXEXTENT

SYS.BOOTSTRAP$ wiederum wird durch den Header Block von File #1 lokalisiert (den man über das event file_hdrs dumpen kann; eine Liste der vorhandenen Dump-Events liefert dabei oradebug dumplist)
Die data_object_id identifiziert ein Segment, während die object_id das zugehörige logische Objekt angibt (und z.B. bei der Ermittlung von dependencies verwendet wird)

die data_object_id ist für die Tabellen in einem Cluster identisch.
sie ändert sich nach einem Neuaufbau (Index rebuild; Table move)
auch ein truncate table ändert die data_object_id, da dadurch der segment header neu formatiert wird.

Mit Hilfe eines refcursors kann man auch nach der Löschung einer Tabelle aus den Blocks Daten lesen! Das gilt selbst dann, wenn die Tabelle mit purge Option gelöscht wurde - sofern die Blocks nicht durch ein neues Segment überschrieben werden. Ursache dafür ist, dass das drop table nur eine Änderung im Data Dictionary bewirkt. Allerdings funktioniert der refcursor-Zugriff nur so lange bis der nächste Zugriff auf den Segment Header erforderlich wird.
Ein Zugriff auf den refcursor nach einem truncate table liefert den Fehler "ORA-08103: object no longer exists", da das truncate den Header neu formatiert (auch wenn man die Option reuse storage verwendet)
Grundlage der Entscheidung, ob ein FTS über den Buffer Cache oder über DIRECT PATH READ erfolgt ist die _SMALL_TABLE_TRESHOLD

in 11.1 gilt, dass für ein Objekt, das > 5 * _SMALL_TABLE_TRESHOLD Blocks umfasst, der FTS über DIRECT PATH erfolgt, damit der Buffer Cache nicht mit nutzlosen Daten gefüllt wird; dabei ist die _SMALL_TABLE_TRESHOLD als 2% des Buffer Pool definiert.
für folgende Releases wird zusätzlich geprüft, wie viele Blocks des betroffenen Segments bereits im Cache vorliegen (Mit Hilfe von X$KCBOQH) und dieser Wert modifiziert dann die Threshold-Arithmetik (wenn das Objekt sowieso schon teilweise im Cache vorliegt, ist der DIRECT PATH Zugriff weniger interessant)

Composite Partitionierung mit mehreren Ebenen

2011-08-24T08:04:00.000-07:00

Dieser Tage beschäftige ich mich mit der Frage, wie man eine große Menge von Transaktionsdaten (ca. 5.000.000.000 Sätze) nach möglichst vielen Kriterien partitionieren kann. Nun habe ich in der Vergangenheit häufiger mit composite partitioning gearbeitet, um solche Daten zunächst zeitlich zu partitionieren und darunter dann nach Regionen oder Häusern zu subpartitionieren. Diesmal würde ich aber gerne noch eine dritte Ebene verwenden, nämlich die der Kunden - wobei in diesem Fall nur eine ganz grobe Zuordnung nach Transaktionen mit bzw. Transaktionen ohne Kundennummer erforderlich (und sinnvoll) wäre. Für meinen Test (in Version 11.2.0.1) verwende ich eine ganz einfache Tabelle mit drei Spalten (StoreId, CustomerId, SalesDate), die alle ihre Rolle bei der Partitionierung spielen sollen. Meine erste Wahl wäre eine Range-List-Partitionierung, da die Anzahl der gegebenen Stores nicht sehr groß ist und in einem Subpartition Template untergebracht werden kann:

Versuch 1: Range-List-Partitionierung mit zwei Spalten im Subpartition-Key

create table test_part_range_list

( storeid number

, salesdate date

, customerid number

, customerflag number generated always as (case when customerid = 0 then 0 else 1 end ))

   partition by range (salesdate)

   subpartition by list (storeid, customerflag)

   subpartition template

      (subpartition sp1_cust values (1, 1)

      , subpartition sp2_cust values (2, 1)

      , subpartition sp1_nocust values (1, 0)

      , subpartition sp2_nocust values (2, 0)

      , subpartition sp_others values (default )

      )

  (partition p1 values less than ( to_date('01.01.2011','dd.mm.yyyy')),

   partition p2 values less than ( to_date('01.02.2011','dd.mm.yyyy'))

  )

;



   subpartition template

   *

FEHLER in Zeile 8:

ORA-14304: List-Partitionierungsmethode erwartet eine einzelne Partitionierungsspalte

Und schon zeigt sich, dass es bisweilen ratsam ist, die Dokumentation etwas genauer anzuschauen: List-Partitionierung funktioniert offenbar nur mit einer einzelnen Schlüsselspalte, was diesen Versuch beendet...

Versuch 2: Range-List-Partitionierung mit einer virtuellen Spalte im Subpartition-Key

Im ersten Versuch hatte ich bereits eine virtuelle Spalte verwendet, um die Fallunterscheidung zwischen Transaktion mit Kundennummer und Transaktion ohne Kundennummer unterzubringen - ohne dafür eine "echte" Spalte definieren zu müssen, die die Breite der Sätze erhöhen würde. Virtual Column-Based Partitioning ist ein neues Feature in Version 11, scheint aber problemlos zu funktionieren.

create table test_part_range_list

( storeid number

, salesdate date

, customerid number

, subpartcode number generated always as (case when customerid = 0 then storeid * 100 + 1 else storeid * 100 end ))

   partition by range (salesdate)

   subpartition by list (subpartcode)

   subpartition template

      (subpartition st1_nocust values (100)

      , subpartition st1_cust values (101)

      , subpartition st2_nocust values (200)

      , subpartition st2_cust values (201)

      , subpartition sp_others values (default )

      )

  (partition p1 values less than ( to_date('01.01.2011','dd.mm.yyyy')),

   partition p2 values less than ( to_date('01.02.2011','dd.mm.yyyy'))

  );



-- Testdaten

insert into test_part_range_list (storeid, salesdate, customerid)

values (1, '31.12.2010', 4711);  



insert into test_part_range_list (storeid, salesdate, customerid)

values (1, '31.12.2010', 0);  



insert into test_part_range_list (storeid, salesdate, customerid)

values (1, '31.01.2011', 4711);  



insert into test_part_range_list (storeid, salesdate, customerid)

values (1, '31.01.2011', 0);  



insert into test_part_range_list (storeid, salesdate, customerid)

values (2, '31.12.2010', 1234);  



insert into test_part_range_list (storeid, salesdate, customerid)

values (2, '31.12.2010', 0);  



insert into test_part_range_list (storeid, salesdate, customerid)

values (2, '31.01.2011', 1234);  



insert into test_part_range_list (storeid, salesdate, customerid)

values (2, '31.01.2011', 0);  



-- Statistikerhebung auf Subpartitionsebene

exec dbms_stats.gather_table_stats(user, 'TEST_PART_RANGE_LIST', Granularity => 'SUBPARTITION')



-- Verteilung der Sätze auf die Subpartitionen

select partition_name

     , subpartition_name

     , num_rows 

  from dba_tab_subpartitions 

 where table_name = 'TEST_PART_RANGE_LIST';  



PARTITION_NAME                 SUBPARTITION_NAME                NUM_ROWS

------------------------------ ------------------------------ ----------

P1                             P1_ST1_NOCUST                           1

P1                             P1_ST1_CUST                             1

P1                             P1_ST2_NOCUST                           1

P1                             P1_ST2_CUST                             1

P1                             P1_SP_OTHERS                            0

P2                             P2_ST1_NOCUST                           1

P2                             P2_ST1_CUST                             1

P2                             P2_ST2_NOCUST                           1

P2                             P2_ST2_CUST                             1

P2                             P2_SP_OTHERS                            0

Die Partitionierung führt also zunächst zur gewünschten Verteilung der Daten auf die Partitionen (wobei die Default-Sub-Partitionen leer bleiben; in ihnen landen alle Sätze, für die im Subpartition Template keine eigene (Sub-)Partition definiert ist. Nun aber zur Frage, ob diese Partitionierung sinnvolles Partition Pruning erlaubt - also den Ausschluss von Partitionen, die keine relevanten Daten enthalten, im Rahmen der Abarbeitung entsprechender Queries. Dazu liefert Autotrace folgende Ergebnisse:

select *

  from test_part_range_list



   STOREID SALESDAT CUSTOMERID SUBPARTCODE

---------- -------- ---------- -----------

         1 31.12.10       4711         100

         1 31.12.10          0         101

         2 31.12.10       1234         200

         2 31.12.10          0         201

         1 31.01.11       4711         100

         1 31.01.11          0         101

         2 31.01.11       1234         200

         2 31.01.11          0         201



8 Zeilen ausgewõhlt.



Abgelaufen: 00:00:00.04



Ausführungsplan

----------------------------------------------------------

Plan hash value: 1185884213



------------------------------------------------------------------------------------------------------------

| Id  | Operation           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |                      |     8 |   136 |    21   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ALL|                      |     8 |   136 |    21   (0)| 00:00:01 |     1 |     2 |

|   2 |   PARTITION LIST ALL|                      |     8 |   136 |    21   (0)| 00:00:01 |     1 |     5 |

|   3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     8 |   136 |    21   (0)| 00:00:01 |     1 |    10 |

------------------------------------------------------------------------------------------------------------





Statistiken

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         62  consistent gets

          0  physical reads

          0  redo size

        917  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          8  rows processed

Diese 62 consistent gets sind also der Referenzwert für das Lesen aller Partitionen. Die folgenden Autotrace-Listings sind aus Gründen der Übersichtlichkeit massiv gekürzt:

-- Datumseinschränkung

select *

  from test_part_range_list

 where salesdate = '31.12.2010';



------------------------------------------------------------------------------

| Id  | Operation              | Name                 | Rows | Pstart| Pstop |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                      |     4|       |       |

|   1 |  PARTITION RANGE SINGLE|                      |     4|     1 |     1 |

|   2 |   PARTITION LIST ALL   |                      |     4|     1 |     5 |

|*  3 |    TABLE ACCESS FULL   | TEST_PART_RANGE_LIST |     4|     1 |     5 |

-------------------------------------------------------------------------------



Statistiken

----------------------------------------------------------

         33  consistent gets

          4  rows processed



--> partition pruning funktioniert (was auf dieser Ebene keine Überraschung ist)



-- Einschränkung auf StoreId

select *

  from test_part_range_list

 where storeid = 1;



----------------------------------------------------------------------------

| Id  | Operation           | Name                 | Rows  | Pstart| Pstop |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |                      |     4 |       |       |

|   1 |  PARTITION RANGE ALL|                      |     4 |     1 |     2 |

|   2 |   PARTITION LIST ALL|                      |     4 |     1 |     5 |

|*  3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     4 |     1 |    10 |

----------------------------------------------------------------------------		  



Statistiken

----------------------------------------------------------

         62  consistent gets

          4  rows processed	  



--> kein Pruning: PARTITION RANGE ALL und PARTITION LIST ALL

--> offenbar kann der Wert der virtuellen Spalte subpartcode nicht aus

    der StoreId abgeleitet werden



-- Einschränkung auf CustomerId

select *

  from test_part_range_list

 where customerid = 4711;



----------------------------------------------------------------------------

| Id  | Operation           | Name                 | Rows  | Pstart| Pstop |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |                      |     3 |       |       |

|   1 |  PARTITION RANGE ALL|                      |     3 |     1 |     2 |

|   2 |   PARTITION LIST ALL|                      |     3 |     1 |     5 |

|*  3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     3 |     1 |    10 |

----------------------------------------------------------------------------		  



Statistiken

----------------------------------------------------------

         62  consistent gets

          2  rows processed



--> gleiches Verhalten wie bei der StoreId, was wiederum konsequent ist



-- Kombinierte Bedingung mit StoreId und CustomerId

select *

  from test_part_range_list

 where storeid = 1

   and customerid = 4711;



----------------------------------------------------------------------------

| Id  | Operation           | Name                 | Rows  | Pstart| Pstop |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |                      |     1 |       |       |

|   1 |  PARTITION RANGE ALL|                      |     1 |     1 |     2 |

|   2 |   PARTITION LIST ALL|                      |     1 |     1 |     5 |

|*  3 |    TABLE ACCESS FULL| TEST_PART_RANGE_LIST |     1 |     1 |    10 |

----------------------------------------------------------------------------



Statistiken

----------------------------------------------------------

         62  consistent gets

          2  rows processed		  



--> auch die Kombination der beiden Basiswerte, aus denen sich die virtuelle

    Partitionsschlüsselspalte definiert, führt nicht zum Partition Pruning



-- Bedinung mit der virtuellen Subpartitionsspalte

select *

  from test_part_range_list

 where subpartcode = 201;



-------------------------------------------------------------------------------

| Id  | Operation              | Name                 | Rows  | Pstart| Pstop |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                      |     2 |       |       |

|   1 |  PARTITION RANGE ALL   |                      |     2 |     1 |     2 |

|   2 |   PARTITION LIST SINGLE|                      |     2 |   KEY |   KEY |

|   3 |    TABLE ACCESS FULL   | TEST_PART_RANGE_LIST |     2 |   KEY |   KEY |

-------------------------------------------------------------------------------



Statistiken

----------------------------------------------------------

         16  consistent gets

          2  rows processed



--> mit dem virtuellen subpartcode funktioniert das Pruning

Demnach sorgt die virtuelle Spalte "subpartcode" zwar für eine sinnvolle Verteilung der Daten auf die Subpartitionen, aber das Partition Pruning funktioniert auf Subpartitionsebene nur, wenn dieses Attribut auch als Bedingung in den zugehörigen Queries erscheint. Damit ist aber auch dieser zweite Fall nur noch von akademischem Interesse, da die Queries, die auf den Daten operieren sollen, nicht notwenig beide Einschränkungen enthalten - und schon gar nicht die virtuelle Zusatzspalte verwenden.

Da der Eintrag jetzt schon recht unübersichtlich geworden ist, verschiebe ich weitere Untersuchungen (Hash-Subpartitioning, Range-Partitioning mit mehreren Spalten) auf ein andermal.

PL/SQL Mythen

2011-08-23T12:39:00.000-07:00

Morten Braten räumt in seinem Blog mit allerlei falschen Vorstellungen auf, die über den Einsatz von PL/SQL existieren.

gather_plan_statistics

2011-08-19T06:14:00.000-07:00

Dass die Erfassung von Ausführungsstatistiken mit Hilfe des gather_plan_statistics Hints (oder Setzung von statistics_level=all) einen relativ großen Einfluß auf die Performance von Queries besitzt, hatte ich schon häufiger festgestellt. In Jonathan Lewis' Blog findet man dazu eine Erklärung: verantwortlich ist offenbar vor allem die timer Funktion des verwendeten Betriebssystems.

Was man mit den Statistiken anfangen kann, erläutert Maria Colgan dieser Tage - und ihrem Artikel verdanke ich auch den Link auf den Scratchpad-Artikel.

Logical I/O Optimization

2011-08-19T05:34:00.000-07:00

Noch mal ein Link auf Randolf Geists Blog: Logical I/O Evolution - Part 3: 11g. Zum Artikel lässt sich sagen, dass:

er ziemlich lang ist, was dem Autor auch aufgefallen zu sein scheint ...
er eine umfassende Untersuchung der in 11g verfügbaren Optimierungen enthält.
man in 11g die Gestalt eines Nested Loops über die Parameter [NO_]NLJ_BATCHING und [NO_]NLJ_PREFETCH steuern kann. Ein paar Links zum Nested Loop Join Batching hatte ich vor einigen Monaten hier gesammelt.
die LIO-Optimierungen in 11g auch ohne Table Prefetching und Nested Loop Join Batching verfügbar sind (und ohne fastpath consistent gets).
die Optimierung stark von der physikalischen Sortierung der Daten abhängt.
das massive Pinning der Blöcke in der optimierten Version anscheinend keine deutlich negative Wirkung auf die Concurrency hat.

Gelegentlich sollte ich den Artikel noch mal lesen, um die Details halbwegs zu fassen ...

Optimizer Trace

2011-08-19T00:47:00.000-07:00

Greg Rahn erläutert einige neue Varianten zur Erstellung von Optimizer Traces in 11g, denn dort gibt es neben Event 10053 noch ein paar andere Möglichkeiten.

DBMS_XPLAN in RAC-Datenbanken

2011-08-19T00:06:00.000-07:00

Dieser Tage habe ich in Jonathan Lewis' Blog eine Frage nach der Verwendbarkeit von dbms_xplan.display_cursor in RAC-Systemen gestellt, aber zunächst nur einen kurzen Kommentar von Dom Brooks dazu bekommen. Da sich im Thread neben dem Herrn Lewis auch noch die Herren Geist und Rahn aufgehalten haben, hoffe ich darauf, dass da vielleicht noch weitere Bemerkungen folgen. Für's erste aber noch mal meine Beobachtungen:

Beim Versuch, in einer RAC-Datenbank den Zugriffsplan für eine über gv$sql ermittelte sql_id mit Hilfe von dbms_xplan.display_cursor anzeigen zu lassen, bekomme ich kein Ergebnis:

select *

  from table(dbms_xplan.display_cursor(sql_id => '13x0qwkc4xrfv', cursor_child_no=>1));

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------

SQL_ID: 13x0qwkc4xrfv, child number: 1 cannot be found

In v$sql_plan sehe ich auch keine zugehörigen Einträge, aber in gv$sql_plan finden sich die gesuchten Informationen:

select count(*)

  from v$sql_plan

 where sql_id = '13x0qwkc4xrfv'

   and child_number = 1;

 

  COUNT(*)

----------

         0

 

select count(*)

  from gv$sql_plan

 where sql_id = '13x0qwkc4xrfv'

   and child_number = 1;

 

  COUNT(*)

----------

         4

Offenbar greift dbms_xplan.display_cursor nicht auf die gv$-Views zu: in einer anderen Datenbank (kein RAC), sehe ich über Event 10046 nur rekursive Zugriffe auf v$-Views:

select /* EXEC_FROM_DBMS_XPLAN */ case when upper(sql_text) like

  '%DBMS_XPLAN%' then 0 else 1 end case, SQL_ID, child_number

from

 v$sql where SQL_ID ='8szmwam7fysa3' and child_number =0

 

...

 

Parsing user id: 68     (recursive depth: 1)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=536 card=1)

Das entspricht auch den Angaben der Doku: "The table function DISPLAY_CURSOR requires to have select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL". Im Widerspruch dazu scheint mir der entsprechende Eintrag in Morgan's Library zu sein: "Display from GV$SQL_PLAN (or GV$SQL_PLAN_STATISTICS_ALL)."

Meiner Meinung nach wäre es günstig, wenn dbms_xplan.display_cursor einen NODE-Parameter beinhalten würde, da die Plan-Informationen über gv$_sql_plan ja offenbar verfügbar wären.

Nachtrag 31.08.2011: in einem Kommentar im Sratchpad wurde ein funktionierender Workaround vorgeschlagen, der die Anzeige der Pläne von anderen Knoten ermöglicht:

select * 

  from table(dbms_xplan.display('gv$sql_plan_statistics_all'

                               , null

                               , null

                               , 'inst_id = &inst_id and sql_id = ''&sql_id'' and child_number = &child_number')

             );

LWGR und Commit

2011-08-18T23:33:00.000-07:00

Jonathan Lewis beschreibt in seinem Blog ein recht seltsames (und potentiell gefährliches) Verhalten des Oracle Servers: wenn eine Transaktion ein Commit durchführt und die Information im Log Buffer landet, dann wird sie für andere Sessions sichtbar - auch dann, wenn der LWGR nicht mehr dazu in der Lage ist, die Commit-Informationen in die Redo Log Dateien zu schreiben. Nach einem Neustart wird diese Transaktion aber nicht als commited betrachtet, da eben die persistierten Log-Informationen fehlen.

Dieses Verhalten widerspricht allem, was ich bisher zum Thema gelesen hatte, denn ich ging davon aus, dass die Festschreibung in den redo Logs zum erfolgreichen Commit gehört. Da der Herr Lewis den Sachverhalt aber in mehreren Releases ab 8.1 reproduzieren konnte, ist das offenbar schon seit langer Zeit so gewesen.

Nachtrag 26.08.2011: zu Jonathan Lewis' Artikel gibt's inzwischen 69 Kommentare und ein umfangreiches Update und Tony Hasler, der das Problem als erster beschrieben hatte, hat noch zwei Artikel zur Frage beigesteuert, was dieses Verhalten für Oracles Verhältnis zu ACID bedeutet.

SSAS und Subquery Factoring

2011-08-17T01:14:00.000-07:00

Gestern ist einem meiner Kollegen folgendes Problem begegnet: eine Query mit exzessivem Subquery Factoring (also vielen Abfrageblöcken im WITH) lief problemlos und ließ sich auch problemlos in einer View verwenden. Nach Einbindung in die DataSourceView eines SSAS-Projekts brachte das Processing eines auf der View basierenden Objekts dann aber die folgende Fehlermeldung (bzw. die deutschsprachige Version dazu):

ORA-32036: unsupported case for inlining of query name in WITH clause

Über Google landeten wir dann schnell bei Dom Brooks, der das Thema gelegentlich in seinem Blog beleuchtet hat: "According to a couple of metalink articles, the feature/bug/issue/problem is that SQL*Plus, Toad and SQLDeveloper are not reporting the error, rather than the correct behaviour as per ODBC or any other driver raising the error." Dort findet man dann neben den Links auf MOS in einem der Kommentare auch noch einen Workaround, mit dem wir vermeiden konnten, die (halbwegs) übersichtliche View noch einmal komplett neu zu definieren: nämlich die Verwendung des Parameters DistribTx=0 im Connection String der DataSource, mit dem man die Verwendung von verteilten Transaktionen deaktiviert - wobei ich nicht behaupten will, den inneren Zusammenhang von Subquery Factoring und Distributed Transactions im Detail verstanden zu haben ...

Grundsätzlich scheint es doch noch ein paar Fälle zu geben, in denen Subquery Factoring nicht ganz so stabil funktioniert wie entsprechende Inline Views. Vermutlich ist das ein ähnlicher Fall wie der der ANSI-Join-Syntax, die bei Oracle ja auch noch nicht ganz so solide ist wie die traditionelle Schreibweise, obwohl auch sie die Übersichtlichkeit von SQL-Code erhöhen kann.

SQL Monitoring und Plan-Längen

2011-08-12T05:52:00.000-07:00

Doug Burns weist darauf hin, dass es ein Limit für die maximale Länge von Plänen gibt, die über dbms_sqltune.report_sql_monitor dargestellt werden können. Das Limit lässt sich über den Parameter _sqlmon_max_planlines steuern (wobei die üblichen Warnungen für das Spielen mit undokumentierten Parametern gelten).

Nachtrag 19.08.2011: offenbar gibt es einen weiteren Parameter _sqlmon_max_plans, der die Anzahl von Plänen beschränkt, die für den SQL Monitor verfügbar sind.

Neue Features im SQL Server Denali

2011-08-12T04:31:00.000-07:00

Und noch mal zu Denali: Dan English führt in seinem Blog 10 Gründe für das Upgrade auf die neue SQL Server Version auf. Auf Position 1 erscheint dabei die neue Reporting-Komponente Crescent und auf Platz 2 die Integration der Vertipaq Engine aus PowerPivot in den SSAS. Dort wird er als tablular model neben das traditionelle multidimensional model gestellt. Über die Performance der neuen Engine hat Teo Latchev mehrere Artikel geschrieben und kommt dabei zum Ergebnis, dass sie nicht notwendigerweise performanter ist als das herkömmliche Verfahren: