Überblick: Die Mitschrift bietet eine detaillierte Anleitung zur Optimierung von Oracle-Datenbanken, zeigt spezifische Methoden zur Leistungssteigerung auf und erklärt die Vorteile von Indizes. Sie enthält praktische Beispiele und Anweisungen für die SQL-Optimierung und gibt Hinweise zur effektiven Konfiguration des DB-Caches.
Welche Vorteile hat Oracle gegenüber anderen Datenbanken
·DB Server ist für unterschiedliche Rechnerstrukturen optimiert
·Datensätze werden ohne Eskalationsverfahren gesperrt à Minimierung von Deadlock Häufigkeiten
·Lesen ohne Sperren für konsistente Leseoperationen bei gleichzeitig beliebigen Änderungsoperationen
·Fast Commit zur Verminderung der physischen Plattenzugriffe
·Verteilte Datenstrukturen auf beliebig viele Platten
·Frei Konfigurierbarer DB-Cache
·SQL Optimierer zur Ermittlung der Optimalen Zugriffspfade zu den Daten
Tunen der Oracle Datenbank
Wichtig beim Tunen ist, daß Schwachstellen erkannt werden. Das hinzufügen von Indices kann z.B. die Antwortzeit um den Faktor 10 bis 1000 verbessern. Im Gegenteil dazu kann z.B. die Erhöhung des DB-Cache in manchen Fällen nur zu Verbesserungen von 1 % führen.
Was kann man Tunen:
1.Die Datenstruktur (logische Struktur der Objekte, Index, Cluster)
2.SQL Operationen SQL Optimierung , Ermittlung der Statistiken für DB Objekte
5.Datenbank Objektverteilung auf Disk, Rollback Segmente, Redo-Log-File
1.Die Datenstruktur
Sie ist grundlegend für die spätere Performance der DB. Wichtig ist vor allem die richtige Auswahl der Indices. Für primary Keys wird automatische ein Index angelegt, für foreign keys sollte auch ein Index angelegt werden. Der Vorteil eines foreign key Index ist, daß bei einer Referenzierung des Primary Key Wertes nur dessen Index Entry gesperrt wird, und man so das kleinste mögliche Sperrgranulat ausnützen kann.
2.SQL Befehls Ebene
SQL Optimierer suchen den schnellsten Zugriffspfad zu den Daten. Es gibt dafür in Oracle7 3 Methoden
·Regel – Methode
·Statistische Methode
·Statistische Methode + Hinweise
Bei der Regel-Methode bestimmen die Bedingungen in der ‚where‛ Klausel den Zugriffspfad.
Bei statistischen Methoden werden statistische Informationen über Tabellen, Indizes und Cluster mit einbezogen. Diese Infos erhält man mit dem ‚analyze‛ Befehl
Analyze table tab compute statistics; //compute liefert immer exakte Werte (dauert sehr lange)
Analyze index iname estimate statistics; //estimate liefert geschätzte Werte die aber genau genug sind
In Oracle 7 wird defaultmäßig die Statistische Methode verwendet, sind keine Statistiken vorhanden führt der SQL Optimierer verschieden Schätzungen durch um den Pfad zu ermitteln
Die Aktivierung von anderen Optimiermethoden kann durch Hinweise geschehen. Diese Hinweise können genutzt werden bei select, update und delete, und müssen als erste Klausel nach dem Operator Schlüsselwort angegeben werden.
Update /*+rule*/ table set
3.Anwendungs Ebene
Positiv wirken sich hier z.B. gespeicherte PL/SQL Programme aus die zentral und kompiliert am Server liegen und einmal aktiviert allen Benutzern zur Verfügung stehen.
Auch sollten Integritätsbedingungen zentral und optimiert am DB-Server ausgeführt werden, anstatt innerhalb des Anwendungsprogrammes
• Download Link zum vollständigen und leserlichen Text • Dies ist eine Tauschbörse für Dokumente • Laden sie ein Dokument hinauf, und sie erhalten dieses kostenlos • Alternativ können Sie das Dokument auch kаufen
4.DB – SERVER Ebene
Hier ist vor allem die Größe und Konfiguration des Cache interessant. Die Größe des Caches sollte so gewählt werden, daß auf ein physische Leseoperation (Platte) etwa 10 – 20 logische Leseoperationen (Cache) kommen, was einer Trefferrate von 90-95 % entspricht.
Die Ermittlung der Trefferrate erfolgt so:
Select name,value
from v$sysstat
where name in(‚consistent gets‛,‛db block gets‛,‛physical reads‛)
Als Ergebnis erhält man nun Beispielsweise diese 3 Werte
Consistent gets: 121.001 Lesezugriffe konsistent und
Db block gets: 340.010 Lesezugriffe aus dem Cache
Physical reads: 85.990 Plattenzugriffe
Physical reads
Trefferrate = (1- ------ ) *100 = 88%
Db block gets + consistent gets
Für eine exakte Ermittlung der optimalen Cachegröße muß man aber über einen bestimmten Zeitraum eine DB Pufferanalyse mit dem typischen Anwendungsprofil durchführen. Daraus erhält man dann genau Statistiken über die Auslastund der einzelnen DB-Cache Blöcke.
5.Datenbank
Hier geht’s es vor allem darum, die unterschiedlichen Filetypen einer Datenbank und die Zuordnung zu den DB Objekten optimal zu gestalten.
So sollten z.B. Index Segmente, Redo-Log-Files und Rollback Segmente von den Benutzerdaten getrennt auf anderen, schnelleren Platten gespeichert werden.
INDEXES
Indexes sollten für jene Spalten angelegt werden die in der ‚where‛ Klausel verwendet werden, da sie die Antwortzeit enorm beschleunigen.
Indexes sind in ihrer Struktur aufsteigend sortierte,selbst ausbalancierte B-Tree’s, die bei jedem insert oder delete neu balanciert werden.
Der Zugriff auf das tatsächliche Datenbankobjekt erfolgt über die ROWID, es wird also zuerst im B-Tree der richtige Eintrag gesucht, und mit Hilfe der ROWID dann das tatsächliche Datenbankobjekt gelesen.
Bsp. Workerskill table
ROWID Name Skill Ability
000204.0001.0001 Dick Jones Smithy Excellent
000204.0002.0001 Wilfred Lowell Discus Slow
000204.0003.0001 John Pearson Smithy Average
000204.0004.0001 John Pearson Combine Driver
Dick Jones Smithy 000204.0001.0001
John Pearson Combine Driver 000204.0004.0001
John Pearson Smithy 000204.0003.0001
Wilfried Lowell Discus 000204.0002.0001
Index erzeugen
Create index indexname on table(column1,column2, .)
Beim Indexnamen sollte folgendes Format eingehalten werden:
TABLENAME_COLUM1_COLUM2
Alle Indexes die vom Benutzer erstellt werden, sind im Table USER_INDEXES abgespeichert.
Wo kann man Indexes verwenden
Index sind vor allem bei jenen Spalten sinnvoll, die in where Klauseln oder bei Joins verwendet werden. Aber Achtung, wird in der where Klausel IS NOT NULL oder IS NULL verwendet, so erhält man keinen Geschwindigkeitsvorteil.
Auch bei ORDER BY, MIN und MAX wird der Index verwendet, aber er wrid nicht bei GROUP BY und DISTINCT genutzt !
Sinnvoll sind Indexes auch nur bei Spalten deren Daten variieren, also zum Beispiel bei Telefonnummern oder Postleitzahlen. Ein Index für eine Spalten die nur ‚Y‛ oder ‚N‛ enthält wäre eine schlechte Wahl, und kann die Systemleistung sogar beeinträchtigen.
Wenn ein Primärschlüssel aus mehreren Spalten besteht, ist es von Vorteil jene Spalte als erste beim create index anzugeben die am meisten variiert. Beim Suchen wird zuerst in der ersten Spalte verglichen, und so scheiden schon hier die meisten Kandidaten aus.
Variieren die Spalten etwa gleich, so sollte die Spalte auf die am öftesten zugegriffen wird die erste sein.
Kleine Tabellen mit weniger als 30 Reihen sollten besser keinen Index enthalten, über 100 Reihen ist ein Index auf jeden Fall sinnvoll.
Wieviele Indexes sollten erstellt werden
Es können pro Tabelle maximal 16 einspaltige Indexes vergeben werden, oder maximal 1000 bis 2000 Byte pro Index (je nach Betriebssystem).
Die Nachteile von so vielen Indexes betreffen jedoch Insert und Delete Funktionen, da ja alle B-Tree’s neu balanciert werden müssen, und es braucht etwas mehr Festplattenspeicher.
Im Normalfall ist der Vorteil den man dadurch erhält größer als der verlorene Plattenplatz.
Legt man einen Index über eine Spalte die auch NULL Werte enthält, so wird für jene Spalten die NULL enthält kein Index angelegt. Bei mehrspaltigen Indexes wird nur dann kein Index angelegt wenn alle Indexspalten NULL enthalten.
Der Vorteil davon soll durch folgendes Beispiel verdeutlicht werden.
Gegeben ist eine Tabelle von Verkäufern mit jeweiligen Gehalt. Hat ein Verkäufer kein Gehalt so soll nicht 0 sondern NULL eingetragen sein.
Will man nun alle Verkäufer mit Gehalt ausgeben geht man normalerweise so vor.
SELECT * from VERK
WHERE Gehalt IS NOT NULL;
Schneller wäre aber,
SELECT * from VERK
WHERE Gehalt > 0;
Denn Verkäufer die kein Gehalt haben erscheinen nicht im Index
Der Vorteil von verschieden Platten für Daten und Indexes ist, daß es keine Zugriffsbehauptungen auf die Dateien gibt.
Create index xx on table (column1, .)
tablespace TABLESPACENAME
Der Tablespacename wird vom Administrator vergeben.
Üblicherweise sollte diese Option zur Optimierung von sehr großen Datenbanken verwendet werden.
Erzeugt man einen neuen Table mit create table so wird der Primary-Key Index standardmäßig jedoch auf der selben Platte abgelegt. Um dem Index eine andere Platte zuzuweisen muß man die USING INDEX Klausel verwenden.
Beispiel:
Create table stock (
Company VARCHAR2(20),
Symbol VARCHAR2(6),
Industry VARCHAR2(15),
Volume NUMBER),
constraint PK_STOCK primary key (Company)
storage (initial 20K next 20K),
constraint UQ_STOCK unique (Symbol)
using index tablespace INDEXES
storage (initial 20K next 20K),
tablespace TALBOT
;
Hier wird zuerst ein Primary Key Constraint mit dem Namen PK_STOCK erzeugt. Dem Index des Primary Key wird der tablespace INDEXES zugewiesen, zusätzlich sind noch storage Parameter angegeben.
Der 2 Constraint erzeugt einen Index für die unique Spalte Symbol.
Die Option tablespace weist den Table dem Tablespace TALBOT zu.
Tablespace und Datenbankstruktur
Beispieldatenbank
Database: TALBOT
Tablespace: Tablespace: Tablespace
ADAH GEORGE SYSTEM
File File File
ADAH.ONE HOME.ONE DBS.ORA
Initial
extent
Worker Comfort Worker Weather
Worker
Weather Worker
Comfort Hockey Comfort next
Weather Cluster extents
Next Comfort
extent
Tablespace: GEORG Tablespace: GEORG
File: HOME.ONE File: HOME.TWO
Jeder Tablespace enthält eigene Tables, Indexes und Cluster. Jeder Table startet mit einem initial extent, dem zum Start zugewiesenen Speicherplatz. Wächst ein Table über seinen reservierten Platz hinaus, wird ihm ein next extend zugewiesen.
Die Tables füllen den gesamten Platz des initial Files HOME.ONE und haben sich ausgeweitet auf das File HOME.TWO
Die Datei home.one wird dem Tablespace TALBOT zugeordnet. Die Dateigröße wird auf 1000 Kbyte begrenzt (auch M für Mbyte möglich). Die Option Default Storage wird für jene create table Befehle angewendet die keine storage option enthalten. Es wird dabei festgelegt, daß der initial Platz 25K beträgt, jeder weiter next extent erhält 10K.
Auch wird mit min und maxextents die Anzahl der Extents festgelegt.
Der Parameter pcincrease betrifft die Größe der next extents. Ist pcincrease nicht 0 so wird jeder weitere next extent um diesen Prozentsatz größer angelegt.
Cluster
Clustering ist eine Methode um Tabellen zu speichern die eng ineinander verknüpft sind, oder oft durch joins verbunden werden.
Bsp.:
Create cluster WorkerandSkill (xyz CHAR(25));
Create table WORKER(
Name VARCHAR2(25) not null,
Age NUMBER,
)
cluster WorkerandSkill (Name)
;
create table WORKERSKILL (
Name VARCHAR2(25)not null,
Skill VARCHAR(25) not null,
Ability VARCHAR2(15)
)
cluster WorkerandSkill (Name)
;
Gespeichert werden nun die gemeinsamen Spalten in einer Tabelle
Age Name Skill Ability
23 Adah Talbo Work Good
29 Andrew Dye
22 Dick Jones Smithy Excellent
25 Roland Brandt
Worker Tabel
Workerskill Tabel
Cluster Key
Sequences
Um das Problem der Zuweisung von eindeutigen Nummern, z.B. bei Kundennummern zu lösen, ohne extra Tabellen anlegen zu müssen, hat Oracle Sequences eingeführt.
Create sequence Kundennummer increment by 1 start with 1000;
EN
Warenkorb
