Tuning

Statistiken - Voraussetzung für gute Performance.
Statistiken sind Voraussetzungen für den Optimizer, um einen optimalen Ausführungsplan bestimmen zu können. Folgende Statistiken sollten vorhanden sein:

Oracle empfiehlt, die fixed_objects_stats nur einmalig oder nach einer signifikanten Änderung im Workload der Applikation auszuführen.

Explain Plan - sehen, wie Abfragen ausgeführt werden.
Mithilfe der Explain-Plan Funktionalität sehen sie den Ausführungsplan, den Oracle aktuell wählen würde. Voraussetzung für die Anzeige der Pläne ist eine aktuelle plan_table:

Die einfachste Variante ist die Nutzung von 'explain paln for sql' und der Anzeige mit der Tablefunction DBMS_XPLAN. Es gibt auch zalreiche Tools, die ein Userinterface zu dieser Funktionalität bieten.

Vorsicht ist jedoch geboten, da dieser Ausführungsplan zur Laufzeit des Codes abweichend sein kann. Hier bieten sich mehrere Möglichkeiten. Zu aller erst kann der Ausführungsplan sowie wesentliche Faktoren für die Performance über die 'set autotrace on' Funktion ausgegeben werden:

Um den Asuführungsplan auf Dauer stabil zu halten bieten sich 3 Möglichkeiten:

Oracle Advisors - Tuning leichtgemacht.
Im ersten Schritt wird eine Tuningtask definiert. Hier können folgende Möglichkeiten genutzt werden:

Nach der Definition der Tuning Tasks müssen diese gestartet werden. Tuning Tasks können sehr lange laufen, daher bietet das Package Funktionen zum Stoppen, Löschen und Fortsetzen von Tuningtasks an.

Datenkomprimierung - Reduziert die Kosten und den I/O.
Oracles 'compress' Funktionalität erfährt in der Praxis nicht die Beachtung, die es verdiehnt hätte. Im VLDB Umfeld können Reduktionen des Storage-Bedarf um ca 50% (over-all) erreicht werden. Einzelne große Tabellen können duchauch auf 25% komprimiert werden. Dies stellt eine erhebliche Kostenreduktion dar, reduziert aber auch die Anforderungen an den I/O Bedarf da weniger Blöcke gelesen werden müssen.

Entscheidend für den Erfolg ist, das Automatismen zur regelmäßigen Wartung zur Verfügung stehen. Daten weden in Oracle nur komprimiert gespeichert, wenn:

Bei Update-Zugriffen wird die der geänderte Record nicht mehr komprimiert zurück gegeben. Lob Segemente werden nicht komprimiert. Um dauerhaft von einer Komprimierung zu profitieren muß diese in eine autoamtische Wartungsroutine eingebunden sein.

Mit dem Redefinition-Package liefert Oracle eine Möglichkeit, im laufenden Betrieb Maintenance-Arbeiten an Tabellen und Indizes durchzuführen. Insbesondere die Partitionionierung von Tabellen bei wachsendem Datenhaushalt läßt sich hiermit einfach umsetzen.

Das Prinzip basiert auf der Erstellung einer neuen Tabelle in der neuen Struktur, dem komfortablen Kopieren alle abhängigen Objekte wie Trigger, Grants, Constraint etc und einer Datensyncronisation basierend auf der Snapshotfunktionalität. Die betroffene Tabelle kann ohne Einschränkungen weiterhin online genutzt werden so das Wartungsintervalle entfallen.

Die Schritte im einzelnen, als Beispiel wählen wir die Partitionierung einer Tabelle.

2. Prüfung, ob sich die Tabelle für die Verwendung zur Redefinition eigent und der Start dieser. In diesem Schritt werden alle Daten der Originaltabelle in die neue Tabelle übertragen und es wird ein Snapshot Log auf der Originaltabelle angelegt.

3. Optional können neue bzw geänderte Indexe , Trigger oder Constraints angelegt werden. In unserem Fall soll der Primary Key partitioniert werden und muß daher eine neue Spalte bekommen. Anschließend müssen diese Objekte für den folgenden Schritt der Prozedur zur automatischen Erstellung der abhängigen Objekte bekannt gemacht werden. Diese erkennt somit, dass Objekte manuell erstellt wurden und versucht nicht, sie zu kopieren.

4. Die restlichen Objekte werden kopiert. Hierbei gibt es einige zu berücksichtigende Optionen.

5. Zum Abschluss wird die neue Tabelle per dbms_stats analysiert und der Redefinitionsprozess wird abgeschlossen. Dabei wird Oracle die in der Zwischenzeit angelaufenen DML's über den Snapshot Log mit der neuen Tabelle syncronisieren und die Tabelle sowie die erstellten abhängigen Objekte umbenennen. Damit ist die Tabelle in die neue Form überführt und die alte Tabelle kann per 'dop' gelöscht werden. Vorsicht: vor dem 'dop' sollte man zur Sicherheit das Ergebnis verifizieren!

Mit Datapump bietet Oracle ab Version 10 eine Weiterentwicklung und Ersatz der export/import Funktionalität an. Dabei bietet Datapump gegenüber dem alten export/import Tool erhebliche Vorteile. Die wichtigsten Features sind:

Export und Imports über Datapump können parallelisiert werden
Export und Imports können unterbrochen und wieder aufgenommen werden
Imports sind über Datenbanklinks möglich, ein Schreiben in das Filesystem entfällt somit vollständig.
Export und Import über Datapump können über externe tools (expdp udn impdp), aber auch über eine PL/SQL API gesteuert werden.

Das Schreiben der Export-Files erfolgt in ein Oracle-Directory auf dem Server. Dies ist bei RAC-Systemen zu beachten. Somit können die Export (Dump) Files nicht mehr über eine SQLNET-Verbindung von einem externen Server gelesen bzw geschrieben werden. Dies ist der parallelen Verarbeitung und dem deutlichen Zugewinn im Durchsatz geschuldet, kann aber in bestimten Umgebungen problematisch sein.

Master Table
Zentrale Einheit jeder Data Pump Operation ist die Master Tabelle. Sie wird im Schema des Benutzers erstellt, der den Data Pump Job aufruft. Sie enthält alle Informationen über den Job:

aktueller Status jedes Objekts,
die benutzerspezifischen Konfigurationsparameter
den Status der Worker Prozesse
Restart-Informationen

Die Master Tabelle wird während des Exports und Imports erstellt und ist mit untem stehendenfolgendem SQL zu finden.

SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name master_table_name
FROM
dba_objects o,
dba_datapump_jobs j
WHERE
o.owner=j.owner_name AND
o.object_name=j.job_name ;

Anhand der Master Tabelle kann der aktuelle Status beim Import/Export schnell verifiziert werden, inbesondere Informationen zum meist zeitintensiven Datenexport/Import sind so schnell verfügbar.

select * from
master_table_name
where
job_name = 'MY_EXPORT_JOB' and
object_type = 'TABLE_DATA'

Client Prozess
Dieser ruft die Data Pump API auf. Die neuen Clients expdp und impdp besitzen neben der bereits in exp und imp enthaltenen Funktionalität zusätzlich eine Reihe von weiteren Features.

Server Prozess
Dies ist ein Dedicated Server Prozess der gestartet wird sobald sich ein Client sich mit der Datenbank verbindet. Nach einem Aufruf von DBMS_DATAPUMP.OPEN startet der Server Prozess den eigentlichen Job anzustoßen und generiert die Master Table.

Master Control Prozess
Pro Job existiert immer nur ein MCP (Master Control Prozess). Dieser kontrolliert die Abarbeitung und Verteilung eines Data Pump Jobs. Der MCP ist unter anderem für folgedes zuständig:

File-Management
die Pflege der Dumpfile Liste
ist für die Einträge in der Master Table zuständig (Job-Status, Job-Beschreibung, Restart- und Dumpfile-Information)

Worker Prozess
Durch Aufruf des Kommandos START_JOB initiiert der MCP parallel Worker Prozesse . Die Parallelität wird durch

DBMS_DATAPUMP.SET_PARALLEL( handle => h, degree => Anzahl_Paralleler_Prozesse );

Hier betrachten wir nur die Steuerung des Datapumps über die PL/SQL API. Somit ist man nicht auf das Vorhandensein der Clients Tools angewiesen und kann eigene PL/SQL Blöcke mit zusätzlichen Funktionalitäten entwicken.

Data Pump über DB Links
Mit Hilfe der Datapump-Api kann über einen DB Link ganze Schemas oder auch nur einzelne Objekte kopiert werden. Wird ein loopback link verwendet kann man so ein Schema in einer DB elegant duplizieren.

create database link my_db_link ...

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
dump_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
scn_number number;

BEGIN
dump_handle := DBMS_DATAPUMP.open(
operation => 'IMPORT',
job_mode => 'SCHEMA',
remote_link => 'my_db_link',
job_name => 'MY_PUMP_JOB',
version => 'LATEST');

dbms_output.put_line(dump_handle);

Flashback-SCN
Eine sehr hilfreiche Option ist die Möglickeit Datapump mit einer bestimmten SCN zu betreiben. Insbesondere in Streaming-Umgebungen oder zum Recovery einzelner Tabellen nach Datenverlussten ist dies ein nützliches Feature. Wichtig ist aber, das es auf der Source-Seite keine DDL Statements seit der verwendeten SCN gegeben hat. Dazu zählt auch 'truncate'!

scn_number := dbms_flashback.get_system_change_number@my_db_link ;
dbms_datapump.set_parameter(h, 'FLASHBACK_SCN', scn_number);
dbms_output.put_line(scn_number);

Objekt-Filter
es sind Objektfilter möglich um den export / import zu filtern und nur die gewünschten Objekte zu bearbeiten.

DBMS_DATAPUMP.metadata_filter(
handle => h,
name => 'SCHEMA_EXPR',
value => '= ''SCHEMA1''');

DBMS_DATAPUMP.metadata_filter(
handle => dump_handle,
name => 'SCHEMA_EXPR',
value => 'in (''SCHEMA1'',''SCHEMA2'')');

dbms_datapump.metadata_filter(handle => dump_handle,
´ name => 'NAME_EXPR',
value => 'IN (''TABLE_NAME'')');

Parallelität
Datapump export und Import können parallel durchgeführt werden. Diese Werte können auch im laufenden Export / Import geändert werden. Zum Beispiel kann während der Online-Zeiten eine Parallelität von 1 oder 2 verwendet werden und in lastschwächeren Zeiten (Nachts / Wochenende) mit einer deutlich höheren Parallelität gefahren werden.

Remap
Hier können währende des Imports bestimmte Werte geändert werden. Dies ist ein deutlicher Vorteil gegenüber der alten export/import Prozeduren. Inbesondere beim Clonen eines Schemas ist diese Einstellung zu setzten:

REMAP_TABLESPACE
REMAP_SCHEMA
REMAP_DATAFILE

select * from
master_table_name
where
job_name = 'MY_EXPORT_JOB' and
object_type = 'TABLE_DATA'

Hierfür muß im ersten Schritt ein Oracle Directory angelegt werden. Wie bereits erwähnt ist bei Systemen mit mehr als einem Knoten dieses Directory mit Bedacht zu wählen. Der Dumpfile selber wird vom Oracle nicht mit einem globalen Read-Privileg (chmod o+r) ausgestattet, der Logfile bekommt ein solches globales Read-Privilege.

create directory MY_DUMP_DIR as '/opt/app/oracle/something_shared'
grant READ, WRITE on directory MY_DUMP_DIR to operation_user;

Anschließend erfolgt der Export über das bereits im obigen Beispiel diskutierte Vorgehen:

DECLARE
dump_handle NUMBER;
BEGIN
dump_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'MY_PUMP_JOB',
version => 'LATEST');

Da diesmal kein DB Link verwendet wird müssen die Dump- und Logfiles explizit angelegt werden.

DBMS_DATAPUMP.add_file(
handle => dump_handle,
filename => 'MY_DUMP_FILE.dmp',
directory => 'MY_DUMP_DIR');

DBMS_DATAPUMP.add_file(
handle => dump_handle,
filename => 'MY_DUMP_FILE.log',
directory => 'MY_DUMP_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

DBMS_DATAPUMP.metadata_filter(
handle => dump_handle,
name => 'SCHEMA_EXPR',
value => '= ''SCHEMA1''');

Da man es gerne schnell hat kann bei größeren Schemas und Tabellen wieder eine parallele Verarbeitung vorgesehen werden

Export / Import der Struktur
Hierdurch können Stukturexporte / Importe erstellt werden. Das Vorgehen entspricht wieder dem aus obigen Beispiel. Diesmal wird ein Datenfilter mit ROWS=0 hinzugefügt.

DECLARE
dump_handle NUMBER;
BEGIN
dump_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'MY_PUMP_JOB',
version => 'LATEST');

DBMS_DATAPUMP.add_file(
handle => dump_handle,
filename => 'MY_DUMP_FILE_STRUCTURE.dmp',
directory => 'MY_DUMP_DIR');

DBMS_DATAPUMP.add_file(
handle => dump_handle,
filename => 'MY_DUMP_FILE_STRUCTURE.log',
directory => 'MY_DUMP_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

DBMS_DATAPUMP.metadata_filter(
handle => dump_handle,
name => 'SCHEMA_EXPR',
value => '= ''SCHEMA1''');

DBMS_DATAPUMP.data_filter(
handle=> dump_handle,
name=> 'INCLUDE_ROWS' ,
value=>0):

DBMS_DATAPUMP.start_job(l_dp_handle);

Bricht ein Export / Import ab und wird beendet kann der Job selber gelöscht sein, man findet aber immer noch Einträge im view dba_datapump_jobs. In diesen Fällen konnte beim Abbruch die Master-Tabelle nicht gelöscht werden. Diese kann problemlos manuell gelöscht werden:

SELECT
'drop table o.owner||'.'||object_name ||';' drop_stmt
FROM dba_objects a, dba_datapump_jobs b
WHERE a.owner=b.owner_name AND a.object_name=b.job_name ;