Oracle 10G and Windows XP - Fachbereich Informatik und

Universität Konstanz
Fachbereich
Informatik und Informationswissenschaft
SS 2007
Hausarbeit
Database Tuning & Administration
Seminar:
Dozent:
Assistant
Vorgelegt von:
Seminar: Database Tuning & Administration
Prof. Dr. Marc H. Scholl
Christian Gruen
Ketevan Karbelashvili & Irina Andrei
1
Inhaltsverzeichnis
1 Einführung............................................................................................................................... 3
1.1 Oracle Datenbank Enterprise Edition (EE) ...................................................................... 3
1.2 Oracle Datenbank Express Edition (XE) ......................................................................... 3
1.3 TPC-H .............................................................................................................................. 4
1.4 CYGWIN ......................................................................................................................... 4
1.5 Zeitmessung ..................................................................................................................... 5
2 DBMS Architektur .................................................................................................................. 5
2.1 Interne Ebene.................................................................................................................... 5
2.1.1 System Global Area (SGA)....................................................................................... 6
2.1.2 Program Global Area (PGA)..................................................................................... 8
2.1.3 Optimierung unter Oracle 10g EE............................................................................. 9
2.1.4 Optimierung unter Oracle 10g XE .......................................................................... 10
2.2 Konzeptuelle Ebene........................................................................................................ 12
2.2.1 Denormalisierung .................................................................................................... 13
2.2.2 Integration von Views in Anfragen......................................................................... 14
2.2.3 Partitionierung von Tabellen................................................................................... 17
2.2.4 Clustering ................................................................................................................ 22
2.2.5 Optimale Indexierung von Tabellen........................................................................ 35
3 Oracle Tuning Pack............................................................................................................... 46
3.1 Oracle Tuning Pack über PL/SQL ................................................................................. 47
3.2 Oracle Tuning Pack über Web Benutzeroberfläche ....................................................... 49
3.3 FAZIT............................................................................................................................. 51
4 FAZIT der Optimierung ........................................................................................................ 51
4.1 Oracle Tuning Tips......................................................................................................... 52
Literaturverzeichnis.................................................................................................................. 53
2
1 Einführung
„Tuning ist die Summe aller Maßnahmen, welche die Optimierung der Antwortzeiten und die
Verbesserung der Skalierbarkeit zum Ziele haben“.
(Dr. Günter Unbescheid)
Heutzutage spielen Datenbanken eine immer wichtigere Rolle in den verschiedensten Bereichen der
Industrie. Um diese Datenbanken und somit die Datenbestände sinnvoll auswerten zu können, werden
die verschiedensten Datenbank-Systeme und -Werkzeuge angeboten.
Um die Leistung der unterschiedlichen Systeme in Bezug auf bestimmte Eigenschaften wie
Anfragezeit und Performanz miteinander vergleichen zu können, müssen die zu vergleichenden DBSysteme standardisierten Tests unterzogen werden, was auch Benchmarking genannt wird. Das
Transaction Performance Processing Council (kurz. TPC) hat es sich zur Aufgabe gemacht
verschiedene Benchmarks zur Verfügung zu stellen, um die Leistungen der unterschiedlichen DBSysteme zu vergleichen. 1
Je nach Verwendungszweck werden die Benchmark-Datenbanken von TCP in 3 verschiedenen Typen
unterteilt:
• TPC-App - simuliert Daten die eines Web-Projektes im 24x7 Modus
• TPC-C - simuliert eine OLTP Datenbank, vorzugsweise im ERP-Bereich
• TPC-H - simuliert eine dispositive Datenbank im Bereich Data Warehouse. 2
Der Benchmark auf den sich diese Arbeit bezieht ist der TPC BenchmarkH (TPC-H).
Der TPC-H Benchmark wurde in dieser Arbeit unter Oracle 10g Express Edition (XE) und Oracle 10g
Enterprise Edition (EE) eingesetzt. Dabei als Betriebssystem wurde Windows XP benutzt.
1.1 Oracle Datenbank Enterprise Edition (EE)
Oracle 10g EE bietet effizientes, zuverlässiges und sicheres Datenmanagement
unternehmenskritischen Anwendungen wie beispielsweise umfangreiche On-Line Transaktions
Prozessing (OLTP) Umgebungen. Die Oracle Datenbank Enterprise Edition beinhaltet Werkzeuge und
Funktionen, um selbst höchste Ansprüche an Verfügbarkeit und Skalierbarkeit problemlos zu erfüllen.
Die Enterprise Edition unterstützt alle Rechnergrößen und hat keine Limitierung der CPU Kapazität.
1.2 Oracle Datenbank Express Edition (XE)
Oracle 10g XE ist die neueste Einstiegsvariante in die Oracle Welt. Die Oracle XE ist vollständig
kostenfrei und kann nach belieben entwickelt, eingesetzt und verteilt werden. Oracle XE mit der
weltweit führenden Datenbanktechnologie kann auf Rechnern aller Größen und ohne Beschränkung
der CPUs eingesetzt werden, ist jedoch beschränkt auf maximal 4 GB Nutzerdaten und die Nutzung
von maximal 1 GB Speicher sowie der Nutzung von lediglich 1 CPUs auf dem Hostserver. 3
1
2
3
[5] Realisierung des TPC-H-Schemas auf einem Oracle-Datenbanksystem
[6] TPC-H Schema erstellen
[7] Datenbank Lizenzen
3
1.3 TPC-H
Nach dem Download der Dateien TPCH unter http://www.tpc.org/tpch/ mussten ”Database Generator“
und ”Query Generator“ erstellt werden. Dafür musste erst das Makefile.suite angepasst werden:
• Makefile wurde erstellt.
• vier Parameter zur Anpassung an die vorhandene Datenbank wurden in Makefile eingestellt:
CC = gcc
DATABASE= SQLSERVER
MACHINE = LINUX
WORKLOAD = TPCH
Kompiliert wurde mit Hilfe des Programs CYGWIN
1.4 CYGWIN
nach dem beide Dateien QGEN.exe und DBGEN.exe erstellt wurden, mussten die Daten erstellt
werden und anschließend in das Datenbank importieren werden, dafür wurde auch mit SYGWIN
gearbeitet.
Cygwin ist eine Emulation der Linux-API. Mit Cygwin lassen sich Programme, die üblicherweise
unter POSIX-Systemen wie GNU/Linux, BSD und Unix laufen, auf Microsoft Windows portieren.
Folgende Befehle wurden unter SYGWIN ausgeführt, um 1 GB Zufallsdaten und 22 Zufallsqueries zu
erstellen:
• run dbgen -v -s 1 (generierte 1 GB an Zufallsdaten)
• run qgen -N -d 1 > 1_gen.sql (generierte 22 Zufallsqueries)
Als nächstes mussten die Erstellten Daten in die Datenbank importiert werden:
• Erst mussten die Tabellen erstellt wurden:
Type "sqlplus Benutzer/Password"
und
@ <full path>/dss.ddl
•
Als nächstes mussten die Tabellen mit den Daten gefüllt werden. Dafür musste für jede
Tabelle ein *.ctl File erstellt werden. Z.B. für CUSTOMER Tabelle:
load data
INFILE 'customer.tbl'
INTO TABLE CUSTOMER
FIELDS TERMINATED BY '|'
(C_CUSTKEY, C_NAME, C_ADDRESS, C_NATIONKEY, C_PHONE,
C_ACCTBAL, C_MKTSEGMENT, C_COMMENT)
•
Anschließend wurden die Daten mit dem Oracle SQL*Loader in die Datenbank eingespielt.
sqlldr Benutzer/Password control = customer.ctl
4
1.5 Zeitmessung
Für die Zeitmessung der Aufgaben wurden sie mehrmals ausgeführt, und die Ergebnisse in einer Excel
Datei gespeichert. Die Mittelwerten wurden weiter betrachten um Vergleiche zu machen und
Prozentsätze zu berechnen.
2 DBMS Architektur
Ein DBMS ist in drei Ebenen unterteilt:
• die interne Ebene (physische Sicht)
• die konzeptuelle Ebene (logische Sicht)
• die externe Ebene (benutzerspezifische Sicht) 4
2.1 Interne Ebene
Die interne Ebene kümmert sich um die physische Abspeicherung der Daten. Weiter ist die
Verwaltung des Pufferspeichers auf dieser Ebene angesiedelt.
4
[8] Datenbanksystem
5
Eine wichtige Aufgabe der internen Ebene ist die Sicherung der Datenkonsistenz zwischen
Datenspeicher und Memory (Synchronisation). Sollten sich Datenobjekte ändern, so verwaltet die
interne Ebene, wie die Speicherseiten verändert werden müssen. Ausserdem hat die interne Ebene die
Aufgabe, die Indizes zu verwalten. 5
2.1.1 System Global Area (SGA)
Zwei Faktoren bestimmen die Qualität eines Puffers. Zum einen die logischen Zugriffe (Logical
Read), unter denen man alle Zugriffe auf einen Block versteht, und zum anderen die physikalischen
Zugriffe, die alle Zugriffe auf einen Block auf der Festplatte ausdrücken (Physical Read).
Aus diesen Faktoren ergibt sich die Qualität des Puffers über folgende Formel:
Qualität (Hitratio) = Anzahl der Treffer/Anzahl der Abfragen × 100%
Die Datenbankpuffer der Oracle-Datenbank befinden sich in der System Global Area (SGA). SGA
fungiert als Hauptkommunikationsplattform für den schnellen Datenaustausch zwischen OracleProzessen. 6
Der globale Systembereich (SGA) von Oracle ist der Speicherbereich, der
beim Systemstart von Oracle zugeordnet wird und Speicherstrukturen zum
Speichern von Daten und Steuerungsinformationen enthält. 7
Die wichtigsten Puffer in der SGA der Oracle-Datenbank sind zur Übersicht in der folgenden Tabelle
dargestellt: 8
5
6
7
8
[8] Datenbanksystem
[9] Oracle-Datenbankadministration für SAP
[10] Oracle Konfigurationsoptionen
[9] Oracle-Datenbankadministration für SAP
6
Die für die folgenden Parameter definierten Werte wirken sich am stärksten auf die SGA-Größe aus:
•
•
•
LARGE_POOL_SIZE
SHARED_POOL_SIZE
DB_CACHE_SIZE
•
LOG_BUFFER
9
Man
kann
wegen
Performance
Engpässen
oder
bestimmten
Oracle
Fehlern
(z.B. ORA-04135) die Größe dieser Speicherbereiche dynamisch anpassen. Allerdings die Maximale
Grösse der SGA (SGA_MAX_SIZE) lässt sich nicht dynamisch ändern, sondern erfordert ein Reboot der
Datenbank. 10
2.1.1.1 Database Buffer Cache
„The buffer cache holds copies of data blocks so as they can be accessed
quicker by oracle than by reading them off disk.“11
Der Speicher wird über einen LRU-Algorithmus (Least Recently Used) verwaltet. Dadurch werden die
Blöcke, die am häufigsten gebraucht werden, immer im Speicher gehalten.
Der Database Buffer Cache besteht aus den Sub-Caches wie z.B:
• DB_KEEP_CACHE_SIZE ist die Größe des Keep Buffer Cache, welcher Blöcke zum
Wiederverwenden speichert.
• DB_BLOCK_SIZE bestimmt die Primary Block Size. 12
Default Cache Parameter Einstellungen unter ORACLE 10g EE nach Installierung.
Die Parameter aus vorhergehender Tabelle können dynamisch geändert werden, das heißt, ein Neustart
der Instanz ist nicht erforderlich.
9
10
11
12
[10] Oracle Konfigurationsoptionen
[11] Oracle-Server Bestandteile
[12] http://www.adp-gmbh.ch/ora/concepts/cache.html
[11] Oracle-Server Bestandteile
7
Shared Pool
Der Shared Pool speichert die zuletzt verwendeten SQL Statements und Datendefinitionen.
SHARED_POOL_SIZE definiert die Größe des Buffers für shared SQL und PL/SQL (Default 16MB bei
32bit bzw. 64MB bei 64 bit).
Java Pool
Der Java Pool ist optional und wird für Java verwendet. JAVA_POOL_SIZE definiert die Größe des
Java Pools (Default 24MB).
Large Pool
Der Large Pool ist ein optionaler Speicherbereich im SGA. Er wird für Session Memory verwendet bei
Shared Server. Weitere Anwendungen sind I/O, Backup/Restore (RMAN), ... Der Large Pool
verwendet kein LRU-Algorithmus. LARGE_POOL_SIZE definiert die Größe des Large Pools (Default 0).
LARGE_POOL_SIZE ist nicht dynamisch.
Streams Pool
Der Streams Pool ist neu in Oracle 10g und wird von Buffered Queues benutzt. Buffered Queues
wurden in Zusammenhang mit Oracle Streams eingeführt. Die Größe des Streams Pool wird mit dem
Parameter STREAMS_POOL_SIZE festgelegt. Der Streams Pool ist Bestandteil der SGA, er muss also in
den Wert für SGA_MAX_SIZE eingerechnet werden.
Redo Log Buffer
Der Redo Log Buffer ist ein Circular Buffer und speichert Änderungen von Datenblöcken. LOG_BUFFER
definiert die Größe des Redo Log Buffer in Bytes.
2.1.2 Program Global Area (PGA)
Der Listener-Prozess registriert Verbindungswünsche von User-Prozessen. Server Prozesse werden
dann auf Verlangen von User-Prozessen gestartet und führen stellvertretend dessen SQL-Statements
aus. Wenn ein Server-Prozess startet, wird ihm Arbeitsspeicher zugewiesen. Diesen nennt man
Program Global Area (PGA). Nach dem Beenden des User-Prozesses wird der PGA wieder
freigegeben.
Die PGA eines Prozesses enthält nur die Daten und Informationen, die von dieser benötigt oder
bearbeitet werden. Besonders bei speicherintensiven Sortier- und Hash-Operationen kommt der Größe
der PGA eine entscheidende Bedeutung zu.
Die Konfiguration der PGA hat sich allerdings seit dem Release Oracle 9i deutlich vereinfacht.
Ähnlich wie beim ASMM für die SGA passt Oracle die PGA-Bereiche aller Serverprozesse
automatisch an.
Der PGA besteht aus diesen Komponenten:
• Private SQL Area
• Persistent Area
8
•
•
•
Run-Time Area
Session Memory
SQL Work Areas 13
2.1.3 Optimierung unter Oracle 10g EE
Getestet wurde Datenbank 10g EE mit Primär-, Fremdschlüsseln mit Standart SGA, PGA
Einstellungen gegen Datenbank mit Primär-, Fremdschlüsseln und
• SGA=700
•
PGA=397
Gegen Standart eingestellten Werten nach der Installation
• SGA=432
•
PGA=143
Diese Abbildungen zeigen, dass den einzelnen Bestandteilen der SGA die Größen automatisch
zugewiesen werden, so braucht der Datenbankadministrator nicht ihnen die Werte manuell
zuzuweisen.
13
[11] Oracle-Server Bestandteile
9
Die Tests zeigten, dass obwohl die Laufzeit fast allen Queries nach der Optimierung mit SGA und
PGA sich verbessert hat, war diese Verbesserung nicht sehr gut und daher könnte man auch weiter mit
den alten Größen die durch das System vergeben wurden arbeiten.
2.1.4 Optimierung unter Oracle 10g XE
2.1.4.1 Buffer Cache Sub-Caches
Im unterschied zu Oracle 10g EE wurde es bei Oracle 10g XE auch versucht die Größen den einzelnen
Database Buffer Cache Sub-Caches manuell zu vergeben.
Default Cache Parameter Einstellungen unter ORACLE 10g XE nach Installierung
Getestet wurde
• Datenbank mit Primär-, Fremdschlüsseln und standard KEEP_CACHE_SIZE Einstellungen gegen
Datenbank mit Primär-, Fremdschlüsseln und KEEP_CACHE_SIZE =25M.
alter system set DB_KEEP_CACHE_SIZE = 25M;
Die folgende Abbildung zeigt, dass die Ergebnisse der
Ergebnisse der Standart DB_KEEP_CACHE_SIZE waren.
10
DB_KEEP_CACHE_SIZE = 25M
schlechter als die
2.1.4.1.1 FAZIT
Die Tests zeigten, dass bei dem manuellen Eingriff in die einzelnen Größen des Database Buffer
Cache man die Größen vorsichtig vergeben sollte und sich lieber auf die Standart eingestellten Größen
durch das System verlasen sollte.
2.1.4.2 Optimierung mit SGA und PGA unter Oracle 10g XE
Die Ergebnissen der Optimierung mit SGA und PGA unter Oracle 10g EE, waren den Ergebnissen der
Optimierung unter Oracle 10g XE ähnlich.
Default Einstellungen von SGA unter ORACLE 10g XE
Default Einstellung PGA von Oracle 10g XE
Getestet wurde Datenbank mit Primär-, Fremdschlüsseln mit Standart SGA, PGA Einstellungen gegen
Datenbank mit Primär-, Fremdschlüsseln und
• SGA = 512
•
PGA = 124
11
Die Graphische Darstellung zeigt, dass obwohl sich die Laufzeit der Query 5 nach der Optimierung
sehr verbessert hat, ist das Gesamtbild der Optimierung nicht sehr gut.
2.1.4.2.1 Fazit der Optimierung mit SGA und PGA
Stellt der Datenbankadministrator bei der Installation der gesamten Datenbank diese zwei Parameter
für die Speichergröße nicht ein so wird von der System die optimale Größe automatisch eingestellt, die
man auch beibehalten kann es sei man hat Schwierigkeiten mit Performance oder bekommt bestimmte
Oracle Fehler, dann können die Größen geändert werden.
Die Tests haben gezeigt, dass nachdem die SGA und PGA erhöht wurden, hat die Laufzeit der Queries
sich verbessert, aber weil es keine sehr gute Verbesserung war, könnte man auch weiter mit den alten
Größen arbeiten und versuchen die Queries mit den anderen Methoden zu Optimieren.
Schaut man aber sich das Gesamtbild der Optimierung unter oracle 10g EE an: Primär- und
Fremdschlüssel, PGA und SGA so sieht man, dass die Durchschnittlaufzeit der Queries im Vergleich
zum Anfangszustand sich merklich verbessert hat.
2.2 Konzeptuelle Ebene
Die konzeptuelle Ebene befasst sich mit der Abstraktion der Daten. D.h. die Darstellung der Daten, die
im physischen Schema vorliegen, werden so angepasst, wie sie der Benutzer haben möchte. 14
14
[8] Datenbanksystem
12
2.2.1 Denormalisierung
Die Denormalisierung ist die bewusste Rücknahme einer Normalisierung zum Zweck der
Verbesserung des Laufzeitverhaltens der Anfragen.
Die Denormalisierung reduziert die Tabellenzahl und verbessert die Antwortzeiten. Mindestens
theoretisch. Das ist der wichtigste Vorteil.
Die Nachteile sind: Datenredundanz und mehr Speicherplatz nötig, Aufwand um die redundanten
Daten konsistent zu halten, Gefahr von Datenanomalien. Die Synchronisierung der Updates
(Aktualisierungen) ist eine konstante Herausforderung für die Integrität der Daten in einer
denormalisierten Datenbank.
Für unsere Datenbank haben wir zuerst die Tabellen NATION und REGION unter Oracle 10g XE in
einer einzigen Tabelle transformiert:
CREATE TABLE nation_region
AS SELECT n_nationkey, n_name, n_comment, r_name AS n_r_name
FROM nation, region
WHERE n_regionkey=r_regionkey
Die Ergebnisse waren aber nicht überzeugend, wahrscheinlich weil die zwei Tabellen sehr klein sind.
Deswegen wurde auch die Tabelle supplier dazu addiert:
Die Denormalisierung der TPCH Datenbank
CREATE TABLE supp_nat_reg
AS SELECT s_suppkey, s_name, s_address, s_nationkey,
s_phone, s_acctbal, s_comment, n_name AS s_n_name,
n_r_name AS s_r_name
FROM supplier, nation_region
WHERE s_nationkey=n_nationkey
ALTER TABLE supp_nat_reg ADD CONSTRAINT pk_snr PRIMARY KEY (s_suppkey)
13
Nach dem Ausführung der geänderten Queries, waren die Ergebnisse überraschend. Die
Ausführungszeit bei Query 5 z.B. hat sich um 6% verschlechtert. Dasselbe ist auch bei Oracle EE
passiert:
Denormalisierung
04:19.2
03:36.0
Zeit
02:52.8
02:09.6
01:26.4
00:43.2
00:00.0
Q2
Q5
Q9
Q11
Q20
Querie s
Normal
Denormalisiert
Die Verschlechterung der Ausführungszeiten bei Oracle XE und EE
Insgesamt sind die Zeiten um 14%, beziehungsweise um 8,6% höher geworden. Um eine Erklärung zu
finden, wurde der Ausführungsplan von Q5 angeschaut.
Der Query Optimizer hat bei der Denormalisierten Datenbank keine Primärschlüssel benutzt und hat
die ganze neue Tabelle supp_nat_reg zugegriffen.
2.2.1.1 FAZIT
Es lohnt sich nicht die Datenbank zu denormalisieren. Obwohl das teoretisch bessere Zeiten bringen
sollte, bei Oracle gilt es nicht.
2.2.2 Integration von Views in Anfragen
Eine View (deutsch Sicht) ist eine logische Relation (auch virtuelle Relation oder virtuelle Tabelle) in
einem Datenbanksystem. Diese logische Relation wird über eine im Datenbankmanagementsystem
(DBMS) gespeicherte Abfrage definiert. Der Datenbankbenutzer kann eine View wie eine normale
Tabelle abfragen.
Neben den herkömmlichen Views gibt es noch so genannte Materialized Views (MS). Diese sind
Views, die physikalisch gespeichert werden, um Zugriffe zu beschleunigen.
14
Im Unterschied zu Klassischen Views werden Materialized Views nicht explizit in der SQL-Query
angesprochen. Eine materialisierte View ist eine Replik von Daten, die von einer Abfrage abgeholt
werden. Benutzerabfragen können zu einer materialisierten View umgeleitet werden, um Große
Tabellen während der Ausführung zu vermeiden. 15
Materialized Views ermöglichen die Ergebnismenge einer View als eigenständige Tabelle
abzuspeichern. Dies bringt besonders bei verteilten Datenbanken einen erheblichen
Performancegewinn, da Abfragen lokal durchgeführt werden können und hierdurch das Netzwerk bei
der Abfrage dieser Views nicht belastet wird.
Der Begriff Materialized View wurde mit der Version Oracle 8i eingeführt und ersetzt den bis dahin
verwendeten Begriff SNAPSHOT.
Der Aufruf der Refresh-Prozedur ermöglicht die manuelle Aktualisierung einer Materialized View
(MV).
•
EXEC DBMS_MVIEW.REFRESH (list=>‘Angestellte‘);
Syntax:
CREATE MATERIALIZED VIEW [schema.]mview
ON PREBUILT TABLE [{WITH | WITHOUT} REDUCED PRECISION]
[USING INDEX storage_options]
[{REFRESH [refresh_options] | NEVER REFRESH]
[FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE]
AS subbquery; 16
Es wurde versucht die Laufzeit der Queries durch Materializes views zu beschleunigen, dafür wurden
die Queries: 7, 8, 9, 11, 20 und 22 benutzt.
Z.B.: Query 8:
Select o_year, sum (case when nation = 'BRAZIL' then volume else 0 end) / sum(volume) as mkt_share
from ( select extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) as volume, n2.n_name
as nation
from part, supplier, lineitem, orders, customer, nation n1, nation n2, region
where p_partkey = l_partkey and
s_suppkey = l_suppkey and
l_orderkey = o_orderkey and
o_custkey = custkey and
c_nationkey = n1.n_nationkey and
n1.n_regionkey = r_regionkey and
r_name = 'AMERICA‘ and
s_nationkey = n2.n_nationkey and
o_orderdate between date '1995-01-01' and
date '1996-12-31‘ and
p_type = 'ECONOMY ANODIZED STEEL')
group by o_year
Order by o_year;
Create Materialized View
create materialized view mv_q8 as
select extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) as volume, n2.n_name as nation
from part, supplier, lineitem, orders, customer, nation n1, nation n2, region
where p_partkey = l_partkey and
s_suppkey = l_suppkey and
l_orderkey = o_orderkey and
o_custkey = c_custkey and
c_nationkey = n1.n_nationkey and
n1.n_regionkey = r_regionkey and
r_name = 'AMERICA‘ and
s_nationkey = n2.n_nationkey and
o_orderdate between date '1995-01-01' and
15
16
[13] http://de.wikipedia.org/wiki/Sicht_%28Datenbank%29
[14] Materialized Views
15
date '1996-12-31' and
p_type = 'ECONOMY ANODIZED STEEL'
Umschreiben der Query 8
select o_year, sum(case when nation = 'BRAZIL' then volume else 0 end) / sum(volume) as mkt_share
from mv_q8
group by o_year
order by o_year;
Tests unter Oracle 10g EE
Tests unter Oracle 10g XE
2.2.2.1 FAZIT
Die durchgeführten Tests haben gezeigt, dass durch das Benutzen der Materialized Views die Laufzeit
der Queires sich erheblich verbessert hat, und sowohl unter Oracle 10g EE als auch unter Oracle 10g
XE ist fast die Gleiche Verbesserung zu sehen. Die Durchschnittslaufzeit der Queries lag nach der
Optimierung unter Oracle 10g EE bei 0,021 Sekunden (0,47 Sekunden vor der Optimierung) und unter
Oracle 10g XE bei 0,6 Sekunden (17 Sekunden vor der Optimierung).
Es ist sehr einfach und empfehlenswert mit Materialized Views zu arbeiten, falls man die Laufzeit der
Queries beschleunigen möchte.
16
2.2.3 Partitionierung von Tabellen
In den letzten Jahren wurden Datenbanken ebenso wie Tabellen immer größer. Heutzutage müssen
häufig Tabellen mit einem Platzbedarf von bis zu mehreren Terrabyte implementiert und verwaltet
werden. Die Verwaltung sehr großer Tabellen und Indizes kann sehr zeitaufwendig sein. Zudem ist oft
eine mangelnde Performance bei Datenzugriffen zu bemerken. Um dem entgegen zu wirken, führte
Oracle bereits in Version 8 die horizontale Partitionierung von Tabellen. Jede einzelne Partition kann
eigene physische Attribute (Tablespace, Storage-Parameter) haben und einzeln angesprochen werden.
Aus Sicht des Benutzers handelt es sich dabei weiterhin um eine einzige Tabelle.
Aufgrund eines „Partition Keys“ werden die eingefügten Daten automatisch in der jeweiligen Partition
gespeichert.
Einige Vorteile von Partitionierung sind:
• die einzelnen Partitionen können unabhängig voneinander verwaltet werden (z.B. Partitionen
löschen, reorganisieren und sichern)
• Performanceverbesserungen (befinden sich die in einer Abfrage benötigten Daten in einer
Partition, so muss nur auf diese Partition zugegriffen werden.)
• bei geeigneter Partitionierung können auf diese Art große Datenmengen schnell gelöscht
werden
Oracle stellt verschiedene Varianten der Partitionierung zur Verfügung.
Die RANGE-Partitionierung
Hierbei handelt es sich um eine Bereichspartitionierung. Ein typisches Beispiel ist die Partitionierung
einer Tabelle nach Datum, um alle Datensätze aus dem gleichen Quartal in einer eigenen Partition zu
speichern.
Die HASH-Partitionierung
Bei der Hash-Partitionierung haben die Datensätze innerhalb der Partitionen keine besondere
Bedeutung. Hier entscheidet das System mit Hilfe einer Hash-Funktion, in welche Partition ein
Datensatz abgelegt wird.
Beispiel: (Part Table wäre Möglich (Q16, 2))
CREATE TABLE part(P_PARTKEY
P_NAME
P_MFGR
P_BRAND
P_TYPE
P_SIZE
P_CONTAINER
P_RETAILPRICE
P_COMMENT
PARTITION BY HASH (p_size)
PARTITIONS 3
STORE IN (data1, data2, data3);
INTEGER NOT NULL,
VARCHAR(55) NOT NULL,
CHAR(25) NOT NULL,
CHAR(10) NOT NULL,
VARCHAR(25) NOT NULL,
INTEGER NOT NULL,
CHAR(10) NOT NULL,
DECIMAL(15,2) NOT NULL,
VARCHAR(23) NOT NULL)
In diesem Beispiel werden die einzelnen Datensätze vom System auf die drei verschiedenen
Partitionen verteilt. Dabei wird eine Hash-Funktion angewendet, die aufgrund der Werte in der Spalte
„p_size“ die Partition für die Speicherung bestimmt. Die Namen der Partitionen legt das System fest.
Diese Partitionierungsmethode kann angewendet werden, wenn in einer DSS Umgebung gearbeitet
wird (in der Regel wird die gesamte Tabelle eingelesen) und die Verteilung der Daten nicht bekannt
ist. Sie stellt eine einfache Möglichkeit zur Verfügung, Daten auf mehrere Tablespaces (Dateien) zu
verteilen, die dann mit Hilfe paralleler Zugriffsmechanismen bearbeitet werden können.
17
Im zweiten Teil des Artikels wird dann intensiv auf die Themen List- und Composite Partionierung
eingegangen. 17
Die LIST-Partitionierung
Die LIST-Partitionierung wurde in Oracle 9i speziell für Datenverteilungen eingeführt, die einzelnen
Werten folgen.
Beispiel:
CREATE TABLE region (R_REGIONKEY INTEGER NOT NULL,
R_NAME
CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152))
TABLESPACE data1
PARTITION BY LIST (r_name)
(PARTITION region_Europe VALUES ('EUROPE') TABLESPACE data2,
PARTITION region_ASIA VALUES ('AISA') TABLESPACE data3,
PARTITION region_AMERICA VALUES ('AMERICA') TABLESPACE data4,
PARTITION region_default VALUES (DEFAULT) TABLESPACE data5
);
Die Datensätze werden aufgrund des Wertes in der Spalte "r_name " den Partitionen zugeordnet.
Datensätze mit dem "r_name " EUROPE werden zum Beispiel in die Partition "region_Europe"
eingefügt. Entspricht das spezifizierte "r_name " keinem der explizit aufgeführten Werte, so wird der
Datensatz der Partition "region_default" zugewiesen.
COMPOSITE-Partitionierung
Es gibt zudem die Möglichkeit die verschiedenen Partitionierungsmethoden miteinander zu
kombinieren, um so eine Partitionierung auf zwei Ebenen zu erreichen. Dadurch ist es möglich, eine
Partition wiederum in eine bestimmte Anzahl an Subpartitionen aufzuteilen. Das kann sinnvoll sein,
wenn die einzelnen Partitionen einer Tabelle noch sehr groß sind. Man unterscheidet die RANGELIST- und die RANGE-HASH-Partitionierung.
Beispiel für eine RANGE-LIST-Partitionierung:
CREATE TABLE lineitem
( L_ORDERKEY
INTEGER NOT NULL,
L_PARTKEY
INTEGER NOT NULL,
L_SUPPKEY
INTEGER NOT NULL,
L_LINENUMBER
INTEGER NOT NULL,
L_QUANTITY
DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE
DECIMAL(15,2) NOT NULL,
L_DISCOUNT
DECIMAL(15,2) NOT NULL,
L_TAX
DECIMAL(15,2) NOT NULL,
L_RETURNFLAG
CHAR(1) NOT NULL,
L_LINESTATUS
CHAR(1) NOT NULL,
L_SHIPDATE
DATE NOT NULL,
L_COMMITDATE
DATE NOT NULL,
L_RECEIPTDATE
DATE NOT NULL,
L_SHIPINSTRUCT
CHAR(25) NOT NULL,
L_SHIPMODE
CHAR(10) NOT NULL,
L_COMMENT
VARCHAR(44) NOT NULL)
PARTITION BY RANGE (L_RECEIPTDATE)
SUBPARTITION BY LIST (L_SHIPMODE)
SUBPARTITION TEMPLATE
(SUBPARTITION mailship VALUES ('MAIL', 'SHIP') TABLESPACE data2,
SUBPARTITION andere VALUES (DEFAULT) TABLESPACE data3)
(PARTITION Jahr_1994 VALUES LESS THAN (TO_DATE('1995-01-01','YYYY/MM/DD')),
PARTITION Andere_Jahre VALUES LESS THAN (TO_DATE('1994-01-01','YYYY/MM/DD')));
17
[15] Oracle: Einsatz von Partitioning
18
Die Tabelle wird mit vier Partitionen erstellt, die wiederum in jeweils zwei Subpartitionen
aufgesplittet sind. Jede Partition nimmt die Subpartitions-Beschreibungen aus dem angegebenen
SUBPARTITION TEMPLATE. Die generierten Subpartitionsnamen sind jeweils Jahr_1995 und
Andere_Jahre,... Datensätze aus dem Juni 1994 werden der Partition Jahr_1994 zugeordnet. In
Abhängigkeit des Wertes in der Spalte "L_SHIPMODE " werden sie dann einer der beiden
Subpartitionen zugeordnet. Lautet das "L_SHIPMODE " MAIL, so wird der Datensatz in die
Subpartition mailship eingefügt.
Indizes
Bei der Zugriffsoptimierung spielen Indizes eine bedeutende Rolle. Dies gilt sowohl bei nichtpartitionierten Tabellen als auch bei partitionierten Tabellen. Beim Anlegen eines Indexes auf eine
partitionierte Tabelle hat man verschiedene Möglichkeiten: Zum einen kann ein Index über die
gesamte Tabelle erstellt werden und zum anderen kann ein partitionierter Index erstellt werden. Dabei
kann der Index entweder gleichpartitioniert sein oder der Index kann mit einer eigenen
Partitionierungsstrategie erstellt werden. Ist ein Index gleichpartitioniert, so spricht man von einem
lokalen Index, anderenfalls von einem globalen Index. Die lokalen Indizes sind im Zusammenhang
mit der Verbesserung von Performance und Fehlertoleranz von großer Bedeutung.
Beispiel für die Erstellung eines lokalen Indizes:
create tablespace idx1 datafile 'E:\oracle\product\10.2.0\oradata\orcl\idx1.dbf' size 1M autoextend on next 500k;
create tablespace idx2 datafile 'E:\oracle\product\10.2.0\oradata\orcl\idx2.dbf' size 1M autoextend on next 500k;
create tablespace idx3 datafile 'E:\oracle\product\10.2.0\oradata\orcl\idx3.dbf' size 1M autoextend on next 500k;
create tablespace idx4 datafile 'E:\oracle\product\10.2.0\oradata\orcl\idx4.dbf' size 1M autoextend on next 500k;
create tablespace idx5 datafile 'E:\oracle\product\10.2.0\oradata\orcl\idx5.dbf' size 1M autoextend on next 500k;
CREATE INDEX l_lineitem_idx
ON lineitem (L_SHIPDATE, L_ORDERKEY, L_LINENUMBER) LOCAL
(PARTITION p1 TABLESPACE idx1,
PARTITION p2 TABLESPACE idx2,
PARTITION p3 TABLESPACE idx3,
PARTITION p4 TABLESPACE idx4,
PARTITION p5 TABLESPACE idx5
);
Der Index, der auf die Spalte "l_orderkey, L_LINENUMBER" gelegt wird, besitzt die gleichen
Partitionen wie die Tabelle "lineitem" (s.o.). Das bedeutet, dass Einträge aus einer Indexpartition auf
genau eine Tabellenpartition verweisen. Diese Art von Index ist für Anfragen, die einzelne Partitionen
betreffen sinnvoll.
Beispiel für einen nicht-partitionierten, globalen Index
CREATE INDEX l_orderkey_idx
ON lineitem (l_orderkey);
Der so erstellte Index ist nicht partitioniert. Diese Art von Indizes ist für Anfragen, die die gesamte
Tabelle betreffen (und nicht einzelne Partitionen) sinnvoll.
Beispiel für einen partitionierten, globalen Index:
CREATE INDEX l_orderkey_idx
ON lineitem (l_shipdate, l_orderkey)
GLOBAL PARTITION BY RANGE (l_shipdate)
(PARTITION p1
VALUES LESS THAN (TO_DATE('1993-12-31','YYYY/MM/DD'))
19
TABLESPACE p1,
PARTITION p2
VALUES LESS THAN (TO_DATE('1995-03-14','YYYY/MM/DD'))
TABLESPACE p2,
PARTITION p3
VALUES LESS THAN (TO_DATE('1995-10-02','YYYY/MM/DD'))
TABLESPACE p3,
PARTITION p4
VALUES LESS THAN (TO_DATE('1997-01-01','YYYY/MM/DD'))
TABLESPACE p4,
PARTITION p5
VALUES LESS THAN (MAXVALUE)
TABLESPACE p5
);
Voraussetzung für die Partitionierung ist, dass das Partitionierungskriterium ein Prefix ist (die zuerst
indizierte Spalte oder Spaltenkombination). 18
2.2.3.1 Tests unter Tabellen Partitionierung
Getestet wurde die RANGE-Partitionierung mit der Tabelle Lineitem, und die LIST-Partitionierung
mit der Tabelle Part.
Die RANGE-Partitionierung wurde mit und ohne den partitionierten lokalen Index getestet. Ein
partitionierter Index geht Hand in Hand mit partitionierten Tabellen. Tatsächlich wird eine
partitionierte Tabelle normalerweise standardmäßig über partitionierte Index verfügen. Ein PräfixIndex ist als Index definiert, dessen am weitesten links stehende Spalten exakt mit denen des
Partitionsschlüssels übereinstimmen. Im Zusammenhang mit partitionierten Indices ist das Konzept
von Präfix-Indices aus folgenden Gründen wichtig:
Eindeutige Präfix-Indices stellen sicher, dass man nur noch auf eine Indexpartition zugreifen muss, um
an die Daten zu gelangen.
Nicht eindeutige Präfix-Indices stellen immer noch sicher, dass man nur eine Indexpartition benötigt,
wenn man den vollständigen Partitionsschlüssel als Bestandteil der WHERE-Klausel angibt. Die
Ausnahme hierzu ist, dass alle Partitionen durchsucht werden, wenn man nur einen Teil des
Partitionsschlüssels angibt.
Die Partitionierung wurde bei den Tabellen Region und Nation nicht benutzt, weil sie viel zu klein
sind.
Die RANGE-Partitionierung auf der Tabelle „Lineitem“ hat bei fast allen Queires deutliche
Verbesserungen gebracht.
create tablespace p1 datafile 'E:\oracle\product\10.2.0\oradata\orcl\p1.dbf' size 1M autoextend on next 500k
create tablespace p2 datafile 'E:\oracle\product\10.2.0\oradata\orcl\p2.dbf' size 1M autoextend on next 500k
create tablespace p3 datafile 'E:\oracle\product\10.2.0\oradata\orcl\p3.dbf' size 1M autoextend on next 500k
create tablespace p4 datafile 'E:\oracle\product\10.2.0\oradata\orcl\p4.dbf' size 1M autoextend on next 500k
create tablespace p5 datafile 'E:\oracle\product\10.2.0\oradata\orcl\p5.dbf' size 1M autoextend on next 500k
CREATE TABLE LINEITEM ( L_ORDERKEY
L_PARTKEY
L_SUPPKEY
L_LINENUMBER
L_QUANTITY
L_EXTENDEDPRICE
18
INTEGER NOT NULL,
INTEGER NOT NULL,
INTEGER NOT NULL,
INTEGER NOT NULL,
DECIMAL(15,2) NOT NULL,
DECIMAL(15,2) NOT NULL,
[16] Oracle: Einsatz von Partitioning (Teil 2)
20
L_DISCOUNT
DECIMAL(15,2) NOT NULL,
L_TAX
DECIMAL(15,2) NOT NULL,
L_RETURNFLAG
CHAR(1) NOT NULL,
L_LINESTATUS
CHAR(1) NOT NULL,
L_SHIPDATE
DATE NOT NULL,
L_COMMITDATE
DATE NOT NULL,
L_RECEIPTDATE
DATE NOT NULL,
L_SHIPINSTRUCT
CHAR(25) NOT NULL,
L_SHIPMODE
CHAR(10) NOT NULL,
L_COMMENT
VARCHAR(44) NOT NULL)
PARTITION BY RANGE (L_SHIPDATE)
(PARTITION p1
VALUES LESS THAN (TO_DATE('1993-12-31','YYYY/MM/DD'))
TABLESPACE p1,
PARTITION p2
VALUES LESS THAN (TO_DATE('1995-03-14','YYYY/MM/DD'))
TABLESPACE p2,
PARTITION p3
VALUES LESS THAN (TO_DATE('1995-10-02','YYYY/MM/DD'))
TABLESPACE p3,
PARTITION p4
VALUES LESS THAN (TO_DATE('1997-01-01','YYYY/MM/DD'))
TABLESPACE p4,
PARTITION p5
VALUES LESS THAN (MAXVALUE)
TABLESPACE p5
);
In diesem Beispiel wird die Tabelle “ LINEITEM” erstellt, die in fünf Partitionen aufgeteilt ist. In der
ersten Partition werden alle Bestellungen gespeichert, deren Bestelldatum vor dem '1993-12-31' liegt.
In der zweiten Partition werden die Datensätze gespeichert, deren Bestelldatum vor dem '1995-03-14'
liegt und die nicht in der ersten Partition gespeichert sind. Die letzte Partition nimmt alle Datensätze
auf, die keiner der anderen Partitionen zugeordnet werden können, da durch die Verwendung des
Schlüsselwortes MAXVALUE keine Obergrenze definiert ist. Die einzelnen Partitionen werden hier
in unterschiedliche Tablespaces abgelegt. Diese Partitionierungsmethode ist gut geeignet für die
Ablage historischer Daten, da das Löschen alter Daten sowie das Hinzufügen neuer Partitionen einfach
und schnell durchführbar ist.
Insbesondere die Query 10, die sonst ungefähr 6 Minuten gebraucht hat, lief mit partitionierten lokalen
Index nur noch 1,4 Minuten. Die Graphische Darstellungen zeigten, dass die besten Ergebnisse kamen
mit unter partitionierten lokalen Index heraus, die zweit Besten Ergebnisse waren unter der Benutzung
des Primärschlüssels zu sehen.
Nachdem
der
partitionierte
lokale
Index
auf
(L_SHIPDATE,
L_ORDERKEY,
L_LINENUMBER)erstellt wurde, haben die Queries im Durchschnitt nur noch 0,45 Sekunden
gebraucht und ohne den partitionierten lokalen Index aber mit dem Primärschlüssel auf
(L_ORDERKEY, L_LINENUMBER) liefen die Queries im Durchschnitt 0,48 Sekunden.
21
LIST-Partitionierung wurde auf Tabelle Part erstellt und hat auch gute Ergebnisse gezeigt. Z.B. die
Laufzeit der Query 19 hatte sich von 0,42 auf 0,32 verbessert.
create tablespace data1 datafile 'E:\oracle\product\10.2.0\oradata\orcl\data1.dbf' size 1M autoextend on next 500k;
create tablespace data2 datafile 'E:\oracle\product\10.2.0\oradata\orcl\data2.dbf' size 1M autoextend on next 500k;
create tablespace data3 datafile 'E:\oracle\product\10.2.0\oradata\orcl\data3.dbf' size 1M autoextend on next 500k;
create tablespace data4 datafile 'E:\oracle\product\10.2.0\oradata\orcl\data4.dbf' size 1M autoextend on next 500k;
create tablespace data5 datafile 'E:\oracle\product\10.2.0\oradata\orcl\data5.dbf' size 1M autoextend on next 500k;
CREATE TABLE part
(P_PARTKEY
P_NAME
P_MFGR
P_BRAND
P_TYPE
P_SIZE
P_CONTAINER
P_RETAILPRICE
P_COMMENT
INTEGER NOT NULL,
VARCHAR(55) NOT NULL,
CHAR(25) NOT NULL,
CHAR(10) NOT NULL,
VARCHAR(25) NOT NULL,
INTEGER NOT NULL,
CHAR(10) NOT NULL,
DECIMAL(15,2) NOT NULL,
VARCHAR(23) NOT NULL)
TABLESPACE data1
PARTITION BY LIST (p_container)
(PARTITION SM VALUES ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') TABLESPACE data2,
PARTITION MED VALUES ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') TABLESPACE data3,
PARTITION LG VALUES ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') TABLESPACE data4,
PARTITION region_default VALUES (DEFAULT) TABLESPACE data5);
create tablespace idx11 datafile 'E:\oracle\product\10.2.0\oradata\orcl\idx11.dbf' size 1M autoextend on next 500k;
create tablespace idx44 datafile 'E:\oracle\product\10.2.0\oradata\orcl\idx44.dbf' size 1M autoextend on next 500k;
create tablespace idx22 datafile 'E:\oracle\product\10.2.0\oradata\orcl\idx22.dbf' size 1M autoextend on next 500k;
create tablespace idx33 datafile 'E:\oracle\product\10.2.0\oradata\orcl\idx33.dbf' size 1M autoextend on next 500k;
CREATE INDEX p_part_idx
ON part (p_container, p_size, p_brand, p_partkey) LOCAL
(PARTITION SM TABLESPACE idx11,
PARTITION MED TABLESPACE idx22,
PARTITION LG TABLESPACE idx33,
PARTITION region TABLESPACE idx44
);
2.2.3.1.1 FAZIT
Die Tests haben gezeigt, dass Tabellen Partitionierung eine sehr gute Methode ist, um die Laufzeit der
Queries zu beschleunigen, dabei wichtig ist, dass die Tabellen die Partitioniert nicht sehr klein sind,
damit die Laufzeit der Queries auch merklich verbessert werden kann.
Leider ist unter Oracle 10g XE die Funktion für Partitionieren nicht aktiviert (ORA-00439: Funktion
nicht aktiviert: Partitioning), daher Partitionierung konnte nur unter Oracle 10g EE getestet werden.
2.2.4 Clustering
Clustering ist ein Mittel zur Strukturierung der Daten in ein oder mehreren Tabellen, so dass deren
Zeilen physisch nebeneinander liegen. 19
Ein Cluster kann eingesetzt werden, wenn mehrere Tabellen eine Zeile desselben Datentyps und
derselben Größe am selben Speicherort aufnehmen. Dadurch lässt sich der Speicherbedarf reduzieren
und in einigen Fällen der Datenzugriff beschleunigen. Der wesentliche Nachteil in Form von
19
[17] Oracle SQL im Überblick
22
Performanzeinbußen tritt bei Aktionen zu Tage, an denen Aktualisierungs-, Einfüge- und
Löschoperationen beteiligt sind. Der DBA sollte den zu erwartenden Mix von Transaktionsarten für
die zu clusternden Tabellen ermitteln und nur diejenigen von ihnen clustern, die häufig miteinander
verknüpft werden und nicht viele Aktualisierungs-, Einfüge- und Löschoperationen erfahren.
Cluster speichern gemeinsam genutzte Datenwerte in denselben physischen Blöcken (die
Schlüsselwerte des Clusters). Bei häufig miteinander verknüpften Tabellen kann dies den Zugriff
beschleunigen; bei Tabellen auf die häufig getrennt voneinander zugegriffen wird, ist das Verknüpfen
keine Antwort. Ein einzelner Tabellencluster zwingt die Schlüsselwerte für diese Tabelle in eine
einzige Gruppe von Blöcken, wodurch der Zugriff auf diese Tabelle beschleunigt wird. Üblicherweise
kommt bei dieser Art von Einzeltabellenclustern auch eine Hash – Struktur zum Einsatz, die die
Zugriffszeiten noch verbessert. 20
Man unterscheidet:
•
Index Cluster: Der Cluster Key entspricht einem oder mehreren Tabellenattributten.
•
Hash Cluster: Der Cluster Key das Ergebnis der Anwendung einer Hashfunktion auf ein oder
mehrere Tabelenattribute.
Idealfall: Der Cluster Key verteilt die Datensätze so, dass pro Cluster Key ein Block benötigt wird.
Erzeugen von Cluster
Ein Cluster wird durch folgenden Befehl erzeugt:
CREATE CLUSTER clustername (attr1 datentyp1 [,attr2 datentyp2] ... )
[PCTUSED integer] [PCTFREE integer]
[SIZE integer [K|M] ]
[INDEX | [HASH IS attr] HASHKEYS integer]
Die Attribute attr1, attr2,... geben den Clusterschlüssel an. Die Parameter PCTUSED und PCTFREE
steuern den Füllgrad von Datenblöcken. Der Parameter PCTUSED bezeichnet dabei den Anteil
(Angabe in Prozent) eines Datenblocks, der mindestens belegt sein sollte. PCTFREE gibt an, wie viel
Prozent des Datenblocks mindestens frei sein sollten. Die Summe aus PCTUSED und PCTFREE darf
den Wert 100 nie überschreiten. Der Parameter SIZE bestimmt, wie viele Bytes innerhalb eines
Datenblockes für denselben Clusterschlüssel-Wert bzw. Hash-Wert verwendet werden. Der Wert darf
die Größe eines Datenblockes nicht überschreiten. Wird die Angabe von SIZE weggelassen, so
reserviert Oracle einen Datenblock pro Schlüsselwert. Die Angabe von INDEX erzeugt einen IndexCluster, während durch HASHKEYS n ein Hash-Cluster mit n verschiedenen Hash-Werten (Buckets)
erzeugt wird. Der Wert n wird zuvor intern auf die nächst größere Primzahl aufgerundet.
Erzeugen von Indizes
Indizes werden durch folgende Anweisung erzeugt:
CREATE INDEX indexname ON { tabellenname(attr1 [,attr2]...) | CLUSTER clustername }
[PCTFREE integer]
Durch die Angabe eines Tabellennamens und der zu indizierenden Attribute der Tabelle wird
ein mittelbarer Index auf der entsprechenden Relation aufgebaut. Durch Angabe von ASC
bzw. DESC wird das Attribut innerhalb des Index aufsteigend bzw. absteigend sortiert. 21
20
21
[18] Oracle – Datenbanken Administration und Management
[19] Datenbanksysteme
23
2.2.4.1 Index Cluster
Prinzip: Tupel mit gleichem Cluster Key werden gemeinsam in Blocken gespeichert.
•
Cluster Gruppe: Falls nicht alle zu einem Cluster-Key gehörenden Datensätze in einem
Datenblock Platz haben, werden die benötigten Blöcke zu einer Cluster Gruppe verkettet.
•
Je Länger die Cluster Gruppen sind desto geringer ist die Effizienz.
•
Cluster-Gruppen sollen auf nicht mehr als zwei DB-Blöcke verteilt sein.
•
Vorteile: Joins der am Cluster beteiligten Tabellen über Cluster key werden schneller (was bei
uns nicht der Fall war). Geringer Speicherbedarf, da Cluster Key nur einmal gespeichert wird.
•
Nachteil: Bei Abfragen, die nur eine Tabelle betreffen, sind mehr Zugriffe als bei
konventioneller Speicherung notwendig, da die Datensätze der Tabelle auf mehr Blöcke
verteilt sind.
•
Unterstützung: point-Queries und range-Queries.
24
2.2.4.1.1 Tests unter Oracle 10g XE
Getestet wurden zwei Tabellen:
• Part
• Partsupp
Bei den am Anfang durchgeführten Tests wurde es versucht erst ohne die Ermittlung des
Speicherbedarfs für Cluster zu arbeiten. So wurde eine Große von 512 Byte gewählt.
CREATE CLUSTER Cluster_Part_partsupp (p_partkey INTEGER ) size 512;
CREATE TABLE PARTSUPP ( P_PARTKEY
PS_SUPPKEY
PS_AVAILQTY
PS_SUPPLYCOST
PS_COMMENT
CLUSTER Cluster_Part_partsupp (P_PARTKEY);
INTEGER NOT NULL,
INTEGER NOT NULL,
INTEGER NOT NULL,
DECIMAL(15,2) NOT NULL,
VARCHAR(199) NOT NULL )
CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,
P_NAME
VARCHAR(55) NOT NULL,
P_MFGR
CHAR(25) NOT NULL,
P_BRAND
CHAR(10) NOT NULL,
P_TYPE
VARCHAR(25) NOT NULL,
P_SIZE
INTEGER NOT NULL,
P_CONTAINER
CHAR(10) NOT NULL,
P_RETAILPRICE
DECIMAL(15,2) NOT NULL,
P_COMMENT
VARCHAR(23) NOT NULL )
CLUSTER Cluster_Part_partsupp (P_PARTKEY);
CREATE INDEX Part_partsupp_INDEX on CLUSTER Cluster_Part_partsupp;
Query 2 Zeit:
Mit Index Cluster: 3,48 seconds
Ohne Index Cluster: 0,89 seconds
Diese Tests haben gezeigt, dass leider mit dem Einsatz des Index Clusters die Laufzeit der Queries
sich verschlechtert hat. Was natürlich nicht der Fall sein sollte.
2.2.4.2 Hash Cluster
•
Einsatz: Wird verwendet, um einzelne Tabellen aber auch um mehrere Tabellen gemeinsam zu
speichern.
•
Vorteile: Extrem effizienter Zugriff über die Attributkombinationen auf die die Hashfunktion
angewendet wird (was leider bei uns nicht der Fall war). Es ist keine Suche im Index
notwendig.
•
Unterstützung: nur point-Queries.
Getestet wurden die gleichen Tabellen:
25
•
•
Part
Partsupp
CREATE CLUSTER Cluster_Part_partsupp (p_partkey INTEGER ) size 512, hash is p_partkey, HASHKEYS 200001
CREATE TABLE PARTSUPP ( P_PARTKEY INTEGER NOT NULL,
PS_SUPPKEY INTEGER NOT NULL,
…………………………………….
PS_COMMENT VARCHAR(199) NOT NULL )
CLUSTER Cluster_Part_partsupp (P_PARTKEY);
CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,
P_NAME
VARCHAR(55) NOT NULL,
…………………………………………
P_COMMENT VARCHAR(23) NOT NULL )
CLUSTER Cluster_Part_partsupp (P_PARTKEY);
Query 2 Zeit:
Mit Index Cluster: 3,48 seconds
Ohne Index Cluster: 0,89 seconds
Mit Hash Cluster: 3,08 seconds
Diese Tests haben im Vergleich zu Index Cluster etwas besser abgeschnitten aber leider war das
Ergebnis trotzdem nicht gut.
2.2.4.3 Index Cluster mit SIZE = 800b
Weil die Optimierungsergebnisse nicht gut waren, wurde hier mit dem einfachen Weg versucht die
Größe des SIZE Parameters zu bestimmen.
Besonders vorteilhaft ist Index Cluster, wenn bei der 1:n Beziehung die Zahl n eine Konstante ist,
daher geclustert wurden noch einmal die gleichen Tabellen:
• Part
• Partsupp
Mit: select * from partsupp order by ps_partkey
wurde die Beziehung zu ps_partkey Schlüssel bestimmt. n war hier immer eine Konstante = 4.
26
Mit: Select table_name, avg_row_len from dba_tables
wurde die durchschnittliche Länge der PART und PARTSUPP Tabellen bestimmt.
Mit Hilfe der gelieferten Ergebnissen konnte man dann die Größe des SIZE Parameters errechnen:
|part|+n*|partsupp| ≈ 800 Byte
Die Ergebnisse waren besser aber trotzdem leider nicht gut.
2.2.4.4 Hash Cluster gegen Index Cluster
Die durchgeführten Tests unter Oracle 10g XE haben gezeigt, dass durch das Einsetzen der
CLUSTER die Laufzeit der Queries sich veschlechtert hat. Auch das Ermitteln der Größe für SIZE
Parameter hat zu keinen besseren Ergebnissen geführt.
2.2.4.5 Tests unter Oracle 10g EE
Wie die folgende Graphische Darstellung zeigt, dass nachdem die Optimierung mit Hilfe der Tabellen
Partitionierung durchgeführt wurde, hat die Laufzeit der Queries sich merklich verbessert, trotzdem
blieb die Laufzeit der Queries 18 und 21 außerhalb der Verbesserung.
Daher wird im Folgenden die Optimierung mit Hilfe der Index und Hash Cluster durchgeführt und die
Laufzeit während der Optimierung wird nur bei diesen beiden Queries betrachtet.
27
2.2.4.6 Ermittlung des Speicherbedarfs für Cluster
Sowohl bei Indexclustern als auch bei Hash – Clustern ist es wichtig, den Platzbedarf für die Daten
abzuschätzen.
Das im folgenden dargestellter Verfahren zeigt, wie sich der anfängliche Speicherbedarf für eine
geclusterte Menge von Tabellen abschätzen lässt. Dieses Verfahren schätzt ausschließlich den
anfänglichen Speicherbedarf ab.
Zum Abschätzen des Speicherbedarfs von Clustern sind folgende Schritte auszuführen:
Schritt 1: Berechnung der Gesamtgröße des Block-Headers und des für Tabellendaten verfügbaren
Speicherplatzes.
Schritt 2: Berechnung des Platzbedarfs pro Zeile.
Schritt 3: Berechnung der gesamten durchschnittlichen Zeilengröße.
Schritt 4: Berechnung der durchschnittlichen Blockgröße für den Cluster.
Schritt: 1 Berechnung der Gesamtgröße des Block-Headers und des für Tabellendaten
verfügbaren Speicherplatzes.
Die folgende Formel liefert als Ergebnis die Menge des in einem Block zur Verfügung stehenden
Speicherplatzes:
Platz im Block nach Abzug der Header (Platz ohne Header) =
DB_BLOCK_SIZE - (KCBH - UB4 - KTBBH - ((INITRANS – 1) * KTBIT – KDBH)
Wert erhält man aus der Data Dictionary Tabelle V$PARAMETER
SELECT value FROM v$parameter WHERE name = 'db_block_size';
Value = 8192 Byte (8 KByte)
DB_BLOCK_SIZE
KCBH, UB4, KTBBH, KTBIT, KDBH
Werte erhält man aus der Data Dictionary Tabelle V$TYPE_SIZE
select * from v$type_size;
KCBH
UB4
KTBBH
KTBIT
KDBH
BLOCK COMMON HEADER
UNSIGNED BYTE
TRANSACTION FIXED HEADER
TRANSACTION VARIABLE HEADER
DATA HEADER
20 Byte
4 Byte
48 Byte
24 Byte
14 Byte
Wert gibt Auskunft darüber, wie viele Transaktionen simultan einen Datenblock updaten
können. Dieser Wert ist natürlich abhängig von der Anzahl der User, die gleichzeitig auf einen Block
(auf eine Tabelle) zugreifen könnten. Jeder Eintrag für eine Transaktion, die gerade auf einen Block
zugreift belegt 24 Bytes freien Speicher des Blockes. Bei der Erstellung eines Indexes, bzw. einer
Tabelle wird gleichzeitig auch der INITRANS Wert festgelegt. Default 1 für Tabellen, 2 für Indizes.
INITRANS
Mit einer Blockgröße von 8 KByte und dem INITRANS – Wer 1 ergibt sich folgende Rechnung:
8192 – 20 – 4 – 48 – (24 * (1-1)) – 14 = 8106 Byte
Schritt 2: Berechnung des Platzbedarfs pro Zeile
Zur Berechnung dieses Wertes muss man auf folgendes achten:
Platzbedarf muss anhand einer durchschnittlichen Zeile für jede Tabelle im Cluster berechnet werden.
Angenommen uns liegen die beiden geclusterten Tabellen vor:
28
CREATE TABLE ORDERS
(L_ORDERKEY
NUMBER (10, 2) NOT NULL,
O_CUSTKEY
NUMBER (10, 2) NOT NULL,
O_ORDERSTATUS
CHAR(1) NOT NULL,
O_TOTALPRICE
NUMBER (15, 2) NOT NULL,
O_ORDERDATE
DATE NOT NULL,
O_ORDERPRIORITY
CHAR(15) NOT NULL,
O_CLERK
CHAR(15) NOT NULL,
O_SHIPPRIORITY
NUMBER (10, 2) NOT NULL,
O_COMMENT
VARCHAR (79) NOT NULL)
CLUSTER Cluster_ORDERS_LINEITEM (L_ORDERKEY);
CREATE TABLE LINEITEM
(L_ORDERKEY
NUMBER (10, 2) NOT NULL,
L_PARTKEY
NUMBER (10, 2) NOT NULL,
L_SUPPKEY
NUMBER (10, 2) NOT NULL,
L_LINENUMBER
NUMBER (10, 2) NOT NULL,
L_QUANTITY
NUMBER (15, 2) NOT NULL,
L_EXTENDEDPRICE
NUMBER (15, 2) NOT NULL,
L_DISCOUNT
NUMBER (15, 2) NOT NULL,
L_TAX
NUMBER (15, 2) NOT NULL,
L_RETURNFLAG
CHAR(1) NOT NULL,
L_LINESTATUS
CHAR(1) NOT NULL,
L_SHIPDATE
DATE NOT NULL,
L_COMMITDATE
DATE NOT NULL,
L_RECEIPTDATE
DATE NOT NULL,
L_SHIPINSTRUCT
CHAR (25) NOT NULL,
L_SHIPMODE
CHAR (10) NOT NULL,
L_COMMENT
VARCHAR (44) NOT NULL)
CLUSTER Cluster_ORDERS_LINEITEM (L_ORDERKEY);
Clusterschlüssel in jeder Tabelle ist L_ORDERKEY.
Es ergeben sich zwei folgende Rechnungen:
D1 (TABLE LINEITEM) = (a + b + c + d + e + f + g + j + h + I + k + l + m) Byte = 10 + 10 + 10 + 10 + 15
+ 15 + 15 + 15 + 25 + 10 + 44 = 179 Byte
D2 (TABLE ORDERS) = (n + p + o + q + r + s + t + v) Byte = 10 + 10 + 1 + 15 + 15 + 15 + 10 + 79 = 155
Byte
Schritt 3: Berechnung der gesamten durchschnittlichen Zeilengröße.
Man kann den in einer geclusterten Tabelle mindestens für eine Zeile benötigten Platz mit folgender
Gleichung berechnen:
Sn Byte / Zeile = Zeilen – Header + Fn + Vn + Dn
Dabei gilt:
Zeilen – Header: Vier Byte pro Zeile einer geclusterten Tabelle.
Fn: Gesamtanzahl der Längenbytes für Spalten der Tabelle n mit einer Länge von 250 oder weniger
Byte. Für jede derartige Spalte wird 1 Längenbyte veranschlagt.
Vn: Gesamtanzahl der Längenbytes für Spalten der Tabelle n, in denen mehr als 250 Byte gespeichert
werden. Für jede derartige Spalte werden 3 Längenbytes veranschlagt.
Dn: Kombinierter Platz für Daten aller Spalten in Tabelle n (aus Schritt 2).
Demnach ergibt sich beispielsweise die gesamte durchschnittliche Zeilengröße für die geclusterten
Tabellen LINEITEM und ORDERS wie folgt:
S1 = (4 + (1 * 13) + (3 * 0) + 179) = 196 Byte
29
S2 = (4 + (1 * 8) + (3 * 0) + 155) = 167 Byte
Schritt 4: Berechnung der durchschnittlichen Blockgröße für den Cluster.
Zum Berechnen der durchschnittlichen Blockgröße des Clusters muss man zunächst die
durchschnittliche Anzahl von Zeilen (für alle Tabellen) pro Clusterschlüssel ermitteln. Sobald dieser
Wert bekannt ist, kann folgenden Gleichung benutzt werden, um die durchschnittliche Blockgröße für
den Cluster zu berechnen:
Durchschn.Cluster – Blockgröße (Byte) = ((R1 * S1) + (R2 * S2) + … + ( Rn * Sn)) + Schlüssel –
Header + Ck + Sk + 2Rt.
Dabei gilt:
Rn: Die durchschnittliche Anzahl von Zeilen in Tabelle n, die einem Clusterschlüssel zugeordnet sind.
(select * from lineitem order by l_orderkey)
Sn: Die durchschnittliche Zeilengröße in Tabelle n (aus Schritt 3).
Schlüssel – Header: Hat den Wert 19.
Ck: Die Spaltengröße für den Clusterschlüssel.
Sk: Der zum Speichern eines durchschnittlichen Clusterschlüssels benötigte Platz.
Rt: Die Gesamtanzahl der Zeilen, die einem durchschnittlichen Clusterschlüssel (R1 + R2 + … + Rn)
zugeordnet sind. Daraus ergibt sich der für jede Zeile des Blocks im Header des Datenblocks benötigte
Platz.
Als Beispiel wurden wieder die Tabellen LINEITEM und ORDERS genommen. Ein durchschnittlicher
Clusterschlüssel verfügt über 4 Zeilen pro Tabelle LINEITEM und über 1 pro Tabelle ORDERS. Des
weiternen ist der Clusterschlüssel von Datentyp NUMBER (Spaltengröße = 1 Byte) und die Zahl
besteht durchschnittlich aus 6 Ziffern (5 Byte). Mit diesen Werten und den vorangegangenen
Ergebnissen ergibt sich die durchschnittliche Größe für den Clusterschlüssel wie folgt:
Größe = ((3 * 196) + (1 * 167) + 19 + 1 + 5 + (2 * 4)) Byte = 788 Byte
Diese ermittelte Größe kann in der SIZE – Klausel angegeben werden, wenn man die Cluster mit Hilfe
des CREATE CLUSTER – Befehls erstellt. Dieser Wert gibt an, wie viel Platz benötigt wird, um einen
durchschnittlichen Clusterschlüssel und die ihm zugeordneten Zeilen aufzunehmen; Oracle verwendet
diesen Wert, um die Anzahl der Clusterschlüssel zu beschränken, die sich einem gegebenen
Datenblock zuordnen lassen. 22
2.2.4.7 Tests unter Oracle 10g EE mit Index Cluster
Hier wird es versucht Index Cluster unter Tabellen
benutzen:
LINEIETEM, ORDERS
und
NATION, SUPPLIER
zu
CREATE CLUSTER Cluster_ORDERS_LINEITEM (L_ORDERKEY NUMBER (10, 2)) size 788;
Als nächstes werden die beiden geclusterten Tabellen LINEIETEM und ORDERS erstellt und zum Schluss
wird auf dem CLUSTER Cluster_ORDERS_LINEITEM eine INDEX ORDERS_LINEITEM_INDEX erstellt.
CREATE INDEX ORDERS_LINEITEM_INDEX on CLUSTER Cluster_ORDERS_LINEITEM;
________________________
22
[18] Oracle – Datenbanken Administration und Management
30
CREATE CLUSTER Cluster_SUPPLIER_NATION (S_NATIONKEY NUMBER (10, 2)) size 902;
CREATE TABLE SUPPLIER ( S_SUPPKEY
NUMBER (10, 2) NOT NULL,
S_NAME
CHAR(25) NOT NULL,
S_ADDRESS
VARCHAR(40) NOT NULL,
S_NATIONKEY
NUMBER (10, 2) NOT NULL,
S_PHONE
CHAR(15) NOT NULL,
S_ACCTBAL
NUMBER (10, 2) NOT NULL,
S_COMMENT
VARCHAR (101) NOT NULL)
CLUSTER Cluster_SUPPLIER_NATION (S_NATIONKEY);
CREATE TABLE NATION (
S_NATIONKEY
NUMBER (10, 2) NOT NULL,
N_NAME
CHAR(25) NOT NULL,
N_REGIONKEY
NUMBER (10, 2) NOT NULL,
N_COMMENT
VARCHAR(152))
CLUSTER Cluster_SUPPLIER_NATION ( S_NATIONKEY);
CREATE INDEX SUPPLIER_NATION_INDEX on CLUSTER Cluster_SUPPLIER_NATION;
Die Graphische Darstellung zeigt, dass die durchgeführten Tests mit Index Cluster unter Oracle 10g
EE erfolgreich waren. Die beiden Queries haben sich im Vergleich zur Optimierung mit RANGE
Partitionierung erheblich verbessert, insbesondere die Query 21 lief anstatt 2,1 Minuten nur noch 0,59
Sekunden.
2.2.4.8 Tests unter Oracle 10g EE mit Hash Cluster
Wie bei Indexclustern ist auch bei Hash – Clustern wichtig, den Platzbedarf für die Daten
abzuschätzen. Aber im Unterschied zu Index Cluster muss man hier folgendes beachten:
• Für Hash – Cluster besteht das Teilziel darin, die Größe für jeden Hash – Cluster zu ermitteln.
Daher muss man nicht nur die Anzahl von Zeilen pro Clusterschlüsselwert, sondern auch die
Verteilung der Clusterschlüssel über die Hash – Schlüssel im Cluster berücksichtigen.
•
Anders als beim Indexcluster wird der Clusterschlüsselwert mit jeder Zeile gespeichert, die
sich in einem Hash – Cluster befindet.
•
Im Schritt 4 muss man auf die Addition des Platzbedarfs für den Clusterschlüsselwert (Ck)
verzichten. Weil dieser Wert bereits im Schritt 3 berücksichtigt wurde. 23
Hier wird es gezeigt wie SIZE Parameter unter Hash Cluster anhand des Beispiels mit den Tabellen
PARTSUPP und LINEITEM ermittelt werden kann:
23
[18] Oracle – Datenbanken Administration und Management
31
Schritt 2:
D1 (TABLE LINEITEM) = (a + b + c + d + e + f + g + j + h + I + k + l + m) Byte = 10 + 10 + 10 + 10 + 15
+ 15 + 15 + 15 + 25 + 10 + 44 = 179 Byte
D2 (TABLE ORDERS) = (n + p + o + q + r + s + t + v) Byte = 10 + 10 + 1 + 15 + 15 + 15 + 10 + 79 = 155
Byte
Schritt 3:
S1 = (4 + (1 * 13) + (3 * 0) + 179 + 1) = 197 Byte
S2 = (4 + (1 * 8) + (3 * 0) + 155 + 1) = 168 Byte
Schritt 4:
Ein durchschnittlicher Clusterschlüssel verfügt über 4 Zeilen pro Tabelle LINEITEM und über 1
Tabelle ORDERS. Des weiternen ist der Clusterschlüssel von Datentyp NUMBER (Spaltengröße
Byte) und die Zahl besteht durchschnittlich aus 6 Ziffern (5 Byte). Mit diesen Werten und
vorangegangenen Ergebnissen ergibt sich die durchschnittliche Größe für den Clusterschlüssel
folgt:
pro
=1
den
wie
Größe = ((3 * 197) + (1 * 168) + 19 + 5 + (2 * 4)) Byte = 791 Byte
__________________________________________________________________________
CREATE CLUSTER Cluster_ORDERS_LINEITEM (L_ORDERKEY NUMBER (10, 0)) size 791, hash is L_ORDERKEY, HASHKEYS
200001;
Als nächstes warden die beiden geclusterten Tabellen LINEIETEM und ORDERS erstellt.
CREATE TABLE ORDERS ( …)
CLUSTER Cluster_ORDERS_LINEITEM (L_ORDERKEY);
CREATE TABLE LINEITEM (…)
CLUSTER Cluster_ORDERS_LINEITEM (L_ORDERKEY);
____________________________________________________________________
CREATE CLUSTER Cluster_SUPPLIER_NATION (S_NATIONKEY NUMBER (10, 0)) size 905, hash is S_NATIONKEY, HASHKEYS
31;
CREATE TABLE SUPPLIER (…)
CLUSTER Cluster_SUPPLIER_NATION (S_NATIONKEY);
CREATE TABLE NATION (…)
CLUSTER Cluster_SUPPLIER_NATION ( S_NATIONKEY);
Die folgende Graphische Darstellung zeigt, dass im Unterschied zur Oracle 10g XE hat durch den
Einsatz der Cluster unter Oracle 10gEE die Laufzeit der Queires sich verbessert. Wobei die Query 18
war langsamer unter Index Cluster und schneller unter Hash Cluster und Query 21 war genau
umgekehrt.
32
2.2.4.9 Weitere Tests
Als nächstes wurde versucht zu testen, wie gut oder schlecht die Ergebnisse unter Oracle 10g EE sind,
wenn man mit den gleichen Queries arbeitet und die gleichen Optimierungsmaßnahmen durchführt
wie bei Oracle 10g XE im Bezug auf Cluster.
Wie bei Oracle 10g XE auch bei Oracle 10g EE wurden zwei Tabellen:
• Part
• Partsupp
getestet.
•
•
•
•
Mit INDEX CLUSTER wobei SIZE = 512 Byte (alle Queries wurden getestet).
Mit HASH CLUSTER wobei SIZE = 512 Byte (alle Queires wurden getestet).
Mit INDEX CLUSTER wobei SIZE = 800 Byte (nur Queires 2 und 16 wurden getestet).
Zum Schluss wurden Tests auf zwei Queries mit Turning Pack durchgeführt. Die Queires 2
und 16 sind die fast einzigen Queries, die den CLUSTER der PART und PARTSUPP Tabellen
benutzen, daher wurden nur die beiden bei Tuning Pack Test benutzt.
Diese Abbildung zeigt, dass im Unterschied zur Oracle 10g XE, verbessert sich die Laufzeit der
Queries unter Oracle 10g EE merklich, wobei INDEX CLUSTER mit SIZE = 512 etwas besser abgeschnitten
hat als HASH CLUSTER mit SIZE = 512, und etwas schlechter als INDEX CLUSTER mit dem errechneten SIZE
Parameter = 800 was zeigt, dass es bei der Arbeit mit dem Cluster wichtig ist den SIZE Parameter
richtig zu berechnen.
Am Besten waren die Ergebnisse nachdem das Oracle Tuning Pack benutzt wurde, was in der
Graphischen Darstellung von Oben deutlich zu sehen ist.
Als nächstes ist der Ausführungsplan des Oracle Tuning Packs für Query 2 unter dem Einsatz des
INDEX CLUSTER zu sehen.
.
33
Dieser Ausführungsplan der Oracle Tuning Packs gibt die Empfehlungen wie man die Laufzeit der
Query beschleunigen kann.
Hier zeigt der Optimierer die Query Ausführungspläne vor der Benutzung und nach der Benutzung des
Oracle Tuning Packs.
Führt man die Vorschläge durch so verbessert sich die Laufzeit der Query von 0,02 auf 0,01
Sekunden.
Das Ergebnis der Optimierung mit dem Oracle Tuning Pack für Query 16 war noch besser, hier
verbesserte sich die Laufzeit von 0,22 auf 0,15 Sekunden.
2.2.4.10 FAZIT
Die durchgeführten Tests unter Oracle 10g XE und Oracle 10g EE im Bezug auf CLUSTER zeigten, dass
es etwas schwer ist mit den CLUSTER zu arbeiten, weil man sehr darauf achten muss den SIZE
Parameter richtig auszuwählen. Außerdem unter Oracle 10g XE sollte man auf die Arbeit mit den
CLUSTER versuchen zu verzichten, weil die Laufzeit der Queries während den Tests sich sofort
verschlechterte, was aber nicht der Fall unter Oracle 10g EE war, hier haben die gleichen Tests zu den
guten Ergebnissen geführt wenn man die CLUSTER benutzte.
34
Am Besten waren die Ergebnisse nachdem das Oracle Tuning Pack benutzt wurde. Wird aber leider
nur unter Oracle 10g EE angeboten. Näher über Oracle Tuning Pack wird in dem letzten Kapitel
diskutiert.
2.2.5 Optimale Indexierung von Tabellen
In den meisten Fällen ist eine indizierte Suche schneller als ein normaler Tabellendurchlauf dann,
wenn die Tabelle mehr als 500 Zeilen enthält. Oracle unterstützt eine Reihe von Indizes wie B-Tree,
bitmap sowie die Erstellung von Index organisierten und geklusterten Tabellen (Index Cluster oder
Hash Cluster).
Obwohl die Indizes und die Primärschlüssel kein Muss für eine Tabelle sind, ist mindestens ein
Primärschlüssel empfohlen. Die Indizierung muss den Ausführungsplänen der Anfragen entsprechen.
Da die Indizes die Update-Operationen verlangsamen, sind sie nur für die Spalten geeignet, die in
Anfragen und Berichten verwendet werden.
2.2.5.1 B-Tree Indexes - Primärschlüssel
Die Unterschiede zwischen einem Primärschlüssel und einem UNIQUE-Schlüssel in Oracle sind:
• Eine Tabelle kann maximal ein Primärschlüssel, aber kann mehrere Unique-Indizes für
verschiedene Spalten haben
• Ein Primärschlüssel erzwingt eine NOT NULL Constraint auf die entsprechenden Spalten,
aber ein Index nicht. Man kann nur eine Zeile hinzufügen oder ändern, der den Wert NULL
für die Unique-Spalten zugewiesen ist.
Zuerst wurden die Queries ohne Primärschlüssel ausgeführt und danach mit, um zu sehen, ob die
Indexes, die Oracle automatisch bei der Deklaration der Primärschlüssel erstellt, nützlich sind.
Die Ergebnisse sind in der folgenden Abbildung zu sehen:
DB ohne und mit PK
17:16.8
14:24.0
Zeit
11:31.2
08:38.4
05:45.6
02:52.8
Q22
Q21
Q20
Q19
Q18
Q17
Q16
Q15
Q14
Q13
Q12
Q11
Q9
Q10
Q8
Q7
Q6
Q5
Q4
Q3
Q2
Q1
00:00.0
Queries
DB ohne
und Primärschlüssel
mit PK
Verbesserung/Verschlechterung der Ausführungszeiten nach dem Erstellen
von
Prozent Verbesserung PK
17:16.8
100.00%EE +5,29%), aber in vielen Fällen ist es
In allgemeinen war es besser (Oracle XE + 15%, Oracle
14:24.0
schlechter geworden.
80.00%
60.00%
08:38.4
40.00%
Zeit
Query 2 war besonders interessant (+97,8% besser).
Die Ausführungsplan:
Zeit
11:31.2
05:45.6
Series1
20.00%
02:52.8
Queries
-40.00%
Queries
35
Q22
Q21
Q20
Q19
Q17
Q
2
Q18 1
Q15
Q
Q16 19
7
Q14Q 1
Q12Q
1
Q13 5
Q10
Q
Q11 13
11
Q8
Q9Q
9
Q
Q7
Q6 7
Q
Q5
Q4 Q 5
Q3
Q2 Q
Q
Q1 1
3
0.00%
00:00.0
-20.00%
Ausführungsplan der Anfrage Q2 ohne und mit Primärschlüssel
Die Erklärung ist es, statt full table access auf den Tabellen partsupp, nation, region wurden sehr
effizient die erstellten Indexes benützt um die Anfrage zu beschleunigen.
Bei der Anfrage Q10 sind sowohl bei Oracle XE, als auch bei Oracle EE höhere Zeiten aufgetreten.
Wenn man den Ausführungsplan schaut, kann man sehen, dass der Optimizer gar keinen Index benützt
hat, als ob es keine Primärschlüssel gegeben hätte. Wir gehen davon aus, die Zeiten wurden von
anderen Faktoren beeinflusst (z.B. Servicezeiten).
Ausführungspläne für Q10 ohne und mit Primärschlüssel
2.2.5.2 B-Tree Indexes - Einfache Indexes
Für alle Tabellen wurden entsprechend Indexes auf je einer Spalte gesetzt, so wie Oracle Tuning Tool
empfiehlt. Alle diese Indexe wurden in einer anderen Tablespace erstellt, um ihre Manipulation zu
vereinfachen. Die Zeiten wurden gemessen und die Ergebnisse sind abgebildet:
36
CREATE TABLESPACE idx_einfach DATAFILE
'C:\oracle\product\10.2.0\oradata\tpch\idx_einfach.dbf' SIZE 1M
AUTOEXTEND ON NEXT 500k;
***Q1***
create index i_l_shipdate on lineitem (l_shipdate) tablespace idx_einfach;
***Q2***
create index i_r_name on region (r_name) tablespace idx_einfach;
***Q3***
create index i_c_mktsegment on customer(c_mktsegment) tablespace
idx_einfach;
create index i_o_orderdate on orders (o_orderdate) tablespace idx_einfach;
***Q4***
create index i_l_commitdate on lineitem(l_commitdate) tablespace
idx_einfach;
create index i_l_receiptdate on lineitem(l_receiptdate) tablespace
idx_einfach;
***Q5***
***hat alle Indexes
***Q6***
create index i_l_quantity on lineitem(l_quantity) tablespace idx_einfach;
create index i_l_discount on lineitem(l_discount) tablespace idx_einfach;
***Q7***
create index i_n_name on nation (n_name) tablespace idx_einfach;
***Q8***
create index i_p_type on part(p_type) tablespace idx_einfach;
***Q9***
create index i_p_name on part(p_name) tablespace idx_einfach;
***Q10***
create index i_l_returnflag on lineitem(l_returnflag) tablespace
idx_einfach;
***Q11***
***hat alle Indexes
***Q12***
create index i_l_shipmode on lineitem(l_shipmode) tablespace idx_einfach;
Erklärungen Einfache Indexes.
***Q13***
create index i_o_comment on orders(o_comment) tablespace idx_einfach;
Einfache Indexes
08:38.4
07:12.0
Zeit
05:45.6
04:19.2
02:52.8
01:26.4
Que rie s
Nur PK
Ausführungszeiten für einfache Indexes bei Oracle XE und Oracle EE
37
Einfache Indexes
Q22
Q21
Q20
Q19
Q18
Q17
Q16
Q15
Q14
Q13
Q12
Q11
Q9
Q10
Q8
Q7
Q6
Q5
Q4
Q3
Q2
Q1
00:00.0
Während bei Oracle XE das eine schlechte Idee war (Zeit um 5% schlimmer), scheint es bei Oracle EE
eine relativ gute Idee zu sein (Zeit um 6% besser).
Wir wollen jetzt den Ausführungsplan der Query 10 sehen. Im Oracle EE sieht es so aus:
Ausführungsplan für Q10 mit einfachen Indexes
Man bemerkt, es wurde nur ein einfacher Index benützt. Es gibt leider 3 full table access Operationen,
die die Anfrage sehr langsam machen. Wir wollen jetzt sehen, ob uns das Tuning Tool eine gute Idee
gibt:
Leider nicht. /
Wenn wir den Ausführungsplan der Query 8 analysieren, sehen wir, dass viele Indexe benützt wurden,
und man sollte eigentlich eine bessere Zeit erhalten.
Ausführungsplan der Anfrage Q8 ohne und mit einfachen Indexen in Oracle XE
38
Ausführungsplan der Query 8 mit einfachen Indexes in Oracle EE
Wir wollen wieder sehen, ob man etwas verbessern kann und bitten um eine Empfehlung. Die sieht so
aus:
Empfehlung für die Verbesserung der Ausführungszeit von Q8
Er sagt also, neue Indexes wären nötig und schlagt einen neuen Ausführungsplan vor:
Der vorgeschlagene Plan und der alten Plan der Q8
Wir wollen die Empfehlung implementieren und drücken auf
.
Nach der Erstellung der neuen Indexes ist die Ausführungszeit 02:53,87 geworden und früher war
02:51,0. Wir sind also enttäuscht, die Zeit hat sich nicht verbessert, wie versprochen.
2.2.5.3 B-Tree Indexes - Mehrspalten Indexes
Man sagt, dass ein mehrspaltig verketteter Index performanter als mehrere separate einspaltige Indizes
ist. Um diese Aussage zu überprüfen, wurden solche Indexes erstellt und die Zeiten wieder gemessen.
Die erste Spalte eines verketteten Indices sollte in den WHERE Klausel der Anfragen verwendet werden.
Damit die indizierte Suche effizient ist, soll die erste Spalte eine hohe Selektivität haben. Die
39
zweitmeistens verwendete Spalte sollte die zweitgrößte Selektivität haben und die zweite in einem
verkettetem Index sein usw. Die Selektivität der ersten Spalte eines veketteten Indizes besimmt die
Selektivität des ganzen Indizes, deshalb soll sie möglichst hoch sein.
Die Selektivität eines Spaltensets von einer Tabelle ist das Bezugsverhältnis m/n, wo
m = Kardinalität des Sets (Anzahl unterschiedliche Werten),
n = Gesamtanzahl der Zeilen in der Tabelle.
Die Selektivität eines Spaltensets meßt die Nützlichkeit des Sets in der Reduzierung der I/O
Operationen bei den Anfragen, die das Set verwenden. Der maximale Wert der Selektivität ist 1 wenn
alle Zeilen unterschiedlich sind, so dass m = n (z. B. die Primärschlüssel-Spalten). Die indizierten
Spalten müssen eine hohe Selektivität haben.
Für diese Indices legen wir zunächst einen neuen Tablespace an:
CREATE TABLESPACE idx_mehr DATAFILE
'C:\oracle\product\10.2.0\oradata\tpch\idx_mehr.dbf' SIZE 1M
AUTOEXTEND ON NEXT 500k;
CREATE INDEX im1_lineitem ON lineitem (l_shipdate, l_returnflag,
l_linestatus) TABLESPACE idx_mehr;
CREATE INDEX im2_lineitem ON lineitem (l_returnflag, l_linestatus,
l_shipdate) TABLESPACE idx_mehr;
CREATE INDEX im_p_size_type ON part (p_size, p_type) TABLESPACE idx_mehr;
CREATE INDEX im1_supplier ON supplier (s_acctbal desc, s_name) TABLESPACE
idx_mehr;
CREATE INDEX im2_supplier ON supplier (s_suppkey, s_name, s_address,
s_phone) TABLESPACE idx_mehr;
CREATE INDEX im3_supplier ON supplier (s_comment, s_suppkey) TABLESPACE
idx_mehr;
CREATE INDEX im3_lineitem ON lineitem (l_shipdate, l_partkey,
l_extendedprice, l_discount) TABLESPACE idx_mehr;
Die Ausführungszeiten:
Mehrspalten Index
05:45.6
05:02.4
04:19.2
Zeit
03:36.0
02:52.8
02:09.6
01:26.4
00:43.2
00:00.0
Q1
Q2
Q13
Q15
Q16
Q17
Q18
Q19
Que rie s
Nur PK
M ehrere Spalten Indexes
Ausführungszeiten für mehrspaltigen Indexes bei Oracle XE und Oracle EE
Hier ist es wieder etwas Interessantes passiert: während das Ergebnis bei Oracle XE schlechter war
(um 17%), haben die Mehrspalten-Indexes gute Ergebnisse bei Oracle EE gezeigt (um 19%).
40
Wir analysieren den Ausführungsplan der Q18 und die Konklusion ist, dass die Benützung der
mehrspaltigen Index i2_part ein bisschen besser war, als full table access auf der Tabelle Part. Der
kleine 2,8% Unterschied wird durch externe Faktoren erklärt.
Ausführungspläne der Q18 vor und nach der Erstallung der mehrspaltigen Indexes
B-Tree Indexes: Fat Indexes (3Sterne)
Ein FAT Index ist ein Index, der mindestens den dritten Stern hat. Er beinhaltet alle Spalten von der
SELECT-Anweisung und besorgt für index access only. Er ist der best-mögliche Index für eine Oracle
Anfrage.
Um für den ersten Stern sich zu qualifizieren, muss ein Index alle Spalten von der WHERE-Klausel
beinhalten. Die sollen am Anfang im Index sich befinden.
Um für den zweiten Stern sich zu qualifizieren, müssen sie die Spalten von ORDER BY haben.
Wenn der Index auch den Rest der Spalten von der SELECT-Anweisung hat, dann bekommt er auch
den dritten Stern.
Es wurden mehreren FAT Indexes erstellt, die von mehreren Anfragen benützt werden:
CREATE TABLESPACE idx_fat DATAFILE
'C:\oracle\product\10.2.0\oradata\tpch\idx_fat.dbf' SIZE 1M
AUTOEXTEND ON NEXT 500k;
CREATE INDEX fi ON lineitem(l_returnflag, l_linestatus, l_shipdate,
l_quantity, l_extendedprice, l_discount, l_tax) TABLESPACE idx_fat;
CREATE INDEX fi2 ON lineitem(l_shipdate, l_returnflag, l_linestatus,
l_discount, l_quantity, l_extendedprice, l_tax) TABLESPACE idx_fat;
CREATE INDEX i3_lineitem ON lineitem(l_shipdate, l_partkey,
l_extendedprice, l_discount) TABLESPACE idx_fat;
CREATE INDEX i3_part ON part(p_brand, p_container, p_size, p_partkey)
TABLESPACE idx_fat;
CREATE INDEX i4_part ON part(p_partkey, p_brand, p_container, p_size)
TABLESPACE idx_fat;
41
Die Ergebnisse sind ausgezeichnet bei Oracle XE und auch bei Oracle EE:
FAT Indexes Effektivität
03:36,0
Zeit
02:52,8
02:09,6
01:26,4
00:43,2
00:00,0
Q6
Q14
Q19
Queries
nur PK
FAT
FAT Indexes Verbesserung bei Oracle XE und EE
Während bei Oracle XE eine 99% Verbesserung zu sehen war, war bei Oracle EE nur 77%, aber noch
sehr gut.
Die Ordnung der Spalten im Index ist sehr wichtig und bestimmt die Ausführungszeit.
Ein Plan einer Anfrage mit FAT Index ist in der Abbildung 2.13.
Ausführungsplan der Query 14 mit FAT Index
2.2.5.4 Index organisierte Tabellen (IOT)
Tabellen können als Index Organized Table verwaltet werden. Dies spart den zusätzlichen Index für
den Primary Key. Insbesondere bei Tabellen mit wenigen Spalten sollte ein Einsatz in Betracht
gezogen werden.
Eine Index organisierte Tabelle sieht ähnlich wie eine normale Tabelle mit einem B*-tree Index für die
Primärschlüssel. Der Unterschied ist, dass bei Index organisierte Tabellen hält Oracle in einer einzigen
B*-tree Struktur sowohl die Primärschlüssel, als auch die anderen Spaltenwerte für jede Zeile. Die
Grundtabelle entfällt. Die Vorteile sind, dass diese Tabellen weniger Speicher verbrauchen und
schneller primärschlüsselbasierter Zugriff für eine Reihe von Anfragen sichern. Zusätzlich sind die
INSERTS, DELETES und UPDATES ein bisschen schneller, weil es eine Seite weniger zu ändern
42
gibt. Die Zeilen der Tabellen haben keine ROWID; ihre Ordnung ist von den Primärschlüssel gegeben.
Man kann keinen zweiten Index auf eine solche Tabelle anlegen.
Der Speicher ist für eine Index organisierte Tabelle in zwei Bereichen verteilt: Index und Overflow.
Der Index-Teil ist in einem Index Tablespace gespeichert und der Overflow Teil in einem separaten
Daten Tablespace. Die Trennung der Zeilen der Tabelle in den zwei Tablespaces kann in der CREATE
TABLE Anweisung festgelegt werden:
• PCTTHRESHHOLD p, mit p Ganzzahl, 0 ≤ p ≤ 50 und
• INCLUDING column_name, wo column_name eine Spalte der Tabelle ist.
Für einen bestimmten p-Wert liefert PCTTHRESHOLD das Equivalent von p% der Größe des
Datenblocks der Datenbank.
Im Vergleich zu den anderen Indexes, weisen die IOTs auch Nachteile, wie zum Beispiel manchmal
mehreren I/Os.
Index organisierte Tabellen können bei vielen Änderungen stark fragmentieren. Eine Online
Reorganisation ist nur mit dem Paket dbms_redefinition möglich.
Die Variable rowlength soll nicht zu lang sein. Wir suchen eine Tabelle, die die Zelen kurz hat:
SELECT table name, avg row len FROm dba tables ORDER BY avg row len ASC
Orders. Wir legen eine neue, Index organisierte Orders-Tabelle an:
CREATE TABLE orders2 (o_orderkey INTEGER NOT NULL, o_custkey INTEGER NOT
NULL, o_orderstatus CHAR(1) NOT NULL, o_totalprice DECIMAL(15,2) NOT
NULL, o_orderdate DATE NOT NULL, o_orderpriority CHAR(15) NOT NULL,
o_clerk CHAR(15) NOT NULL, o_shippriority INTEGER NOT NULL, o_comment
VARCHAR(79) NOT NULL, CONSTRAINT pk_o2 PRIMARY KEY(o_orderkey))
ORGANIZATION
INDEX
Und
führen die Queries
aus, die einen Join mit dieser Tabelle machen. Ergebnisse:
TABLESPACE idx_fat;
INSERT INTO orders2 SELECT * FROM orders;
IOT
04:19,2
03:36,0
Zeit
02:52,8
02:09,6
01:26,4
00:43,2
00:00,0
Q3
Q4
Q8
Q12
Queries
nur PK
IOT
IOT bei Oracle XE und EE
Bei Oracle EE war eine durchschnittliche Verschlechterung der Zeit um 48,8%, ung. gleichmäßig bei
allen Indexes. Bei der XE hatte die IOT Orders einen sehr schlechten Benehmen (199%), aber als die
IOT Tabelle Linitem2 angelegt wurde, war die Verbesserung 14%.
43
Ausführungsplan der Query 3 mit IOT
2.2.5.5 Bitmapped Index und Bitmapped Join Index
B*- tree Indexes sind für Spalten mit geringe Selektivität nicht geeignet. Oracle empfiehlt für die
Queries, die mehr als 15% der Zeilen zurückliefern, eine „full scan“ zu verwenden. Für solche
Zwecken hat Oracle seit der Version 7.3 die Bitmap Indexes eingeführt. Ein Bitmap Index passt in den
nächsten Situationen:
• Die Spalte hat eine geringe Selektivität
• Die Tabelle ist selten aktualisiert
• Die Tabelle ist sehr groß (mindestens 500.000 Einträge)
• Die Anfrage enthält eine oder mehrere mit AND oder OR verknüpfte Spalten.
Die Bitmap Indexes bestehen aus einer bitmap (oder bit vector) für jede verschiedene Spalte. Jede
Bitmap hat einen Bit für jeden Zeile in der Tabelle. Der Bit wird auf 1 gesetzt falls die Zeile einen
bestimmten Wert hat, auf 0 sonst.
Diese Indexes sind insbesondere brauchbar für Anfragen mit komplexen und unvorhersehbaren
zusammengesetzten Prädikaten in großen Tabellen. Das passiert, weil man sehr schnell AND- und
OR-Prädikate ausführen kann, auch wenn Millionen von Zeilen teilnehmen. Die entsprechenden
Operationen mit B-Tree Indexes sammeln und sortieren eine große Menge von Pointern.
Auch diese Art der Indexstruktur hat ihre Nachteile, denn durch ihre Struktur sind
Änderungsoperationen sehr aufwendig, da für sie Matrixmodifikationen nötig werden.
Das Beste bei richtig erstellten B-Tree Indexes ist es, dass sie den Tabellenzugriff eliminieren. Bei den
Bitmap Indexes müssen die Tabellen zugegriffen werden, außer die Anfrage nur COUNTs hat.
Deswegen kann manchmal in dem Fall dieser Indexes längere Ausführungszeiten auftreten.
44
Wir haben die bereits existierende B-Tree Indexes durch Bitmap Indexes ersetzt oder neue Join
Bitmap Indexes erstellt.
CREATE TABLESPACE idx_bm DATAFILE
'C:\oracle\product\10.2.0\oradata\tpch\idx_bm.dbf' SIZE 1M
AUTOEXTEND ON NEXT 500k;
CREATE BITMAP INDEX bi ON lineitem(l_shipdate) TABLESPACE idx_bm;
CREATE BITMAP INDEX bi2 ON part(p_size) TABLESPACE idx_bm;
CREATE BITMAP INDEX jbi_c_mktsegment ON orders(customer.c_mktsegment)
FROM orders, customer WHERE c_custkey = o_custkey TABLESPACE idx_bm;
CREATE BITMAP INDEX bi3 ON orders(o_orderdate) TABLESPACE idx_bm;
CREATE BITMAP INDEX jbm2 ON customer(orders.o_orderdate) FROM customer,
orders WHERE c_custkey = o_custkey TABLESPACE idx_bm;
CREATE BITMAP INDEX jbm3 ON lineitem(part.p_type) FROM lineitem, part
WHERE p_partkey = l_partkey TABLESPACE idx_bm;
CREATE BITMAP INDEX bm_l_returnflag ON lineitem(l_returnflag) TABLESPACE
idx_bm;
CREATE BITMAP INDEX bi_l_shipmode ON lineitem(l_shipmode) TABLESPACE
idx
bm;
Die
Ergebnisse
waren sehr zufriedend:
Bitmap Indexes
03:36,0
Zeit
02:52,8
02:09,6
01:26,4
00:43,2
00:00,0
Q1
Q2
Q3
Q4
Q6
Q8
Q12
Q13
Q14
Q16
Q19
Queries
nur PK
Bitmap
Ausführungszeiten der Anfragen mit Bitmap Indexes
Mit der Ausnahme von Q19 (0,8%), ist es in allen Fällen eine Verbesserung aufgetreten.
Durchschnittlich 34,5%. Das zeigt, dass die Bitmap Indexes eine sehr gute Alternative für die Bitmap
Indexes sein können.
2.2.5.6 Optimale Indexierung: Fazit
Indexes auf Spalten vom Typ DATE oder die indexierten Comment Spalten wurden in keinem Query
Plan benützt. PK wurde auch sehr oft im Query Plan nicht benützt. In Oracle sind die MehrspaltenIndexes besser als die Indexes für eine Spalte, und die FAT Indexes die besten.
Indexes sollen den Query Plan entsprechen. Zu viele Indices verursachen INSERT und UPDATE
Problemen. Die Index organisierte Tabellen (IOT) sind besser nur in den Fällen, wenn es keine Joins
gibt.
45
Der Indexierungsgrad hängt eng von der Art der Applikation ab. Ein hohes Indizierungsgrad steigt die
Geschwindigkeit der Anfragen und Berichten, verlangsamt aber die Update-Operationen. Deshalb ist
eine hohe Indexierung z. B. geeignet für Entscheidungssysteme und nicht geeignet für OLTPAnwendungen.
Die Indexierungsproblemen bei Oracle bestehen daraus, dass nicht immer die richtige Spaltennummer
in der richtigen Ordnung im Index sich befinden. Oft definiert man nicht genug Indexes oder erstellt
nur unnützliche.
Die Kosten für die Speicherung der Indexes kann zu gross sein. Mann sollte immer ein Gleichgewicht
zwischen der Zahl von Indexes und dem verfügbaren Speicherplatz.
Der Oracle Database Tuning Tool empfehlt meistens die Erstellung von Indexes. Man kann aber auch
bessere Optionen finden, wie z.B. materialisierte Views anlegen.
Indexdesign ist nicht einfach, wenn man einen Index für jede Anfrage erstellen muss (außer wenn
einen identischen Index existiert bereits). Man muss immer mehrere Faktoren berücksichtigen: die
SELECT Anfrage, Statistiken der Datenbank (Anzahl von Zeilen, Seiten, u.a.), bestehende Indexes,
usw.
3 Oracle Tuning Pack
Im Unterschied zur Oracle 10g XE bietet Oracle 10g EE die Möglichkeit mit dem Oracle Tuning Pack
zu arbeiten, welche die Laufzeit der Queires ohne großen Aufwand verbessern kann.
Die Tests gezeigten, dass nachdem die Primärschlüsseln erstellt wurden, hat die Laufzeit der Queries
sich verbessert. Die Graphische Darstellung zeigt, dass trotzt der Verbesserung ist die Laufzeit der
Queries: 3, 9, 10, 15, 18, 21 etwas länger als von der anderen Queries, daher werden bei der
Optimierung mit dem Oracle Tuning Pack nur diese 6 Queries betrachtet.
Oracle 10g allows the optimizer to run in tuning mode where it can gather additional
information and make recommendations about how specific statements can be tuned further. 24
SQL-Tuning werden vier Analysetypen ausgeführt:
• Statistische Analyse: Der Query Optimizer benötigt aktuelle Objektstatistiken, um gute
Ausführungspläne zu generieren. Bei diesem Analysetyp werden Objekte mit veralteten oder
fehlenden Statistiken identifiziert und geeignete Empfehlungen zur Lösung des Problems
gegeben.
24
[20] Automatic SQL Tuning in Oracle Database 10g
46
•
•
•
SQL-Profilerstellung: Dieses Feature ist neu in Oracle Database 10g und verkörpert einen
komplett revolutionierten SQL-Tuning-Ansatz. Bisher beinhaltet das SQL-Tuning die
manuelle Bearbeitung des Anwendungs-Codes mit Hilfe von Optimizer Hints. Durch die
SQL-Profilerstellung entfällt dieser manuelle Prozess. Mit der SQL-Profilerstellung wird das
Tuning automatisch und sofort durchgeführt.
Zugriffspfadanalyse: Indizes können die Performance einer SQL-Anweisung extrem
verbessern, weil nicht mehr so viele Full Table Scans notwendig sind. Die effektive
Indizierung ist daher eine gebräuchliche Tuning-Technik. Bei diesem Analysetyp werden neue
Indizes identifiziert und empfohlen, die die Abfrage-Performance beträchtlich steigern
können.
SQL-Strukturanalyse: Probleme mit der Struktur von SQL-Anweisungen können zu einer
schlechten Performance führen. Dabei könnte es sich um syntaktische, semantische oder um
Designprobleme der Anwendung handeln. Bei diesem Analysetyp werden relevante
Vorschläge zur Umstrukturierung der SQL-Anweisung gegeben, um die Performance zu
steigern.
Automatisches SQL-Tuning stellt eine umfassende SQL-Tuning-Lösung dar. Das automatische SQLTuning wird über zwei neue Advisor in Oracle Tuning Pack 10g bereitgestellt, SQL Tuning Advisor
und SQL Access Advisor, die in Enterprise Manager Database Control und Grid Control integriert
sind.
Automatisches SQL-Tuning ist über Enterprise Manager auf "Advisor Central" Seite oder über
PL/SQL durch das Nutzen des DBMS_SQLTUNE Packets zu erreichen. 25
Im folgen wird das Automatisches SQL-Tuning erst über PL/SQL eingesetzt.
3.1 Oracle Tuning Pack über PL/SQL
Um das SQL Tuning Advisor API zu erreichen, braucht der user das ADVISOR Privile:
SQL> CONN / AS SYSDBA
SQL> grant advisor to tpch
SQL> grant select_catalog_role to tpch;
SQL> GRANT EXECUTE ON DBMS_SQLTUNE TO tpch;
SQL> conn tpch/tpch;
Als erstes muss das neue Tuning Task erstellt werden:
-- Tuning task created for a manually specified statement. Q3
DECLARE
l_sql
VARCHAR2(1000);
l_sql_tune_task1 VARCHAR2(100);
BEGIN
L_sql:= 'select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority ' ||
'from customer, orders, lineitem ' ||
'where c_mktsegment = '||''''||'BUILDING'||''''||' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date
'||''''||'1995-03-15'||''''||' and l_shipdate > date '||''''||'1995-03-15'||''''||' ' ||
'group by l_orderkey, o_orderdate, o_shippriority ' ||
'order by revenue desc, o_orderdate ' ;
l_sql_tune_task1 := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
user_name => 'TPCH',
scope
=> DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'emp_dept_tuning_taskQ3',
description => 'Tuning task for an EMP to DEPT join query.');
DBMS_OUTPUT.put_line('l_sql_tune_task1: ' || l_sql_tune_task1);
END;
/
25
[21] ORACLE TUNING PACK FOR DATABASE
47
Als nächstes muss das erstellte Tuning Task ausgeführt werden:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_taskQ3');
Sobald das Tuning Task erfolgreich ausgeführt wird, können die Empfehlungen ausgeführt werden:
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_taskQ3') AS recommendations FROM dual;
SET PAGESIZE 24
Braucht man das Tuning Task nicht mehr so kann es mit DROP_TUNING_TASK Funktion gelöscht werden:
BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => 'emp_dept_tuning_taskQ3');
END;
/
Im folgen werden die Optimierungsvorschläge des Oracle Tuning Packs betrachtet, allerdings gab es
für Queries 10, 15 und 18 leider keine Verbesserungsvorschläge.
Von den restlichen Queires waren besonders die Vorschläge für Queries 3 und 9 interessant. Für
Query 3 werden diese Vorschläge etwas näher angeschaut.
Query 3
------------------------------------------------------------------------------EXPLAIN PLANS SECTION
------------------------------------------------------------------------------Ausführungsplan ohne SQL-Profil
48
Ausführungsplan mit SQL-Profil
Es ist deutlich zu sehen, dass falls man das empfohlene SQL-Profil akzeptiert verbessert sich die
Laufzeit von 00:54:70 auf 00:50:27.
Nachdem das empfohlene SQL-Profil akzeptieren wurde, konnte man die weiteren Empfehlungen des
Oracle Tuning Packs erhalten.
Führt man die restlichen Vorschläge des Oracle Tuning Packs aus, so Verbessert sich die Laufzeit auf
00:40:27.
3.2 Oracle Tuning Pack über Web Benutzeroberfläche
Oracle Tuning Pack bietet die Möglichkeit unter Web Benutzeroberfläche die Geschichte aller SQLAnfragen mit zusätzlichen Informationen zu bekommen. Unter Performance ÎTop Activity Î
Historical Î Period SQL ist eine Tabelle mit allen Anfragen zu sehen. Man kann eine bestimmte
Zeitspanne auswählen, für welche die Queries aufgelistet werden. Die ausgeführten Anfragen sind
nach Ausführungszeit/Kosten geordnet. Leider nicht auch chronologisch. Man kann eine bestimmte
Anfrage nicht suchen, sondern in der sehr langen Liste finden, wo auch die System-Anfragen gemischt
sind. Manchmal sind nicht alle Anfragen vorhanden. Das ist der Grund, warum uner Enterprise
Edition weniger Informationen über Ausführungspläne gefunden /gesucht gefunden wurden.
Bei Oracle gibt es auch einen Tuning Pack, mit dem man automatisch Anfragen optimieren kann. Um
zu sehen, ob es wirklich funktioniert, wurden Tests mit Query 5 durchgeführt. Am Anfang hat es 15
Minuten gedauert, um das Ergebnis zu sehen. Der Ausführungsplan war:
49
Bei Tuning Information war keine Information vorhanden:
Um Empfehlungen zu bekommen, musste man den Tuning Advisor starten.
Empfehlungen:
Oracle zeigt parallel auch die zwei Ausführungspläne: den empfohlene und den alten.
Die Empfehlung wurden durchgeführt:
50
Danach wurde die Query wieder ausgeführt und die Ausführungszeit hat sich deutlich verbessert: von
15:02,09 auf 02:48,68.
3.3 FAZIT
Es war sehr hilfreich mit Oracle Tuning Pack zu arbeiten. Kam der Optmierer mit den eigenen
Methoden nicht mehr weiter, so konnten nach den Empfehlungen des Oracle Tuning Packs
nachgeschaut werden. Unter PL/SQL waren sie leicht auszuführen aber immer noch etwas
komplizierter als unter Web Benutzeroberfläche, hier musste man selbst nichts implementieren, alle
Optimierungen wurden automatisch durchgeführt und man musste bei den Optimierungen nur
Zustimmen damit sie in Gang gesetzt werden konnten. Die Ergebnisse waren immer sehr
zufriedenstellend.
4 FAZIT der Optimierung
Die durchgeführten Tests mit den beiden DB Systemen haben gezeigt, dass obwohl es sehr leicht war
mit Oracle 10g XE zu arbeiten, bat dieses System nicht so viele Möglichkeiten für die Optimierung an.
Man hat gesehen, dass sehr viele Funktionen nicht aktiviert sind, Cluster führen zur Verschlechterung
und man hatte auch keine Möglichkeit gehabt mit Oracle Tuning Pack zu arbeiten. Ausführungszeiten
waren in allgemeinen viel schlechter bei Oracle XE als bei Oracle EE.
Im Unterschied zur Oracle XE war es etwas komplizierte mit Oracle EE zu arbeiten, man hatte
Schwierigkeiten bei der Installation, die Web Benutzeroberfläche hat oft nicht funktioniert und war
etwas unübersichtlicher als bei Oracle XE. Aber dieses System bat sehr viele Möglichkeiten für
Optimierung an, und vor allem man konnte hier immer mit Oracle Tunung Pack arbeiten, was zu sehr
guten Ergebnissen geführt hat.
Optimizers sind nicht perfekt
• Sie sehen nicht immer die beste Alternative
•
•
•
•
Insbesondere wenn service time > Anfrage time besteht diese Gefahr
Komplizierte Predikate (LIKE %)
Nicht nötige Sortierungen
Kosten falsch geschätzt
51
4.1 Oracle Tuning Tips
Die unten aufgelisteten Regeln sind einfach aber sehr hilfreich, wenn um die Performanz geht.
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
26
Do not index every column in the table - have only those indexes, which are required.
Distribute your data to parrallelise the IO operations among multiple disks especially for those
tables which have lots of IO's.
Try to avoid large data retrievals, since if the data being retrieved from a table is more than
10%-20% of the total data, Oracle will most likely do the FST.
Never do a calculation on an indexed column (e.g., WHERE salary*5 > :myvalue).
Whenever possible, use the UNION statement instead of OR conditions.
Avoid the use of NOT IN or HAVING in the WHERE clause. Instead, use the NOT EXISTS
clause.
Always specify numeric values in numeric form and character values in character form (e.g.,
WHERE emp_number = 565, WHERE emp_name = 'Jones').
Avoid specifying NULL in an indexed column.
Avoid the LIKE parameter if = will suffice. Using any Oracle function will invalidate the
index, causing a full-table scan.
Never mix data types in Oracle queries, as it will invalidate the index. If the column is
numeric, remember not to use quotes (e.g., salary = 50000). For char index columns, always
use single quotes (e.g., name = 'NAME').
Remember that Oracle's rule-based optimizer looks at the order of table names in the FROM
clause to determine the driving table. Always make sure that the last table specified in the
FROM clause is the table that will return the smallest number of rows. In other words, specify
multiple tables with the largest result set table specified first in the FROM clause.
Avoid using subqueries when a JOIN will do the job.
Use the Oracle "decode" function to minimize the number of times a table has to be selected.
To turn off an index you do not want to use (only with a cost-based optimizer), concatenate a
null string to the index column name (e.g., name||') or add zero to a numeric column name
(e.g., salary+0). With the rule-based optimizer, this allows you to manually choose the most
selective index to service your query.
If your query will return more than 20 percent of the rows in the table, use a full-table scan
rather than an index scan.
Always use table aliases when referencing columns. 26
[4] Oracle Tuning Tips
52
Literaturverzeichnis
[1]
Haas, F.: Oracle Tuning in der Praxis. Hanser, 2005.
[2]
Lahdenmäki, T., Leach, M.: Relational Database Index Design and the Optimizers. Wiley,
2005.
[3]
Mittra, S.: Database Performance Tuning and Optimization. Springer, 2003
[4]
Oracle Tuning Tips
http://blogs.ittoolbox.com/database/software/archives/oracle-tuning-tips-8151
[5]
Realisierung des TPC-H-Schemas auf einem Oracle-Datenbanksystem
ftp://ftp.informatik.uni-stuttgart.de/pub/library/medoc.ustuttgart_fi/STUD-1945/STUD-1945.pdf
[6]
TPC-H Schema erstellen
http://www.look-for-it.de/index.php?page=tpc-h-schema-erstellen
[7]
Datenbank Lizenzen
http://www.datenbank-lizenzen.com/datenbanklizenzen_smartm/html/f/10/oracle_datenbank_lizenzen_editionen.html
[8]
Datenbanksystem
http://www.it-academy.cc/article/1462/Datenbanksysteme.html
[9]
Oracle-Datenbankadministration für SAP
http://www.sap-press.de/download/dateien/1285/sappress_oracle_datenbankadmin.pdf
[10]
Oracle Konfigurationsoptionen
https://publib.boulder.ibm.com/tividd/td/tec/SC32-1233-00/de_DE/HTML/ecoimst96.htm
[11]
Oracle-Server Bestandteile
http://rowa.giso.de/oracle/latex/Oracle_Server_Bestandteile.html
[12]
http://www.adp-gmbh.ch/ora/concepts/cache.html
[13]
http://de.wikipedia.org/wiki/Sicht_%28Datenbank%29
[14]
Materialized Views
http://www.imn.htwk-leipzig.de/~ctheil1/DB2_vortrag/MaterializedViews_Dokument.pdf
[15]
Oracle: Einsatz von Partitioning
http://www.isr.de/news.nsf/0/B3A50BE438F6EF7BC1256FF00039BA5A?OpenDocument
[16]
Oracle: Einsatz von Partitioning (Teil 2)
http://www.isr.de/news.nsf/0/D7BA47F20C2F8DF3C1256FF00039BA61?OpenDocument
[17]
Oracle SQL im Überblick
http://www.inf.uni-konstanz.de/dbis/teaching/ws0102/informationssysteme/local/sql.pdf
[18]
Michael Ault: Oracle – Datenbanken Administration und Management. Mitp 2003
ISBN: 3-8266-1316-3
[19]
Datenbanksysteme
www.mpi-inf.mpg.de/departments/d5/teaching/ws99_00/dbs/uebungen/p4.ps
[20]
Automatic SQL Tuning in Oracle Database 10g
http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php
53
[21]
ORACLE TUNING PACK FOR DATABASE
http://www.oracle.com/lang/de/collateral/downloads/ORACLE_Tuning_Pack.pdf
[22]
Kevin Loney “Oracle Database 10g Die umfassende Referenz“
Oracle Press, ISBN: 3-446-22833-0
54