Lösung: Eine unerwartete Bedingung in der Where Clausel

Die merkwürdige Bedingung wird von der Datenbank automatisch generiert.
Die Ursache ist die DDL Optimization, die es in der Form seit Version 11G gibt.
Wenn man bei einer Tabelle eine zusätzliche Spalte einfügt, muss diese Spalte nicht zwingend physisch erzeugt werden.
Es kann auch eine “DDL optimized” Spalte erzeugt werden und wenn man einen Default angibt, kann diese Spalte auch not null sein.
Damit erspart die Datenbank sich den Aufwand, jeden Datensatz um eine Spalte zu erweitern.
Satt dessen wird nur ein Eintrag ins Dictionary gemacht, was natürlich viel schneller geht.
Jede Datenzeile kann einen Wert für die “DDL optimized” Spalte enthalten, wenn der Wert über einen insert eingefügt wurde.
Wenn kein Wert eingefügt wird, wird der Default Wert verwendet.
Da es möglich ist, dass kein erfasster Wert existiert, muss die Datenbank den spaltennamen durch die Formel ersetzen.
Hier eine einfaches Beispiel :

create table x (y number);
insert into x select rownum from dual connect by rownum < 1000000;
commit;
alter table x add ( z number default 1 not null);
select 1 from x where z=1;

Wenn wir uns den Execution Plan der Abfrage ansehen, bemerken wir, dass der Spaltenname Z durch die Formel


(NVL("Z",1)=1)

ersetzt wurde.

Hier noch der Link zu Carlos Blog: Interesting case where a full table scan is chosen instead of an index

Eine unerwartete Bedingung in der where Klausel

Es macht mich stolz zu erfahren, dass Carlos Sierra meinen Blog verfolgt. Carlos ist mir aus meiner Zeit bei Oracle schon lange bekannt, auch wenn wir uns erst kürzlich das erste Mal getroffen haben. Ich schätze Carlos als einen Mann der Tat. Wenn er einen Missstand sieht, beklagt er sich nicht, sondern tut etwas dagegen.
Mit meinem nächsten Beispiel will ich zeigen, dass er auch ein scharfsinniger Analytiker ist.
Vor Kurzem sah ich in einem Plan eine Bedingung, die so nicht im Sql statement stand. Ich wollte wissen, wie die Bedingung in den Plan gekommen war.
Ich gebe dazu ein einfaches Beispiel: Gegeben sei eine Tabelle x die wie folgt aussieht


SQL> desc x
Name Null? Typ
----------------------------------------- -------- -----------------
Y NUMBER
Z NOT NULL NUMBER

Ich lasse die folgende Abfrage laufen:


select count(*) from x where z=1;

Der Exection Plan sieht aus wie folgt:


Plan hash value: 989401810

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   420 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| X    |  1144K|    14M|   420   (2)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter(NVL("Z",1)=1)

Wieso weisst der Plan diese merkwürdige Bedingung auf? Die Antwort finded Ihr in Carlos Sierra’s Blog. Noch ein Hinweis: Es hat etwas mit Default Werten zu tun.

 

Lösung: Die parallele Aktivität bricht ein

Wie Sie sich erinnern werden, hatten wir einen Hash Outer Join mit dominanten Nullwerten. In Folge bekam der parallelen Prozess, der die Nullwerten behandelte, bei weitem die meiste Arbeit zu tun.
Wie sollen wir dies lösen? lassen Sie uns einige grundsätzliche Überlegungen anstellen.
Ein Null Wert ist ein Sonderfall . Wir vergleichen einen Null Wert im Foreign Key mit einem Primärschlüssel , der not null definiert ist. Wir können sicherlich keinen Join Treffer für die Null Werte zu finden. Daher kann jeder der parallelen Sklaven den Vergleich durchführen, solange gewährleistet ist, dass dadurch nie ein Treffer erzeugt wird.
Es lohnt sich also die Null Werte in beliebige andere Werte um zu wandeln, solange nur zwei Voraussetzungen erfüllt sind:

  1. wir müssen die Nullwerte in einer solchen Art und Weise umwandeln, dass die gleichmäßig über alle parallel Slaves verteilt werden
  2. wir müssen sicherstellen, dass im Vergleich zu vorher kein zusätzlicher Satz ins Resultat kommt

Die Lösung zu finden hat mich viel Zeit gekostet. Zuerst habe ich versucht mit negativen Werten zum Ziel zu kommen. Aber auch sie wurden auch an nur einen Parallel Prozess gesandt, genau wie die Null Werte.
Das Problem ist, dass unsere neu generierten Schlüssel etwa im gleichen Bereich wie der Primary Key der Build-Tabelle sein müssen, um über alle Slaves verteilt zu werden.
Ich hätte versuchen können über eine with Klausel den Bereich heraus zu finden, aber das hielt ich für uncool und konnte mich nicht dazu überwinden.
Stattdessen habe ich etwas anderes versucht, was ehrlich gesagt ein bisschen wie ein Hack wirkt, aber viel besser aussieht. Ich benutzte den Primary Key der äußeren Tabelle einen nicht null wertigen breit streuenden Schlüssel zu generieren.
Ich habe auch die Tatsache zunutze, dass die IDs integer waren und veränderte die Join-Bedingung zu:

FROM T_TAB1 T1
LEFT OUTER JOIN T_TAB2 T2
ON ( NVL(T2.X_ID, T2_id +0.5)    = T1.X_ID)

Das ist sicherlich ein bischen getrickst. Das einzig Positive daran ist, dass es funktioniert.
Die Aktivity Tab im Sql Monitor sah nachher so aus:

Vergleichen Sie dies mit dem vorherigen Bild. Es ist ziemlich beeindruckend.
Nun, in der Version 12 die Datenbank sollte in der Lage sein, mit schräg verteilten join Schlüsseln um zu gehen.
Aber es funktioniert nicht in jedem Fall, wie Randolf Geist feststellte.
Wenn jemand meine Lösung nicht gut findet, sollte er vielleicht er einen Blick auf_fix_control 6808773 werfen, aber ich gebe für keinen Fall Garantien ab.😉

 

Die parallele Aktivität bricht ein

In einem meiner DWH POCs für Oracle’s Real World Performance Group bemerkten wir einen plötzlichen Einbruch in der Datenbank Aktivität. Unser Kunde verlangte eine Erklärung dafür.
Im SQL Monitor Activity Reiter sah das so aus:

Nach einer sorgfältigen Untersuchung erkannte ich, dass ein outer join die Ursache war. Formal sah dieser join so aus:

FROM T_TAB1 T1
LEFT OUTER JOIN T_TAB2 T2
ON T2.X_ID    = T1.X_ID

Das Problem war, dass 90% aller Werte in T1.X_ID Null Werte waren. Für die Aufteilung der Arbeit auf die Parallel Prozesse wurde der Hash Schlüssel benutzt. Als Folge bekam ein Parallelpozess 90% der ganzen Arbeit zugeteilt. Eine andere Aufteilung der Arbeit, etwa über einen broadcast war nicht möglich, da T1 zu gross war.
(Randolg Geist schildert das Problem ausführlich: Parallel Execution Skew – Demonstrating Skew).

Können Sie die join Bedingung so umschreiben, dass die Arbeit gleichmässig auf alle paralleln Prozesse verteilt wird? (Die Version war 11G. In version 12 sollte sich der optimizer automatisch um die Fragestellung kümmern.) Noch eine Annahme: die tabelle T2 hat einen Primary Key in Form der Spalte T2_id.

 

Lösung: Die Collection in der Collection

Die entscheidenden Hinweise zur Lösung der Aufgabe habe ich bei Adrian Billington gefunden. Es ist schon ein älterer Eintrag, aber immer noch gültig: introduction to bulk pl/sql enhancements in 9i.
Der Code unten sollte mit den Anmerkungen selbst erklärend sein.
Um zu zeigen, wie die Zugriffe auf die Daten funktionieren, habe ich noch eine Ausgabe über DBMS_output eingefügt.

— Zuerst muss man den Typ für die nested collection erzeugen

CREATE OR REPLACE type emp_t
AS
  object
  (
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    SAL  NUMBER(7,2),
    COMM NUMBER(7,2) 
  )
/
create or replace TYPE tbl_emp  AS TABLE OF emp_t
/

 

DECLARE
  CURSOR c1
  IS
    SELECT deptno,
      dname,
      CAST (MULTISET( SELECT empno,ename,sal, comm FROM emp e WHERE e.deptno= d.deptno
                     ) AS tbl_emp  
           ) as emps -- ich brauche einen alias um die eingebette Collection referenzieren zu können
  FROM dept d;

  TYPE tbl_dept IS TABLE OF c1%ROWTYPE;
  depts tbl_dept ;
BEGIN
   OPEN c1;
   FETCH c1 BULK COLLECT INTO depts;
   CLOSE c1;
   FOR i IN 1..depts.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE(depts(i).deptno);
      FOR j IN  1.. depts(i).emps.COUNT
      LOOP
        DBMS_OUTPUT.PUT_LINE('***'||depts(i).emps(j).ename);
      END LOOP;
   END LOOP;
END;
/

 

Die Collection in der Collection

wir mussten einen sehr häufig genutzte PL/SQL Function umschreiben. Der Code litt stark unter dem Context Switch. Er wurde millionenfach aufgerufen. Wir setzen uns das Ziel, mit einem einzigen Bulk Collect alle Daten aus der Datenbank aus zu lesen. Ich gebe hier ein codeskellet, dass ich auf das scott/tiger schema angepasst habe.

DECLARE
   CURSOR c1
   IS
   SELECT deptno, dname
     FROM dept;
   CURSOR c2 (p_deptno NUMBER)
       IS
       SELECT empno,ename,sal, comm
         FROM emp
        WHERE deptno= p_deptno;
BEGIN
   FOR c1rec IN C1
   LOOP
      FOR c2rec IN c2(c1rec.deptno)
      LOOP
         NULL;
      END LOOP;
   END LOOP;
END;
/

In der Realität steht natürlich komplexe Logic statt null in der Scheife. Ich arbeitete gemeinsam mit einem Entwickler des Softwareherstellers an dem Problem. Wir hatten nicht genug Zeit um den Code zu verstehen. Wie beschlossen, den Code nur ganz formal um zu wandeln und die Schleifen zu belassen. Wir wollten nul also alle Daten in einem einzigen Schritt in ein geschachteltes Array lesen.
Also, alle Departments enthalten alle Employees. Wie in unserem Beispiel war auch in der Realität die Resultatsmenge pro Abfrage so klein, dass Alles locker in ein Array passte.

Wie sieht der zugehörige Select aus?

 

Lösung: Ist die Migrationsdatenbank langsamer?

In OTN wurde einmal gefragt, ob der Optimizer beim Erstellen des Planes die Grösse des Buffercaches berücksichtigt. Unter den üblichen Vorbehalten (es könnte in die Kostenbewertung mit einfliessen) würde ich sagen: Nein. Wie sollte sich die Grösse des Caches auch auswirken? Wenn ich einen grösseren Buffer cache habe, ist wahrscheinlicher dass ein X Segement im Cache ist, daher mache ich einen Indexzugriff ? (Ich argumentiere hier analog zum Parameter OPTIMZER_INDEX_CACHING.) Das ist keine gute Logik.  Die Grösse es Buffer caches ist kein sicherer Indikator dafür, ob ein spezifisches Segment auch tatsächlich im Cache ist. Wie man später sehen wird, kann  die Grösse des Caches auch ein Argument für den Full Table Scan sein.

Für die Erstellung des Execution Plans ist die Selektivität der entscheidende Faktor.

Wie jedoch ein bestehender Execution Plan umgesetzt wird, steht auf einem anderen Blatt. Hier hat die  Execution Enigine unter Umständen noch ein Wort mit zu sprechen.

Zum Beispiel bei einem Full Table Scan (FTS). Oft wird dieser als „direct path read“ umgesetzt. Das heisst, dass zwingend ein physischer I/O gemacht werden muss. (Für die Datenbank ist es auch dann noch ein physischer I/O wenn das Resultat aus dem File System Cache kommt. )

Wenn die Execution Engine aber erkennt, dass eine Tabelle sich fast vollständing im Buffer Cache befindet, kann von einem „direct path read“ auf einen „scattered read“ umgeschalten werden, Der „scattered read“ kann im Gegensatz zum „direct path read“ Nutzen aus dem Buffer Cache ziehen.

Ein ältere, aber gute Beschreibung stammt aus der Feder von Tanel Poder: Optimizer statistics-driven direct path read decision for full table scans .

Kurz gesagt gibt es zwei Voraussetzungen ob ein Umschalten über die Execution Engine stattfinden kann

  1. Der Buffer Cache muss gross genug sein, dass das Segment, das Gegenstand des FTS ist im Buffer Cache Platz hat
  2. Das Segment muss auch tatsächlich fast vollständig im Buffer Cache sein

Der Erste Punkt lässt sich ja leicht klären.  Die Analyse ergab folgendes: Die Tabelle war in der Produktion und in der Migration ca. 25GB gross.
Der Buffercache betrug in der Produktion aktuell 55 GB. In der Migration waren es lediglich 5 GB.
Damit lässt sich mit Sicherheit sagen: In der Produktion kann die besagte Tabelle vollständig gecacht werden. Theoretisch kann die Runtime Engine einen Scan im Memory veranlassen. In der Miragtion ist dies sicher nicht möglich.
War jetzt aber in der Produktion eine ausreichend grosse Anzahl Blöcke gecacht um einen Scan im Memory zu machen?
Zur Klärung dieser Frage dient folgendes Statement, welches die Anzahl der gecachten Blöcke einer Tabelle ermittelt.


SELECT sum(num_buf)
FROM X$KCBOQH x, dba_objects o
WHERE x.obj#=o.object_id
AND object_name='my table'
;

Wenn die so ermittelte Anzahl fast identisch zur Anzahl der Blöcke der Tabelle ist (nach meinen Tests > 90%, aber ohne Gewähr) wird auf „scattered read“ umgestellt.
Die Analyse der produktiven Datenbank ergab, dass die besagte Tabelle wirklich zum grössten Teil im Buffer Cache war. Ich konnte auch zeigen, dast der schnelle FTS nicht garantiert war, sondern nur dann stattfindet wenn die Tabelle ausreichend gecache. Wir machten noch die Gegenprobe und erhöhten den Buffer Cache der Migrationsumgebung. Sodann luden wir die Tabelle in den Cache.
Danach war der FTS auch in der Migrationsumgebung schnell.

Einige Anmerkungen:

Ein neuere, noch tiefere Analyse des Themas findet sich bei Frits Hoogland:

Investigating the full table direct path / buffered decision

Aufgrund verschiedener Beobachtungen denke ich, dass der Storage Server einer Exadata ähnliche Algorithmen in Bezug auf den Smart Scan anwenden kann, und ein Smart Scan in einen Scan des Flash Cache umgewandelt werden kann. Auch diese Information muss ich aber ohne Gewähr geben.

Ist die Migrationsdatenbank langsamer?

Für eine Mirgation wurde eine Datenbank in einer virtuellen Umgebung bereitgestellt.  Erste Tests zeigen, dass die Migrationsdatenbank ein Vielfaches langsamer ist als die Produktionsdatenbank. Die Tests konzentrieren sich auf eine bestimmte Abfrage.

Hier ein Ausschnitt aus einem AWR der Migrationsdatenbank. Zu sehen sind die relevanten Daten des Sql Befehls, den man untersuchen soll als Ausschnitt aus der Liste „SQL ordered by elapsed time“.

 

apsed Time (s)

Executions

Elapsed Time per Exec (s)

%Total

%CPU

%IO

199.24

1

199.24

98.16

3.93

96.72

Zum Vergleich die selben Daten aus der produktiven Datenbank:

Elapsed Time (s)

Executions

Elapsed Time per Exec (s)

%Total

%CPU

%IO

11.02

1

11.02

65.95

99.98

0.0

Was fällt Ihnen auf? Mit welcher Arbeitshypothese würden Sie die Untersuchung beginnen und was würden Sie prüfen? Hinweis: Der Befehl ist ein count welcher nur eine Tabelle liest. Der Execution plan ist in beiden Fällen identisch, es ist jeweils ein Full Table Scan.

Lösung: Warum ist die neue Hardware langsamer?

Wie Martin Preiss auf Twitter meldete hatte Tanel bereits dokumentiert LOBREAD SQL Trace entry in Oracle 11.2 dass Einträge wie LOBREAD tatasächlich auf LOBs hinweisen. Der Traceeintrag kam vermutlich mit Version 11.2.0.2.
So wussten wir, dass es eine Schemaänderung in der Datenbank auf der neuen Hardware stattgefunden hatte. Der nächste entscheidende Hinweis kam über diesem Auschnitt des raw trace (u.a. wieder einmal entdeckt von Martin Berger):


FETCH #25:c=1154407,e=1152124,p=0,cr=102603,cu=0,mis=0,r=101 ,dep=0,og=1,tim=650755949521

verglichen mit:

FETCH #601010888:c=31200,e=22483,p=0,cr=3706,cu=50,mis=0, r=1 ,dep=0,og=1,plh=3621104505,tim=39783214696

Nun, sieht so aus als ob wir ohne LOB 101 Datensätze auf einmal in einem Arry Fetch holen. Mit LOB ist es jeweils nur einer. Wie ist das möglich, wenn der Programmcode identisch ist?
Verhindert der LOB in irgendeiner Weise den Array Fetch?
Nun, in der Tat ist das der Fall, wie hier dokumentiert:Single Row Fetch from a LOB (Danke Hemant). Stefan Köhler hat darauf hin gewiesen, dass es auch vom Treiber abhängt single row fetch depends on client.
Nachdem wir die LOB Spalte durch eine Varchar2 Spalte ersetzt hatten, war die neue Hardware in diesem test schneller als die alte.

Warum ist die neue Hardware langsamer?

Man kauft neue Hardware um schneller zu werden. Das ist eine ganz normale Erwartung. Was aber wenn die neue Hardware langsamer ist als die alte? Die Spekulationen über die Ursache gingen wild hin und her. Da ich auf diesen Einsatz urlaubsbedingt lange warten musste, war die Spannung gross als die Untersuchung endlich beginnen konnte.

Eine schneller Überprüfung zeigte, dass die neue Hardware nicht langsamer war als die alte. Den entscheidenden Hinweis lieferte ein raw trace. Ich zeige hier nur ein entscheidenden Ausschnitt.

Auf der alten Hardware sah der trace so aus:


FETCH #25:c=1154407,e=1152124,p=0,cr=102603,cu=0,mis=0,r=101,dep=0,og=1,tim=650755949521

Cursor #25 ist ein grosses Select, das langsam läuft. Auf der neuen Hardware hingegen sah der trace wie folgt aus:


FETCH #601010888:c=31200,e=22483,p=0,cr=3706,cu=50,mis=0,r=1,dep=0,og=1,plh=3621104505,tim=39783214696
WAIT #601010888: nam='SQL*Net message from client' ela= 171 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217398
LOBGETLEN: c=0,e=3,p=0,cr=0,cu=0,tim=39783217416
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217423
WAIT #0: nam='SQL*Net message from client' ela= 117 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217546
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217560
LOBREAD: c=0,e=12,p=0,cr=1,cu=0,tim=39783217567

Cursor #601010888: entspricht Cursor #25 auf der alten Hardware. Die Datenbank auf der neuen Hardware ist Version 11, die Datenbank auf der alten Hardware ist Version 10.
Offensichtlich besteht ausser bei der Version mindestens ein weiterer Unterschied zwischen beiden Datenbanken. Was ist es? Wie wirkt sich dieser Unterschied aus?
Beide Datenbanken werden über das exakt selbe Programm angesprochen, welche mit MS Visual Studio realisiert ist.