Buch Partnerkatalog.indb

DOAG Oracle Jahrbuch
2007/2008
Praxisleitfaden und Partnerkatalog für die Oracle-Community
Herausgegeben von DOAG e.V.
ISBN-10: 3-446-41375-8
ISBN-13: 978-3-446-41375-7
Leseprobe
Weitere Informationen oder Bestellungen unter
http://www.hanser.de/978-3-446-41375-7
sowie im Buchhandel
Neue Features für den CBO in Oracle 11g
Von Lutz Hartmann, sysdba database consulting GmbH
Dieser Artikel behandelt eine Reihe von neuen und sehr verheißungsvollen Features für den
Query Optimizer in Oracle 11g.
Nachdem der Autor schon eine ganze Zeit mit der
Beta-Version von 11g testen konnte, sind hier seine
ersten Eindrücke zusammengefasst. Er hat sich dabei
auf fünf Features beschränkt, die alle mit dem CBO
und Statistiken in Verbindung stehen. Diese Features
sind sehr vielversprechend und scheinen eine ganze
Reihe von ernsthaften Problemen, die in 9i und 10g
mit dem CBO auftraten, zu beheben.
Individuelle Thresholds für Statistiken
auf Tabellen-Level
Seit 10g sammelt der Server Optimizer Statistiken für
neu angelegte Segmente sowie für Objekte deren
Statistiken veraltet sind und ein Stale-Flag im Data
Dictionary haben. Oracle hat das im GATHER_STATS_
JOB per Default implementiert; der Job ist in jeder
10g-Datenbank sofort enabled und läuft jede Nacht
sowie am Wochenende.
Die zugrunde liegende Technologie heißt TableMonitoring und ist für alle Tabellen aktiviert. Das bedeutet, dass Oracle Tabellen beobachtet, in denen sich
mindestens 10 Prozent aller Werte seit dem letzten
Analysieren geändert haben. Diese Objekte werden
dann als Kandidaten für das Erneuern der Statistiken
markiert. Dieser Job-Mechanismus ist nun in 11g geändert worden. Oracle verwendet jetzt ein anderes
Package als Auto-Task-Framework. Dem DBA wird damit einige Last von den Schultern genommen.
Zudem haben einige Anwender festgestellt, dass
der Schwellwert von 10 Prozent bei manchen Objekten nicht ideal gesetzt ist und man lieber einen kleineren Wert auf Segment-Ebene verwenden möchte.
Lutz@orcl11 SQL> desc dba_tab_stat_prefs
Name
Null?
Type
------------------OWNER
NOT NULL
VARCHAR2(30)
TABLE_NAME
NOT NULL
VARCHAR2(30)
PREFERENCE_NAME
VARCHAR2(30)
PREFERENCE_VALUE
VARCHAR2(1000)
Lutz@orcl11 SQL> exec dbms_stats.set_table_prefs(ʻSYSTEMʼ,ʼTʼ, ʻSTALE_PERCENTʼ, ʻ15ʼ)
PL/SQL procedure successfully completed.
Lutz@orcl11 SQL> SELECT
FROM dba_tab_stat_prefs
WHERE owner=ʼLUTZʼ
AND table_name=ʼTʼ;
OWNER TABLE_NAME PREFERENCE_NAME REFERENCE_VALUE
------ ----------------------- --------------SYSTEM T
STALE_PERCENT
15
71
Buch Partnerkatalog.indb 71
24.10.2007 9:51:29 Uhr
Produktlinie Database – Aus der Praxis – Schwerpunkt 11g
Diese Problematik ist Oracle ebenfalls in 11g angegangen. Jetzt kann der DBA individuelle Thresholds
für Statistiken auf Tabellen-Level setzen. Dazu gibt es
jetzt in DBMS_STATS neue Prozeduren und Funktionen
sowie eine Reihe von Data Dictionary Views (Siehe
Beispiel auf Seite 71).
Verzögerte Veröffentlichung von neu
gesammelten Statistiken
Ein weiteres Highlight von 11g ist die verzögerte Veröffentlichung von neu gesammelten Statistiken. Der
DBA kann bestimmen, wann neue Statistiken für den
Optimizer sichtbar werden. Das ist zum Beispiel sehr
interessant, wenn für alle abhängigen Objekte die
Berechnung neuer Statistiken bereits abgeschlossen
sein soll, bevor der Report anläuft, damit nicht ein Teil
der Objekte noch alte Statistiken verwendet und ein
anderer bereits die neuen. Der DBA kann Statistiken
im PENDING-Zustand halten und damit beeinflussen,
wann sie verwendet werden sollen.
DBMS_STATS.RESUME_GATHER_STATS ist die Prozedur, die es ermöglicht, sogar mit dem Sammeln der
neuen Statistiken genau an der Stelle wieder aufzusetzen, an dem der vorherige Job-Run, aus welchen
Gründen auch immer, erfolglos abgebrochen wurde.
Es ist auch möglich, die noch unveröffentlichten
Statistiken an eine Test-Umgebung zu senden und
dort erst einmal ihre Auswirkungen auf den Workload
auszuprobieren, bevor sie in der Produktion verwendet werden. Sie werden als PENDING STATISTICS bezeichnet und in speziellen Tabellen zwischengelagert.
Die entsprechenden Views im Data Dictionary haben
Namen wie beispielsweise *_PENDING_STATS.
Multi Colums Statistics
Ein weiteres, sehr interessantes Feature in 11g sind
Multi Colums Statistics: Man kann damit Statistiken
für Gruppen von korrelierten Spalten sammeln und
verwenden. Damit werden gleich mehrere Probleme
ausgemerzt, die der Optimizer noch in 10g hatte, als
die ersten dieser Funktionalitäten eingeführt wurden.
Der Optimizer hatte damals zwar bereits die Möglichkeit, mit korrelierten Werten umzugehen, allerdings
beschränkt auf einige wenige Spezialfälle. Jonathan
Lewis hat dem ein ganzes Kapitel in seinem Buch gewidmet [1]. Warten wir ab, was der Meister des CBO
mit seinen Test-Cases herausfinden wird.
Inkrementell aktualisierte globale Statistiken
für partitionierte Tabellen
Oracle 10g sammelt Statistiken für partitionierte Objekte auf drei Ebenen:
• Global-Table
• Partition-Level
• Sub-Partition-Level
Table Monitoring ist daher auch auf Partitionsebene
eingeschaltet. Das Problem vor 11g bestand darin,
dass das Sammeln der Global Level Statistics einen
Full Table Lock benötigt und damit einen erheblichen
Overhead verursachen kann. Die Partition Level Statistics werden dagegen nur auf den Partitionen gesammelt, für die mehr als 10 Prozent der Werte geändert
wurden.
In den meisten Fällen sind es nur ein paar Partitionen, auf denen DMLs stattfinden, während die
restlichen stabil sind – zumindest bei der Verwendung
von Range Base Partitioning. In 11g wird eine neue
Technologie namens Synopsen für einige Statistiken
wie number of distinct values verwendet. Damit kann
Oracle die globalen Statistiken inkrementell aktualisieren, indem neue Statistiken in den dynamischen
Partitionen mit den alten Statistiken aus den statischen Partitionen gemerged werden.
Diese Synopsen belegen jedoch zusätzlichen Plattenplatz im SYSAUX Tablespace, was zusätzliche I/Os
bedeutet. Dafür ist aber das Sammeln der globalen
Statistiken erheblich schneller.
Noch ein Hinweis: Histogramme werden übrigens
nicht inkrementell gepflegt. Weitere Information stehen im Performance Tuning Guide für 11g.
Extended Statistics on expressions
Als Beispiel dient eine Where Clause wie die Folgende:
WHERE UPPER(last_name) = ‹King›
Hier verwendet der Optimizer 1 Prozent als Wert für
SELECTIVITY, sofern kein Function Based Index vorhanden ist: Das führt natürlich in den meisten Fällen zu
einem sehr schlechten Execution-Plan.
In 10g kann der Optimizer bereits mit ein paar
Built-In-Functions umgehen, etwa TO_NUMBER. Mit
72
Buch Partnerkatalog.indb 72
24.10.2007 9:51:29 Uhr
Neue Features für den CBO in Oracle 11g
11g können wir nun erweiterte Statistiken für Funktionen sammeln:
exec DBMS_STATS.GATHER_TABLE_STATS(‹HR›,
‹EMPLOYEES›, METHOD_OPT => ‹for all
columns size 1 columns (upper(last_
name)) size 5ʼ)
Fazit
Es gilt jetzt, die neuen Features intensiv zu testen und
Erfahrungen damit zu sammeln. Die Richtung, in die
Oracle sich hier bewegt, ist sicherlich richtig.
Literatur
[1] Jonathan Lewis, Cost-Base Oracle Fundamentals,
ISBN: 1590596366.
73
Buch Partnerkatalog.indb 73
24.10.2007 9:51:30 Uhr
Erstellen einer Oracle 11g RAC-Datenbank per Skript
Von Dierk Lenz, Herrmann & Lenz Services GmbH
Die Oracle Datenbank 11g ist seit Ende August für die Linux-Plattform verfügbar. Wie in vielen
anderen Bereichen wurde auch bei der RAC-Option an einigen Stellen Feinschliff betrieben,
sodass die Aufgabe einfacher geworden ist, eine RAC-Datenbank manuell beziehungsweise
per Skript aufzusetzen.
Oracle propagiert bei der Erstellung von Datenbanken einen Konfigurationsassistenten – in diesem Fall
den DBCA. Dessen Einsatz hat den Vorteil, dass das
Ziel schneller und einfacher erreicht werden kann als
bei der Verwendung von Skripten. Nachteilig sind die
fehlende Nachvollziehbarkeit (Sitchwort: Erstelle die
gleiche Datenbank für eine weitere Testumgebung!)
sowie die Unklarheit über einige Details.
Die Installation
Die eigentliche Installation selbst soll hier nicht beschrieben werden – das tun die entsprechenden
Handbücher sehr gut. Allerdings an dieser Stelle der
Hinweis, bitte nicht mit dem Aufruf des Installers anzufangen, bevor die Installationsvoraussetzungen für
die Clusterware erledigt sind. In den Bereichen Betriebssystem, Security, Netzwerk und Shared Storage
ist zuvor einiges zu tun. Es ist im Übrigen sehr empfehlenswert, die Möglichkeit zu nutzen, Clusterware
und Datenbank-Software für ASM und DB-Instanzen
jeweils unter einem eigenen Benutzer zu installieren
(beispielsweise crs, asm und oracle).
Clusterware und Cluster Registry
Nach der Installation aller Software-Komponenten
werden die aktiven Komponenten eingerichtet. In
einer RAC-Umgebung reicht es jedoch nicht, etwa
einen Listener wie gewohnt zu konfigurieren. Zusätzlich muss der Listener der Cluster Registry bekanntgemacht werden, damit diese den Listener
nach einem Knoten-Reboot wieder starten kann. Für
die Cluster Registry sind die Beziehungen zwischen
den Komponenten wichtig – etwa die Abhängigkeit einer DB-Instanz von einer ASM-Instanz. Nur bei
einer korrekt definierten Abhängigkeit lassen sich
die Komponenten in der richtigen Reihenfolge starten.
Somit sind für eine RAC-Konfiguration immer zwei
Dinge wichtig: Einerseits die Konfiguration einer Komponente und andererseits die Definition der Komponente in der Cluster Registry. Dafür wird grundsätzlich
das Werkzeug srvctl genutzt.
Der Listener
Beim Listener ist der erste Unterschied zum Vorgänger festzustellen: Mit Oracle 10gR2 war die ClusterKonfiguration der Listener ausschließlich mit dem
Net-Konfigurationsassistenten netca möglich. Für 11g
wurde srvctl um das Kommando „add listener“ erweitert. Im folgenden Beispiel wird je ein Listener auf
einem Knoten angelegt.
srvctl add listener
product/11.1.0/db
srvctl add listener
product/11.1.0/db
-n
-l
-n
-l
rac1 -o /orabase/
LISTENER_RAC1
rac2 -o /orabase/
LISTENER_RAC2
Für das Starten der Listener muss die Konfiguration
im Verzeichnis $ORACLE_HOME/network/admin verfügbar sein; die Variable $TNS_ADMIN wird nicht ausgewertet.
Für die Konfiguration der Listener gilt, dass sie
auf einer Adressliste mit den public und virtuellen
Adressen starten sollten, damit sie ausschließlich auf
dem „richtigen” Knoten starten. Weiterhin sollte jeder
ADDRESS-Eintrag die Klausel (IP = FIRST) beinhalten.
Die Listener sollten aus dem Database-Installationsverzeichnis (hier unter dem Benutzer oracle)
starten. Alle später konfigurierten Instanzen (sowohl
ASM als auch DB) sollten mit dem Initialisierungspara-
75
Buch Partnerkatalog.indb 75
24.10.2007 9:51:31 Uhr
Produktlinie Database – Aus der Praxis – Schwerpunkt 11g
meter remote_listener eine über die Datei tnsnames.
ora aufgelöste Adressliste mit den virtuellen Adressen
aller Listener erhalten, damit die dynamische ServiceRegistrierung funktioniert.
ASM-Instanzen
Als Basis für eine RAC-Datenbank dient grundsätzlich
ein Shared-Disk-IO-Subsystem. Der Zugriff kann dabei über Raw Devices, ein Cluster-File-System oder
auch ASM erfolgen. ASM hat sich hier mittlerweile
gut bewährt, sodass wir es für dieses Beispiel nutzen.
Unter Linux ist weiterhin die Verwendung der
ASMLIB-Treiber empfehlenswert – beim Oracle Enterprise Linux werden diese sogar direkt mitgeliefert.
Die Konfiguration der ASM-Instanzen erfolgt über eine
init.ora-Datei (Ein SPFILE muss im RAC „shared“ zur
Verfügung stehen; da ASM jedoch unser Mechanismus
für shared files ist, kann er dafür nicht genutzt werden …) und enthält nur wenige Parameter:
instance_type = asm
asm_diskgroups = DATA
asm_diskstring = /dev/oracleasm/disks/*
cluster_database = true
instance_number = 1
remote_listener = listeners.hl.de
Die instance_number unterscheidet sich natürlich für
alle angelegten Instanzen; die Instanznamen sind typischerweise +ASM1, +ASM2, … Das Bekanntmachen
der Instanzen für die Cluster Registry geschieht wiederum mit srvctl:
srvctl add asm -n rac1 -i +ASM1 -o /orabase/product/11.1.0/asm
srvctl add asm -n rac2 -i +ASM2 -o /orabase/product/11.1.0/asm
In unserem Beispiel wurden die ASM-Instanzen unter dem Benutzer asm in einem eigenen ASM-Home
konfiguriert. Das Anlegen der Diskgruppe DATA erfolgt
beispielsweise unter SQL*Plus mit folgendem Befehl:
CREATE DISKGROUP DATA
EXTERNAL REDUNDANCY
DISK ‚/dev/oracleasm/disks/ASM_01ʻ
/
Die Disk ASM_01 muss vorher mit ASMLIB erstellt
werden; hierzu wird das Skript /etc/init.d/oracleasm
genutzt:
/etc/init.d/oracleasm createdisk ASM_01
/dev/sd<d><n>
DB-Instanzen
Auch in diesem Fall wird die Datenbank zunächst erstellt und dann mit srvctl der Cluster Registry bekannt
gemacht. Beim CREATE DATABASE arbeitet man zunächst mit cluster_database = false; erst später werden die entsprechenden Voraussetzungen geschaffen
und die Instanzen mit cluster_database = true erneut
gestartet.
Eine init.ora-Datei existiert für die DB-Instanz, jedoch nur zur initialen Erstellung des SPFILEs. Für eine
einfache Test-DB sehen die Parameter wie folgt aus:
db_name = RACDB
db_domain = hl.de
db_create_file_dest = +DATA
db_cache_size = 300M
shared_pool_size = 300M
large_pool_size = 100M
java_pool_size = 100M
pga_aggregate_target = 500M
fast_start_mttr_target = 300
remote_listener = listeners.hl.de
undo_tablespace = undots1
Das CREATE DATABASE-Kommando sowie der Aufbau
des Data Dictionaries entsprechen dem Standard und
werden aus Platzgründen hier nicht gezeigt. Interessant ist eine neue FROM-MEMORY-Klausel des CREATESPFILE-Kommandos, mit dem danach das SPFILE im
ASM angelegt werden kann – inklusive des control_
files-Parameters, der als Oracle-managed-Filename
beim CREATE DATABASE automatisch hinzukommt:
CREATE SPFILE = ‚+DATA/RACDB/PARAMETERFILE/SPFILERACDB.ORAʻ
FROM MEMORY
/
Damit die DB-Instanzen ordnungsgemäß starten,
wird im Verzeichnis $ORACLE_HOME/dbs die Datei
76
Buch Partnerkatalog.indb 76
24.10.2007 9:51:31 Uhr
Erstellen einer Oracle 11g RAC-Datenbank per Skript
initRACDB<n>.ora angelegt, die einen einzigen Parameter enthält:
spfile = ‚+DATA/RACDB/PARAMETERFILE/SPFILERACDB.ORAʻ
spfile = ‚+DATA/RACDB/PARAMETERFILE/SPFILERACDB.ORAʻ
Hier ist mit $ORACLE_HOME das DB-Home gemeint,
die zweite Installation der DB-Software unter dem Benutzer oracle. Per Skript werden nach einem Neustart
der Instanz die für RAC erforderlichen Änderungen im
SPFILE erzeugt, ein weiterer UNDO-Tablespace erzeugt
sowie ein weiterer Log-Thread angelegt:
ALTER SYSTEM SET cluster_database = true SCOPE = SPFILE SID = ‚*ʻ
/
ALTER SYSTEM SET instance_number = 1 SCOPE = SPFILE SID = ‚RACDB1ʻ
/
ALTER SYSTEM SET instance_number = 2 SCOPE = SPFILE SID = ‚RACDB2ʻ
/
ALTER SYSTEM SET thread = 1 SCOPE = SPFILE SID = ‚RACDB1ʻ
/
ALTER SYSTEM SET thread = 2 SCOPE = SPFILE SID = ‚RACDB2ʻ
/
ALTER SYSTEM SET undo_tablespace = ‚undots1ʻ SCOPE = SPFILE SID = ‚RACDB1ʻ
/
ALTER SYSTEM SET undo_tablespace = ‚undots2ʻ SCOPE = SPFILE SID = ‚RACDB2ʻ
/
CREATE UNDO TABLESPACE undots2
DATAFILE SIZE 300M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
/
ALTER DATABASE ADD LOGFILE
INSTANCE ‚RACDB2ʻ GROUP 4 SIZE 100M,
GROUP 5 SIZE 100M,
GROUP 6 SIZE 100M
/
ALTER DATABASE ENABLE INSTANCE ʻRACDB2ʼ
/
Abschließend werden mit srvctl die DB-Instanzen bei
der Cluster Registry bekanntgemacht sowie die Abhängigkeiten zu den ASM-Instanzen definiert:
srvctl
srvctl
srvctl
srvctl
add database -d
add instance -d
add instance -d
modify instance
RACDB -o
RACDB -i
RACDB -i
-d RACDB
/orabase/product/11.1.0/db
RACDB1 -n rac1
RACDB2 -n rac2
-i RACDB1 -s +ASM1
77
Buch Partnerkatalog.indb 77
24.10.2007 9:51:31 Uhr
Produktlinie Database – Aus der Praxis – Schwerpunkt 11g
srvctl modify instance -d RACDB -i RACDB2 -s +ASM2
srvctl start database -d RACDB
Fazit
Nun ist es geschafft! Die RAC-Datenbank läuft und
kann getestet werden. Sicherlich ist der Aufruf des
DB-Konfigurationsassistenten einfacher – das Skripten
des DB-Aufbaus hat jedoch den Vorteil, dass Abhängigkeiten zwischen den Komponenten und das RACZusammenspiel besser verstanden werden.
Natürlich sind die Möglichkeiten damit noch lange
nicht ausgeschöpft: srvctl bietet beispielsweise einige
Kommandos zur Konfiguration von Services an, die
zur Aufteilung der Anwendungen auf die einzelnen
RAC-Knoten genutzt werden können. Auch die Konfiguration des Enterprise Managers fehlt noch, sie lässt
sich jedoch auch per Skript mit dem emca-Kommando
durchführen.
78
Buch Partnerkatalog.indb 78
24.10.2007 9:51:31 Uhr