Oracle 10G and Windows XP

Seminar
Database Tuning &
Administration
Irina Andrei
Ketevan Karbelashvili
Database Tuning
Tuning ist die Summe aller Maßnahmen, welche
die Optimierung der Antwortzeiten und die
Verbesserung der Skalierbarkeit zum Ziele
haben.
(Dr. Günter Unbescheid)
Inhalt
„
„
1 TPC-Datenbank
… 1.1 TPC-H Schema
… 1.2 CYGWIN
2 DBMS Architektur
… 2.1 Interne Ebene
„
„
…
2.1.1 System Global Area (SGA)
2.1.2 Program Global Area (PGA)
2.2 Konzeptuelle Ebene
„
„
„
„
2.2.1 Denormalisierung
2.2.2 Integration von Materialized Views in Anfragen
2.2.3 Partitionierung von Tabellen
2.2.4 Optimale Indexierung von Tabellen
…
…
…
…
„
FAZIT
2.2.4.1 B-Tree Indexes
2.2.4.2 Index organisierte Tabellen
2.2.4.3 Bitmapped index und Bitmapped join index
2.2.4.4 Clustering
ƒ 2.2.4.4.1 Index Cluster
ƒ 2.2.4.4.2 Hash Cluster
1 TPC-Datenbank
„
TPC-Datenbank = Leistungs-Benchmark.
…
…
„
TPC ist in drei verschiedenen Typen unterteil:
…
•
…
„
„
„
Für leichtes Erzeugen sehr große Datenbanken
Für das Testen der Auslastung einer Datenbank
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
Benutzt wurde:
Betriebssystem:
Datenbank:
TPC-H Modell.
Windows XP
Oracle 10g Express Edition
1.1 TPC-H Schema
„
Nach dem Download der Dateien TPCH:
„
”Database Generator“ und ”Query Generator“ mussten erstellt
werden.
Dafür wurde Datei makefile.suite angepasst.
„
…
…
Makefile wurde erstellt.
vier Parameter zur Anpassung an die vorhandene Datenbank wurden in
Makefile eingestellt:
„
„
„
„
„
CC = gcc
DATABASE= SQLSERVER
MACHINE = LINUX
WORKLOAD = TPCH
Quelldateien wurden mittels make unter CYGWIN kompiliert.
…
Beiden Tools dbgen.exe und qgen.exe wurden erzeugt.
1.2 CYGWIN
„
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.
Ausgeführte Befehle unter SYGWIN:
…
run dbgen -v -s 1 (generierte 1 GB an Zufallsdaten)
… run qgen -N -d 1 > 1_gen.sql (generierte 22 Zufallsqueries)
1.2.1 Importieren der erstellen Daten in
die Datenbank mit Hilfe von SYGWIN:
„
Neues Schema TPCH wurde unter ORACLE 10g XE angelegt
…
1) Erstellen der Datenbanktabellen:
„ Type under SYGWIN : "sqlplus user/Password"
„ Run the following command in the SQL prompt:
… @ <full path>/dss.ddl
…
2) Erstellen der *.ctl Files:
„ Zum Beispeil customer.ctl:
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)
1.2.2 Einspielen der Daten
„
Daten wurden mit dem Oracle SQL*Loader in die
Datenbank eingespielt.
„
Beispielaufruf für die Datei customer.tbl unter SYGWIN:
… sqlldr benutzer/passwort@datenbank
control=customer.tbl
1.3 Zeitmessung
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)
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.
„
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.
2.1.1 System Global Area (SGA)
„
„
„
Der globale Systembereich (SGA, System Global Area) von Oracle ist der
Speicherbereich, der beim Systemstart von Oracle zugeordnet wird und
Speicherstrukturen zum Speichern von Daten und Steuerungsinformationen
enthält.
Wichtigsten Komponenten von SGA:
… Data block buffers (Database Buffer Cache)
… Redo log buffers
… Shared SQL pool
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.“
…
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.
2.1.1 System Global Area (SGA)
„
Default Einstellungen von SGA unter ORACLE 10g XE
2.1.1.1 System Global Area (SGA):
Database Buffer Cache
„
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.
Default Cache Parameter Einstellungen unter ORACLE 10g XE nach Installierung
2.1.1.1 System Global Area (SGA):
Database Buffer Cache
„
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;
„
Und
… Datenbank mit Primär-, Fremdschlüsseln und standard
KEEP_CACHE_SIZE Einstellungen gegen Datenbank mit Primär-,
Fremdschlüsseln und KEEP_CACHE_SIZE =50M.
2.1.1.1 System Global Area (SGA):
Database Buffer Cache
„
Für SGA mit 212M war die Verteilung DB_KEEP_CACHE_SIZE = 50M
nicht optimal. Die Ergebnisse im Vergleich zu DB_KEEP_CACHE_SIZE =
25M waren schlechter.
2.1.1.2 System Global Area (SGA)
„
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.
„
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 ServerProzess startet, wird ihm Arbeitsspeicher zugewiesen. Diesen nennt man
Program Global Area (PGA). Nach dem Beenden des User-Prozesses wird
der PGA wieder freigegeben.
„
Der PGA besteht aus diesen Komponenten:
… Private SQL Area
… Persistent Area
… Run-Time Area
… Session Memory
… SQL Work Areas
2.1.2 Program Global Area (PGA)
„
Default Einstellung PGA von Oracle 10g XE
2.1.3 SGA und PGA
•
Getestet wurde Datenbank mit Primär-, Fremdschlüsseln mit Standart
SGA, PGA Einstellungen gegen Datenbank mit Primär-, Fremdschlüsseln
und
SGA = 512
o PGA = 124
o
2.1.3 SGA und PGA
„
„
Die besten Ergebnisse hat die DB unter Einstellungen SAG = 512 und PGA
= 124 gehabt (21% Verbesserung).
Tests haben gezeigt, dass bei dem manuellen Eingriff man die Größen
vorsichtig vergeben muss.
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.
2.2.1 Denormalisierung
= bewusste Rücknahme einer Normalisierung zum Zweck der Verbesserung
des Laufzeitverhaltens der Anfragen
-
-
Vorteile:
- Verbesserung der Laufzeit
Nachteile:
- Datenredundanz und mehr Speicherplatz nötig
- Aufwand um die redundanten Daten konsistent zu halten
- Gefahr von Datenanomalien
2.2.1 Denormalisierung (2)
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
2.2.1 Denormalisierung (3)
Q5
SELECT …
FROM
customer,
orders,
lineitem,
supplier,
nation,
region
WHERE …
Q5_neu
SELECT
FROM
WHERE
…
customer,
orders,
lineitem,
supp_nat_reg
…
Zeit
Q5
40,6
Q5_neu
43,2
-6,4%
2.2.1 Denormalisierung (4)
Fazit: Denormalisierung : -14% => nicht empfohlen
2.2.2 Integration von Views in Anfragen
„
„
„
Eine View ist eine logische Relation 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. Diese sind Views, die physikalisch gespeichert
werden, um Zugriffe zu beschleunigen. Das findet vor allem bei den
großen Datenmengen bei OLAP Anwendung.
Materialized Views wurden auf die folgenden verschachtelten
Queries angewendet:
…
…
…
…
…
…
Query 7
Query 8
Query 9
Query 11
Query 20
Query 22
2.2.2.1 Materialized Views
„
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 date
'1996-12-31'
and p_type = 'ECONOMY ANODIZED STEEL'
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;
Zeit
Q8
Q8 MV
21,38
0,00
100%
2.2.2.1 Materialized Views
„Die
Ergebnisse waren deutlich besser: +91%
2.2.3 Partitionierung von Tabellen
„
Oracle bietet seit Version 8 die Möglichkeit, Tabellen in mehrere physische
Einheiten, sogenannte Partitionen, aufzuteilen. Jede einzelne Partition kann
eigene physische Attribute (Tablespace, Storage-Parameter) haben und
einzeln angesprochen werden. Dies ist insbesondere für die Administration
und die Reorganisation von grossen Tabellen ein nicht mehr
wegzudenkender Vorteil. 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.
„
Partition wurde Versucht auf Tabelle Lineitem zu erstellen
2.2.3.1 Partitionierte Tabelle Lineitem
„
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_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 (TO_DATE('2007-07-14','YYYY/MM/DD'))
TABLESPACE p5
);
„
create tablespace p1 datafile
'D:\oracle\oradata\xe\p1.dbf' size 1M
autoextend on next 500k
„
create tablespace p2 datafile
'D:\oracle\oradata\xe\p2.dbf' size 1M
autoextend on next 500k
„
create tablespace p3 datafile
'D:\oracle\oradata\xe\p3.dbf' size 1M
autoextend on next 500k
„
create tablespace p4 datafile
'D:\oracle\oradata\xe\p4.dbf' size 1M
autoextend on next 500k
„
create tablespace p5 datafile
'D:\oracle\oradata\xe\p5.dbf' size 1M
autoextend on next 500k
„
ORA-00439: Funktion nicht aktiviert:
Partitioning
2.2.4 Optimale Indexierung von Tabellen
„
„
„
„
Die Verwendung von Zugriffstrukturen soll die Anzahl der Blöcke
reduzieren, die bei der Abarbeitung einer Anfrage gelesen werden müssen:
Index
… B Baum Index
… Bitmap Index
Index – Organisierte Tabellen
Cluster
… Index Cluster
… Hash Cluster
2.2.4.1 B-Tree Indexes: PK (1)
ALTER TABLE partsupp ADD CONSTRAINT pk_partsupp PRIMARY KEY (ps_partkey,
ps_suppkey);
…
2.2.4.1 B-Tree Indexes: PK(2)
Durchschnittliche Verbesserung: 15%
2.2.4.1 B-Tree Indexes: PK (3)
Query plan Q2: + 97,8%
2.2.4.1 B-Tree Indexes: PK (4)
Query plan Q10: -12%
2.2.4.1 B-Tree Indexes: Indexes auf einer
Spalte (1)
Q8
SELECT …
FROM …
WHERE r_name = 'AMERICA'
AND o_orderdate between date '1995-01-01' AND DATE '1996-12-31 '
AND p_type = 'ECONOMY ANODIZED STEEL'
CREATE INDEX i_o_orderdate ON orders (o_orderdate) TABLESPACE idx1;
CREATE INDEX i_r_name ON region (r_name) TABLESPACE idx1;
CREATE INDEX i_p_type ON part(p_type) TABLESPACE idx1;
2.2.4.1 B-Tree Indexes: Indexes auf einer
Spalte (2)
Zeit
Q8
43,2
Q8 Index
46,3
-7,2%
2.2.4.1 B-Tree Indexes: Indexes auf einer
Spalte (3)
Indexes auf einer Spalte: Verschlechterung 5%
2.2.4.1 B-Tree Indexes: Mehrspalten-Indexes
(1)
Q17
Select sum(l_extendedprice) / 7.0 as avg_yearly
From lineitem, part
Where p_partkey = l_partkey and p_brand = 'Brand#23‘ and p_container = 'MED
BOX' and l_quantity < (
select 0.2 * avg(l_quantity)
from lineitem
where l_partkey = p_partkey );
create index i2_part on part(p_partkey, p_brand, p_container)
tablespace idx1;
2.2.4.1 B-Tree Indexes: Mehrspalten-Indexes
(2)
Zeit
Q18
35,2
Q18 M IDX
36,2
-2,8%
2.2.4.1 B-Tree Indexes: Mehrspalten-Indexes
(3)
Indexes auf mehreren Spalten: - 17%
2.2.4.1 B-Tree Indexes: Fat Indexes
(3Sterne)
-
Alle Spalten in der SELECT-Anfrage => Index Only Access
* WHERE SPALTE=… (eine nach der anderen so nah wie möglich)
** ORDER BY – SPALTEN (kein Sort)
***Alle andere Spalten (eliminiert Tabellenzugriff)
2.2.4.1 B-Tree Indexes: Fat Indexes (2)
c) Fat Indexes
Q14
Select 100.00 * sum(case
when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount)
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
From lineitem, part
Where l_partkey = p_partkey
and l_shipdate >= date '1995-09-01'
and l_shipdate < date '1995-09-01' + interval '1' month;
create index i2_lineitem on lineitem(l_extendedprice, l_discount,
l_shipdate, l_partkey) tablespace idx1;
create index i3_lineitem on lineitem(l_shipdate, l_partkey,
l_extendedprice, l_discount) tablespace idx1;
Zeit
Q14
35,2
i2
18,3
+48%
i3
0,23
+99,3%
⇒Fat Indizes Verbesserung: +99%
⇒Die Ordnung der Spalten im Index ist wichtig
2.2.4.1 B-Tree Indexes: Fat Indexes (3)
2.2.4.1 B-Tree Indexes: Fat Indexes (4)
Fat Indexes sind ideal für ein SELECT: 99%-100% Verbesserung
2.2.4.2 Index organisierte Tabellen (IOT)
„
„
„
Tabellen werden als Index abgespeichert, Grundtabelle entfällt
Dies spart den zusätzlichen Index für den Primärschlüssel.
Insbesondere bei Tabellen mit wenigen Spalten sollte ein Einsatz in
Betracht gezogen werden.
select table_name, avg_row_len from dba_tables order by avg_row_len asc
⇒
Orders
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
Tablespace idx;
2.2.4.2 Index organisierte Tabellen (2)
Q3
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders2,
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;
Zeit
Q3
43,8
Q18 IOTO
160
-265%
2.2.4.2 Index organisierte Tabellen (3)
IOT mit joins: Verschlechterung 199%
2.2.4.2 Index organisierte Tabellen (4)
CREATE TABLE LINEITEM2 ( …)
ORGANIZATION INDEX
TABLESPACE idx;
Q1
SELECT …
From lineitem2
where
l_shipdate <= date '1998-1201' - interval '90' day (3)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
Zeit
Q1
Q1 IOT LI
37,2
31
16,7%
2.2.4.2 Index organisierte Tabellen (5)
„
IOT ohne Join: Verbesserung 14%
2.2.4.3 Bitmapped Index und Bitmapped
Join Index (1)
„
Besonders effizient ist ein Bitmap-Index, wenn es nur wenig
mögliche Schlüsselwerte gibt, die durch den Index dargestellt
werden müssen.
„
Characteristika
…
…
„
Effizient bei
…
…
…
„
„
Weniger speicheraufwändig
Speichert NULL-Werte
Kombinierten, nicht sehr selektiven Auswahlbedingungen
Schnelle AND- und OR-Kombinationen über mehrere Indizes
SELECT COUNT(*) Abfragen
Aber auch diese Art der Indexstruktur hat ihre Nachteile, denn durch
ihre Struktur sind Änderungsoperationen sehr aufwendig, da für sie
Matrixmodifikationen nötig werden.
(Nur leider bei Oracle 10g XE ist die Funktion nicht aktiviert)
2.2.4.3 Bitmapped Index und Bitmapped
Join Index (2)
„
Die Tabellen Lineitem und
Orders könnten gute Kandidaten
für Bitmapped index sein.
„
Lineitem:
…
„
Die Spalten: Returnfalg und
Linestatus
Orders:
…
Die Spalten: Orderstatus und
Orderpriority
„
Haben nicht sehr selektiven
Auswahlbedingungen
„
Anlegen:
…
CREATE BITMAP INDEX Returnfalg
ON lineitem (l_returnflg)
„
Hier wurde versucht
ein Bitmapped join
Index zu erstellen
„
create bitmap index fatq2 on
supplier (p_size, p_type)
from
part,
partsupp,
where
p_partkey = ps_partkey
tablespace idx1;
2.2.4.4 Clustering (1)
„
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.
2.2.4.4.1 Index Cluster (1)
„
Prinzip: Tupel mit gleichem
Cluster Key werden
gemeinsam in Blocken
gespeichert.
2.2.4.4.1 Index Cluster (2)
„
„
„
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.
2.2.4.4.1 Index Cluster (3)
„
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.
2.2.4.4.1 Index Cluster (4)
„
Getestet wurden zwei Tabellen:
… Part
… Partsupp
„
CREATE CLUSTER Cluster_Part_partsupp (p_partkey INTEGER ) size 512
„
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);
„
CREATE INDEX Part_partsupp_INDEX on CLUSTER Cluster_Part_partsupp;
Q2 Zeit:
Mit Index Cluster: 3,48 seconds
Ohne Index Cluster: 0,89 seconds
2.2.4.4.2 Hash Cluster (1)
„
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.
2.2.4.4.2 Hash Cluster (2)
„
Getestet wurden die gleichen Tabellen:
… 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);
Q2 Zeit:
Mit Index Cluster: 3,48 seconds
Ohne Index Cluster: 0,89 seconds
Mit Hash Cluster: 3,08 seconds
2.2.4.4.2.1 Hash Cluster gegen Index Cluster
„
Sgfdhs
2.2.4.4.2.2 Index Cluster mit SIZE = 800b
„
„
Besonders vorteilhaft ist Index Cluster, wenn bei der 1:n Beziehung
die Zahl n eine Konstante ist.
Geclustert wurden noch einmal Tabellen:
…
…
„
„
Mit: select * from partsupp order by ps_partkey
wurde die Beziehung zu ps_partkey Schlüssel bestimmt. n war
immer eine Konstante = 4.
Mit: Select table_name, avg_row_len from dba_tables
wurde die durchschnittliche Länge der part und partsupp Tabellen
bestimmt:
…
„
Part
Partsupp
|part|+n*|partsupp|~800b
Die Ergebnisse waren besser aber trotzdem nicht gut.
2.2.4.4.2.3 Hash Cluster gegen Index
Cluster
2.2.4.5 Optimale Indexierung: Fazit (1)
„
Indexes auf Spalten vom Typ DATE oder die indexierten Comment-Spalten
wurden in keinem Query Plan benützt
„
PK sehr oft im Query Plan nicht benützt
„
In Oracle sind die Mehrspalten-Indexes besser als die Indexes für eine
Spalte
„
Fat Indizes – die besten
„
Für bessere Ergebnisse soll der erste Feld eines Mehrspalten-Index die
höchste Selektivität haben
(Die Selektivität misst die Nützlichkeit einer Gruppen von Spalten im Bezug
auf die I/Os nötig für die SELECT-Anfrage
„
2.2.4.5 Optimale Indexierung: Fazit (2)
„
Indexes sollen den Query Plan entsprechen
„
Zu viele Indexes = INSERT und UPDATE Problemen
„
IOT besser nur wenn keine Joins
„
Trotz der Voraussetzungen, war Clustering keine gute Option
-150%
en
o
e
T
IO
T
FA
T
te
n
al
te
Sp
al
IO
re
r
Sp
PK
+
jo
rm
in
al
is
ie
ru
S
ng
db
G
A
_k
&
ee
PG
p_
A
ca
ch
e_
siz
In
e
de
x
In
Cl
de
us
x
te
Cl
r
us
te
rn
H
eu
as
h
Cl
us
te
r
M
Vi
ew
s
-200%
D
-100%
Ei
ne
-50%
M
eh
Prozent
FAZIT
Vergleich Tuning Maßnahmen
150%
100%
50%
0%
-250%
Maßnahmen
FAZIT
„
„
Oracle 10g XE: max 5GB => sehr oft kein Speicherplatz mehr
Drop Index => Speicher in der DB nicht befreit
FAZIT
„
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
…
…
…
Oracle 10 g Tools
„
„
„
Oracle Enterprise Manager
Oracle Diagnostics Pack
Oracle Database Tuning Pack – automatizier DB Tuning
… SQL Tuning Advisor