PL/SQL optimieren?

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Ich verwende in meinen PL/SQL Packages an mehreren Stellen SQL-Statements, welche via SELECT … INTO … (also ein single-row fetch) genau eine Zeile in Bind-Variablen einlesen. Nach eingehender Analyse (dbms_stat) komme ich zu dem Ergebnis, dass diese Abfragen sehr cpu-intensiv sind. Offenbar gibt es sehr viele buffer-gets und der Cursor wird imho stets neu geparsed.

Welche Möglichkeiten gibt es, das Parsen zu reduzieren? In Summe düften es etwa ein dutzend SQL-Statements sein, allesamt mit bind Variablen. IMHO müsste genau das seltener geparsed werden. Da ich implizite Cursor verwende, scheint Oracle das nicht erkennen zu können … aber kann man mit expliziten Cursors auch einen single-row fetch ausführen?

Gruß Markus

#2

Hallo Markus,

zum Parsen mache ich mich noch schlau, da habe ich gerade nichts parat.

Aber nach meinen Tuningerfahrungen kann ich Dir sagen: Kümmere Dich zuerst um die Optimierung der Zugriffe und der Anwendung selbst. Hier liegt der Hund meistens begraben, hier holst Du 90% raus.

Ganz zum Schluß läßt sich dann trefflich über das Vorgehen des Datenbanksystems streiten. Oder über den ach so klein dimensionierten Server. Oder über die fünfundzwanigste Tabelle in der Abfrage, die leider, leider nicht in Normalform XY vorliegt.

Viele Grüße
Bonsai

#3

Hallo Markus!

Kannst du mal einen von den Selects exemplarisch reinstellen? Vielleicht fällt mir dann was auf.
Ansonsten noch: Welche DB-Version und wie ist der Wert von OPEN_CURSORS?

Gruss,
Martin

#4

Hallo Markus!

Kannst du mal einen von den Selects exemplarisch reinstellen?
Vielleicht fällt mir dann was auf.
Ansonsten noch: Welche DB-Version und wie ist der Wert von
OPEN_CURSORS?

PROCEDURE meine\_funktion( input IN rein, output IN OUT raus ) AS
 var1 ...
 var2 ...
 var3 ...
BEGIN
 SELECT sp1, sp2, sp3
 INTO var1, var2, var3
 FROM ... WHERE ...;
END;

So sieht es im Kern aus. Die Statements selbst sind schon via execution-plan optimiert worden, d.h. hier muss nichts mehr optimiert werden.

Das ganze läuft auf Oracle 8.1.7.4. Was ist OPEN_CURSORS? Ein Systemwert?

Gruß Markus

#5

Hallo,

ich würde es mal so sagen: ich sehe nichts, was nicht vollkommen ist.

Bist Du sicher, dass Deine Statements wirklich dauernd geparst werden?

Die Ausführungspläne sollten in der SGA liegen und (wenn diese nicht zu groß ist) auch schnell gefunden werden. Solange Du BIND Variable benutzt, bist Du auch bei der 8i schon auf der sicheren Seite.

  1. Hast Du überhaupt ein Performance Problem? Und interpretierst Du die Daten richtig? Bist Du wirklich sicher, dass die DB ihre Zeit mit Parsen verbringt?

  2. Hast Du die Statements wirklich optimiert, oder nur den optimizer angeschaltet. Da kanns noch einiges Potential geben. Vom einfachen Umstellen und strukturieren der Bedingungen bis zu Indexen. Benutzt Du Funktionen in der Abfrage im WHERE-Teil?

  3. I/O verringern. Wenn Du ständig auf dieselben Tabellen zugreifst kannst Du die KEEP Option nutzen. Dann bleiben die Daten im Speicher.

  4. Wenn Du auf Untermengen davon oft zugreifts, kannst Du einen Materialized View verwenden (Snapshot)

  5. Die Systemeinstellungen.

Gruß

Peter

#6
  1. Hast Du überhaupt ein Performance Problem? Und
    interpretierst Du die Daten richtig? Bist Du wirklich sicher,
    dass die DB ihre Zeit mit Parsen verbringt?

Ich habe mittels dbms_stat eine Statistik erstellt, interessanterweise ist der execution count ähnlich dem parse count, wobei der count in der Größenordnung 100.000 liegt.

  1. Hast Du die Statements wirklich optimiert, oder nur den
    optimizer angeschaltet. Da kanns noch einiges Potential geben.
    Vom einfachen Umstellen und strukturieren der Bedingungen bis
    zu Indexen. Benutzt Du Funktionen in der Abfrage im
    WHERE-Teil?

Indizes werden genutzt, aber du hast recht mit der Funktion in der WHERE-Clause. Hier wird ein BITAND zur fein-Auswahl verwendet. Allerdings sollte die Ergebnismenge hier auf wenige Zeilen (max 5) eingeschränkt sein.

Der Execution Plan sieht wie folgt aus:

CHOOSE SELECT STATEMENT Cost = 4
1.1 COUNT STOPKEY : No Stats
2.1 NESTED LOOPS : Cost=4 Card=1 Bytes=79
3.1 NESTED LOOPS : Cost=3 Card=1 Bytes=77
4.1 NESTED LOOPS : Cost=3 Card=1 Bytes=73
5.1 TABLE ACCESS BY INDEX ROWID T_SUBSCRIBER : Cost=2 Card=1 Bytes=12
6.1 INDEX UNIQUE SCAN INDEX_1 : Cost=1 Card=2
5.2 TABLE ACCESS FULL TABLE1 : Cost=1 Card=1 Bytes=61
4.2 INDEX UNIQUE SCAN IINDEX2 Card=2 Bytes=8
3.2 INDEX RANGE SCAN INDEX3 : Cost=1 Card=2 Bytes=4

(5.2 scheint mir recht teuer zu sein, kann aber auch am CBO liegen, in der Tabelle liegen nur wenige Zeilen)

  1. I/O verringern. Wenn Du ständig auf dieselben Tabellen
    zugreifst kannst Du die KEEP Option nutzen. Dann bleiben die
    Daten im Speicher.

Das hört sich interessant an. Wo und wie verwendet man diese Option?

  1. Wenn Du auf Untermengen davon oft zugreifts, kannst Du
    einen Materialized View verwenden (Snapshot)

Ich brauche die Dynamik. DML findet zwar selten statt, Änderungen müssen aber asap sichtbar sein.

  1. Die Systemeinstellungen.

Was gibt es da zu beachten?

Gruß Markus

#7
  1. Hast Du überhaupt ein Performance Problem? Und
    interpretierst Du die Daten richtig? Bist Du wirklich sicher,
    dass die DB ihre Zeit mit Parsen verbringt?

Ich habe mittels dbms_stat eine Statistik erstellt,
interessanterweise ist der execution count ähnlich dem parse
count, wobei der count in der Größenordnung 100.000 liegt.

  1. Hast Du die Statements wirklich optimiert, oder nur den
    optimizer angeschaltet. Da kanns noch einiges Potential geben.
    Vom einfachen Umstellen und strukturieren der Bedingungen bis
    zu Indexen. Benutzt Du Funktionen in der Abfrage im
    WHERE-Teil?

Indizes werden genutzt, aber du hast recht mit der Funktion in
der WHERE-Clause. Hier wird ein BITAND zur fein-Auswahl
verwendet. Allerdings sollte die Ergebnismenge hier auf wenige
Zeilen (max 5) eingeschränkt sein.

Der Execution Plan sieht wie folgt aus:

CHOOSE SELECT STATEMENT Cost = 4
1.1 COUNT STOPKEY : No Stats
2.1 NESTED LOOPS : Cost=4 Card=1 Bytes=79
3.1 NESTED LOOPS : Cost=3 Card=1 Bytes=77
4.1 NESTED LOOPS : Cost=3 Card=1
Bytes=73
5.1 TABLE ACCESS BY INDEX ROWID T_SUBSCRIBER
: Cost=2 Card=1 Bytes=12
6.1 INDEX UNIQUE SCAN INDEX_1 :
Cost=1 Card=2
5.2 TABLE ACCESS FULL TABLE1 : Cost=1
Card=1 Bytes=61
4.2 INDEX UNIQUE SCAN IINDEX2 Card=2
Bytes=8
3.2 INDEX RANGE SCAN INDEX3 : Cost=1 Card=2
Bytes=4

(5.2 scheint mir recht teuer zu sein, kann aber auch am CBO
liegen, in der Tabelle liegen nur wenige Zeilen)

Der ganze Execution Plan ist eher teuer. Du benutzt ja NUR Nested Loops. Das ist gut - wenn die Mengen klein oder alle Zeilen nötig sind. Stell mal die Where bedingungen um und lass das SELECT nochmals durchlaufen. Mach mal auf jedes Attribut in der WHERE BEdingung einen Suchindex. Schau, ob sich der Ausführungsplan verbessert.

  1. I/O verringern. Wenn Du ständig auf dieselben Tabellen
    zugreifst kannst Du die KEEP Option nutzen. Dann bleiben die
    Daten im Speicher.

ALTER TABLE xxx STORAGE ( BUFFER_POOL KEEP );

Das hört sich interessant an. Wo und wie verwendet man diese
Option?

  1. Wenn Du auf Untermengen davon oft zugreifts, kannst Du
    einen Materialized View verwenden (Snapshot)

Ich brauche die Dynamik. DML findet zwar selten statt,
Änderungen müssen aber asap sichtbar sein.

Auch das läßt sich mit einem Snapshot machen. Gerade bei seltenen Änderungen ist er umso effektiver.

  1. Die Systemeinstellungen.

Was gibt es da zu beachten?

Die SGA nicht zu hoch setzten. Dort werden die SQL-Befehle gepuffert. Wenn dieser Bereich zu groß ist, verliert man mehr Zeit durch Suchen als man gewinnt. 100 MB sind für JEDE DB mehr als ausreichend.
BUFFER POOL hochsetzen. Hubraum ist durch nichts zu ersetzen außer durch Hubraum.
Mehrprozessormaschine? Dann lass Oracle sie auch benutzen.
Literaturempfehlung: Thomas Kyte (beide Bücher) Sind auf englisch, aber sehr gut zu lesen.

Gruß

Peter

Gruß Markus

1 Like
#8

Der ganze Execution Plan ist eher teuer. Du benutzt ja NUR
Nested Loops. Das ist gut - wenn die Mengen klein oder alle
Zeilen nötig sind. Stell mal die Where bedingungen um und
lass das SELECT nochmals durchlaufen. Mach mal auf jedes
Attribut in der WHERE BEdingung einen Suchindex. Schau, ob
sich der Ausführungsplan verbessert.

Das Sql-Statement führt eine join-Operation über vier Tabellen aus. Ich sehe da kaum Möglichkeiten, durch Umstellen diese Loops zu umgehen.
Siehst du das anders?

  1. I/O verringern. Wenn Du ständig auf dieselben Tabellen
    zugreifst kannst Du die KEEP Option nutzen. Dann bleiben die
    Daten im Speicher.

ALTER TABLE xxx STORAGE ( BUFFER_POOL KEEP );

Das klingt sehr interessant. Werde es gleich morgen mal ausprobieren.

  1. Wenn Du auf Untermengen davon oft zugreifts, kannst Du
    einen Materialized View verwenden (Snapshot)

Ich brauche die Dynamik. DML findet zwar selten statt,
Änderungen müssen aber asap sichtbar sein.

Auch das läßt sich mit einem Snapshot machen. Gerade bei
seltenen Änderungen ist er umso effektiver.

Ich habe den Materialized View als eine Art Schnappschuss der Datenbank verstanden, also ein Replikat von Tabellen. Der join meiner Tabellen
sieht wie folgt aus: T1 x T2 x T3 x T4

T1 - ca. 10.000.000 Datensätze
T2 - m:n Relation Table für T1 und T3
T3 - einige dutzend Datensätze
T4 - einige hundert Zeilen

In T1 kommen von Zeit zu Zeit Zeilen hinzu, T2 ändert von Zeit zu Zeit die Beziehungen. Änderungen müssen ASAP sichtbar werden. T3 und T4 sind nahezu konstant.
Siehst du da Chancen?

  1. Die Systemeinstellungen.

Was gibt es da zu beachten?

Die SGA nicht zu hoch setzten. Dort werden die SQL-Befehle
gepuffert. Wenn dieser Bereich zu groß ist, verliert man mehr
Zeit durch Suchen als man gewinnt. 100 MB sind für JEDE DB
mehr als ausreichend.

D.h. mit einer kleineren SGA kann man CPU Zeit einsparen? Hört sich zumindets logisch an.

BUFFER POOL hochsetzen. Hubraum ist durch nichts zu ersetzen
außer durch Hubraum.
Mehrprozessormaschine? Dann lass Oracle sie auch benutzen.
Literaturempfehlung: Thomas Kyte (beide Bücher) Sind auf
englisch, aber sehr gut zu lesen.

Ich habe zwei Bücher von ihm: wie nutze ich die CPU’s optimal aus?

Gruß Markus

#9

Hallo,

Ja, man kann mit bloßem Umstellen viel erreichen. Der Optimizer verwendet fast nur Nested Loop Joins, die sind billig bei kleinen und teuer bei der großen Tabelle. Manchmal kann es vorkommen, dass die Reihenfolge der WHERE Bedingungen den Optimizer verwirrt. Es geht nicht darum, die Loops zu umgehen, sondern nur darum, sie anders - besser - anzuordnen. Der Trick mit den Indexen klappt auch erstaunlich gut.

Den Materialized View kannst Du bei jeder Änderung aktualisieren. Das ist sogar besonders günstig, wenn die Änderungen selten sind.

In der INIT Datei ist ein Parameter - oGott, wie hieß er denn noch - Ich glaube, es steht sogar als Kommentar direkt darüber. Der Wert muss 4 + die Anzahl der Prozessoren betragen. Steht normalerweise auf 5.

Gruß

Peter

#10

Hallo,

Ja, man kann mit bloßem Umstellen viel erreichen. Der
Optimizer verwendet fast nur Nested Loop Joins, die sind
billig bei kleinen und teuer bei der großen Tabelle. Manchmal
kann es vorkommen, dass die Reihenfolge der WHERE Bedingungen
den Optimizer verwirrt. Es geht nicht darum, die Loops zu
umgehen, sondern nur darum, sie anders - besser - anzuordnen.
Der Trick mit den Indexen klappt auch erstaunlich gut.

Mal sehen, ob ich das richtig verstanden habe: die nested Loops sind also Schleifen, die der Optimizer etwa dann erzeugt, wenn ich einen inner join formuliere => suche alle T2.fk_id, für die gilt T1.id = T2.fk_id
Richtig?
Mit grossen Tabellen meinst du aber nicht Tabellen mit vielen Datensätzen, sondern viele Datensätze bzgl. der nested loop oder?

Wenn ich einen join über mehrere Tabellen benutze, aber die Ergebnismenge im vorhinein durch einen Schlüssel entsprechend klein halte, ist es dann von Vorteil den join durch einen View zu formulieren?
Oder sollte man Views vermeiden, wenn es um Performance geht?

Den Materialized View kannst Du bei jeder Änderung
aktualisieren. Das ist sogar besonders günstig, wenn die
Änderungen selten sind.

Die Leseabfragen finden mehrere hundert mal pro Sekunde statt, die Schreiboperationen nur einige dutzend mal. Das ist im Vergleich selten, imho für einen materialzed view doch sehr häufig.
Welchen Vorteil habe ich denn durch den materialized view? Die Abfrage düfte dadurch doch nicht schneller verarbeitet werden…

In der INIT Datei ist ein Parameter - oGott, wie hieß er denn
noch - Ich glaube, es steht sogar als Kommentar direkt
darüber. Der Wert muss 4 + die Anzahl der Prozessoren
betragen. Steht normalerweise auf 5.

Danke für den Tipp!

Gruß Markus

#11

Hallo,

  1. bei einem normalen View wird die Abfrage im Moment des Zugriffs ausgeführt. Der Vorteil gegenüber dem Join ist, dass die Abfrage bereits kompiliert vorliegt. Bei einem Materialized view liegen bereits die Werte vor.

  2. Nested-loop Join: hier wird jede Zeile mit jeder kombiniert.
    Index-Join: hier wird über den Index auf die andere Relation zugegriffen. (Index wird benötigt)
    Hash-Join: die Daten der kleineren RFelation werden gehasht und dann mit der anderen Relation verglichen (Abfrage auf Gleichheit)
    Merge-Join: die Daten werden sortiert und dann in Intervallen verglichen. (Sortierung nötig)

Gruß

Peter

#12
  1. Die Systemeinstellungen.

Was gibt es da zu beachten?

Die SGA nicht zu hoch setzten. Dort werden die SQL-Befehle
gepuffert. Wenn dieser Bereich zu groß ist, verliert man mehr
Zeit durch Suchen als man gewinnt. 100 MB sind für JEDE DB
mehr als ausreichend.
BUFFER POOL hochsetzen. Hubraum ist durch nichts zu ersetzen
außer durch Hubraum.
Mehrprozessormaschine? Dann lass Oracle sie auch benutzen.
Literaturempfehlung: Thomas Kyte (beide Bücher) Sind auf
englisch, aber sehr gut zu lesen.

Ich habe mich in diese Thematik eingelesen und verstehe den grundsätzlichen Bezug zur SGA nicht. Nach meinem Verständnis ist die SGA ein Oberbegriff für eine Reihe von Buffers. Die beiden wichtigsten Buffer sind der BUFFER_POOL und der SHARED_BUFFER. Beide in Summe machen die SGA aus.
Verstehe ich dich richtig, das BUFFER_POOL hoch gesetzt werden muss und der SHARED_BUFFER entsprechend klein zu dimensionieren ist?

Gruß Markus

#13

Genau
Genau

#14

Hallo Markus
Hast du einen Parameter cursor_sharing in der init.ora?
Wenn der auf ‚EXACT‘ steht, versuch ihn mal auf ‚FORCE‘ zu stellen.
Gruss, Ulrich