Beiträge

Header Oracle SQL Model Clause

Oracle SQL Model Clause

Theorie und Aufbau

Die Oracle Model Clause ist seit Version 10g verfügbar, ihre teils hohe Komplexität und die dadurch bedingte anfangs recht steile Lernkurve macht sie aber trotzdem zu einem Nischen-Feature. Im Prinzip kann man mit diesem Feature einzelne Zellen direkt ansprechen und sehr gezielt differenzierte Berechnungen anstellen. Da man mit diesem Feature allerdings auch ein enorm mächtiges Werkzeug zur Verfügung hat, soll hier im Folgenden ein kurzer Einblick gegeben werden, wie man sich langsam an die ersten Anwendungsfälle wagen kann, ohne von dem schieren Umfang and Möglichkeiten erschlagen zu werden. Dabei wird Information aus der offiziellen Oracle Dokumentation zusammengefasst und mittels anschaulicher Beispiele in praktischer Umsetzung verdeutlicht.

Lt. Doku (https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6) ist der grundlegende Aufbau der gesamten Model Clause wie folgt:

Abbildung 1: Grundlegender Aufbau SELECT

Abbildung 1: Grundlegender Aufbau SELECT

Hier sieht man nach der Group By Clause die Model Clause. Diese ist wiederum wie folgt aufgebaut:

Abbildung 2: model_clause

Abbildung 2: model_clause

Hieran sieht man bereits, dass lediglich die Komponente main_model verpflichtend ist. Um den Einstieg so einfach wie möglich zu halten, betrachten wir in diesem Artikel auch nur das main_model:

Abbildung 3: main_model

Abbildung 3: main_model

Wir betrachten auch hier nur die verpflichtenden Aspekte, die model_column_clauses und die model_rules_clause:

Abbildung 4: model_column_clauses

Abbildung 4: model_column_clauses

Die Model Column Clause definiert die genutzten Spalten und wie die Spalten verwendet werden. Dabei gibt es 3 Gruppen:

PARTITION BY:

Wie auch bei analytischen Funktionen kann man die Datenmenge anhand von beliebigen Spalten (und theoretisch auch Ausdrücken wie Funktionen) in Gruppen aufteilen (=partitionieren). Im Beispiel am Ende wird darauf noch genauer eingegangen. Dieser Aspekt ist optional.

DIMENSION BY:

Die Spalten der Dimension identifizieren eindeutig eine Zeile innerhalb einer Parition (falls vorhanden). Man sieht an den Keywords bereits, dass das ganze Konzept aus dem Analytics Bereich kommt.

MEASURES:

Hier werden die tatsächlichen Spalten definiert auf denen Berechnungen durchgeführt werden.

Abbildung 5: model_rules_clause

Abbildung 5: model_rules_clause

In der Model Rules Clause werden die tatsächlichen Berechnungen definiert, die im Prinzip Zuweisungen mit einer linken Seite (Ziel der Zuweisung) und einer rechten Seite (Wert der zugewiesen wird) bestehen. Der obere Bereich der Abbildung 5 ist komplett optional, daher betrachten wir erneut lediglich den unteren Bereich. Der wichtige Punkt hierbei ist cell_assignement.

Abbildung 6: cell_assignement

Abbildung 6: cell_assignement

Wir werden uns vorerst mal dem einfachsten Fall widmen, der direkten Identifizierung einer oder mehrerer Zellen. Wie oben bereits beschrieben, definiert die Gesamtheit der Spalten welche als Dimensionen definiert wurden eindeutig eine Zeile (innerhalb einer Partition falls vorhanden). Damit wird eine Zelle oder eine Menge an Zellen eindeutig definiert, indem die Measure Spalte definiert wird und für die Dimensionsspalten Werte angegeben werden (mehrere Zellen können angegeben werden indem beispielsweise Wildcards genutzt werden). Zur Veranschaulichung beginnen wird direkt mit einem praktischen Beispiel.

Praktisches Beispiel

Die Basis für unser praktisches Beispiel ist die Tabelle Schulnoten. Erstellskript und Testdaten können am Ende des Blogeintrags heruntergeladen werden. Der Aufbau ist wie folgt:

create table SCHULNOTEN
(
  schulstufe     NUMBER,
  schueler       VARCHAR2(255),
  schuelernummer NUMBER,
  klasse         VARCHAR2(1),
  jahr           NUMBER,
  fach           VARCHAR2(30),
  note           NUMBER
)

Die Tabelle beinhaltet Schulnoten für Schüler in einer sehr vereinfachten Form. Die Testdaten beinhalten die Daten von jeweils 16 Schülern einer 3. Klasse für 3 Schulstufen sowie zwei 2. Klassen mit je 2 Schulstufen. Auf Basis dieser Daten sollen nun diverse Auswertungen durchgeführt werden. Starten wir mit einer simplen Abfrage: Für den Schüler mit der Nummer 1, für das Fach „Deutsch“, für die 4. Schulstufe im Jahr 2021 soll die voraussichtliche Note berechnet werden als gerundeter Durchschnittswert der Vorjahre. Natürlich kann man diese Berechnung auch über Aggregatsfunktionen berechnen, aber es dient als simpler Einstieg:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0)); 

Hier sehen wir uns die einzelnen Komponenten an:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'

Das ist die grundlegende Abfrage. Wenn die Model Clause verwendet wird, müssen ALLE Spalten, die hier abgefragt werden als PARTITION, DIMENSION oder MEASURE deklariert werden. Weiters müssen hier bei Verwendung von Alias-Bezeichnungen diese angegeben werden. Das bedeutet, dass z.B. folgende Statements nicht funktionieren werden (der fehlerhafte Teil ist jeweils fett markiert):

select schulstufe, klasse, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0));

Die Spalte Klasse wird hier in keiner der drei Kategorien angeführt und führt daher zu einem ORA-32614: unzulässiger MODEL SELECT Ausdruck.

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler name, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0));

Hier wird in den Dimensionen der Spalte SCHUELER der Alias NAME gegeben, daher müsste auch im Select-Teil die Bezeichnung NAME verwendet werden.
Ansonsten folgt der Select-Teil dem üblichen Standard. Wir schränken die Datenmenge hier per Where Clause bereits stark ein. Damit kommen wir zum nächsten Punkt:

MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)

Das Keyword MODEL leitet die Model Clause ein. Da PARTITION BY optional ist und wir ohnehin auf genau einen Schüler und ein Fach einschränken erübrigt sich ihr Nutzen hier. Die Abschnitte für DIMENSION BY und MEASURES sind klar ersichtlich, wir haben also 3 Dimensionen und 1 Measure (Faktum wäre das häufig benutzte deutsche Wort dafür). Hier können für die einzelnen Spalten Alias-Bezeichnungen vergeben werden, wie im Fehlerbeispiel oben ersichtlich war. Diese Alias-Bezeichnungen sind dann durchgehend zu nutzen, auch beim nächsten und in diesem Fall letzten Abschnitt:

(note [ 4, 'Anton Anger', 2021 ] =
round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0));

Zur besseren Lesbarkeit wurden die linke und rechte Seite getrennt. Hier sieht man eine exakte Zuweisung zu einer Zelle auf der linken Seite (in diesem Fall, würde es mehrere Schüler mit dem exakt gleichen Namen geben würde die Zuweisung mehrere Zellen befüllen) sowie mehrere exakte Zuweisungen (oder besser Abfragen) auf der rechten. In einfachen Worten steht hier:
Addiere die Noten des angegebenen Schülers aus Stufe 3 im Jahr 2020 bzw. aus Stufe 2 im Jahr 2019 und Stufe 1 im Jahr 2018 und dividiere die Summe durch 3 – eine manuelle Durchschnittsberechnung. Da wir in diesem Select Daten erhalten die es in der Tabelle gar nicht gibt, wäre eine Umsetzung ohne Model Clause nur über Analytic Functions oder Mengenoperationen wie UNION möglich. Beides würde mehr Code benötigen.
Nun kann man den Sinn einer manuellen Durchschnittsberechnung hinterfragen, wenn Oracle dafür praktische Aggregationsfunktionen anbietet und das durchaus zu Recht. Würden wir das Statement anpassen (weil z.B. nicht von 3 sondern von mehreren 100 Zeilen der Durchschnitt berechnet werden soll) würde es wie folgt aussehen:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round(avg(note) [ any, 'Anton Anger', jahr between 2018 and 2020 ],0));

Das wirkt gleich deutlich sauberer und einfacher. Vorsicht bei der Setzung der Klammern, die Aggregationsfunktion wird NUR um die Measure Spaltenbezeichnung gemacht, das Dimensions-Array in eckigen Klammern steht danach (wird das nicht gemacht gibt die Datenbank einen Fehler zurück: ORA-00934: Gruppenfunktion hier nicht zulässig).

Ein weiterer Aspekt hier sind der Wildcard Operator und eine Range Angabe, beides Mittel, um mehrere Zeilen auf einmal anzusprechen (was hier durch Verwendung der Aggregationsfunktion Sinn macht).

Der Wildcard Operator any bewirkt genau das was das Keyword vermuten lässt: Diese Spalte wird nicht betrachtet bei der Berechnung. Die Range-Angabe between 2018 and 2020 wiederum funktioniert exakt gleich wie eine entsprechende Where Clause.

Bevor wir die Model Clause gewinnbringender einsetzen, noch eine kurze Erklärung zur Zuweisung der Werte der Dimensionen in den Regeln. Die Beispiele bisher nutzten fast ausschließlich eine positionelle Referenz, das bedeutet die Werte stehen an der Stelle im Array an welcher in der Dimensionsdefinition die jeweilige Spalte steht.

Abbildung 7: Zuweisung der Werte der Dimensionen

Abbildung 7: Zuweisung der Werte der Dimensionen

Alternativ dazu kann man auch wie beim Aufruf einer PL/SQL Prozedur eine symbolische Referenz verwenden. Bei der Range-Angabe wie oben MUSS das gemacht werden. Wenn wir das nun für alle Stellen so umsetzen würde es wie folgt aussehen:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ schulstufe=4, schueler='Anton Anger', jahr=2021 ] = round(avg(note) [ schulstufe is any, schueler='Anton Anger', jahr between 2018 and 2020 ],0));

VORSICHT: Wenn symbolische Referenzen verwendet werden, dann können keine neuen Zeilen eingefügt werden. Das oben angeführte Statement ergibt also keine neue Zeile für 2021, das gilt allerdings nur für die linke Seite, folgendes Statement funktioniert also dann wieder korrekt:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round(avg(note) [ schulstufe is any, schueler='Anton Anger', jahr between 2018 and 2020 ],0));

So weit war alles sehr simpel und eigentlich nicht des Aufwands einer Model Clause wert. Dehnen wir das Thema aus und lassen uns für den Schüler mit Nummer 1 für alle Fächer die geschätzten Noten für das Jahr 2021 ausgeben.

select schulstufe, schueler, jahr, fach, note
  from schulnoten
 where schuelernummer = 1
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr, fach)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021, for fach in (select distinct fach from schulnoten) ] = round(avg(note) [ any, 'Anton Anger', jahr between 2018 and 2020 , cv(fach)],0))
   order by 1,4;

Um alle Fächer zu berechnen, muss eine Schleife genutzt werden, das ist dieser Teil:

for fach in (select distinct fach from schulnoten)

Grundsätzlich könnte auch der Any Operator genutzt werden, aber dann gibt die Abfrage nur Zeilen zurück, welche bereits existieren, ich könnte also nur die Noten aus den Jahren 2018 – 2020 berechnen lassen aber keine neuen Zeilen für 2021. Aus diesem Grund muss das ganze über eine Schleife gemacht werden. In diesem Zusammenhang ist dann auch noch folgender Operator wichtig:

cv(fach)

Der CV() Operator gibt den Current-Value, den aktuellen Wert zurück. Im Rahmen dieser Schleife gibt er also bei jedem Durchlauf den Wert des Durchlaufs zurück. Mit diesen beiden Informationen können wir nun für alle Schüler und Fächer der Klasse die neuen Noten berechnen.

select schulstufe, schueler, jahr, fach, note
  from schulnoten
  where schuelernummer in (select schuelernummer from schulnoten where schulstufe = 3)
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr, fach)
   MEASURES(note)
   (note [ 4, for schueler in (select distinct schueler from schulnoten), 2021, for fach in (select distinct fach from schulnoten) ] = round(avg(note) [ any, cv(schueler), jahr between 2018 and 2020 , cv(fach)],0))
   order by 1,2,4;

An diesem Beispiel erkennt man auch gut, dass die Model Clause nur auf Daten zugreifen kann, welche auf Basis der Where-Bedingung existieren. Würde man die Where Clause statt mit einer IN Clause mit einem direkten Filter auf die Schulstufe machen, würde man nur Daten für Schulstufe 3 und 4 erhalten und der Durchschnittswert basiert nur auf den Werten aus Schulstufe 3.
Soweit können wir nun für alle Fächer für jeden Schüler einer bestimmten Klasse/Schulstufe eine simple Voraussage der Noten in der nächsten Schulstufe abfragen. Idealerweise können wir das allerdings für jeden Schüler, unabhängig von der Schulstufe für jedes Fach. Das wäre der gewünschte Endzustand. Ein mögliches Statement dafür könnte wie folgt aussehen:

select schuelernummer,schulstufe, schueler, klasse, jahr, fach, note
  from schulnoten
 MODEL
   PARTITION BY(schuelernummer, schueler, klasse) 
   DIMENSION BY(jahr, fach)
   MEASURES(schulstufe,note)
   (note [ 2021, for fach in (select distinct fach from schulnoten) ] = round(avg(note) [ jahr between 2018 and 2020 , cv(fach)],0),
    schulstufe [ 2021, for fach in (select distinct fach from schulnoten) ] = max(schulstufe) [ jahr between 2018 and 2020 , cv(fach)] + 1)
   order by 2,5,3,6;

Da sinnvollerweise auch Daten zu den Schulstufen der jeweiligen Jahre vorhanden sein sollen und diese aber nicht mehr hartcodiert übergeben werden können ist die Schulstufe in die Measures verschoben würden. Zusätzlich werden die Daten mittels PARTITION BY nach Schüler aufgeteilt, das hat den simplen Grund, dass die Noten immer nur in Abhängigkeit der Noten des jeweiligen Schülers berechnet werden, das erspart auch mühsame Arbeit bei der Dimensionsdefinition. Die Klasse ist kein echtes Partitionierungskriterium, da sie aber ausgegeben werden soll kann sie problemlos hier mit eingetragen werden. Für die Schulstufe wird eine neue Regel erfasst, welche den Maximalwert des jeweiligen Schülers für den jeweiligen Zeitraum berechnet und um eins erhöht. Da wird genau genommen auch jedes Fach für sich betrachten könnten wir hier aber auch einfach das Fach von den Dimensionen in die Partition Clause schieben und sparen uns damit die For Schleife, das würde dann so aussehen:

select schuelernummer, schulstufe, schueler, klasse, jahr, fach, note
  from schulnoten
 MODEL
   PARTITION BY(schuelernummer, schueler, klasse, fach) 
   DIMENSION BY(jahr)
   MEASURES(schulstufe,note)
   (note [ 2021 ] = round(avg(note) [ jahr between 2018 and 2020 ],0),
    schulstufe [ 2021 ] = max(schulstufe) [ jahr between 2018 and 2020 ] + 1)
   order by 2,5,3,6;

Nochmal etwas simpler, denn genau genommen ist das Jahr die einzige Variable die wir hartcodiert setzen.
Einen letzten Fall sehen wir uns noch an. Die Kinder bekommen in der 4. Schulstufe statt Sachunterricht die beiden Fächer Biologie und Physik. Die Note für Biologie soll sich aus zu je 50% aus den Noten der Fächer Deutsch und Sachunterricht der 3. Schulstufe berechnen, die Note für Physik zu je 50% aus den Noten der Fächer Mathematik und Sachunterricht der 3. Schulstufe. Wir betrachten also nur die Schüler der 3. Schulstufe in diesem Fall. Das Statement dafür könnte man zum Beispiel wie folgt strukturieren.

select schulstufe, schueler, jahr, fach, note
  from schulnoten
  where schuelernummer in (select schuelernummer from schulnoten where schulstufe = 3)
 MODEL
   PARTITION BY(schueler)
   DIMENSION BY(schulstufe, jahr, fach)
   MEASURES(note)
   (note [ 4, 2021, for fach in (select distinct fach from schulnoten where fach <> 'Sachunterricht') ] = round(avg(note) [ any, jahr between 2018 and 2020 , cv(fach)],0),
    note [ 4, 2021, 'Biologie' ] = round(( note[ 3, 2020, 'Sachunterricht' ] + note[ 3, 2020, 'Deutsch' ] ) / 2,0),
    note [ 4, 2021, 'Physik' ] = round(( note[ 3, 2020, 'Sachunterricht' ] + note[ 3, 2020, 'Mathematik' ] ) / 2,0))
   order by 1,2,4;

Eine Kombination des vorigen mit diesem Statement ist nicht möglich, da die Schulstufe einmal als Dimension herangezogen wird und einmal als Measure berechnet wird. In diesem Fall kann ich also nur einen von beiden Fällen innerhalb einer Model Clause abdecken.
Wie bereits eingangs erwähnt, wurden in diesem Artikel lediglich die grundlegenden Funktionen der Model Clause abgedeckt. Weitere Artikel, welche die zusätzlichen Möglichkeiten erklären, werden folgen.

Bulk Processing

Beinahe jeder Quellcode enthält sowohl PL/SQL als auch SQL Statements. PL/SQL Statements werden von der PL/SQL engine ausgeführt, SQL Statements von der SQL engine. Folgendes Beispiel:

Angenommen es gäbe 1000 customers mit der account_mgr_id 145, dann würde die PL/SQL engine 1000 Mal zwischen PL/SQL engine und SQL engine hin und her wechseln. Und das kostet bei einer großen Datenmenge Performance, getreu dem Motto „Row by Row = Slow by Slow“.

Eine Möglichkeit, um dies zu verhindern, wäre die Verwendung von Bulk Collect. Anstatt Datensatz für Datensatz zu holen, ist es möglich, ein komplettes Datenset auf einmal zu holen. Hierfür kann man alle drei Collection Types verwenden: Assoziative Arrays, Nested Tables und VARRAYs. Das Beispiel von oben kann wie folgt verbessert werden:

Wir haben nun eine sogenannte nested table deklariert und darauf basierend eine Variable. Danach werden alle Daten auf einmal mittels BULK COLLECT in die Variable v_cust_ids gespeichert. Nun wird im FORALL Statement das angegebene DML Statement abgearbeitet. FORALL ist keine Schleife. Alle entsprechenden Datensätze werden auf einmal bearbeitet, somit entsteht nur ein Wechsel zwischen PL/SQL engine und SQL engine. Jedes FORALL Statement darf nur ein DML Statement enthalten. Müssen n DML Statements abgearbeitet werden, braucht man dementsprechend n FORALL Statements.

Mit dem Attribut SQL%ROWCOUNT kann die Anzahl der abgearbeiteten Datensätze ausgegeben werden.

FORALL und DML Errors

Angenommen, wir wollen 10.000 Datensätze in eine Tabelle speichern. Tritt beim 9.001. Datensatz ein Fehler auf, leitet die SQL engine den Fehler zurück an die PL/SQL engine und diese terminiert das ganze FORALL Statement. Die restlichen Datensätze werden nicht mehr gespeichert.

Mittels SAVE EXCEPTION zwingt man die PL/SQL engine dazu, alle validen Datensätze zu speichern. Somit werden die Fehler ignoriert und das Satement wird zu Ende gebracht. Die PL/SQL engine wirft anschließend einen ORA-24381 Fehler. Mit dem Attribut SQL%BULK_EXCEPTIONS können die fehlerhaften Datensätze ausgelesen und beispielsweise in eine Log-Tabelle gespeichert werden.

JavaScript in Quick Picks in Oracle APEX

Wer diesen Blog schon öfter besucht hat, wird das Feature der Quick Picks bereits kennen. Diese dienen dazu, Usereingaben zu optimieren, indem mittels eines Klicks auf ein entsprechendes Quick Pick direkt ein Wert in ein Feld eingetragen wird.

 Quickpicks_dynamic_01

Ein Nachteil der Quick Picks ist aber, dass standardmäßig nur vorgefertigte (sprich beim Rendern der Seite bereits definierte) Werte eingesetzt werden können. Auch im letzten Blogeintrag über Quick Picks, in dem wir die Anzahl der Quick Picks dynamisch variierbar gemacht haben, haben wir dieses Problem.
Würde man mit den Quick Picks nun beispielsweise immer den aktuellen Wert eines anderen Feldes übernehmen wollen, so ist dies nicht einfach so möglich.
Angenommen es wäre zum Beispiel gewünscht, den Wert des Feldes „Betrag“ (in der oben angeführten Abbildung) mittels eines Klicks auf einen Quick Pick in das Feld „Betrag bezahlt“ zu übernehmen.
Mit den bisher bekannten Methoden kann man zwar das Feld als Quick Pick angeben (siehe folgende Abbildung), allerdings wurde der zu setzende Betrag bereits beim Rendern der Seite erstellt. War dieser davor „1234“ und wurde nachträglich vom Endbenutzer auf „12345678“ geändert, so wäre auch der Quick Pick immer noch mit dem Wert „1234“ definiert und zwar solange, bis die Region (oder die gesamte Seite) erneut geladen werden.

Quickpick_Eigenschaften_marked

Wie Leser des letzten Beitrages über Quick Picks bereits wissen, arbeiten diese selbst mit JavaScript-Code. Es ist also möglich, beim Erstellen eigener Quick Picks beliebigen JavaScript-Code auszuführen. Anstatt also einen Quick Pick zu generieren, der mittels JavaScript ein Item mit einem vordefinierten Wert befüllt, kann dieser Code auch direkt den Wert zum aktuellen Zeitpunkt aus einem anderen Item auslesen.
Der HTML-Code für das entsprechende Quick Pick würde in diesem Beispiel also wie folgt aussehen:

<span class="apex-quick-picks"><a href="javascript:$s('#CURRENT_ITEM_NAME#',$v('P1_BETRAG'), 'JS-QP')">JS-QP</a></span>

„$s“ ist die in Oracle APEX verwendete JavaScript-Methode zum setzten von Items, während „$v“ die Methode zum Auslesen eines Wertes eines beliebigen Items verwendet werden kann. In diesem Beispiel wird also der Wert aus Item „P1_BETRAG“ in das Item „#CURRENT_ITEM_NAME#“ gesetzt.
„#CURRENT_ITEM_NAME#“ ist nur ein Platzhalter für das gewünschte Item. Wenn dieser HTML-Code nun im Post-Text eines gewünschten Items eingetragen wird (in diesem Fall im Item „P1_BETRAG_BEZAHLT“), wird dieser durch den korrekten Itemnamen ersetzt.
Quickpicks advanced

Um das Layout der Quick Picks korrekt darzustellen muss noch im selben Eigenschaftsfenster im Bereich „Quick Picks“ das Flag „Show Quick Picks“ auf „Yes“ gesetzt werden.

Nun wird der entsprechende HTML-Code mittels CSS in die bekannte Formatierung und Positionierung gebracht. Bei Klick auf den Quick Pick wird immer der aktuelle Wert des Feldes „Betrag“ übernommen, ganz egal, wann dieser geändert wurde.

Dynamic Quick Picks

Um die Quick Picks nun wieder dynamischer zu erstellen, kann man natürlich auf „Shortcuts“ in den Shared Components“ zurückgreifen und die Quick Picks über PL/SQL dynamisch erstellen.

Oracle APEX

Wenn man einen neuen Shortcut erstellt muss als Type „PL/SQL Function Body“ ausgewählt und im Feld „Shortcut“ ein entsprechender PL/SL-Code geschrieben werden.

declare
  v_qp varchar2(32767);
begin
  v_qp := apex_string.format(q'{%1}', 'P1_BETRAG', 'JS-QP');
  return apex_string.format('%0', v_qp);
end;

Create Shortcut

Danach muss der Name des Shortcuts noch im Eigenschaftsfenster des gewünschten Items unter „Post Text“ mittels Anführungszeichen angegeben werden, um APEX mitzuteilen, dass hier der aus dem Shortcut generierte HTML-Code ausgegeben werden soll.

Advanced Post

Man erhält dasselbe gewünschte Ergebnis, es ist aber bei der Umsetzung mit Shortcuts möglich, beliebig viele Quick Picks mit unterschiedlicher Funktion zu erstellen, ohne ständig das Item anpassen zu müssen.

Dynamic Quick Picks

Generell ist es so nun möglich, jeden erdenklichen JavaScript-Code in Quick Picks zu verpacken, es muss nicht einmal eine Quick Pick-Funktionalität dahinterstehen.

Dynamische Quick Picks in Oracle APEX

Wer seine Web-Applikation in Oracle APEX auf Usability optimiert, wird wahrscheinlich schon einmal über das Feature der sogenannten Quick Picks gestoßen sein.
Unter Quick Picks versteht man vordefinierte Usereingaben, die ein Endanwender mittels Mausklick auslösen kann und das entsprechende Feld mit einem vordefinierten Wert befüllt, ohne diesen extra eintippen zu müssen.

 Quickpicks_dynamic_01

Konfiguriert werden diese Quick Picks über das Eigenschafts-Fenster des jeweiligen Page-Items.

Quickpick Eigenschaften

Quick Picks können also die Eingabe des Endbenutzers ein wenig beschleunigen, allerdings sieht es auf den ersten Blick so aus, als ob nur statische Werte vergeben werden können. Dies ist nicht ganz korrekt, denn mit der üblichen Notation in Oracle APEX können auch Werte aus beliebigen Items angesprochen werden (Kaufmännisches Und + Item-Name + Punkt), dies sieht zum Beispiel wie folgt aus:

Quickpick_Eigenschaften_marked

Diese Art der Definition führt dazu, dass der Wert aus dem jeweiligen Item als Quick Pick gesetzt wird.

Quickpick dynamic zweiter wert

Einzige Bedingung hierfür ist allerdings, dass der Wert bereits beim Aufbau der Seite gesetzt wird, denn der Quick Pick wird vorab mit dem Wert des entsprechenden referenzierten Items erstellt und ändert sich nicht dynamisch mit. Auch ist die Anzahl der Quick Picks vorab auf die statisch definierten Labels und Values beschränkt, es ist also auf diese Art beispielsweise nicht möglich dynamisch einmal zwei und einmal drei Quick Picks unter einem Feld anzuzeigen.

Um nun beliebig viele Werte dynamisch als Quick Picks anzuzeigen, muss man etwas tiefer in die Trickkiste greifen. Um dies zu ermöglichen, bedient man sich dem Wissen, dass die Quick Picks über eine CSS-Klasse positioniert und gestaltet werden und der entsprechende HTML-Code einfach an das gewünschte Item angehängt wird. Mit diesem Wissen ist es nun möglich, die Quick Picks eigenständig zu erstellen.

Für die hier verwendete Testseite wurde eine simple Tabelle erstellt, welche beliebig viele Werte und Bezeichnungen für Quick Picks enthält. Die Tabelle wurde QP_TAB genannt und beinhaltet aktuell folgende Werte:

Werte Tabelle Quickpick

Ziel soll es nun sein für jede Zeile dieser Tabelle einen separaten Quick Pick für ein Item zu generieren. (Allgemein ist es egal, woher die Daten für die Quick Picks stammen, einzige Bedingung ist, dass man diese mittels PL/SQL ermitteln kann.)

Um die Anzahl der Quick Picks nun auch dynamisch umzusetzen zu können, benötigt man ein weiteres Oracle APEX Feature: die sogenannten „Shortcuts“. Diese befinden sich in den Shared Components wie im folgenden Screenshot ersichtlich ist.

Oracle Apex

Klickt man nun auf Shortcuts, kann man auf der Folgeseite ein neues Objekt erstellen.

Create Shortcut

Wichtig hierbei ist, dass als „Type“ „PL/SQL Function Body” ausgewählt wird und dann im Feld „Shortcut“ ein entsprechender Code geschrieben wird, der die gewünschten Daten in eine entsprechende HTML-Struktur bringt. Der Code für das hier verwendete Beispiel sieht nun folgendermaßen aus:

declare
  v_qp varchar2(32767);
begin
  for rec_qp in (select bezeichnung, wert from qp_tab) loop
    if v_qp is not null then
      v_qp := v_qp || ', ';
    end if;
      v_qp := v_qp || apex_string.format(q'{%1}', rec_qp.wert, rec_qp.bezeichnung);
  end loop;
  return apex_string.format('%0', v_qp);
end;

Wichtig ist hier eigentlich nur, dass die einzelnen Links (die resultierenden Quick Picks) in einem Span-Container der CSS-Klasse „apex-quick-picks“ liegen.

Der eigentliche Link ist dann Javascript-Code, welcher mit „$s“ das Setzen eines Items mit der entsprechenden Angabe des Wertes umsetzt. (Der Platzhalter „#CURRENT_ITEM_NAME#“ wird im folgenden Schritt aufgelöst.)

Wieder zurück auf der Seite kann man im Eigenschaftsfenster des gewünschten Items nun im „Advanced“-Bereich im „Post Text“-Feld den Namen des zuvor erstellten Shortcuts in Anführungszeichen eintragen. Dadurch wird der durch den Shortcut generierte HTML-Text nach dem Item im Quellcode eingefügt. Oracle APEX weiß nun auch, dass der Platzhalter „#CURRENT_ITEM_NAME#“ mit dem Namen des Items ersetzt wird (diese Funktionalität des Ansprechens des Item-Namens ist über den Post Text allgemein möglich).

Bei den Quick Picks werden diesmal keine statischen Werte angegeben (da der Code für die Quick Picks ja bereits durch den Shortcut an das Item angehängt wurde). Um aber die Positionierung des durch den Shortcut erstellten HTML-Code auch korrekt anzuzeigen, wird das Flag „Show Quick Picks“ auf „Yes“ gesetzt.

So erhält man die dynamisch erstellten Quick Picks:

Würde man in Tabelle QP_TAB nun einen weiteren Eintrag erfassen, würde dieser auch entsprechend auf der APEX-Seite als weiterer Quick Pick erscheinen, ohne dass man dafür das Item anpassen müsste.

Die einzige Limitierung der Anzahl der Quick Picks entsteht durch die Zeichenanzahl des VARCHAR2 im angelegten Shortcut.

Oracle APEX: Custom error messages mit APEX_ERROR

Die Haupt-Anwendungsfälle im Zusammenhang mit der Ausgabe von Fehlermeldungen in APEX-Applikationen dürften die meisten Entwickler nach einer verhältnismäßig kurzen Einarbeitungszeit kennen:

  • Entweder werden Validations ausgeführt, schlagen an und sollen den Endanwender darüber informieren, dass eine entsprechende Bedingung verletzt wurde.
  • Oder während der Ausführung eines Page Processes tritt ein Fehler auf, über den der Endanwender in Kenntnis gesetzt werden soll.

 

Wollten Entwickler in älteren APEX-Versionen eigene Fehlermeldungen ausgeben, geschah dies häufig über die Nutzung der Variable apex_application.g_print_success_message unter zusätzlicher Angabe von Style-Informationen:

apex_application.g_print_success_message := '<span style="color:red;">Achtung: Bei der Aufbereitung der Datei-Inhalte sind insgesamt 23 Fehler aufgetreten, die Daten konnten daher nicht importiert werden.</span>';

Mittlerweile jedoch stellt das APEX-eigene Package APEX_ERROR viel elegantere Möglichkeiten für den Umgang mit eigenen Fehlermeldungen zur Verfügung:

 

Die überladene Prozedur ADD_ERROR bietet verschiedene Varianten an um eigene Fehlermeldungen auf den Error Stack zu legen, die Darstellung übernimmt dabei APEX, so dass die Notwendigkeit zu expliziten Style-Angaben entfallen kann. Ein entsprechender Prozess im Page Processing könnte damit beispielsweise so aussehen:

DECLARE
 -- define some variables
 v_status NUMBER;
 ...
BEGIN
-- do some fancy stuff
...
 v_status := my_schema.my_package.my_function(p_var => '[MY_VALUE]');

IF v_status > 0 THEN
apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
p_message => 'Achtung: Es ist ein Fehler aufgetreten, so dass…');
 END IF;
EXCEPTION
WHEN OTHERS THEN
-- do some error logging
...
RAISE;
END;

Das eigentliche Potential entfaltet dieses Package aber dann, wenn man auf auftretende Fehler speziell reagieren will, beispielsweise weil gewisse Fehler häufiger auftreten und die Seite speziell für bestimmte Fehlerfälle bestimmte zusätzliche Button-Aktionen oder Auswertungen zur Verfügung stellen soll. Dafür kann man statt der Variable v_status im obigen Code ein Hidden Item P12_STATUS setzen und nach dem Ausführen der Prozesse über einen Branch die Seite neu aufrufen und das Item dabei übergeben. Der Aufruf von APEX_ERROR geschieht nun nicht mehr im Page Processing sondern im Page Rendering und nur dann wenn P12_STATUS einen Wert > 0 enthält

IF :P12_STATUS = '1' THEN
apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
 p_message => 'Achtung: Das Hochladen der Daten war nicht erfolgreich, so dass…');
ELSIF :P12_STATUS = '2' THEN
 apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
p_message => 'Achtung: Das Parsen der Daten ist fehlgeschlagen, so dass…');
ELSE
...
END IF;

Zusätzlich besteht nun natürlich die Möglichkeit status-abhängig zusätzliche Items etc. anzubieten – zum Beispiel ein Button um die Daten erneut hochzuladen für P12_STATUS = ‚1‘ oder ein Report mit den Parsing-Fehlern für P12_STATUS = ‚2‘.

Oracle Active Dataguard and Cloning of PDBs – the easy way

In diesem Artikel werden wir mehrere Features der 12.1.0.2 Datenbank beschreiben.

Active Data Guard, ein kostenpflichtiges Feature um Reporting auf die Standby Seite zu bringen (und tolle Administrationsfeatures beinhaltet, wie z. B. vereinfachtes Cloning (wie im Artikel beschrieben) oder Global Data Services).
Multitenant, ein Feature, das man für Datenbank-Konsolidierung benutzt und neue Möglichkeiten zur Administration anbietet.
PDB Cloning in einem ADG Environment: Clones kann man als eine Art von Backup sehen, wenn man Applikation Upgrades oder irgendwelche invasiven Tätigkeiten durchführt und einen schnellen Fallback haben möchte.
In höheren Versionen gibt es bessere Möglichkeiten, wie Flashback PDB oder PDB PITR.

Dieser Artikel dient zur Verbesserung des Daily Business, um Kunden zufriedener machen zu können. Wenn man schon Lizenzen besitzt, sollte man auch die zur Verfügung stehenden Features benutzen, nicht wahr?

Manche unserer Kunden haben Multitenant und Active Data Guard lizensiert, wobei man Multitenant sehr selten für Cloning oder verschiedene andere Tätigkeiten benutzt. Es ist auch klar, weshalb dies so ist, denn viele Kunden kennen diese Features nicht. Es gehört zu unseren Aufgaben, unseren Kunden diese Features vorzustellen und deren Einsatzgebiete zu erklären. Ein glücklicher Kunde ist der beste Kunde.

Bis vor kurzen war es so, dass wir entweder Restore Points erstellt haben, die in Wahrheit in einem 12.1.0.2 CDB Environment nicht wirklich benutzbar sind, denn man kann nur den ganzen Container zurückspielen oder wir mussten Duplicates (Restore) erstellen, was Zeit beanspruchte und unnötige Downtime verursachte. Da wir gern diese Tätigkeiten optimieren, ist es nötig Kunden zu informieren wie man es in Zukunft machen könnte, damit die Projekte weitergehen und jeder zufrieden ist.
Dieses Dokument beschreibt die Möglichkeiten, die sehr einfach sind, wenig Zeit kosten, nur freien Storage und einen DBA brauchen, der diese Features kennt.

Active Data Guard erlaubt es den Kunden das Reporting auf die Disaster Systeme zu schwenken, damit die Performance der Produktivsysteme, bei performance-lastigen Reports, nicht beeinträchtigt wird. Es ermöglicht uns weiterhin die Dinge zu tun, die schnell sind, Downtimes vermeiden und Service Agreements leicht erfüllen.

Wenn man in einem Active Data Guard Environment eine lokale PDB klont, werden die Standby PDBs automatisch erstellt und in einer 12.1.0.2 sind dazu nur 5 Kommandos notwendig (denn man muss die PSORGER PDB wieder READ WRITE öffnen).
In höheren Versionen kann man einen Hot Clone erstellen, wenn man Local Undo benutzt, das heißt Zero Downtime.

 

SYS@CDB1 SQL> alter pluggable database psorger close immediate instances=all ;
Pluggable database altered.
SYS@CDB1 SQL> alter pluggable database psorger open read only instances=all ;
Pluggable database altered.
SYS@CDB1 SQL> create pluggable database psorgerc from psorger ;
Pluggable database created.


[oracle@exa2vm01 ~]$ tail -f $al
  Mem# 0: +DATAX6C1/E2CDB1/ONLINELOG/group_22.2191.970881745
  Mem# 1: +RECOX6C1/E2CDB1/ONLINELOG/group_22.9224.970881745
1: +RECOX6C1/E2CDB1/ONLINELOG/group_22.9224.970881745
Thu Sep 10 20:36:27 2020
Recovery created pluggable database PSORGERC
Thu Sep 10 20:36:54 2020
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file +DATAX6C1/E2CDB1/AEFAD6D19935CDF7E0537D001BAC3B4F/DATAFILE/system.2920.1050784587 from +DATAX6C1/E2CDB1/647EDCDA7FB28F8DE0538B001BACEE69/DATAFILE/system.1628.967329427
Datafile 23 added to flashback set
Successfully added datafile 23 to media recovery
Datafile #23: '+DATAX6C1/E2CDB1/AEFAD6D19935CDF7E0537D001BAC3B4F/DATAFILE/system.2920.1050784587'
Thu Sep 10 20:37:23 2020
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file +DATAX6C1/E2CDB1/AEFAD6D19935CDF7E0537D001BAC3B4F/DATAFILE/sysaux.2929.1050784615 from +DATAX6C1/E2CDB1/647EDCDA7FB28F8DE0538B001BACEE69/DATAFILE/sysaux.1629.967329427
Datafile 24 added to flashback set

Wie man sieht reichen 3 Kommandos aus, um eine Pluggable Database zu klonen (EE 12.1.0.2 mit ADG). Eine 100GB Datenbank kann auf einer Exadata in wenigen Minuten geklont werden und somit verfügt man über eine „Point in Time“ Kopie der PDB, die man im Fall eines schnellen „Restores“ wieder benutzen kann. Es handelt sich hierbei nicht um einen Restore, aus Kundensicht sieht es jedoch so aus, als würde man etwas aus der Vergangenheit zurückbringen. Letztendlich wird hierbei nur ein mächtiges Feature der Enterprise Edition von Oracle genutzt.

Natürlich gibt es aber auch Situationen, in denen ein Restore erforderlich wird, da keine Clones erstellt wurden. In diesem Fall ist es am besten einen DUPLICATE vom Backup zu erzeugen. Dies ist einfach umzusetzen, braucht nur wenige Kommandos und ist automatisiert, so dass der DBA wenig Zeit mit der Kommandoeingabe verliert. Mehr Informationen zu diesem Thema folgen in einem weiteren Artikel.

Jetzt werden wir uns aber dem Thema cloning weiter widmen und zeigen in wenigen Kommandos, wie wir aus dem Duplikat die Pluggable Database wieder in den korrekten Container einfügen können.

Das Scenario beinhaltet:
1x Active Data Guard Configuration (12.1.0.2.200114) – CDB1 mit mehreren PDBs, PDB1, PDB2, PDB3
1x Duplikat der CDB Point in Time Recovered (12.1.0.2.200114) – CDBDUP mit PDB1

Die technischen Schritte um die PDB1 aus der CDBDUP in die CDB1 zu kopieren sind folgende:

1. In der CDB1 löschen wir die PDB1

alter pluggable database pdb1 close immediate instances=all ;
drop pluggable database pdb1 including datafiles ;

2. In der CDBDUP der PDB1 erstellen wir einen Benutzer mit Berechtigungen zum Klonen

ALTER SESSION SET CONTAINER=pdb1 ;
CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

3. In den tnsnames.ora beider DB Systeme definieren wir einen Connect String der im Scenario benutzt wird. Es ist gut nicht produktive Connect Strings zu benutzen, so weiß man zu 100% wo man sich einloggt

PDB1_PS=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost)(PORT=1521))(CONNECT_DATA=(SID=CDBDUP)(SERVICE_NAME=pdb1)(INSTANCE_NAME=CDBDUP)))

4. In der CDB1 auf der Standby Seite setzen wir einen Parameter damit die PDB automatisch in das Standby Environment kopiert wird.

alter system set standby_pdb_source_file_dblink='pdb1_ps' ;

5. In der CDBDUP setzen wir die PDB1 in READ ONLY Modus (nach Duplicate ist die DB im MOUNT Zustand, in höheren Versionen ist diese Read Only Restriction nicht mehr da, da könnte man dann einen Relocate zB. machen)

alter pluggable database PDB1 open read only ;

6. In der CDB1 erstellen wir einen Database Link zu der PDB1 im CDBDUP

create database link pdb1_ps connect to remote_clone_user identified by remote_clone_user using 'pdb1_ps' ;

7. Wir erstellen einen Klon der PDB in der CDB1 und warten bis es fertig wird. Auf einer Exadata X6-2 dauerte eine 110GB PDB 10 Minuten. Im Background wird der Database Link für den Klon auf die Standby Seite benutzt.

create pluggable database PDB1 from pdb1@pdb1_ps ;

8. Wir öffnen die PDB1 und sind mit der Bereitstellung der PDB1 fertig. Wir öffnen die PDB mit dem Application Service damit es auf den korrekten Instanzen startet (im RAC kann es 1 Node sein oder auch 10)

alter pluggable database PDB1 close immediate instances=all ;
srvctl start service -db CDB1 -pdb pdb1_svc

Somit haben wir die PDB1 wieder in Betrieb genommen und die Welt ist für heute gerettet.
In einem Data Guard Environment geht es leider nicht so einfach, man muss manuell die Datafiles auf die Standby Seite transferieren. Um mehr Informationen darüber zu erfahren bitte den Oracle Note durchlesen.
Using standby_pdb_source_file_dblink and standby_pdb_source_file_directory to Maintain Standby Databases when Performing PDB Remote Clones or Plugins (Doc ID 2274735.1)

Oracle Statspack verbessern: Historische Pläne – Teil 2

Grundlegendes: Ändern des Statspack Code

English version here : Improving Statspack: Add Support for Plan Stability

Natürlich sollte man den Statspack Code nicht leichtfertig ändern. Das wird Niemand wollen. Die hier besprochene Änderung erlaubt es, schnell und ohne großes Nachdenken einen Plan aus der Vergangenheit wieder her zu stellen.

Für manche Applikationen und Datenkonstellationen ist diese Möglichkeit wirklich wichtig.
In diesem Fall sollte man es sich gründlich überlegen, ob man die hier beschriebene Veränderung nicht doch machen will.

Schließlich hat man den Source Code des Statspack. Man sollte die originale Codeversion behalten und kann daher jederzeit den Originalcode schnell wiederherstellen. Die zusätzliche optionale Spalte stört da nicht.

Ausgangslage

Bei meinem Webinar „The good plan and the bad Plan” für die DOAG ging es darum, gute Pläne aus der Vergangenheit wieder zu aktivieren.

Kann man die AWR Daten lesen, so kann man beispielsweise aus den DBA_HIST Daten mittels dbms_xplan.display_awr ein Outline erzeugen. Dieses Outline kann man dann in aktuelle Pläne einpflanzen.

Ich wurde damals gefragt, ob eine solche Methode auch auf einer Oracle Standard Edition Datenbank funktioniert.

Mit dem SQL_PATCH [2] gibt es eine legale Möglichkeit, ein Outline in einen SQL Befehl einzupflanzen.

Die Frage ist nur, wie man auf einer Oracle Standard Edition zu einem Outline eines alten Planes kommt.
Gibt es mit Statspack eine Funktion ähnlich dem dbms_xplan.display_awr?

Uwe Küchler, alias Oraculix hat eine sehr kreative Lösung parat [1].

Man verwendet einfach dbms_xplan.display im Zusammenhang mit einer Suchfunktion:

select * from table(dbms_xplan.display(
  table_name   => 'perfstat.stats$sql_plan',
  statement_id => null,
  format       => 'ALL -predicate -note',
  filter_preds => 'plan_hash_value = '|| &&phv
);

Man muss dazu noch eine Spalte in der Tabelle stats$sql_plan ergänzen, die Einzelheiten finden Sie im Oraculix Blog.

Was passiert nun, wenn man jetzt einfach im Format noch +OUTLINE angibt?
Dann müsste man doch das Outline bekommen. Da könnte man dann über einen SQL_PATCH in ein Statement schreiben und voila, hat man den alten Plan zurück.

Oder etwa nicht?

Nein, die Format Anweisung wird scheinbar einfach ignoriert.
Der Grund hierfür ist, dass in den Statspack Plänen einige Spalten fehlen. Konkret geht es hier um die Spalte OTHER_XML, die nicht mitgespeichert wird.

Die Spalte other_xml

In dieser Spalte findet sich unter anderen das Outline. Wer also die sehr wichtige Funktion der historischen Pläne haben will, darf nicht davor zurückscheuen, den Statspack code zu verändern. Wenn Oracle schon die der MOS Note 2182680.1 vorschlägt das Statspack zu verändern, weshalb sollte wir nicht ein wenig weiter gehen?

Wir beginnen, in dem wir die Spalte hinzufügen.

ALTER TABLE perfstat.stats$sql_plan ADD timestamp INVISIBLE AS (cast(NULL AS DATE));
ALTER TABLE perfstat.stats$sql_plan ADD OTHER_XML CLOB;

Danach müssen wir den Code anpassen. Analog zum Vorgehen in MOS Note 2182680.1 habe ich zuerst eine Kopie des aktuellen Codes gemacht.
Wieder ist es das Skript spcpkg, welches angepasst werden muss.

Zu ändern ist das Insert Statement beginnend mit:

insert into stats$sql_plan
                 ( plan_hash_value
                 , id
                 , operation
                 , options
                 , object_node
                 , object#

Hier muss die Spalte Other_XML ergänzt werden. Dabei gibt es jedoch eine Schwierigkeit.

Im Select Teil des Inserts steht eine Maximum Funktion, z.B. so:

                 , max(sp.operation)
                 , max(sp.options)
                 , max(sp.object_node)
                 , max(sp.object#)
                 , max(sp.object_owner)
                 , max(sp.object_name)
                 , max(sp.object_alias)
                 , max(sp.object_type)

Die Maximum Funktion ist ziemlich sicher nur dafür da, um duplizierte Sätze zu vermeiden.

Also schreiben wir doch einfach: Max(other_xml), oder?

Prompt kommt ORA-00932. Der Grund dafür ist, dass CLOB nicht sortierbar sind, daher kann auch kein Maximum berechnet werden.

ANY_VALUE

Natürlich gäbe es noch andere Möglichkeiten, um die Werteliste eindeutig zu machen.
Beispielsweise analytische Funktionen wie row_number ().

Ich wolle jedoch nicht so viel am Original Code verändern.

Mir fiel eine Idee aus dem Oracle Technologie Network ein, bei der es darum ging, für genau solche Probleme eine neue Art von Gruppenfunktion ein zu führen, genannt ANY_VALUE [3.].

Die Idee wird anscheinend mit Version 20c umgesetzt. Zu schade. Mit Version 19 wäre besser.

Als ehemaliger Oracle Mitarbeiter weiß ich jedoch, dass manchmal die Vorgängerversionen undokumentiert schon Änderungen der Folgeversion enthalten.

Und in der Tat: any_value gibt es schon.
Leider wirft auch diese Implementation völlig unnötig ORA-00932.

Ich habe Chris Saxon darauf aufmerksam gemacht und hoffe das Oracle die Implementierung noch ändert.
Nun, wenn Oracle nicht liefert muss ich das selbst machen.

Ich habe mir über das User-Defined Aggregate Functions Interface die Funktion selbst definiert und any_lob genannt.

Den Code finden sie am Ende dieses Blogs. Hier erst einmal der vollständige Insert Befehl. Die alle vom Standard abweichenden Änderungen sind mit Rot gekennzeichnet. Die Hints habe ich im vorigen Blog zum Thema Statspack beschrieben.

            insert into stats$sql_plan
                 ( plan_hash_value
                 , id
                 , operation
                 , options
                 , object_node
                 , object#
                 , object_owner
                 , object_name
                 , object_alias
                 , object_type
                 , optimizer
                 , parent_id
                 , depth
                 , position
                 , search_columns
                 , cost
                 , cardinality
                 , bytes
                 , other_tag
                 , partition_start
                 , partition_stop
                 , partition_id
                 , other
                 , other_xml                 
                 , distribution
                 , cpu_cost
                 , io_cost
                 , temp_space
                 , access_predicates
                 , filter_predicates
                 , projection
                 , time
                 , qblock_name
                 , remarks
                 , snap_id
                 )
            select /*+ leading(spu@np ssp@sq  s sp) */
                   new_plan.plan_hash_value
                 , sp.id
                 , max(sp.operation)
                 , max(sp.options)
                 , max(sp.object_node)
                 , max(sp.object#)
                 , max(sp.object_owner)
                 , max(sp.object_name)
                 , max(sp.object_alias)
                 , max(sp.object_type)
                 , max(sp.optimizer)
                 , max(sp.parent_id)
                 , max(sp.depth)
                 , max(sp.position)
                 , max(sp.search_columns)
                 , max(sp.cost)
                 , max(sp.cardinality)
                 , max(sp.bytes)
                 , max(sp.other_tag)
                 , max(sp.partition_start)
                 , max(sp.partition_stop)
                 , max(sp.partition_id)
                 , max(sp.other)
                 , any_lob(sp.other_xml)
                 , max(sp.distribution)
                 , max(sp.cpu_cost)
                 , max(sp.io_cost)
                 , max(sp.temp_space)
                 , 0 -- should be max(sp.access_predicates) (2254299)
                 , 0 -- should be max(sp.filter_predicates)
                 , max(sp.projection)
                 , max(sp.time)
                 , max(sp.qblock_name)
                 , max(sp.remarks)
                 , max(new_plan.snap_id)
              from (select /*+ QB_NAME(NP)  */  
                           spu.plan_hash_value
                         , spu.hash_value    hash_value
                         , spu.address       address
                         , spu.text_subset   text_subset
                         , spu.snap_id       snap_id
                      from stats$sql_plan_usage spu
                     where spu.snap_id         = l_snap_id
                       and spu.dbid            = p_dbid
                       and spu.instance_number = p_instance_number
                       and not exists (select /*+ QB_NAME(SQ)  */ *
                                         from stats$sql_plan ssp
                                        where ssp.plan_hash_value 
                                            = spu.plan_hash_value
                                      )
                   )          new_plan
                 , v$sql      s      -- join reqd to filter already known plans
                 , v$sql_plan sp
             where s.address         = new_plan.address
               and s.plan_hash_value = new_plan.plan_hash_value
               and s.hash_value      = new_plan.hash_value
               and sp.hash_value     = new_plan.hash_value
               and sp.address        = new_plan.address
               and sp.hash_value     = s.hash_value
               and sp.address        = s.address
               and sp.child_number   = s.child_number
             group by 
                   new_plan.plan_hash_value, sp.id
             order by
                   new_plan.plan_hash_value, sp.id; -- deadlock avoidance

Zusammenfassung

Der Code scheint recht gut zu funktionieren.

Nur eine Sache war etwas ärgerlich: Immer, wenn ich Syntaxfehler im Insert hatte, musste ich auch die Funktion any_lob neu kompilieren.

Hinweis: Man muss den Snap Level auf 6 oder höher schalten, damit das Statspack Pläne sammelt.  Z.B.:

EXECUTE statspack.snap(i_snap_level => 7);

Create or replace type any_lob_type as object
(
  v_clob CLOB,

  static function ODCIAggregateInitialize
    ( sctx in out any_lob_type )
    return number ,

  member function ODCIAggregateIterate
    ( self  in out any_lob_type ,
      value in     CLOB
    ) return number ,

  member function ODCIAggregateTerminate
    ( self        in  any_lob_type,
      returnvalue out CLOB,
      flags in number
    ) return number ,

  member function ODCIAggregateMerge
    ( self in out any_lob_type,
      ctx2 in     any_lob_type
    ) return number
);
/

create or replace type body any_lob_type
is
  static function ODCIAggregateInitialize
  ( sctx in out any_lob_type )
  return number
  is
  begin
    sctx := any_lob_type( null ) ;
    return ODCIConst.Success ;
  end;

  member function ODCIAggregateIterate
  ( self  in out any_lob_type ,
    value in     CLOB
  ) return number
  is
  begin
    self.v_clob := value ;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate
  ( self        in  any_lob_type ,
    returnvalue out CLOB ,
    flags       in  number
  ) return number
  is
  begin
    returnvalue := self.v_clob;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge
  ( self in out any_lob_type ,
    ctx2 in     any_lob_type
  ) return number
  is
  begin
      return ODCIConst.Success;
  end;
end;
/

create or replace function any_lob
  ( input CLOB )
  return CLOB
  deterministic
  parallel_enable
  aggregate using any_lob_type
;
grant execute on any_lob to public;
create public sysnonym for sys.any_lob;

Quellen

 

Oracle Statspack verbessern: Schnellere Snapshots – Teil1

Oracle Statspack verbessern: Schnellere Snapshots – Teil1

Ausgangslage: Langsamer Statspack Snapshot

Auf verschiedenen Standard Edition Datenbanken sieht man immer wieder den Statspack Snapshot lange laufen.

Dies ist für mich als Consultant sehr unangenehm.
Oft muss ich den Kunden sagen, dass das Statspack auf einer Standard Edition Datenbank unverzichtbar ist.

Und dann sieht mein Kunde, wie der Statspack Snapshot minutenlang einen der kostbaren, limitierten Cores blockiert.

Ich beschloss, etwas dagegen zu unternehmen.
Bei näherer Betrachtung erkennt man zwei Statements, die die Datenbank belasten.

In erster Linie ist dies:

INSERT INTO stats$sql_plan

Aber auch dieser Befehl läuft lange:

INSERT INTO stats$seg_stat

Grundsätzliches: Hints im Statspack

Man hört oft, dass man Hints so wenig wie möglich machen soll und lieber andere Mittel verwenden soll, um den Optimizer zu steuern.

Gute Statistiken zum Beispiel.
Ich bin auch dieser Meinung.
Allerdings gibt es auch Ausnahmen.

Bei Tools wie Statspack kann man sich nicht darauf verlassen, dass die Statistiken immer aktuell sind.

Es werden auch interne Tabellen X$ verwendet. Nicht jeder DBA macht fixed table stats und selbst wenn, schwanken diese Statistiken oft stark.

Ein Tool wie Statspack muss immer zuverlässig laufen.
Glücklicherweise ist bei den zwei Statspack Queries die wir betrachten werden, ziemlich klar wie der Plan auszusehen hat.

Hints stellen also kein großes Risiko dar. Jedoch habe ich dennoch so wenig wie möglich festgelegt.

Statement : INSERT INTO stats$sql_plan

Das Problem wird in der MOS Note 2182680.1 behandelt.

In dieser Note wird vorgeschlagen eine alternative Implementation des Statspack Package (SCPPKG.SQL) herunter zu laden und zu implementieren.

Diese Implementation enthält einen geänderten Hint, den ich im folgenden Beispiel mit Rot hervorgehoben habe.

Vom ganzen Insert Statement zeige ich nur den SELECT Teil, weil der Eintrag sonst zu lange wird.
Auch vom Select Teil habe ich die Liste der Spalten gekürzt, damit der Code Teil übersichtlich bleibt.

SELECT /*+ no_merge(new_plan) leading(new_plan s sp) use_nl(s) use_nl(sp) */
            new_plan.plan_hash_value,
            sp.id,
            MAX(sp.operation),
            MAX(sp.options),  
            . . .
            MAX(new_plan.snap_id)
        FROM
            (
                SELECT /*+ index(spu) */
                    spu.plan_hash_value,
                    spu.hash_value    hash_value,
                    spu.address       address,
                    spu.text_subset   text_subset,
                    spu.snap_id       snap_id
                FROM
                    stats$sql_plan_usage spu
                WHERE
                    spu.snap_id = :b3
                    AND spu.dbid = :b2
                    AND spu.instance_number = :b1
                    AND NOT EXISTS (
                        SELECT /*+ nl_aj */
                            *
                        FROM
                            stats$sql_plan ssp
                        WHERE
                            ssp.plan_hash_value = spu.plan_hash_value
                    )

Wie unschwer zu erkennen ist, befinden sie relevanten Suchkriterien auf der Tabelle stats$sql_plan_usage mit dem Alias spu.

Es ist also wichtig, dass im Execution Plan mit dem Lesen dieser Tabelle begonnen wird.

Der Hint Leading(new_plan .. ist also folgerichtig.

Das Problem besteht jedoch darin, dass new_plan keine Tabelle, sondern der Name einer Unterfrage ist.
Obwohl Version 19c den Hint als korrekt meldet, wird er in tieferen Versionen oft nicht akzeptiert.

Der Alias für den Hint wäre eigentlich spu, jedoch ist dieser in der Hauptabfrage nicht zugänglich, weil die Tabelle stats$sql_plan_usage in einer Unterabfrage angesprochen wird. (Achtung: wenn ein Alias vorhanden ist, muss der Alias im Hint angegeben werden und nicht der Tabelle Name.)

Diese Unterabfrage bildet einen eigenen Queryblock und dessen Inhalt ist von der Hauptabfrage nicht direkt referenzierbar.

In der Tat zeigt der Plan des Insert Befehles, dass der Leading Hint ignoriert wird.

Hier ein Beispiel von einem unserer Kunden:

Operation | Name | A-Time 
-------------------------------------------------------------------------------
INSERT STATEMENT | |00:05:07.72
 LOAD TABLE CONVENTIONAL | STATS$SQL_PLAN |00:05:07.72
  SORT GROUP BY | |00:05:07.72
   NESTED LOOPS | |00:05:07.71
    NESTED LOOPS | |00:05:07.71
     FIXED TABLE FULL | X$KGLCURSOR_CHILD |00:00:02.30
     VIEW PUSHED PREDICATE | |00:05:05.33
      NESTED LOOPS ANTI | |00:05:05.23
       TABLE ACCESS BY INDEX ROWID BATCHED| STATS$SQL_PLAN_USAGE |00:03:42.53
        INDEX RANGE SCAN | STATS$SQL_PLAN_USAGE_PK|00:02:22.49
       INDEX RANGE SCAN | STATS$SQL_PLAN_PK |00:01:03.19
   FIXED TABLE FIXED INDEX | X$KQLFXPL (ind:3) |00:00:00.01
-------------------------------------------------------------------------------

Wie man sieht, benötigt der Insert 5 Minuten und 8 Sekunden.

Queryblöcke

Um eine Queryblock in der Hauptabfrage referenzieren zu können, muss man dem untergeordneten Queryblock mit dem QB_NAME hint einen Namen geben.

Dann kann man die Tabellen des untergeordneten Queryblocks mittels „tabellenalias@queryblock“ ansprechen.
Dies ist ein dokumentiertes Vorgehen und sollte in allen Versionen stabil funktionieren.

In unserem Beispiel sieht das so aus:

SELECT /*+ leading(spu@np ssp@sq  s sp) */
            new_plan.plan_hash_value,
            sp.id,
... 
            MAX(new_plan.snap_id)
        FROM
            (
                SELECT /*+ QB_NAME(NP)  */  
                    spu.plan_hash_value,
                    spu.hash_value    hash_value,
                    spu.address       address,
                    spu.text_subset   text_subset,
                    spu.snap_id       snap_id
                FROM
                    stats$sql_plan_usage spu
                WHERE
                    spu.snap_id = :b3
                    AND spu.dbid = :b2
                    AND spu.instance_number = :b1
                            AND NOT EXISTS (
                        SELECT /*+ QB_NAME(SQ)  */
                            *
                        FROM
                            stats$sql_plan ssp
                        WHERE
                            ssp.plan_hash_value = spu.plan_hash_value
                    )
            ) new_plan,

Diesmal hält der Optimzer sich an die Hints, das Ergebnis sieht wie folgt aus:

--------------------------------------------------------------------------------
Operation                                | Name                     |   A-Time  
--------------------------------------------------------------------------------
INSERT STATEMENT                         |                          |00:00:00.05
 LOAD TABLE CONVENTIONAL                 | STATS$SQL_PLAN           |00:00:00.05
  SORT GROUP BY                          |                          |00:00:00.05
   NESTED LOOPS                          |                          |00:00:00.05
    NESTED LOOPS                         |                          |00:00:00.04
     HASH JOIN ANTI                      |                          |00:00:00.04
      TABLE ACCESS BY INDEX ROWID BATCHED| STATS$SQL_PLAN_USAGE     |00:00:00.01
       INDEX RANGE SCAN                  | STATS$SQL_PLAN_USAGE_PK  |00:00:00.01
      INDEX FAST FULL SCAN               | STATS$SQL_PLAN_PK        |00:00:00.01
     FIXED TABLE FIXED INDEX             | X$KGLCURSOR_CHILD (ind:1)|00:00:00.01
    FIXED TABLE FIXED INDEX              | X$KQLFXPL (ind:3)        |00:00:00.01
--------------------------------------------------------------------------------

Also 5 hundertstel Sekunden statt 5 Minuten.

Das ist über 6000x schneller und kann sich sehen lassen.

Nachdem ich die Hints gefunden hatte, habe ich eine Sicherheitskopie des Scriptes SCPPKG.SQL angelegt und den neuen Hint in das Package kopiert.
Durch den Aufruf des Scriptes wurde das neue Package dann installiert.

Statement: INSERT INTO stats$seg_stat

Wie sieht nun das zweite Insert aus?

Diesmal gibt es keine MOS Note.

Auch hier zeige ich wieder den sql code, mit den hervorgehobenen Hints:

SELECT /*+  ordered use_nl(s1.gv$segstat.X$KSOLSFTS) */
            :b3,
            :b2,
...
            SUM(decode(s1.statistic_name, 'row lock waits', value, 0))
        FROM
            v$segstat s1
        WHERE
            ( s1.dataobj#,
              s1.obj#,
              s1.ts# ) IN (
                SELECT /*+ unnest */
                    s2.dataobj#,
                    s2.obj#,
                    s2.ts#
                FROM
                    v$segstat s2
                WHERE
                    s2.obj# > 0
                    AND s2.obj# < 4254950912
                    AND ( decode(s2.statistic_name, 'logical reads', s2.value, 0) > :b10
                          OR decode(s2.statistic_name, 'physical reads', s2.value, 0) > :b9
                          OR decode(s2.statistic_name, 'buffer busy waits', s2.value, 0) > :b8
                          OR decode(s2.statistic_name, 'row lock waits', s2.value, 0) > :b7
                          OR decode(s2.statistic_name, 'ITL waits', s2.value, 0) > :b6
                          OR decode(s2.statistic_name, 'gc cr blocks received', s2.value, 0) > :b5
                          OR decode(s2.statistic_name, 'gc current blocks received', s2.value, 0) > :b4 )
            )
        GROUP BY
            s1.ts#,
            s1.obj#,
            s1.dataobj#
;

Wieder sieht man hier ein sehr ungewöhnliches Hint Format.

Wieder meldet aber die Report Funktion in 19c keinen Fehler und in unseren Test hat der Optimizer den Hint befolgt.

Jedoch bin ich diesmal mit der Wirkung des Hints nicht einverstanden.

Sehen wir uns dazu Laufzeitstatitiken an:

------------------------------------------------------------------------------
Id  | Operation                | Name          | Starts | A-Rows |   A-Time   
------------------------------------------------------------------------------
  0 | INSERT STATEMENT         |               |      1 |      0 |00:01:16.83 
  1 |  LOAD TABLE CONVENTIONAL | STATS$SEG_STAT|      1 |      0 |00:01:16.83 
  2 |   HASH GROUP BY          |               |      1 |    746 |00:01:16.83 
  3 |    NESTED LOOPS          |               |      1 |  19396 |00:01:16.81 
  4 |     VIEW                 | VW_NSO_1      |      1 |    747 |00:00:00.19 
  5 |      SORT UNIQUE         |               |      1 |    747 |00:00:00.19 
  6 |       FIXED TABLE FULL   | X$KSOLSFTS    |      1 |    814 |00:00:00.19 
  7 |     FIXED TABLE FULL     | X$KSOLSFTS    |    747 |  19396 |00:01:16.62 
------------------------------------------------------------------------------

Immerhin läuft auch dieser Befehl noch über eine Minute.

Die Zeit fällt fast zu 100% in der Zeile 7 an und zwar deshalb, weil die Zeile 747 Mal wiederholt wird.

Ein Hash join wäre hier wesentlich Laufzeit stabiler.
Der Nested Loop join hat Vorteile bei kleinen Datenmengen.

Allerdings spielt es ohnehin keine Rolle, welchen Join man nimmt, bei kleinen Datenmengen sind alle schnell.

Ich habe also den use_nl hint gegen einen use_hash hint getauscht.

Das ist das Resultat:

-------------------------------------------------------------------------------
Id  | Operation                | Name           | Starts | A-Rows |   A-Time   
-------------------------------------------------------------------------------
  0 | INSERT STATEMENT         |                |      1 |      0 |00:00:00.39 
  1 |  LOAD TABLE CONVENTIONAL | STATS$SEG_STAT |      1 |      0 |00:00:00.39 
  2 |   HASH GROUP BY          |                |      1 |    748 |00:00:00.39 
  3 |    HASH JOIN             |                |      1 |  19448 |00:00:00.38 
  4 |     VIEW                 | VW_NSO_1       |      1 |    748 |00:00:00.19 
  5 |      SORT UNIQUE         |                |      1 |    748 |00:00:00.19 
  6 |       FIXED TABLE FULL   | X$KSOLSFTS     |      1 |    815 |00:00:00.19 
  7 |     FIXED TABLE FULL     | X$KSOLSFTS     |      1 |    390K|00:00:00.13 
-------------------------------------------------------------------------------

Insgesamt läuft der Snapshot jetzt in 6 Sekunden durch.

Long Parse aufgrund von Skew Detection in Hybrid Hash Distribution

Für einen detaillierten Überblick wie Skew Detection/Skew Handling in Hybrid Hash Distributionen funktioniert und welche Ziele es verfolgt, empfehle ich den sehr guten Blogeintrag von Randolf Geist zu lesen: https://oracle-randolf.blogspot.com/2014/05/12c-hybrid-hash-distribution-with-skew.html

Im Blog von Herrn Geist werden unter anderem sieben Vorbedingungen genannt, die nötig sind, damit dieses Feature wirksam wird.

Zumindest in Oracle 12.2 konnte ich auf Basis meiner Untersuchung eine weitere Vorbedingung finden:
Dynamic Sampling muss ebenfalls aktiviert sein (d.h auf einem Level > 0 konfiguriert sein; der Default ist 2).

Die rekursive Query die während der Optimierungsphase (=Hard Parse) ausgeführt wird, um einen Skew zu erkennen sieht nun auch ein wenig anders aus.

Zum Großteil wurden hier weitere Hints hinzugefügt.
Auf Oracle 12.2 hat sie nun folgende Form:

--kkopqSkewInfo: Query:SELECT /* DS_SKEW */ /*+ RESULT_CACHE no_parallel dynamic_sampling(0) no_sql_tune no_monitoring */ * FROM (SELECT SYS_OP_COMBINED_HASH("ID"), COUNT(*) CNT, TO_CHAR("ID") FROM "ASC_SKEW_PART" SAMPLE(99.900000) SEED(1) GROUP BY "ID" ORDER BY CNT DESC, "ID") WHERE ROWNUM <= 2;

Was hier sofort auffällt ist die Größe des gewählten Samples (99.9%).

Nach ein paar Tests mit unterschiedlichen Tabellengrößen, kam ich zum Schluss, dass das Sample so gewählt wird, um ca. 5.500 Rows aus der Tabelle zu selektieren, um auf dieser Datenbasis die Skew Erkennung durchzuführen.

Die Beschränkung auf 5.500 Rows wurde in älteren Releases schon vorgenommen, wenn Histogramme im Zuge der Statistikberechnung erzeugt wurden. Für Tabellen die weniger als 5.500 Rows haben, wird das Sample mit 99,9% gewählt.

Sehen wir uns nun an unter welchen Umständen das zu sehr langen Parse Zeiten führen kann:

--#######################################
--# Small lookup table                  #
--#######################################
drop table asc_pids;
create table asc_pids
as
select 45e6 + rownum id
  from dual
 connect by level <= 13;
 
--#######################################
--# Big, partitioned fact table         #
--#######################################
--5 mio records in each partition from P1 to P9
--only 4 records in partition P10
drop table asc_skew_part;
create table asc_skew_part
nologging
partition by range(id)
(
   partition P1 values less than (5e6),
   partition P2 values less than (10e6),
   partition P3 values less than (15e6),
   partition P4 values less than (20e6),
   partition P5 values less than (25e6),
   partition P6 values less than (30e6),
   partition P7 values less than (35e6),
   partition P8 values less than (40e6),
   partition P9 values less than (45e6),
   partition P10 values less than (50e6)
)
as
with a as
(
    select rownum id
      from dual
     connect by level <= 1e5
)
select /*+ parallel(4) */rownum id, 
       lpad('*', 255, '*') padding
  from a, a
 where rownum <= 45e6
union all
select 45e6 + mod(rownum, 2) + 1 id,
       lpad('*', 255, '*') padding
  from dual
 connect by level <= 4;
 
--#######################################
--# histograms on id on fact table      #
--####################################### 
exec dbms_stats.gather_table_stats(user, 'asc_skew_part', method_opt=>'for all columns size 1 for columns id size 255', granularity=>'ALL');


--#######################################
--# clear RC                            #
--#######################################
exec dbms_result_cache.flush();

Ich habe eine große, partitionierte Faktentabelle erstellt. Darin befinden sich jeweils 5 Millionen Rows in den Partitionen P1 bis P9 und nur 4 Rows in der letzten Partition P10 (2 Rows pro Value, das reicht um die notwendige Vorbedingung für die Skew Erkennung zu erfüllen).

Nachdem ich Histogramme auf der Faktentabelle erzeugt und den Result Cache (aufgrund des Hints in der rekursiven Query) geflushed habe, joine ich diese Tabelle nun zur kleinen Lookup-Tabelle.
Dabei schränke ich in der where Klausel aber so ein, dass nur Datensätze der kleinen Partition P10 selektiert werden.

--#######################################
--# note the long parsing time          #
--#######################################
explain plan for
select /*+ leading(b a) use_hash(a) parallel(8) pq_distribute(a HASH HASH)*/*
  from asc_skew_part a, asc_pids b
 where a.id = b.id
   and a.id between 45000000 and 50000000;

Die rekursive Query die im 10053 Event/Optimizer Trace ersichtlich ist, ist exakt jene die ich schon zu Beginn des Posts gezeigt habe:

--kkopqSkewInfo: Query:SELECT /* DS_SKEW */ /*+ RESULT_CACHE no_parallel dynamic_sampling(0) no_sql_tune no_monitoring */ * FROM (SELECT SYS_OP_COMBINED_HASH("ID"), COUNT(*) CNT, TO_CHAR("ID") FROM "ASC_SKEW_PART" SAMPLE(99.900000) SEED(1) GROUP BY "ID" ORDER BY CNT DESC, "ID") WHERE ROWNUM <= 2;

Hier wurde während des Parsens ein 99,9% Sample über alle Partitionen in einer Tabelle mit 45 Millionen Rows gelesen, eine Hash Funktion auf der Join Spalte angewandt und aggregiert.

Es sieht so aus als wäre die Größe des Samples nur von der kleinen Partition P10 (4 Rows) abgeleitet worden, die ich mit meiner where Klausel treffe.

Angewandt wurde das Sampling dann aber über die gesamte Tabelle, eine where Klausel um auf die entsprechende Partition zu filtern gibt es in der rekursiven Query nicht.

Im konkreten Fall ist das auf einer Produktions-DB mit Milliarden von Rows und hunderten Partitionen aufgetreten, woraufhin das bl0ße Parsen über Stunden gedauert hat.

Update: 

Offensichtlich wird die Anzahl der Zeilen, die der Optimizer nach Anwendung der Filter in der where Klausel schätzt, als Basis für die Größe des Samples herangezogen.

Wenn wir eine Tabelle mit 1 Mrd. Rows hätten, dabei ein Wert besonders oft vorkommen würde (≥30% defaultmäßig) und die anderen eindeutig wären, würde ein Filter auf einen der eindeutigen Werte zu einem Sample von 99,9% in der rekursiven Query führen (da der CBO 1 Row nach dem Filter schätzen würde; 1 ≤ 5,500 deswegen → 99,9%).

Natürlich müssen alle anderen Vorbedingungen (Histogramme, Hash Join, etc.) natürlich weiterhin erfüllt sein.

Um eine Zeile aus einer Tabelle von 1 Mrd. Rows zu lesen, möchte man es wahrscheinlich vermeiden 99,9% aller Rows während des Parsens zu lesen und zu aggregieren 😉

Beispiel:

create table asc_skew
nologging
as
with generator as
(
   select *
     from dual
    connect by level <= 1e4
)
select (case when mod(rownum, 2) = 0 then -1 else rownum end)            
       id, 
       rpad('*', 255, '*') padding
  from generator, generator
 where rownum <= 50e6;

create table asc_pids
as
select rownum id
  from dual
 connect by level <= 13;

exec dbms_stats.gather_table_stats(user, 'asc_skew', method_opt=>'for all columns size 1 for columns id size 255', granularity=>'ALL');

--this will parse very long
explain plan for
select /*+ leading(b a) use_hash(a) parallel(8) pq_distribute(a HASH HASH)  */*
  from asc_skew a, asc_pids b
 where a.id = b.id
   and a.id = 42;

Fazit:

Meiner Meinung nach sollte die Auswahl des Samples angepasst werden auf die Größe des Segments vor der Anwendung der where Klausel.

Weiters sollte auf partitionierte Tabellen besonders Rücksicht genommen werden.

Eventuell wäre es auch sinnvoll bei 99,9% komplett auf die sample Klausel zu verzichten, da diese z.B auch Smart Scans auf Exadata verhindert.

 

Oracle Text Troubleshooting

Oracle Text Index – Concept and troubleshooting its related issues

With Oracle text indexes (or Domain index), we can index text documents and search it based on contents using text patterns with specialized text query operators.

Oracle Text index is different from the traditional B-Tree or Bitmap indexes. They have several components communicates internally.

In a query application, the table must contain the text or pointers to where the text is stored. Text is usually a collection of documents but can also be small text.

Oracle Text index differs from the traditional B-Tree or Bitmap. In an Oracle Text index, the text data is not directly indexed rather, the text data is split into a set of tokens (these splits stored in database internal tables) and tokens are indexed.

Oracle Text Index objects

Oracle Text index has four tables: $I, $K, $N and $R tables.

The $I table contains the data which is being indexed, all the tokens (words) generated from the text document is stored in this table. The tokens in this table are indexed by a B-Tree index with name format DR${index_name}$X.

The $K table maps the internal DOCID values to external ROWID values (fetching a DOCID when we know the ROWID value) .

The $R table maps the ROWID values to DOCID values, (fetching a ROWID when we know the DOCID value). The entries from this table are indexed by a B-Tree index with name format DRC${index_name}$R.

The $N table contains a list of deleted DOCID values, which are cleaned up by the index optimization process.

Oracle Text Health Check

 Oracle Text Status and Version:

  1. A: Status of all CTXSYS objects status :
SELECT * FROM dba_objects
WHERE status !='VALID' AND OWNER = 'CTXSYS' 
ORDER BY object_type,     object_name;

B: The query for health check of the index.
The idx_docid_count is Number of documents indexed. The number of idx_docid_count should be the same or close to the number of rows of base table. The domidx_status  is domain index status.

SELECT c.idx_owner,c.idx_name,c.idx_text_name,c.idx_type,
c.idx_docid_count, i.status,i.domidx_status
FROM ctxsys.ctx_indexes c, dba_indexes i
WHERE c.idx_owner = ‘OWNER’
AND c.idx_name = ‘INDEX_NAME' and c.idx_name=i.index_name
ORDER BY 2,3;

C: Compilation errors of invalid Text-related objects:

SELECT owner, name, type, line, position, text
  FROM dba_errors
 WHERE owner = 'CTXSYS'
      OR (owner = 'SYS' AND (name like 'CTX_%' or name like 'DRI%'))
  ORDER BY owner, name, sequence;

  SELECT * FROM ctxsys.ctx_index_errors
   ORDER BY err_timestamp DESC, err_index_owner, err_index_name;

D: Extract the DDL of the existing index :

SELECT CTX_REPORT.CREATE_INDEX_SCRIPT('SCHEMA.INDEX_NAME') FROM DUAL;
  1. Validating the Index integrity:

A: Validate $K against the base table(there should be no rows selected for a valid INDEX) :

select  *
from dr$INDEX_NAME$k k
where not exists (select 1
from TABLE_NAME t
where k.textkey = t.rowid);

The keys on $K  table should be match with the base table rowids.

B: Validate $R against $K(there should be no rows selected for a valid INDEX) :

select  *
from table(ctx_diag.decode_r('dr$INDEX_NAME$R')) r
where not exists (select 1
from dr$INDEX_NAME$k k
where r.textkey = k.textkey);

C: validate $R (find duplicates) (there should be no rows selected for a valid INDEX) :

column docids for a40

select  textkey, listagg(docid, ', ') within group (order by docid) docids
from table(ctx_diag.decode_r('dr$INDEX_NAME$R'))
group by textkey
having count(*) > 1;

The above queries for validating the TEXT index should have no return values therefore the index would be consistence.

Types of Oracle Text Indexes

CONTEXT

Use this index to build a text retrieval application when your text consists of large coherent documents.

You can index documents of different formats such as MS Word, HTML or plain text.

You can customize the index in a variety of ways.

This index type requires CTX_DDL.SYNC_INDEX after DML on base table.

Note! Transactional CONTEXT Indexes: The new TRANSACTIONAL parameter to CREATE INDEX and ALTER INDEX enables changes to a base table to be immediately queryable.

CTXCAT

Use this index type for better mixed query performance.

Typically, with this index type, you index small documents or text fragments.
Other columns in the base table, such as item names, prices, and descriptions can be included in the index to improve mixed query performance.

This index is larger and takes longer to build than a CONTEXT index.

The size of a CTXCAT index is related to the total amount of text to be indexed, the number of indexes in the index set, and the number of columns indexed.
Consider your queries and your resources before adding indexes to the index set.

This index type is transactional, automatically updating itself after DML to base table.

No CTX_DDL.SYNC_INDEX is necessary.

CTXRULE

Use CTXRULE index to build a document classification or routing application.

This index is created on a table of queries, where the queries define the classification or routing criteria. 

ALTER INDEX Sync Methods

 MANUAL:

No automatic synchronization. This is the default. You must manually synchronize the index with CTX_DDL.SYNC_INDEX.

EVERY:

Automatically synchronize the index at a regular interval specified by the value of interval-string.

ON COMMIT:

Synchronize the index immediately after a commit.   

TRANSACTIONAL:

Specify that documents can be searched immediately after they are inserted or updated.

If a text index is created with TRANSACTIONAL enabled, then, in addition to processing the synchronized rowids already in the index, the CONTAINS operator will process unsynchronized rowids as well.

To turn on TRANSACTIONAL index property:

ALTER INDEX myidx REBUILD PARAMETERS('replace metadata transactional');
                To turn off TRANSACTIONAL index property:
ALTER INDEX myidx REBUILD PARAMETERS('replace metadata nontransactional');

Oracle Text Index preferences

DATASTORE:

  1.  DIRECT_DATASTORE: Indicates that the data is stored internally in text columns of a database table.
  2. MULTI_COLUMN_DATASTORE: Indicates that the data is stored in text table in more than one column. Columns are concatenated (joined) to create a virtual document and each concatenated row is indexed as a single document
  3. DETAIL_DATASTORE: Indicates that the data is stored internally in a text column.
  4. NESTED_DATASTORE: Indicates that the data is stored in nested tables
  5. FILE_DATASTORE: Indicates that the data is stored in Operating System files. This type of data source is supported only for CONTEXT index.
  6. URL_DATASTORE: Indicates that the data is stored over Internet.
  7. USER_DATASTORE: Indicates that the documents would be synthesized at index time by a user defined stored procedure

FILTER:

In this phase the text stream can be converted to format that is recognized by the Oracle text processing engine. 

SECTIONER:

 The task of the sectioner is to divide the incoming text stream into multiple sections based on the internal document structures (HTML or XML).

 LEXER:

This property determines the language associated with the incoming document.

 How to SYNC_INDEX after DML on base table

The following script is useful to synchronize the index with the base table when using context type of text index:

export ORACLE_SID=SID_NAME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/lib32:/usr/lib
sqlplus "/as sysdba" << EOF
exec ctx_ddl.sync_index(idx_name =>'SCHEMA.INXEX’);
exit;
EOF