Oracle8i
Werbung
Architektur, SQL/PLSQL, Ablaufintegrität, Tuning Ing. Hermann Zauner Oracle 8i – Weiterführende Literatur Zauner Hermann 1998 – 2001 Ing. Hermann Zauner [email protected] 1. Auflage Umschlaggestaltung: Hermann Zauner Text, Abbildungen und Programme wurden mit größter Sorgfalt erarbeitet. Autoren können jedoch für eventuell verbliebene fehlerhafte Angaben und deren Folgen weder eine juristische Verantwortung noch irgendeine Haftung übernehmen. Dieses Dokument ist für Lernzwecke gedacht und darf in diesem Rahmen reproduziert werden. Weiterführende Literatur: Oracle 7 – Die verteilte semantische Datenbank Günther Stürner - dbms publishing – ISBN 3-930124-00-9 Oracle 8 für den DBA – Verwalten, optimieren, vernetzen Uwe Herrmann, Dierk Lenz, Günter Unbescheid Addison-Wesley – ISBN 3-8273-1310-4 Oracle 8 effizient einsetzen Andreas Christiansen, Michael Höding, Claus Rautenstrauch, Gunter Saake Addison-Wesley – ISBN 3-8273-1347-3 Oracle Magazin Danbankmodelle, Datenbanksprachen und Datenbank-Management-Systeme Gottfried Vossen – Addison-Wesley – ISBN 3-89319-566-1 Taschenbuch der INFORMATIK Werner u.a. – Fachbuchverlag Leipzig – ISBN 3-343-00892-3 Internet: www.oracle.at, www.oracle.com, www.oracle.de -2- Oracle 8i – Oracle Architektur 1. Zauner Hermann ORACLE ARCHITEKTUR ....................................................................................................................... 4 1.1 DIE PHYSISCHE STRUKTUR ........................................................................................................................ 4 1.1.1 Datenbank Files .............................................................................................................................. 5 1.1.2 REDO-Log Files.............................................................................................................................. 5 1.1.3 Kontrol Files ................................................................................................................................... 5 1.2 DIE LOGISCHE STRUKTUR .......................................................................................................................... 6 1.3 DIE TABLESPACE STRUKTUR ..................................................................................................................... 7 1.4 DIE SEGMENTE .......................................................................................................................................... 8 1.5 DIE BLOCKSTRUKTUR ............................................................................................................................... 9 1.5.1 DB-Block Kopf .............................................................................................................................. 10 1.5.2 DB-Block Datenbereich ................................................................................................................ 10 1.5.2.1 1.5.2.2 DB-Freibereich (PCTFREE)..................................................................................................................... 10 DB-Einfügebereich (PCTUSED).............................................................................................................. 10 1.5.3 FREE-DB-Block-Liste (FREELISTS)............................................................................................ 10 1.6 DIE DATENSATZSTRUKTUR ..................................................................................................................... 11 1.6.1 Datensatzkopf................................................................................................................................ 12 1.6.2 Datensatzrumpf ............................................................................................................................. 12 1.7 DIE PROZEß STRUKTUR ........................................................................................................................... 13 1.7.1 Database-Write Prozeß (DBWR) .................................................................................................. 13 1.7.2 REDO Log-Writer Prozeß (LGWR)............................................................................................... 14 1.7.3 Prozeß Monitor (PMON) .............................................................................................................. 15 1.7.4 SYSTEM Monitor (SMON) ............................................................................................................ 15 1.7.5 Archivier Prozeß (ARCH) ............................................................................................................. 15 1.8 DER DATENBANK-CACHE ....................................................................................................................... 16 1.8.1 DB-Block Puffer ............................................................................................................................ 16 1.8.2 Shared Pool................................................................................................................................... 16 1.8.3 Instance-Informationen ................................................................................................................. 16 2 SQL / PLSQL............................................................................................................................................. 17 2.1 SQL......................................................................................................................................................... 17 2.1.1 Historie.......................................................................................................................................... 17 2.1.2 Ausführung .................................................................................................................................... 17 2.1.2.1 2.1.2.2 2.1.2.3 2.1.2.4 2.1.2.5 2.1.2.6 Der Cursor ................................................................................................................................................ 17 Die Open-Phase ........................................................................................................................................ 18 Die Parse-Phase ........................................................................................................................................ 18 Die Execute-Phase.................................................................................................................................... 18 Die Fetch-Phase........................................................................................................................................ 18 Die Close-Phase........................................................................................................................................ 18 2.2 PLSQL .................................................................................................................................................... 19 2.2.1 Historie.......................................................................................................................................... 19 2.2.2 Ausführung .................................................................................................................................... 19 2.2.3 Beispiel.......................................................................................................................................... 19 2.3 SQL AUFGABE 1 ..................................................................................................................................... 20 3 ABLAUFINTEGRITÄT ........................................................................................................................... 21 3.1 SPERRMECHANISMEN .............................................................................................................................. 21 3.1.1 Sperrtypen ..................................................................................................................................... 21 3.1.2 Automatische Sperrmechanismen.................................................................................................. 21 3.1.3 Manuelle Sperrmechanismen ........................................................................................................ 22 3.2 DAS TRANSAKTIONSKONZEPT ................................................................................................................. 22 3.2.1 Transaktionsverarbeitung nach SQL92......................................................................................... 22 3.2.2 Transaktionssteuerung über mehrere abhängige Tabellen ........................................................... 23 4 LÖSUNGEN............................................................................................................................................... 26 4.1 SQL AUFGABE 1 ..................................................................................................................................... 26 -3- Oracle 8i – Oracle Architektur Zauner Hermann 1. Oracle Architektur 1.1 Die physische Struktur Eine Oracle DB besteht aus folgende Komponenten: • • • Beliebig viele Datenbank-Files, die auf beliebige Plattenlaufwerke verteilt werden können. Mindestens zwei REDO-Log-Files und Mindestens zwei Kontroll-Files Datenbank-Files File 1 File 2 File 3 File 4 Online Redo-Log Files Kontroll-Files Abbildung 1: Die physische Strukturelemente einer Oracle-Datenbank -4- Oracle 8i – Oracle Architektur Zauner Hermann 1.1.1 Datenbank Files In den Datenbank Files befinden sich alle Oracle Objekte . Sowohl das Oracle-DataDictionary als auch die vom Benutzer angelegten Datenstrukturen, Zugriffsstrukturen, prozedurale Objekte und die eigentlichen Daten werden hier verwaltet. 1.1.2 REDO-Log Files Man unterscheidet zwischen zwei Arten von REDO-Log Files: a) Online REDO Log Dienen dazu alle Datenänderungen, die innerhalb einer Transaktion durchgeführt werden, zu protokollieren und abzuspeichern. Der Schreibvorgang auf das REDO Log erfolgt erst, wenn die Transaktion (COMMIT) abgeschlossen wurde. Die Transaktion gilt erst dann als erfolgreich abgeschlossen, wenn alle Änderungen und die Information über das Transaktionende erfolgreich im REDO Log abgespeichert wurde. Um die Sicherheit zu erhöhen, können mehrere Online REDO Logs parallel betrieben werden. b) Offline REDO Log (optional) Archivierte Online Redo Logs (DB im ARCH Modus) 1.1.3 Kontrol Files In den Kontroll-Files werden die Grundstrukturen und Grundinformationen über eine OracleDB abgelegt. • • • • • Der Zeitpunkt der Datenbank-Erstellung Die Namen aller Datenbank-Files Die Namen aller REDO-Log Files und deren Sequenz-Nummern Zeitpunkt des letzten Checkpoints und zugehörige REDO-Log Files Datenbank-Strukturänderungen z.B. Vergrößern der Datenbank und vieles mehr -5- Oracle 8i – Oracle Architektur Zauner Hermann 1.2 Die logische Struktur Für das ORACLE-Datenbanksystem sind die Datenbankfiles lediglich Speicherlieferant, dem das ORACLE System seine eigene, für die Verwaltung großer Mengen von Daten optimale Struktur aufprägt. Logische Sicht Physische Sicht Tablespace A Tablespace B Tabelle 1 File 1 File 1 File 2 File 3 Abbildung 2: Zusammenhang zwischen logischer und physischer Sicht Ein DB-Objekt kann innerhalb eines Tablespaces wachsen, auch über Filegrenzen(File 1) und somit über Plattengrenzen hinweg. Ein DB-Objekt (Tabelle 1) kann sich jedoch nicht über Tablespacegrenzen hinweg ausdehnen. Vorteile dieser Strukturierung: • Fehlertoleranz gegenüber Plattenfehlern Datenbank Files sollten auf unterschiedliche Plattenlaufwerke verteilt werden und die Files eines Plattenlaufwerkes einem Tablespace zugeordnet werden, um beim Ausfall eines Plattenlaufwerkes den Schaden begrenzen zu können. • Performance kann gesteigert werden Sind Tabellendaten und dazugehörige Indexe auf unterschiedliche Tablespaces und somit auf verschiedene Plattenlaufwerken verteilt, kann in den meisten Fällen die Performance gesteigert werden. -6- Oracle 8i – Oracle Architektur Zauner Hermann 1.3 Die Tablespace Struktur I I D Tablespace Segmente Extents DB-Blöcke Datensatz Abbildung 3: Elemente eines Oracle Tablespaces Alle DB-Objekte, die innerhalb einer Oracle-DB angelegt werden, sind stets einem Tablespace zugeordnet. In welchem Tablespace das entsprechende DB-Objekt mit dem dazugehörigen Segment angelegt wird, hängt ab: • • • Auf welche Tablespaces der Benutzer, der das Objekt anlegt, Zugriff hat Mit welchen Privilegien der Benutzer, der das Objekt anlegt, ausgestattet ist Welche Optionen der create Befehl, der das Objekt erzeugt, beinhaltet. Ein neu angelegter Benutzer hat standardmäßig keine Tablespace Verbrauchsrechte. Charakteristika: • • • • Speicherplatzstruktur Verteilen der I/O Zugriffe Backup/Recovery Schema Platzverwaltung auf Benutzerebene Um einen Tablespace zu definieren siehe Segmente. -7- Oracle 8i – Oracle Architektur Zauner Hermann 1.4 Die Segmente Im wesentlichen können vier Objekt-Typen bzw. Segmentarten unterschieden werden: • • • • Tabellen (Datensegmente) Indices (Indexsegmente) Rollback-Segmente Temporäre-Segmente Wobei Temporäre-Segmente vom System angelegt werden. Tablespace B Initial TAB1 __ __ _ _____ __ __ _ _____ __ __ _ _____ Extent Block Extent Abbildung 4: Segmentstruktur Ein Segment besteht aus einem oder mehreren „extents“, das sind Speicherbereiche in bestimmbarer Größe, die einem Segment zugeordnet werden können. Dabei wird unterschieden zwischen einem Anfangs-Extent (initial extent), das bereits zum Erstellungszeitpunkt des Segments angelegt wird und beliebig viele Folge-Extents (next). Die Folge-Extents werden immer dann vom System erzeugt, wenn der bisherige allokierte Speicherplatz nicht ausreicht, um neue Datensätze aufzunehmen. Die Größe des Next-Extents eines Segmentes ist definierbar und kann zusätzlich mit einem Wachstums-Parameter (PCTINCREASE) versehen werden, der die Größe eines Next-Extents gegenüber dem Vorgänger-Extent entsprechend vergrößern kann. Jedes Extent wiederum besteht aus einer bestimmten Anzahl von ORACLE-Datenblöcken, die bei den meisten Implementierungen 2KB groß sind. -8- Oracle 8i – Oracle Architektur Zauner Hermann Gibt die Größe des ersten Extents eines Segmentes an Gibt die Größe des nächsten Extents des Segments an. Gibt die Anzahl der Extents an, die bereits bei der Erstellung eines Segments angelegt werden sollen. Gibt die maximale Anzahl von Extents an, die ein Segment besitzen kann Gibt den Wachstumsfaktor eines Extents in Prozent an. Ein Wert von 50 bedeutet, daß das jeweils nächste Extent um 50% gegenüber den Vorgängerextent vergrößert wird. (Empfehlung 0) Gibt die freelists eines Segmentes an Gibt die optimale Größe eines Rollback-Segments an. Nur für ein Roll.-Seg. definierbar. INITIAL NEXT MINEXTENTS MAXEXTENTS PCTINCREASE FREELISTS OPTIMAL • Alle Tablespaces anzeigen: Select * from user_tablespaces; • DB-Objekte und den dazugehörigen Tablespace anzeigen: Select * from user_tables; 1.5 Die Blockstruktur Man unterscheidet zwischen zwei Bereiche: • DB-Block Kopf und • Der DB-Block Datenbereich DB-Block Kopf PCTFREE Für sich vergrößernde Datensätze (Schlupf) Update Insert PCTUSED Abbildung 5: DB-Block Struktur -9- D A T E N B E R E I C H Oracle 8i – Oracle Architektur Zauner Hermann 1.5.1 DB-Block Kopf Der DB-Block enthält Verwaltungsinformationen wie zum Beispiel das Datensatz (Row)Directory, das für jeden Datensatz des betreffenden DB-Blockes 2Bytes reserviert und das Transaktions-Directory, das Informationen beinhaltet über die aktuellen Transaktionen. Ein einzelner Transaktionseintrag belegt dabei 23Bytes, wobei die Anzahl der TransaktionsEntires pro DB-Block durch die DB-Block Parameter INITRANS und MAXTRANS definiert werden. 1.5.2 DB-Block Datenbereich 1.5.2.1 DB-Freibereich (PCTFREE) Der notwendige Freibereich für „update“ Operationen kann mit dem „PCTFREE“ Parameter definiert werden. Dieser Parameter gibt an, wieviel Prozent des DB-Block-Datenbereiches als Freiplatz für sich vergrößernde Datensätze freigehalten werden soll. • Dynamische Tabellen 60% • Statische Tabellen 0-30% Kann eine „update“ Operation die gewünschte Änderung nicht innerhalb des Blockes durchführen, wird der betreffende Datensatz in einen neuen DB-Block oder in einen DBBlock mit genügend freiem Speicherplatz übertragen, um in diesem neuen DB-Block die Änderungen durchzuführen. Ein solcher Datensatz ist mit seinem ursprünglichen DB-Block verkettet. Der ursprüngliche DB-Block speichert dabei die neue Adresse (die neue ROWID) des Datensatzes unter der alten Datensatzadresse ab. Dadurch muß der Index bei einer Verkettung nicht geändert werden. 1.5.2.2 DB-Einfügebereich (PCTUSED) „Insert“ Operationen werden solange durchgeführt bis der verfügbare Platz im Block weniger als PCTFREE betragen würde. Sinkt der Datenbereich unter die PCTUSED Grenze so steht der DB-Block wieder für „Inserts“ zur Verfügung. Die Summe von PCTUSED + PCTFREE + ein Schlupf (10-30% ~ 1-2 Datensatzlängen) sollten nicht 100% übersteigen. Zu beachten ist, dass durch einen großen Schlupf und einen großen PCTFREE Freibereich die Speicherplatzausnützung verschlechtert wird. PCUSED + PCTFREE + 1ROW ≈100% 1.5.3 FREE-DB-Block-Liste (FREELISTS) In der FREE-Liste werden die partiell gefüllten DB-Blöcke verwaltet. DB-Böcke deren PCTFREE Wert noch nicht erreicht ist stehen in der FREE-DB-Block-Liste. Wird durch eine INSERT Operation der PCTFREE Wert unterschritten, wird der DB-Block aus der FREE-DB-Block-Liste entfernt und steht somit für „Inserts“ nicht mehr zur Verfügung. Fällt der aktuelle Füllgrad eines DB-Blockes durch „delete“ oder „update“ Operationen unter PCTUSED definierten Wert, wird dieser DB-Block wieder in die FREE-Liste aufgenommen. - 10 - Oracle 8i – Oracle Architektur Zauner Hermann Wird in der FREE-DB-Block-Liste kein genügend großer Speicherbereich gefunden, werden noch nicht benutzte DB-Blöcke des Segmentextents herangezogen und in die FREE-Liste eingetragen. Können nicht genügend DB-Böcke bereitgestellt werden, wird ein neues Extent aus dem aktuellen Tablespace angelegt. Da bei vielen Transaktionen sehr oft auf die FREE-Liste zugegriffen wird, ist es sinnvoll mehrere FREE-Lists im CREATE TABLE Befehl mit dem FREELISTS Parameter zu definieren. 1.6 Die Datensatzstruktur Man unterscheidet zwischen 2 Bereiche: • Datensatzkopf und • Datensatzrumpf Spaltenlänge 1 oder 3 Byte Datensatz Kopf Daten der Spalte Variabel (VARCHAR2) Fest (CHAR) Daten der einzelnen Spalten (Datensatzrumpf) Anzahl der Spalten 1Byte Kettenadresse für Sätze > Blöckgr. (Byte) Datensatz-Kopf 2Bytes Cluster Schlüssel (optional) 1Byte Abbildung 6: Datensatzformat - 11 - Oracle 8i – Oracle Architektur Zauner Hermann 1.6.1 Datensatzkopf Der Datensatzkopf enthält Verwaltungsinformationen: • Anzahl der Spalten innerhalb des Datensatzes • Verkettungsadressen, falls der Datensatz größer als ein DB-Block oder falls der Datensatz wegen zu kleinem DB-Block Freibereich in einen anderen DB-Block übertragen werden mußte. • Cluster-Schlüssel Informationen, falls der Datensatz zu einer geclusterten Tabelle gehört. Der Datensatzkopf besteht im Minimum aus drei Bytes und kann für geclusterte und verkettete Datensätze fünf Bytes als Maximum erreichen. 1.6.2 Datensatzrumpf Im Datensatzrumpf werden die eigentlichen Daten abgelegt. Die physische Spaltenreihenfolge muß nicht mit der logischen Reihenfolge, wie beim CREATE TABLE Befehl angegeben, übereinstimmen. Eine Spalte mit dem Type LONG oder LONG RAW wird stets an das physische Ende eines Datensatzes plaziert. Jedem Spaltenwert geht eine Spaltenlängeangabe oder Spaltenkopf voraus: • 1Byte: NUMBER, CHAR, DATE • 3Bye: VARCHAR2, VARCHAR, LONG, RAW und LONG RAW - 12 - Oracle 8i – Oracle Architektur Zauner Hermann 1.7 Die Prozeß Struktur Man unterscheidet zwischen mehreren Hintergrundprozessen: • • • • • Database-Write Prozeß Log Writer Prozeß Prozeß Monitor System Monitor Archivier Prozeß DATENBANK-CACHE (System Global Area) DB Puffer DBWR DB-Files REDO Puffer LGWR PMON REDO-Log Files Shared SQL Pool SMON Kontrol File ARCH Archivierte REDO-Log Files Abbildung 7: Die Prozeß Struktur 1.7.1 Database-Write Prozeß (DBWR) Der DBWR-Prozeß hat die Aufgabe, die bereits im DB-Cache geänderten Datenbank-Blöcke auf die Datenbank-Files zurückzuschreiben. Das Schreiben der durch eine Transaktion geänderten DB-Blöcke wird nicht unmittelbar bei Transaktionsende durchgeführt, sondern wird, um die DB-Böcke möglichst lange im DBCache zu halten und von beliebigen Prozessen nutzen zu können, erst durch bestimmte Ereignisse ausgelöst: - 13 - Oracle 8i – Oracle Architektur • • • • Zauner Hermann Ein Prozeß weitere DB-Böcke von den Datenbank-Files benötigt, aber der DB-Cache belegt ist. Anzahl der modifizierten DB-Blöcke innerhalb des DB-Caches zu groß wird. Wird ein bestimmter Prozentsatz überschritten, dann wird der DBWR-Prozeß aktiv. Wird der DBWR innerhalb einer bestimmten Zeit durch kein externes Ereignis aktiviert, dann wird DBWR von sich aus aktiv. (DBWR-Timeout ca. alle 3 Sekunden) Wird ein CHECKPOINT erreicht, dann werden alle modifizierten DB-Böcke auf die Datenbank-Files zurückgeschrieben. Es werden jene Blöcke zurückgeschrieben, die von Benutzerprozessen am wenigsten benutzt werden. Ein CHECKPOINT wird durch den Wechsel eines REDO-Log Files ausgelöst. Dieser wird duchgeführt, wenn ein REDO-Log File gefüllt ist oder wenn der CeckpointInitialisierungsparameter (LOG_CHECKPOINT_INTERVALL, LOG_CHECKPOINT_TIMEOUT) in der INIT.ORA erreicht ist. Im Normalfall sollte man die Initialisierungsparameter nicht ändern. Vor dem DBWR-Schreibvorgang wird stets der LGWR-Prozeß aktiviert. 1.7.2 REDO Log-Writer Prozeß (LGWR) Der LGWR-Prozeß hat die Aufgabe, den REDO-Log Puffer auf das REDO-Log File zu schreiben. Die Protokollierung der Transaktionen ist notwendig, um bei Fehlsituationen die Datenbank wieder in einen konsistenten Zustand bringen zu können, da die geänderten DB-Böcke nicht bei Transaktionsende auf die Datenbank-Files geschrieben werden. Im REDO-Log Puffer werden alle Datenbank-Änderungen, die innerhalb einer Transaktion im DB-Cache durchgeführt werden, protokolliert. Der REDO-Log Puffer enthält sowohl die neuen Datenwerte, als auch die ursprünglichen Datenwerte, die innerhalb der Rollbacksemgente aufbewahrt werden. Der LGWR-Prozeß wird durch folgende Ereignisse aktiviert: • • • Der aktuelle REDO-Log Puffer ist zu 80% gefüllt. Ein Benutzerprozeß signalisiert ein Transaktionsende. Der DBWR Prozeß signalisiert einen Schreibvorgang. Wird ein CHECKPOINT ausgeführt, hat der LGWR-Prozeß zusätzliche Aufgaben: • • • • Alle modifizierten DB-Blöcke des DB-Caches zu markieren. (DBWR) Alle File-Header der DB-Files bezüglich des Checkpoints zu aktualisieren. Den aktuellen REDO-Log Block auf das REDO-Log File zu schreiben. Dem DBWR-Prozeß den Checkpoint zu signalisieren, der daraufhin alle durch den LGWR-Prozeß markierten DB-Blöcke auf die DB-Files zurückschreibt. - 14 - Oracle 8i – Oracle Architektur Zauner Hermann Vorteile: • Performance kann gesteigert werden. DB-Blöcke können länger im DB-Cache gehalten werden • Systemsicherheit ist gewährleistet. Alle abgeschlossenen Transaktionen werden im REDO-Log File permanent gemacht 1.7.3 Prozeß Monitor (PMON) Der PMON-Prozeß hat die Aufgabe, abgebrochene Transaktionen aus dem System zu entfernen. Dazu müssen alle gesperrten Datensätze wieder freigegeben und alle von der offenen Transaktion durchgeführten Änderungen zurückgesetzte werden. Der PMON-Prozeß wird periodisch aktiviert und überprüft, ob abgebrochene Transaktionen existieren. Man spricht in diesem Zusammenhang auch von Prozess-Recovery. 1.7.4 SYSTEM Monitor (SMON) Der SYSTEM Monitor hat die Aufgabe, beim Starten der Oracle-Instanz zu überprüfen, ob es innerhalb der Datenbank noch offene Transaktionen gibt, oder ob Transaktionen vorhanden sind, die zwar abgeschlossen sind, deren Daten jedoch noch nicht in die Datenbank-Files geschrieben wurden. Folgende Gründe kommen in Frage: • • • Datenbank-Administrator schließt die Datenbank im IMMEDIATE- oder ABORT-Modus Zusammenbruch des DBMS oder des Betriebsystems Hardware-Fehler (kein Plattenfehler), Stromausfall Beim erneuten Starten der Datenbank-Instanz, nach einem der genannten Ereignisse, übernimmt der SMON-Prozeß das komplette Recovery. Mit Hilfe der Einträge im REDO-Log File werden alle Transaktionsschritte nachvollzogen, d.h. für alle abgeschlossenen Transaktionen werden die gültigen Daten in das Datenbank-File geschrieben und etwaige Daten in den Datenbakfiles von nicht abgeschlossenen Transaktionen werden zurückgesetzt. Nach Abschluß des Instance-Recovery Vorganges ist die Datenbank in einem konsistenten Zustand. 1.7.5 Archivier Prozeß (ARCH) Der ARCH-Prozeß ist ein optionaler Hintergrundprozeß, dessen Vorhandensein abhängt, ob die Oracle-Instanz im ARCHIVE-Modus gestartet wurde. Im Falle eines Datenverlustes, der durch einen Plattenfehler hervorgerufen wurde, kann die gesamte Datenbank mit Hilfe eines BACKUPs der betreffenden Datenbankfiles und der dazugehörigen Log-Files rekonstruiert werden. Dieses Sichern übernimmt der ARCH-Prozeß. Zusätzlich macht dieser noch die notwendigen Einträge in den Kontroll-Files, die im Falle eines Medium Recovery´s benötigt werden. - 15 - Oracle 8i – Oracle Architektur Zauner Hermann 1.8 Der Datenbank-Cache Der DB-Cache ist ein Hauptspeicherbereich, der von allen Benutzerprozessen, die mit dieser Oracle-Instanz arbeiten, benutzt werden kann. Alle Hintergrundprozesse, die für den Betrieb eines Oracle-Systems notwendig sind, nutzen diesen Hauptspeicherbereich sehr intensiv, einerseits um Datenblöcke oder Indexblöcke zu verändern, andererseits um Informationen untereinander auszutauschen. 1.8.1 DB-Block Puffer DB-Blöcke, die das Anwenderprogramm anfordert, werden in den DB-Cache gelesen, dort verarbeitet und dem Benutzerprozeß zur Verfügung gestellt. Ein bereits im DB-Cache befindlicher DB-Block kann nicht nur von dem Prozeß benutzt werden, der ihn ursprünglich angefordert hat, sondern steht allen Prozessen zur Verfügung. Es werden jene DB-Böcke aus dem DB-Cache entfernt, die am längsten nicht mehr benutzt wurden (Least Recently Used - Algorithmus). 1.8.2 Shared Pool Es wird unterschied zwischen einem „shared“ SQL und einem „privatem“ SQL Bereich. Innerhalb des „shared“ Bereiches des DB-Caches werden die geparsten SQL Befehle, compilierte Funktionen, Prozeduren, Data-Dictionary Informationen und Datenbank-Trigger gehalten. Im „privatem“ Bereich werden sessionorientierte (private) Informationen wie Werte der Bindevarible, Ausführungsstatus eines Befehls usw. abgelegt. Die im Shared Pool abgelegten Objekte unterliegen einem LRU-Algorithmus (s. 0). 1.8.3 Instance-Informationen Hier wird eingetragen, welche Resourcen gesperrt sind, in welcher Reihenfolge eine Warteschlange abgearbeitet wird, welche Transaktionen aktiv sind und welchen Status diese Transaktionen haben. Hierzu kann auch die Request-Queue und die Response-Queue bei einer MTS-Konfiguration gezählt werden. - 16 - Oracle 7,8 – SQL, PLSQL Zauner Hermann 2 SQL / PLSQL 2.1 SQL 2.1.1 Historie Die Geschichte der Datenbanksprache SQL steht in unmittelbarem Zusammenhang mit der Entwicklungsgeschichte des relationalen Datenbankmodells, das 1970 durch eine Forschungsabteilung der IBM entstand. Das Relationale Datenbankmodell brachte folgende Vorteile gegenüber den Hierarchischen Datenbanksystemen: • • • • Entlastung der Programmierer Datenunabhängigkeit der Programme Flexibilität der Datenstrukturen Benutzung auch durch den End-Benutzer Oracle wurde 1977 gegründet und bot mit der Version 2 die erste kommerziell verfügbare relationale SQL basierende Datenbank an. Auch IBM kündigte Anfang der 80er Jahre zwei relationale SQL-Datenbanksysteme an: SQL/DS für DOS/VSE- und VM- Maschinen und DB2 für MVS-Maschinen. 1986 wurde der erste Standart unter den Namen SQL1 bzw. SQL86 von der ISO herausgegeben. Ergänzungen erfolgten 1989, 1992 (SQL2) und 1999. SQL 1999 unterstützt Objektorientierung einschließlich Benutzer definierter Typen mit hierarchischer Vererbung, dynamischer Einsatzverteilung und vollständiger Einkapselung. Objekt/relationale Datenbanksysteme wie Oracle8i besitzen bereits diese Merkmale. Das Objektmodell von SQL 1999 ist ähnlich wie das von Java, und erleichtert damit die gemeinsame Nutzung beider Sprachen. Mit einem neuen Teil von SQL 1999, der 2000 veröffentlicht werden soll, können SQL-Befehle sogar in Java-Programme eingebettet werden. Weitere Fähigkeiten sind in Vorbereitung, um Java-Methoden in SQL-Code nutzen zu können. 2.1.2 Ausführung Die Abarbeitung eines SQL-Befehles erfolgt nicht in einem Stück, sondern in mehreren Phasen. 2.1.2.1 Der Cursor Der Cursor enthält den betreffenden SQL-Befehl als Zeichenkette, den Ausführungsplan des SQL-Befehls als Zeiger auf den Shared Pool sowie Zeitstempel- und Zustandsinformationen. Der Cursor wird stets als dynamisches SQL übergeben. Dies Bedeutet, dass die SQL-Befehle der Anwendungen dem ORACLE Server vor der Ausführung nicht bekannt sein müssen. - 17 - Oracle 7,8 – SQL, PLSQL Zauner Hermann 2.1.2.2 Die Open-Phase Hier wird lediglich die Datenstruktur des Cursors bereitgestellt und initalisiert. In einigen Programmierumgebungen wird die nachfolgende Parse-Phase automatisch beim Open ausgeführt 2.1.2.3 Die Parse-Phase Die Parse-Phase dient zur Vorbereitung der Ausführung eines SQL-Befehls. Wird der SQL-Befehl im Shared Pool gefunden und kann er verwendet werden, wird die Parse-Phase sofort erfolgreich beendet. Ansonsten erfolgt eine Syntaxüberprüfung, die Auswertung der betroffenen Datenbankobjekte (Tabellen, Views etc.) inklusive einer Zugriffsprüfung sowie die Bestimmung eines Ausführungsplans durch den Optimizer. Der Ausführungsplan und der SQL-Befehl werden im Shared Pool (1.8.2) abgelegt. Vor dem Ausführen des SQL-Befehls werden noch die Bindvariablen gebunden. 2.1.2.4 Die Execute-Phase Es wird ein Lesekonistenzzeitpunkt (SCN) im Cursor vermerkt. Es wird damit gewährleistet, dass ein SQL-Befehl immer lesekonsistent arbeitet, d.h. aus Sicht des Befehls haben sich die Daten seit Befehlsstart nicht verändert. Beim SELECT werden bei Sortierungen und Gruppierungen die Daten entsprechend aufbereitet und in einem Temporärbereich abgelegt. 2.1.2.5 Die Fetch-Phase Diese Phase wird ausschließlich für SELECT Befehle durchgeführt. In der Fetch-Phase werden die Daten an die Anwendung übertragen. Dabei wird entweder aus den Daten- und Indexblöcken oder aus dem in der Execute-Phase vorbereiteten Temporärbereich gelesen. ORACLE bietet auch die Möglichkeit die Ergebnisse in einem Array Fetch zu übertragen. Damit kann die Performance gesteigert und die Netzbelastung minimiert werden. 2.1.2.6 Die Close-Phase Wenn der Cursor nicht mehr gebraucht wird, kann er durch einen Close beendet und entfernt werden. - 18 - Oracle 7,8 – SQL, PLSQL Zauner Hermann 2.2 PLSQL 2.2.1 Historie Die Sprache PL/SQL wurde in starker Anlehnung an die Programmiersprache ADA definiert. Mit der Version 6 des ORACLE-Datenbanksystems wurde die Sprache PL/SQL als prozedurale Erweiterung von SQL eingeführt. Wesentliche Gründe waren: • • Komplexe datenbankorientierte Operationen sollten vollständig innerhalb des DatenbankServers durchgeführt werden können, ohne daß nach jeder SQL-Operation die Kontrolle an das Anwendungsprogramm zurückgegeben werden muß. (Performancegewinn) PL/SQL als Standard in allen ORACLE-Programmen, sowohl innerhalb des Servers als auch innerhalb der ORACLE Softwareentwicklungsumgebung. Erweiterungen in Oracle 8i • Manipulation mehrerer Datenelemente mit nur einer SQL-Anweisung (Bulk Binds) • Parameterübergabe per Referenz in PL/SQL • Eingebettetes dynamisches SQL in PL/SQL • Beschreibung des PL/SQL-Codes für Werkzeugeentwickler • Unbeschränkte PL/SQL-Paketgröße 2.2.2 Ausführung PL/SQL wird generell in einem P-Code übersetzt. Der P-Code ist darauf optimiert, möglichst schnell interpretiert werden zu können. Der P-Code wird zu allen gespeicherten PL/SQLProgrammeinheiten (Proceduren, Funktionen, Pakete, Trigger) im Data Dictionary abgelegt und muß zur Ausführung lediglich in den Shared Pool geladen werden. Befindet sich die Funktion etc. bereits im Shared Pool wird sie von dort geladen. 2.2.3 Beispiel DECLARE -- declaration section num_err NUMBER; num_varcol MV_TERMIN.terminid%TYPE; CURSOR my_cursor IS SELECT terminid FROM MV_TERMIN; BEGIN -- execution section dbms_output.put_line( 'HALLO WORLD'); OPEN my_cursor; LOOP FETCH my_cursor INTO num_varcol; EXIT WHEN my_cursor%NOTFOUND; END LOOP; IF my_cursor%ISOPEN THEN CLOSE my_cursor; END IF; EXCEPTION -- error section WHEN OTHERS THEN num_err := SQLCODE; END; - 19 - Oracle 7,8 – SQL, PLSQL Zauner Hermann 2.3 SQL Aufgabe 1 Richtigstellung (Ergebnis): Motivation: In der Tabelle t_master wurde irrtümlich für jede Buchung ein Master Datensatz (t_master) angelegt. Nun sollen die Daten laut Tabelle richtiggestellt werden. SQL> select * from t_master; ID PARTNERID ---------- ---------1 1 2 3 3 2 7 4 Aufgabenstellung: Löschen der doppelten Partnerdaten. Dabei sollen jene Datensätze mit dem kleinsten Schlüssel (id) erhalten bleiben. SQL> select * from t_buchung; Anweisung: Lösung mit möglichst wenigen SQL Befehlen und nach folgen Punkten. 1. SQL 86 2. SQL 92 3. PLSQL etc. ID BUCHUNGID ---------- ---------1 101 2 101 3 103 1 104 3 105 2 106 7 107 Lösungen an: [email protected] SQL> select * from t_master; Script: ID PARTNERID ---------- ---------1 1 2 3 3 2 4 1 5 2 6 3 7 4 drop table t_buchung; drop table t_master; create table t_master (id number(2) constraint masterid_pk primary key, partnerid number(12) not null); insert insert insert insert insert insert insert SQL> select * from t_buchung; ID BUCHUNGID ---------- ---------1 101 2 101 3 103 4 104 5 105 6 106 7 107 into into into into into into into t_master t_master t_master t_master t_master t_master t_master values values values values values values values (1,1); (2,3); (3,2); (4,1); (5,2); (6,3); (7,4); create table t_buchung (id number(2) constraint id_fk references t_master(id), buchungid number(12) not null, constraint buchung_pk primary key (id, buchungid) ); insert insert insert insert insert insert insert commit; into into into into into into into - 20 - t_buchung t_buchung t_buchung t_buchung t_buchung t_buchung t_buchung values values values values values values values (1,101); (2,101); (3,103); (4,104); (5,105); (6,106); (7,107); Oracle 7,8 – Ablaufintegrität Zauner Hermann 3 Ablaufintegrität 3.1 Sperrmechanismen 3.1.1 Sperrtypen EXCLUSIVE MODE (X) Die Tabelle ist exklusiv gesperrt. Andere Prozesse können jedoch lesend zugreifen SHARE MODE(S) Andere Prozesse können die Tabelle ebenfalls im SHARE Mode sperren und die Tabelle lesen, jedoch kann kein Prozeß Änderungsoperationen auf die Tabelle durchführen. ROW EXCLUSIVE(RX) Standard Sperrverhalten innerhalb Oracle, das beliebigen Prozessen RX-Locks gestattet. Beliebig viele Prozesse können gleichzeitig Änderungsoperationen auf die Tabelle durchführen. ROW SHARE(RS) Gestattet weiteren Prozessen RS- und RX Locks zu setzen. Andere Prozesse können die Tabelle lesen, jedoch nicht exclusiv (X) sperren. Zum UPDATE Zeitpunkt werden die RS-Sperren in eine RX-Sperre umgewandelt. SHARE ROW EXCLUSIVE(SRX) Andere Prozesse können die Tabelle lesen und im RS-Mode sperren. Exclusive Sperren (X) oder Share Sperren (S) sind nicht erlaubt. 3.1.2 Automatische Sperrmechanismen Bei ORACLE werden bei allen DML-Operationen (insert, update, delete) stets nur die von der Operation betroffenen Datensätze exklusiv (x) gesperrt. Während einer solchen Operation befindet sich die entsprechenden Tabellen im Datensatz (ROW) Exlusiv-Modus(RX). Da die RX-Sperre von beliebigen Prozessen geleichzeitig gehalten werden kann, können mehrere Prozesse gleichzeitig dieselben Tablle mit INSERT, UPDATE oder DELETE bearbeiten. Nur bei einer exclusiv(X) Sperre, muss der andere Prozess so lange warten bis die Sperre wieder aufgehoben wird. Operation Sperrmodus Sperrmodus Bemerkung Datensatz Tabelle SELECT Ein lesender Zugriff braucht keine Sperren INSERT, UPDATE, X RX Alle DML-Operationen sperren auf DELETE Satzebene DDL/DCL X ALLE DDL-Operationen (create, Befehle drop) sperren auf Tabellenebene Abbildung 8: Automatische Sperrmechanismen - 21 - Oracle 7,8 – Ablaufintegrität Zauner Hermann Die Implementierug des ORACLE-Sperrverfahrens basiert prinzipiell auf drei Komponenten: • Transaktionsverzeichnis innerhalb eines jeden DB-Blockes • System Change/Commit Number (SCN) • Transaktions-Tabelle Jede Transaktion, die gestartet wird, erhält eine eindeutige Transaktionsnummer (SCN), die innerhalb der Transaktionstabelle verwaltet wird. Alle Datensätze, die durch eine Transaktion geändert werden, erhalten die SCN dieser Transaktion. Ein Datensatz gilt als gesperrt, wenn die SCN für den betreffenden Datensatz mit einer aktiven, in der Transaktions-Tabelle befindlichen SCN übereinstimmt. Der Satz wird durch einen Commit oder Rollback wieder freigegeben, indem die SCN aus der Transaktionstabelle gelöscht wird. 3.1.3 Manuelle Sperrmechanismen Darüberhinaus besteht die Möglichkeit Datensätze oder Tabellen manuell zu Sperren Datensätze sperren: Select * from dual for update (nowait) Tabellen sperren: Lock table dual in exclusive [share, etc.] mode (nowait) Die Aktualität des Datensatzes am Client kann durch die Einführung eines Update-Counts oder durch einen Timestamp überprüft werden. Wurde der Datensatz von einem anderen Benutzer verändert, so muss der Datensatz unter Umständen erneut eingelesen werden (Aktualisierung). 3.2 Das Transaktionskonzept Eine Transaktion kann jeweils nur vollständig durchgeführt werden, d.h. nach Beendigung einer Transaktion wird in allen Fällen ein konsistenter Datenbankzustand erreicht. Ist der Übergang zu einem neuen konsistenten Zustand nicht möglich, so muss die Transaktion vollständig zurückgesetzt (ROLLBACK) werden, um den alten konsistenten Zustand wiederherzustellen. 3.2.1 Transaktionsverarbeitung nach SQL92 Dirty Read Eine Abfrage greift auf nicht bestätigte Transaktionen zu und liefert daher falsche Ergebnisse Non-Repeatable Read Eine erneute Ausführung einer Abfrage führt zu unterschiedlichen Ergebnissen, da durch zwischenzeitlichen Änderungen (update) oder Löschen (delete) die Datensätze verändert wurden. Dabei sind die einzelnen Abfrageergebnisse konsistent, beziehen sich aber auf unterschiedliche Zeitpunkte, z.B. den jeweiligen Beginn der Abfrage Phantom Read Eine erneute Ausführung einer Abfrage führt zu einer unterschiedlichen Ergebnissmenge, da durch zwischenzeitlichen Datensätze eingefügt (insert) wurden. - 22 - Oracle 7,8 – Ablaufintegrität Zauner Hermann Oracle unterstützt dabei 2 Isolation Levels: Isolation Level Dirty Read Phantom Read Ja - Non-Repeatable Read Ja Ja Read Uncommited Read Commited (Oracle) Repeatable Read Serializable (Oracle) - - Ja - Ja Ja Abbildung 9: Isolation Level nach SQL92 Zusätzlich unterstützt Oracle noch den Modus read only. Wie Serializable Mode aber es kann ausschließlich nur lesend zugegriffen werden. Mit dem Befehl SET TRANSACTION kann die Transaktion in die verschiedene TransaktionsModi gebracht werden. 3.2.2 Transaktionssteuerung über mehrere abhängige Tabellen Eine Transaktion beeinflußt eine andere Transaktion bei einem fast zeitgleichen Transaktionsstart. Dazu wird eine Tabelle hz_main mit 1000 Datensätzen und eine abhängige Tabelle hz_detail mit 100 000 Datensätzen geladen. Anschließend liest die Transaktion update.sql die Anzahl der Detail-Sätze (hz_detail) für jeden MasterRecord. In der Zwischenzeit wird die Anzahl der Detail-Sätze durch die Transaktion insert2.sql erhöht. DECLARE -- declaration section err_num NUMBER; err_txt VARCHAR2(200); key1 NUMBER; key2 NUMBER; MAXD CONSTANT NUMBER := 100; BEGIN -- execution section FOR key1 in 1..1000 LOOP insert into hz_main values (key1, MAXD, null); FOR key2 in 1..MAXD LOOP insert into hz_detail values (key1, key2); END LOOP; END LOOP; COMMIT; EXCEPTION -- error section WHEN OTHERS THEN err_num := SQLCODE; err_txt := SQLERRM; ROLLBACK; dbms_output.put_line( err_txt ); insert into tmp_error values (err_num, err_txt); COMMIT; END; INSERT.SQL – Datenbefüllen der TEST-Tabellen - 23 - Oracle 7,8 – Ablaufintegrität Zauner Hermann DECLARE -- declaration section err_num NUMBER; err_txt VARCHAR2(200); num_err NUMBER; num_varcol hz_main.id1%TYPE; CURSOR my_cursor IS SELECT id1 FROM hz_main; BEGIN -- execution section OPEN my_cursor; LOOP FETCH my_cursor INTO num_varcol; EXIT WHEN my_cursor%NOTFOUND; UPDATE HZ_MAIN SET COUNT_NEW = (SELECT COUNT(*) FROM hz_detail WHERE id1 = num_varcol) WHERE id1 = num_varcol; END LOOP; IF my_cursor%ISOPEN THEN CLOSE my_cursor; END IF; COMMIT; EXCEPTION -- error section WHEN OTHERS THEN err_num := SQLCODE; err_txt := SQLERRM; ROLLBACK; dbms_output.put_line( err_txt ); insert into tmp_error values (err_num, err_txt); COMMIT; END; UPDATE.SQL INSERT INTO hz_detail values (500, 101); Commit; INSERT2.SQL INSERT2.SQL wird nach UPDATE.SQL ausgeführt. Die Transaktion INSERT2.SQL ist vor UPDATE.SQL mit einem COMMIT abgeschlossen d.h. die Änderungen der Tabelle hz_detail werden für die Transaktion UPDATE.SQL wirksam (aber niemals für den SELECT hz_main = READ COMMITED). Transaktion INSERT2.SQL UPDATE.SQL t Abbildung 10: Transaktionssteuerung anhand eines Bsp. - 24 - Oracle 7,8 – Ablaufintegrität SQL> Zauner Hermann select * from hz_main where count_new <> 100; ID1 COUNT_OLD COUNT_NEW --------- --------- --------500 100 101 Wie man sehen kann, wurde innerhalb der Transaktion UPDATE.SQL das Ergebnis verändert. Wenn man will, dass keine anderer Transaktion die eigene Transaktion beeinflussen kann, kann man die Tabelle mit dem Befehl LOCK TABLE hz_detail IN EXCLUSIVE MODE NOWAIT sperren, oder die Transaktion im Isolation Level Serializable mit dem Befehl SET TRANSACTION ISOLATION LEVEL SERIALIZABLE starten. - 25 - Oracle 7,8 – Ablaufintegrität Zauner Hermann 4 Lösungen 4.1 SQL Aufgabe 1 -- Lösung SQL Aufgabe 1 mit Oracle PLSQL -- (c) by Hermann Zauner 2/2001 Declare v_id number; buchung_record t_buchung%ROWTYPE; -- alle Buchungen die zu ändern sind cursor c1 is select id, buchungid from t_buchung where id not in (select min(id) from t_master group by partnerid); begin for buchung_record in c1 loop -- hole kleinsten Schlüssel pro Partner select min(id) into v_id from t_master where partnerid = (select partnerid from t_master where id = buchung_record.id); -- ändern der id in den buchungen update t_buchung set id = v_id where id = buchung_record.id and buchungid = buchung_record.buchungid; end loop; -- löschen der überflüssigen master sätze -- der geänderten buchungen delete t_master where id not in (select min(id) from t_master group by partnerid); end; - 26 - Oracle 7,8 – Ablaufintegrität Zauner Hermann Abbildungsverzeichnis: Abbildung 1: Die physische Strukturelemente einer Oracle-Datenbank Abbildung 2: Zusammenhang zwischen logischer und physischer Sicht Abbildung 3: Elemente eines Oracle Tablespaces Abbildung 4: Segmentstruktur Abbildung 5: DB-Block Struktur Abbildung 6: Datensatzformat Abbildung 7: Die Prozeß Struktur Abbildung 8: Automatische Sperrmechanismen Abbildung 9: Isolation Level nach SQL92 Abbildung 10: Transaktionssteuerung anhand eines Bsp. - 27 - 4 6 7 8 9 11 13 21 23 24
