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.

Advertisements

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.

 

Lösung: Warum ist kein Alias nötig?

Nun, beantworten wir zunächst die Zusatzfrage. Es scheint, dass in sql die Typen nicht direkt genutzt werden können.
Wie Martin Berx im Kommentar zum Englischen Blog richtig bemerkt, muss man zuerst auf den Typ casten.
In diesem Fall:

TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'

Dies it ein nützlicher kleiner Trick, den man immer wieder einmal brauchen kann.
Jetzt kann man das Statement ausführen. Mein Vermutung war, dass der natural Join dafür sorgt, dass man keinen Alias genötigt.
In der Tat resultiert

SELECT order_id,
SUM(quantity*unit_price)
FROM order_items
HAVING SUM(quantity *unit_price) > ALL
(SELECT SUM(quantity*unit_price)
FROM customers
JOIN orders o USING (customer_id)
JOIN order_items oi ON (oi.order_id=o.order_id )
WHERE TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'
GROUP BY order_id
)
GROUP BY order_id

in:

FEHLER in Zeile 10:
ORA-00918: Spalte nicht eindeutig definiert

Erst wenn man einen Prefix für Order id im group by definiert geht es:


SELECT order_id,
SUM(quantity*unit_price)
FROM order_items
HAVING SUM(quantity *unit_price) > ALL
(SELECT SUM(quantity*unit_price)
FROM customers
JOIN orders USING (customer_id)
JOIN order_items ON (order_items.order_id=orders.order_id )
WHERE TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'
GROUP BY o.order_id
)
GROUP BY order_id

Meiner Meinung nach sollte es eigentlich immer so funktionieren wie im Fall des natural join. Da hat man schon definiert, dass die Felder gleich sind, soll sich der Optimizer doch eines aussuchen.

 

Warum ist kein Alias nötig?

Hier ist eine Frage von OTN. Ich bin gespannt, was Sie zu sagen haben. (Bitte nicht die Antwort auf OTN nachsehen.)


select order_id, sum(quantity*unit_price)
from order_items
having sum(quantity*unit_price) > ALL
(select sum(quantity*unit_price)
from customers
join orders using (customer_id)
join order_items using (order_id )
where STATE_PROVINCE = 'FL'
group by order_id)
group by order_id;

Die Frage lautet: Warum brauchen wir kleinen Alias für order_id in der „Group order_id“ Klausel der Unterabfrage, obwohl die Spalte in beiden Tabellen angezeigt wird?
So, um es ein bisschen schwieriger zu machen, habe ich eine kleine zusätzliche Hürde errichtet. STATE_PROVINCE ist in der OE-Schema für Version 12 (für andere Versionen habe ich nicht getestet) Bestandteil einer Typvariable. Die Abfrage läuft nicht wie hier gezeigt. Wie sollen wir die Abfrage so ändern, dass es funktionieren wird?

 

Lösung: Woher kommen die Downgrades?

Wie Stefan schon korrekt im Englischen Blog bemerkte, sind die downgrades zu einem Teil die Folge von parallelen Sessions die Toad stehen lässt. Dabei kann der auslösende Clientprozess schon lange nicht mehr existieren, die Datenbank sessions bestehen weiter. Dadurch kamen wir immer mehr unter Druck, da die Anzahl dieser Sessions langsam anwuchs. Es konnten immer weniger neue px alloziert werden, da der pool nicht unendlich ist.

Als Massnahme dagegen haben wir ein Profile aktiviert, dass inaktive Session nach einer gewissen Zeit killt.

Der zweite Grund lag daran, dass der Parameter parallel_adaptive_multi_user  gesetzt war. Dieser sorgte dafür, dass etwa die Häfte des parallel session pools für parallel processing verwendet werden konnte. Der Parameter war offenbar in einem DWH sinnwidrig. Die lokalen DBA’s bestanden aber darauf die Default wert nicht zu verändern. Zum ersten Mal war ich mit der absurden Idee konfrontiert, dass ein Default wert so etwas wie ein Tabu darstellt. Stattdessen wurde der Parameter parallel_threads_per_cpu triumphierend auf den Default von 2 gesetzt. Jedoch hatte das OS bereits alle Threads als CPU gemeldet, sodass jetzt doppelt so viele CPUs gemeldet waren als wirklich vorhanden waren, ebenso doppelt so viele Threads.

Für die downgrades haben sich drei sinnlose Werte in den Parametern glücklich neutralisiert. Wozu allerdings der Resource Manager noch lief, ist mir ehrlich gesagt nicht klar. 😉

 

 

Woher kommen die Downgrades?

In einem DWH kam es verstärkt zu downgrades der Parallelität. Es schien mit jedem Tag schlimmer zu werden. Tatsächlich zeigte sich, dass die frei verfügbaren parallel prozesse konstant abnahmen. Eine nähere Untersuchung der verwendeten PX zeigte bei vielen Toad als Quelle.
Wie war es möglich, dass die Anzahl dieser Toad verursachten parallelen sessions ständig wuchs?
Andererseits es waren immer noch ausreichend PX zur Verfügung, dennoch kam es zu downgrade. Welchen Parameter sollte man sich hier näher ansehen?

Lösung: Viele Hierarchien gleichzeitig navigieren

Zuerst Legen wir die nötigen Indexe an:


create index i1 on tree_emp( empno);
create index i2 on tree_emp( mgr);

Dannach definieren wir das pipe table package. Natürlich geht es auch effizienter, aber ich möchte hier das Prinzip zeigen.


create or replace package parallel_access
as

TYPE R_REC IS RECORD (empno tree_emp.empno%TYPE,
sum_salaries Number); — result record, change defintion according to your needs
TYPE refcur_t IS REF CURSOR RETURN R_REC;

TYPE result_Tab is TABLE OF R_REC;

FUNCTION passData (p_ref refcur_t) RETURN result_Tab
PIPELINED
PARALLEL_ENABLE(PARTITION p_ref BY ANY); — function will inherit parallelism from ref cursor

END parallel_access;
/

create or replace package body parallel_access
as
FUNCTION passData (p_ref refcur_t) RETURN result_Tab
PIPELINED PARALLEL_ENABLE(PARTITION p_ref BY ANY)
IS
out_rec r_rec;
BEGIN
execute immediate ‚alter session set „_old_connect_by_enabled“=true‘;
LOOP — for each parallel process
FETCH p_ref INTO out_rec;
EXIT WHEN p_ref%NOTFOUND;
SELECT sum(sal)
INTO out_rec.sum_salaries
FROM tree_emp
CONNECT BY PRIOR EMPNO = MGR
START WITH mgr = out_rec.empno;

PIPE ROW(out_rec);
END LOOP;
RETURN;
END passData;

END parallel_access;
/

Verwenden Können wir as Pacakge wie folgt. Parallel 4 ist nur ein Beispiel, man kann die Parallelität je nach Bedarf wählen.

SELECT b.* FROM
TABLE(parallel_access.passdata (CURSOR( select /*+ parallel (d 4) */ empno , null from tree_emp where mgr is null))) b;

Viele Hierarchien gleichzeitig navigieren

Nehmen wir an Sie hätten eine grosse Anzahl flacher Hierachien. Als Beispiel nehmen wir die emp table aus dem Scott Schema.
Daraus erzeugen wir eine Tabelle Tree_emp wie folgt:


create table tree_emp as select * from emp;

alter table tree_emp modify (empno number(18));
alter table tree_emp modify (mgr number(18));

INSERT INTO tree_emp
( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
)
;
SELECT EMPNO+(10000*step.n) empno,
ENAME,
JOB,
MGR+(10000*step.n) mgr,
HIREDATE,
TRUNC(DBMS_RANDOM.value(low => 8, high => 50))*100 SAL,
COMM,
DEPTNO
FROM emp,
(SELECT rownum n FROM dual CONNECT BY rownum <= 1000000
) step
;

Weisen Sie für alle Presidenten (mgr is NUll) die Summe der Gehälter sämtlicher Untergebener aus. Schreiben Sie dau eine pipeline table function, die mehrere Bäume gleichzeitig navigieren kann. Verwenden Sie Oracle’s connect by Syntax.für die Baumnavigation.
Sie können nach Belieben indexieren.