Table - HTW Dresden

Deckblatt
Verwaltung sehr großer Datenmengen
mit Sybase 15
Jürgen Bittner
SQL GmbH
Wie groß sind VLDB ? - 2005
DB-Stammtisch 06/2006, 2
DSS versus OLTP Workload
•Performance & Effektivität
•Skalierfähigkeit(Daten & Anwender)
zeilenbasiertes DBMS
(entworfen vor +20 Jahren für OLTP)
DB2, Oracle, MS SQL, ASE, NCR etc.
2x1000x
OLTP=einfache Query
-verarbeitet 10s von Rows
-1000s von Rows pro Sekunde
-einfache (½-Seite) SQL Statements
spaltenbasiertes IQ Multiplex
(entworfen vor wenigen Jahren für DSS)
2x1000x
DSS=komplexe Query
-verarbeitet M(B)ILLIONS von Rows
-Millionen von Rows pro Sekunde
-komplexe (10-Seiten) SQL Statements
DB-Stammtisch 06/2006, 3
Verwaltung großer Datenmengen mit
•
Adaptive Server IQ
– Neues zu IQ 12.6
•
Adaptive Server Enterprise 15 – Konzepte und
Funktionalitäten für große Datenmengen
– Partitionen
– Berechnete Spalten und Funktions-basierter Index
– Verbesserungen der Anfrage-Verarbeitung
• Arbeitstabellen
• Hash-Join
– Optimierer-Steuerung
DB-Stammtisch 06/2006, 4
Der traditionelle RDBMS-Ansatz
Berechne den
durchschnittlichen Absatz
von „Radeberger“
in Gastronomie-Einrichtungen
in Sachsen je Monat
SELECT AVG (Abs), SUM(Abs)/AnzGSA/36
FROM Absatz,
Mon
Einr
Typ
Land
Prod
Abs
0105
32
G
SA
Werne
12
0105
36
G
MV
Becks
9
0105
38
G
SA
Radeb
28
0105
41
K
NS
Jever
11
0105
43
G
SA
Radeb
9
0105
46
G
BY
Paula
3
0105
47
M
NW
Dortm
70
0105
49
K
SA
Lands
12
(SELECT COUNT(DISTINCT Einr) AS AnzGSA
Traditioneller
FROM Absatz Ansatz:
WHERE
Land
= ‘SA‘
AND verfügbar
• Benutze
einen
Index
wenn
-
benötigt normalerweise
Table Scan
Typ = ‘G‘)
•WHERE
Gehe Land
zu den
ausgewählten
Datenseiten und addiere die Zahlen
= ‘SA‘
AND
Zufällige
Verteilung der Daten führt dazu, daß fast alle Seiten gelesen werden
Typ = ‘G‘ AND
müssen.
‘Radeb‘
- AufProd
jeder= Seite
müssen alle - auch die irrelevanten - Daten gelesen werden.
-
DB-Stammtisch 06/2006, 5
Vertikale Partitionierung
Sybase IQ: Es werden nur
die relevanten Spalten
gelesen
Berechne den
durchschnittlichen Absatz
von Radeberger
in Gastronomie-Einrichtungen
in Sachsen
Vorteile:
Ohne weitere Techniken
kann IQ den Disk-I/O
sehr stark reduzieren
Mon
Einr
Typ
Land
Prod
Abs
9805
32
G
SA
Werne
12
9805
36
G
MV
Becks
9
9805
38
G
SA
Radeb
28
9805
41
K
NS
Jever
11
9805
43
G
SA
Radeb
9
9805
46
G
BY
Paula
3
9805
47
M
NW
Dortm
70
9805
49
K
SA
Lands
12
DB-Stammtisch 06/2006, 6
Sybase IQ Advanced Bit-Mapped Indizes
Bitmap Index für Land
Land
SA
MV
SA
NS
SA
BY
NW
SA
row-id
BY
MV
NS
NW
SA
1
0
0
0
0
1
2
0
1
0
0
0
3
0
0
0
0
1
4
0
0
1
0
0
5
0
0
0
0
1
6
1
0
0
0
0
7
8
0
0
0
0
0
0
1
0
0
1
z 25 Jahre bewährte Technologie (z.B. Model 204)
z Vor Sybase IQ nur für Daten mit geringer Kardinalität
(< 100 Werte) verwendbar
z Sybase IQ erweitert diesen Wert auf > 1.000 Werte durch die
Kombination von Bitmaps und Komprimierung.
z Ohne weitere intelligente Indizes allerdings von begrenztem
Nutzen, da nur wenige Queries nur Bitmaps brauchen
DB-Stammtisch 06/2006, 7
Dramatische I/O-Reduzierung
“Wieviele Männer sind in Kalifornien nicht versichert?“
800 Bytes x 20M
RDBMS
= 1.000,000 I/Os
Geschlecht
Staat
Versichert
16K Seite
M
CA
J
M
W
M
M
W
20M
Sätze
CA
NY
CA
MA
CT
N
J
N
J
N
„
Verarbeitet grosse Mengen nicht
benötigter Daten
„
Erfordert oft “Full Table Scan”
800 Bytes/Satz
Geschlecht
1
2
3
4
M
M
W
M
Staat
CA
CA
NY
CA
Versichert
J
N
J
N
20M Bits x 3 Spalten / 8 = 470 I/Os
16K Seite
20M
Bits
1
1
0
1
+
1
1
0
1
+
0
1
0
1
=
2
DB-Stammtisch 06/2006, 8
OLTP DBMS vs. IQ-Multiplex engine
OLTP-centric
DBMS
IQ Multiplex
SQL: Create table ABC
yellow, blue, red..magenta
SQL:Select sum (red) from ABC
1: Standard SQL language: (no patents, trivial migration and retraining)
2: Column structure ( patented, invisible to users )
-Designed for DW: Ad-Hoc/complex queries (no OLTP tradeoffs, never FTS)
- 90%-99% reduction in I/O compared with traditional_DBMS-based DW
-45,000 columns/table => enables complexity AND flexibility, low RAM use
3: Indexing: Bit-wise, Word, Bitmap etc. (patented, invisible to users )
-Speed: designed for DSS=>2x-1,000x faster than OLTP-centric DBMS
-Data-driven (vs “one-size-fits-all”, I.e. B-tree)
-low-cost(=no index “explosion”): index every column (some multiple times)
4: Data Compression ( patented, invisible to users )
-Additional reduction of DB size AND I/O by 30-80% (typ); improves security
-IQM DB size(fully indexed) : 75% of raw data (typ); IQM as an ARCHIVE?
-OLTP-centric DB size is 3x-10x of raw data size =>4x-15x more disk than IQM
-small IQM size & low I/O=>fast/low_cost Backup/R, HA and Disaster Site site
5: IQ Multiplex
-Shared-disk (non-partitioned data) with 97% multi-node scalability
-ZERO inter-node interference => Architecture with Built-in High Availability
-Scalability and Availability beyond MPP and pure-SMP+HA solutions
Small size, random I/O gets less than
1MB/s-3MB/s from single disk
1 2 3 4 ….. 100
Db page
2-32KB
IQM large, semi-sequential I/O gets
10MB/s-30MB/s from single disk
Db page
1 2 3 4 …. 100
64KB-512KB
DB-Stammtisch 06/2006, 9
Administration der Indexstrukturen
Indextypen Sybase IQ
Indexerstellung zur Designzeit
•
•
•
Keine Datenspeicherung
Kardinalität
Datentyp
Keine Pflege der Indexstatistiken
DB-Stammtisch 06/2006, 10
Index Advisor
•
Vorschläge von Indextypen für
–
–
–
–
–
•
Single Column Predicate (e.g. HG/LF, Date)
Join Column
Subquery Predicate
Group By Column
Two Columns compared in Same Table (CMP index)
Erkennt ungeeignete Index-Definitionen
DB-Stammtisch 06/2006, 11
IQ Multiplex 12.5 (und davor)
1 Sybase IQ Write Server
N Sybase IQ Query Server
Base Tables
Developer Tables
Analyst Tables
WEB Access
Query Only Users
DBAs
Analysts
Developers
ETL Processes
IQ Main Store
DB-Stammtisch 06/2006, 12
IQ Multiplex 12.6 w/ Local Store
N Sybase IQ
Query Only Server
1 Sybase IQ Write Server
Base Tables
M Query
Server
with a
Local Store
WEB Access
Query Only Users
Analysts
Developers
DBAs
ETL Processes
Analysis Area
IQ Main Store
Developer
Tables
Local Store
DB-Stammtisch 06/2006, 13
IQ Multiplex – Local Store für Query Server
•
Query Server ermöglichen einen 'Local IQ Store' , der es den
Benutzern erlaubt, permanente Objekte in der Datenbank anzulegen
–
Unterstützung für Benutzer wie Analysten und Entwickler, die ihre
eigenen Tabellen oder Prozeduren kreieren wollen
•
Objekte, die im Local Store angelegt wurden, werden persistent nach
einem Recycle des Query Servers
•
Diese Objekte sind nur für die Benutzer sichtbar, die mit dem Query
Server verbunden sind, zu dem der Local IQ Store gehört
–
•
Objekt-Zugriff gemäß Standard Rechten
Ein Backup des IQ Main Store realisiert NICHT ein Backup im Local IQ
Stores
–
–
Local Store backup muß separat ausgeführt werden
Gleiches gilt für die Konsistenz-Prüfung der Daten im Local Store
DB-Stammtisch 06/2006, 14
SUN-Sybase Referenz DWH - Weltgrößtes DWH in Menlo Park
Business Case Telco
•2002
•2004
•179 Milliarden Zeilen
•1 Billion Zeilen
•48 TB Inputdaten
•155 TB Inputdaten
•Kompression der Inputdaten v.
48 TB auf 22 TB
•Kompression der Inputdaten v.
155 TB auf 55 TB
•Ladezeit: 160 Millionen CDRs in
<1h
•Ladezeit: bis zu 1,8 Mrd.
Datensätze pro Stunde
DB-Stammtisch 06/2006, 15
2005: World’s TopTen
Company
AT&T
Amazon.com
France Telecom
Health Insurance Review Agency
Barclays Bank
FedEx Services
Samsung Card.
Kmart
Cho-Hung Bank
LG Card
Company
France Telecom
AT&T
SBC
Anonymous
Amazon.com
Kmart
Claria Corporation
Health Insurance Review Agency
FedEx Services
Vodafone D2 GmbH
Company
AT&T
Nielsen Media Research
France Telecom
SBC
Kmart
FedEx Services
Kroger
LG Card
Health Insurance Review Agency
AT&T Wireless Services
Company
ComScore Networks Inc.
ComScore Networks Inc.
ComScore Networks Inc.
Ordina
Arclight Systems LLC
Edgars Consolidated Stores
Hewlett Packard Company
Gomez
DataQuick
NAREX, Inc
Company
ComScore Networks Inc.
ComScore Networks Inc.
ComScore Networks Inc.
Hewlett Packard Company
Hewlett Packard Company
Arclight Systems LLC
Input DATA Size, All Environments & UNIX
DB size
Input data(GB) DBMS
Syst Arch. DBMS vendor
26,269
94,305 Daytona
SMP
AT&T
13,001
34,219
29,232
29,735
11,942
29,299
IQ
MPP/Cluster
Sybase
6,408
24,756 Teradata MPP/Cluster
Teradata
9,981
14,745 Teradata MPP/Cluster
Teradata
7,684
14,567
IQ
MPP/Cluster
Sybase
12,592
13,874 Teradata MPP/Cluster
Teradata
3,361
12,350
IQ
MPP/Cluster
Sybase
6,336
12,313
IQ
MPP/Cluster
Sybase
server
Sun
storage
Sun
HP
NCR
NCR
HP
NCR
Sun
Sun
EMC
LSI
EMC
HP
LSI
Hitachi
EMC
server
storage
Sun
NCR
IBM
HP
NCR
Sun
HP
NCR
NCR
Sun
LSI
IBM
HP
LSI
Hitachi
Hitachi
EMC
LSI
UNIX
Syst Arch. DBMS vendor
SMP
AT&T
MPP/Cluster
Sybase
SMP
Oracle
MPP/Cluster
Teradata
MPP/Cluster
Teradata
MPP/Cluster
Teradata
MPP/Cluster
IBM
SMP
Sybase
MPP/Cluster
Sybase
MPP/Cluster
Teradata
server
Sun
Sun
HP
NCR
NCR
NCR
IBM
Sun
HP
NCR
storage
Sun
Sun
HP
LSI
LSI
EMC
IBM
EMC
Hitachi
NCR
DB ROWS Windows
rows (M)
DBMS
Syst Arch. DBMS vendor
61,361
IQ
MPP/Cluster
Sybase
44,195
IQ
MPP/Cluster
Sybase
40,523
IQ
MPP/Cluster
Sybase
6,402 SQL Server
SMP
Microsoft
6,246 SQL Server
SMP
Microsoft
5,543 SQL Server
SMP
Microsoft
3,704
Oracle
SMP
Oracle
3,418 SQL Server
SMP
Microsoft
3,273 SQL Server
SMP
Microsoft
3,228 SQL Server
SMP
Microsoft
server
HP
Dell
Dell
IBM
IBM
IBM
HP
HP
Dell
Dell
storage
EMC
EMC
EMC
IBM
EMC
IBM
HP
other
EMC
Dell
(exploded) DB SIZE Windows
input size GB
DB size
DBMS
Syst Arch. DBMS vendor
19,604
8,852
IQ
MPP/Cluster
Sybase
14,464
7,458
IQ
MPP/Cluster
Sybase
14,636
7,336
IQ
MPP/Cluster
Sybase
2,165
Oracle
SMP
Oracle
1,755
Oracle
SMP
Oracle
1,640 SQL Server
SMP
Microsoft
server
HP
Dell
Dell
HP
HP
IBM
storage
EMC
EMC
EMC
HP
HP
IBM
( Exploded ) DB Size, All
Input data(GB)
DB size
29,735
29,232
94,305
26,269
NULL
24,805
8,591
16,191
34,219
13,001
13,874
12,592
4,361
12,100
29,299
11,942
14,745
9,981
NULL
9,108
Environments & UNIX
DBMS
Syst Arch. DBMS vendor
SMP
Daytona
SMP
AT&T
Teradata MPP/Cluster
Teradata
DB2
MPP/Cluster
IBM
Oracle
SMP
Oracle
Teradata MPP/Cluster
Teradata
Oracle
SMP
Oracle
IQ
MPP/Cluster
Sybase
Teradata MPP/Cluster
Teradata
Teradata MPP/Cluster
Teradata
DB ROWS All Environments &
rows (M)
DBMS
496,041 Daytona
380,000
IQ
156,788
Oracle
144,417 Teradata
133,079 Teradata
89,343 Teradata
77,837
DB2
74,703
IQ
72,093
IQ
64,534 Teradata
input size GB
14,464
19,604
14,636
29 TB
15 TB
12 TB
China Telecom :134 B
380 B
IRS :15 B
74,7 B
Citadel :83 B
SKCC :170 B
72 B
61 B
www.wintercorp.com
DB-Stammtisch 06/2006, 16
Verwaltung großer Datenmengen mit
•
Adaptive Server IQ
– Neues zu IQ 12.6
•
Adaptive Server Enterprise 15 – Konzepte und
Funktionalitäten für große Datenmengen
– Partitionen
– Berechnete Spalten und Funktions-basierter Index
– Verbesserungen der Anfrage-Verarbeitung
• Arbeitstabellen
• Hash-Join
– Optimierer-Steuerung
DB-Stammtisch 06/2006, 17
Neue Limits
ASE 15.0 very large storage system
Vor ASE 15
Maximale device Anzahl = 231 (2 Billion)
256
Maximale device Größe = 4TB
32 GB
Datenbanken/Server = 32 767 (noch)
Das maximale Speichervolumen:
Datenbank-Größe:
231 pages * 16KB pg = 32 TB
256 * 32 GB = 8 TB
Theoretisches Speichervolumen eines Servers:
32,767 DB’s * 32 TB = 1 EB (exabyte) = 1,048,544 TB
DB-Stammtisch 06/2006, 18
Verwaltung großer Datenmengen mit
•
Adaptive Server IQ
– Neues zu IQ 12.6
•
Adaptive Server Enterprise 15 – Konzepte und
Funktionalitäten für große Datenmengen
– Partitionen
– Berechnete Spalten und Funktions-basierter Index
– Verbesserungen der Anfrage-Verarbeitung
• Arbeitstabellen
• Hash-Join
– Optimierer-Steuerung
DB-Stammtisch 06/2006, 19
Überblick zum Partitionieren
•
Große Tabellen benötigen entsprechend große
Ressourcen:
–
–
–
•
Table Scans benötigen viele E/A
Verwaltungsaufgaben benötigen viel Zeit – und viele Sperren
OLTP und DSS behindern sich gegenseitig
Durch die Partitionen werden die Tabellen in Teilmengen
zerlegt:
–
–
–
Die Daten jeder Partition können unabhängig verarbeitet werden
Die Bearbeitung kann parallel erfolgen
Administrative Aufgaben können gezielt auf speziellen Partitionen
erfolgen
DB-Stammtisch 06/2006, 20
Vor ASE 15
•
Partitionieren durch Segmente
•
•
Nur die Tabellen (nicht die Indexe) sind
partitioniert
Primäre Ziele
•
•
•
•
Reduzieren der last page Konflikte
Parallelisierte query-Verarbeitung
Parallelisiertes dbcc checkstorage
Parallelisiertes create index
DB-Stammtisch 06/2006, 21
ASE 15 Semantische Partitioinierung
•
•
Partitionierte Tabellen und Indexe
–
Partition types in ASE 15.x:
•
Range
•
List
•
Hash
•
Round-robin (ersetzt Segment)
–
Partition maintenance
•
Add oder Alter eine oder mehrere partitions
Globale und lokale Indexe auf partitions
–
•
Verbesserung der query-Verarbeitung
–
•
Clustered/non-clustered indexes
Optimizer und execution support (Parallelisieren & Eliminieren)
Partition-basierte Wartung
–
–
Update statistics auf einer oder allen partitions
Truncate, reorg, dbcc, bcp (out) partition
DB-Stammtisch 06/2006, 22
Range Partition
•
Es werden Grenzwerte für jede Partition festgelegt:
–
•
Günstige Anwendung bei:
–
•
Großen Tabellen in Anwendungen mit hohen Anforderungen an die
Performance.
“ Gleitende Sichten” auf die Daten sind möglich.
–
•
Die Werte in den Datensätzen sind kleiner oder gleich diesem
Grenzwert
Datenmengen können ohne Auswirkungen auf andere Partitionen
hinzugefügt oder gelöscht werden.
Die Balance der Partition hängt von den definierten Grenzwerten ab.
DB-Stammtisch 06/2006, 23
Range Partition – Syntax und Beispiel
create table tablename
(column definitions)
partition by range (columnlist)
(partitionlabel values <= ({value | MAX}) [on segmentname] [,...])
create table sales
(cust_id
char(10)
not null,
ord_id
char(10)
not null,
salesdate
date
not null,
salespsn
int
not null)
partition by range (salesdate)
(q1 values <= ('3/31/2005') on seg1,
q2 values <= ('6/30/2005') on seg2,
q3 values <= ('9/30/2005') on seg3,
q4 values <= (MAX)
on seg4)
DB-Stammtisch 06/2006, 24
Range Partition
sp_helpartition:
q1
q2
q3
q4
4/1/05
1/3/05
7/5/05partition_keys
10/1/05
name
type
partition_type
partitions
4/2/05
1/3/05 -------------7/5/05
10/2/05
----- -------------------- -------------4/3/05
7/6/05
sales1/4/05
base table range
4 salesdate 10/3/05
4/2/05 pages 7/7/05
1/5/05
partition_name
partition_id
segment 10/4/05
1/5/05 ------------ ----------- -------7/8/05
11/2/05
-------------12/2/05
q1 1/5/05 1024003648
452 7/8/05
seg1
q2
1040003705
331 seg2
q3 seg1 1056003762
422 seg3
seg2
seg3
seg4
q4
1072003819
628 seg4
DB-Stammtisch 06/2006, 25
List Partition
•
Der Nutzer definiert Listen mit Werten für jede Partition:
– Mindestens ein Wert muss jeder Partition zugewiesen werden
– Jeder Wert darf nur einer Partition zugewiesen werden
– Die Werte sind in der Partition nicht sortiert
•
Günstige Anwendung bei wenigen unterschiedlichen
Schlüsselwerten mit häufiger Wiederholung.
•
Die Balance der Partition hängt von den zugeordneten
Werten ab.
DB-Stammtisch 06/2006, 26
List Partition – Syntax und Beispiel
create table tablename
(column definitions)
partition by list (column name)
([partition label] values (constant[,...])[on segmentname ]... )
create table
(cust_id
cust_name
state
phone
partition by
(west values
east values
customers
char(10)
not null,
varchar(30)
not null,
char(2)
not null,
char(10)
not null)
list (state)
('CA', 'OR', 'WA') on seg1,
('NY', 'NJ')
on seg2
DB-Stammtisch 06/2006, 27
List Partition
customers
sp_helpartition:
west
east
name
type
partition_type partitions
CA
NY
partition_keys
CA
---------- ----------------------- NY
---------- ------------OR
NJ
cust_table baseWA
table list
2 state
NY
partition_name partition_id pages
segment
CA
-------------- ------------ ----------- ------OR864003078
west
62 seg1
east
880003135
41 seg2
Partition_Conditions
seg1
seg2
------------------------VALUES ('CA', 'OR', 'WA')
VALUES ('NY', 'NJ')
DB-Stammtisch 06/2006, 28
Hash Partition
•
Adaptive Server verwendet eine Hash Funktion um eine
entsprechende Partition anzusteuern
– Basierend auf bis zu 31 Schlüsselspalten
•
Günstige Anwendung bei:
– DSS Operationen
– Große Tabellen mit vielen Partitionen
– Tabelle mit unsortierten Daten
–
•
Die Balance der Partitionen ist von der Anzahl der
doppelten Schlüsselwerte abhängig.
DB-Stammtisch 06/2006, 29
Hash Partition – Syntax und Beispiel
create table tablename
(column definitions)
partition by hash (column list)
{(partitionlabel [on segmentname] [,...])
| number_of_partitions [on (segmentname [,...])]}
create table lineitem
( l_orderkey
int
not null,
l_partkey
char(10)
not null,
l_suppkey
char(4)
not null,
l_linenumber int
not null,
l_quantity
int
not null)
partition by hash (l_orderkey, l_linenumber)
(litem_hash1 on seg1,
litem_hash2 on seg2,
litem_hash3 on seg3)
DB-Stammtisch 06/2006, 30
Round Robin Partition
•
Es sind keine Angaben zur Aufteilung notwendig
– Die Sätze werden den Partitions “rotierend” zugewiesen
– Die Sätze sind in den Partitionen unsortiert
– Ermöglicht eine hohe Parallelität bei mehreren Einfügepunkten
•
Diese Methode ist annähernd vergleichbar mit der
Arbeitsweise von 1 Partition/ Segment der älteren ASE
Versionen
•
Sichert die Kompatibilität mit älteren Versionen
•
Die Partitionen sind automatisch in guter Balance
(Löschen ?!)
DB-Stammtisch 06/2006, 31
Risiko Partition Key Update
create table t (status int, other_cols ... )
partition by list (status)
(ptn1 values (1), ptn2 values (2), ptn3 values (3))
select * from t
ptn1
ptn2
ptn3
1
Ergebnis:
- Nur Zeilen mit 2!
- Zeilen mit 3 und 1 fehlen!
2
3
update t
set
a = 1
where a = 3
DB-Stammtisch 06/2006, 32
Partitionierung des Index
• Verbesserte Parallelität durch mehrere Eintrittspunkte
• Reduzierte root page contention
• Index size ergibt sich aus der Zeilenanzahl in jeder Partition
• Für kleinere Partitionen werden weniger index pages durchsucht
bzw. traversiert
Vor ASE 15 nicht partitionierter Index Lokaler Index auf partitionierter Tabelle
DB-Stammtisch 06/2006, 33
Globale Indizes
•
Dieser Indextyp enthält die Daten aller Partitionen der
Tabelle.
•
Bei “range”, “hash” oder “list” Partitionierung muß der
Index “nonclustered” sein.
•
Bei “round robin” Partitionierung sind Clustered oder
Nonclustered Indizes möglich.
DB-Stammtisch 06/2006, 34
Non-clustered Global Index
Create unique index ci_nkey_ckey on customer(c_nationkey,
c_custkey)
on segment4
DB-Stammtisch 06/2006, 35
Lokale Indizes
•
Jede Partition hat ihre eigene Indexstruktur
•
Kann clustered oder nonclustered sein
•
Bei einem lokalen “clustered index” sind die Daten innerhalb der Partition
sortiert.
•
Wenn ein Index lokal, unique und clustered ist:
–
–
•
müssen die Index- alle Partitionspalten enthalten
muß die Reihenfolge der Index- der der Partitionspalten entsprechen
Lokale Indizes mit den Eigenschaften “Unique clustered” sind nicht
möglich bei Tabellen die “round robin” partitioniert sind (mit mehr als einer
Partition).
DB-Stammtisch 06/2006, 36
Non-clustered Local Index
Create unique index ci_nkey_ckey on customer(c_nationkey,
c_custkey)
on segment4 local index
DB-Stammtisch 06/2006, 37
Clustered (Local) Index
•
Customer-Tabelle ist range-partitioniert auf der Spalte
c_custkey
Create unique clustered index ci_nkey_ckey on customer
(c_custkey, c_nationkey)
DB-Stammtisch 06/2006, 38
Zugriffsplan - Beispiel
DB-Stammtisch 06/2006, 39
Geringerer Einfluß von Administrationsarbeiten
•
Folgende Funktionen können auf einzelnen Partitionen
ausgeführt werden:
–
–
–
–
–
•
reorg rebuild
update statistics
truncate table
dbcc
bcp in / out
Mit der richtigen Plazierung der Partitionen auf
verschiedene physische Device sind reduzierte
Wartungszeiten möglich.
DB-Stammtisch 06/2006, 40
Verwaltung großer Datenmengen mit
•
Adaptive Server IQ
– Neues zu IQ 12.6
•
Adaptive Server Enterprise 15 – Konzepte und
Funktionalitäten für große Datenmengen
– Partitionen
– Berechnete Spalten und Funktions-basierter Index
– Verbesserungen der Anfrage-Verarbeitung
• Arbeitstabellen
• Hash-Join
– Optimierer-Steuerung
DB-Stammtisch 06/2006, 41
Computed Column Index und Function Based Index
•
Berechnete Spalten
–
–
–
–
–
Tabellenspalte wird als Ausdruck definiert
Ausdrücke können Spalten der gleichen Zeile referenzieren
Ausdrücke können Funktionen, arithmetische Operatoren, case Ausdrücke,
Globale Variable, Java Objekte, Pfadnamen oder andere Ausdrücke enthalten
Materialisiert oder nicht materialisiert (virtuelle Spalte)
Determiniert oder nicht determiniert
create
table
rental_materialized
create table
rental_not_materialized
create
table [database.[owner].] table_name
(cust_id
start_date
getdate()materialized,
(cust_id int, int,
start_date
as getdate(),
datetime )
(column
name aslast_change_dt
{datatype | {compute | as} expression
last_change_dt datetime)
[materialized | not materialized]} ...
Insert
into
rental_not_materialized
(cust_id, last_change_dt)
•
Index auf berechneten Spalten
Insert
into rental_materialized (cust_id, last_change_dt)
Values (1,getdate())
Eine oder mehrere Spalten des Schlüssels sind berechnete materialisierte Spalten
Values– (1,getdate())
Select * from rental_not_materialized
* from rental_materialized
• Select
Funktionsbasierter
Index
cust_id start_date
last_change_dt
–
Ein Index mit einem oder mehreren determinierten Ausdrücken anstelle von Spalten
cust_id
start_date
last_change_dt
--------------------------------------create [unique] [nonclustered] index index_name
--------------------------------------1
Mar 30 2005 4:00PM
Mar
16 2005 3:14PM
on [owner.]table_name
1
Mar 16 2005
3:14PM [asc
Mar
2005 3:14PM
(expression
| 16
desc][,expression
[asc | desc ]...
DB-Stammtisch 06/2006, 42
Function Based Index - Beispiel
– 100,000-Zeilen-Tabelle; Index auf (a*10)
select * from t3
where a*10 = 76543210
– Vor ASE 15: 2-3 Sekunden; 16,000 I/Os
– In ASE 15.0: < 50 Millisekunden; 3 I/Os
DB-Stammtisch 06/2006, 43
Computed Column Index und Function Based Index
•
Index auf berechneten Spalten
alter table orders
add up11 as unit*price*1.1 materialized
go
create index up11_ix on orders (up11)
go
•
Funktionsbasierter Index
create index ix1 on orders (unit*price*1.1)
select unit*price*1.1 from orders
where up11 > 200
Î benutzt Index auf up11, nicht auf (unit*price*1.1)
select unit*price*1.1 from orders
where unit*price*1.1 > 200
Î benutzt Index auf (unit*price*1.1), nicht auf up11
DB-Stammtisch 06/2006, 44
Verwaltung großer Datenmengen mit
•
Adaptive Server IQ
– Neues zu IQ 12.6
•
Adaptive Server Enterprise 15 – Konzepte und
Funktionalitäten für große Datenmengen
– Partitionen
– Berechnete Spalten und Funktions-basierter Index
– Verbesserungen der Anfrage-Verarbeitung
• Arbeitstabellen
• Hash-Join
– Optimierer-Steuerung
DB-Stammtisch 06/2006, 45
Weniger E/A für Arbeitstabellen
•
Beispiel:
select count(*), type
from titles
group by type
order by type
•
Vor Adaptive Server 15.0:
– Die Arbeitstabelle wird physisch angelegt
– 624 I/O auf der Tabelle und 29 I/O auf der Arbeitstabelle
•
Adaptive Server 15.0
– Die Arbeitstabelle wird logisch angelegt
– 624 I/O auf der Tabelle und 0 I/O auf der Arbeitstabelle
DB-Stammtisch 06/2006, 46
Weniger E/A bei Arbeitstabellen
•
Beispiel:
select sum(total_sales), max(price), type, pub_id
from titles
group by type, pub_id
•
Vor Adaptive Server 15.0:
– Eine Arbeitstabelle für group by und die Aggregate
– 624 I/O auf der Tabelle titles, 8056 I/O auf der Arbeitstabelle
•
Adaptive Server 15.0
:
– Verwendung der “hash based” Gruppierung im Speicher
– 624 I/O auf der Tabelle und 0 I/O auf der Arbeitstabelle
DB-Stammtisch 06/2006, 47
Weniger E/A bei Arbeitstabellen
•Vor ASE 15.0:
STEP 1
The type of query is
SELECT (into Worktable1).
GROUP BY
FROM TABLE titles ...
Table Scan ...
TO TABLE Worktable1.
STEP 2
The type of query is
SELECT.
FROM TABLE Worktable1
...
Table: titles scan count
1, logical reads:
(total=624) ...
Table: Worktable1 scan
count 1, logical reads:
(total=8056) ...
• ASE 15.0:
The type of query is SELECT
ROOT:EMIT Operator
...
| Using Worktable1 for
internal storage.
| |SCAN Operator
| | FROM TABLE
| | titles
| | Table Scan.
Table: titles scan count 1,
logical reads: (total=624)
physical reads: (total=0)
...
DB-Stammtisch 06/2006, 48
Verbesserungen bei der “distinct” - Klausel
•
Beispiel:
–
•
select distinct type from titles
Vor Adaptive Server 15.0:
– Eine Arbeitstabelle für distinct
– 624 I/O auf der Tabelle titles, 5125 I/O auf der Arbeitstabelle
•
Adaptive Server 15.0:
– Verwendung der “hash based” distinct Methode im Speicher
– 624 I/O auf der Tabelle und 0 I/O auf der Arbeitstabelle
DB-Stammtisch 06/2006, 49
Verbesserungen bei der OR Klausel
•
Beispiel:
select * from titles_idpr
where title_id between "BU" and "BW"
OR price between 2.99 and 4.99
•
•
-- Indizes auf title_id und price
Vor Adaptive Server 15.0:
– Der Optimierer verwendet die ODER Strategie
– Es werden 255 logische I/O benötigt
•
Adaptive Server 15.0:
– Verwendung der speicherbasierten “hash based union”
Methode
– Es werden 123 logical I/O benötigt
DB-Stammtisch 06/2006, 50
Verbesserungen bei der Union - Klausel
•
Beispiel:
select title_id, title,type from titles
where title_id < "T7"
union
select title_id, title, price from titles
where price > $45.00
-- Keine Indizes auf title_id oder price
•
Vor Adaptive Server 15.0:
–
–
•
Zwei Tabellen - Scans und eine Arbeitstabelle für distinct
Es werden 6770 I/O benötigt
Adaptive Server 15.0:
–
–
Verwendung der “hash based” union distinct Methode
Es werden 1248 I/O benötigt
DB-Stammtisch 06/2006, 51
Verbesserungen bei der Union - Klausel
Vor Adaptive Server 15.0:
Adaptive Server 15.0:
STEP 1
...
... Table Scan ...
|HASH UNION Operator has 2
children.
TO TABLE Worktable1.
|
|SCAN Operator
STEP 1
|
|
FROM TABLE
The type of query is INSERT.
|
|
titles
The update mode is direct.
|
|
Table Scan.
FROM TABLE ...
|
|
Forward Scan ...
|
|SCAN Operator
|
|
FROM TABLE
|
|
titles
|
|
Table Scan. ...
STEP 1
The type of query is SELECT.
This step involves sorting.
FROM TABLE Worktable1.
Using GETSORTED ...
DB-Stammtisch 06/2006, 52
Nicht übereinstimmende Datentypen
•
Beispiel:
–
select * from A, B where A.a=B.b
– A.a - float
– B.b - int , indiziert
– B ist eine große Tabelle
•
Vor Adaptive Server 15.0:
– Indexauswahl ist problematisch – der Optimierer kann den
Startpunkt im Index B nicht ermitteln, da A.a einen anderen
Datentyp hat
•
Adaptive Server 15.0:
– Indexauswahl konvertiert die Werte automatisch
– Index B wird für entsprechende Werte in A.a verwendet
DB-Stammtisch 06/2006, 53
Nicht übereinstimmende Datentypen
1.
set showplan on
2.
Index auf die total_sales Spalte der Tabelle titles anlegen
create index idx_ts on titles(total_sales)
3. Anfrage ausführen:
declare @x float
select @x = 3000.00
select title_id, total_sales
from titles
where total_sales = @x
The type of query is SELECT...
SCAN Operator
|
|
|
FROM TABLE
titles
Index : idx_ts ...
DB-Stammtisch 06/2006, 54
Datenverteilung
•
Beispiel:
select * from authors, publishers
where authors.state = publishers.state
and authors.state = "CO“
•
Vor Adaptive Server 15.0:
–
–
–
•
Einzelne, häufig auftretende Werte führen zu einem Ungleichgewicht
Dieses Ungleichgewicht führt dazu, daß der Optimierer die Join –
Reihenfolge falsch berechnet
Geschätzte Kosten – 5400 I/O
Adaptive Server 15.0:
–
–
Joinhistogramme werden dynamisch angelegt und dadurch die
Zugriffskosten korrekter geschätzt
Geschätzte Kosten – 360 I/O
DB-Stammtisch 06/2006, 55
Verwaltung großer Datenmengen mit
•
Adaptive Server IQ
– Neues zu IQ 12.6
•
Adaptive Server Enterprise 15 – Konzepte und
Funktionalitäten für große Datenmengen
– Partitionen
– Berechnete Spalten und Funktions-basierter Index
– Verbesserungen der Anfrage-Verarbeitung
• Arbeitstabellen
• Hash-Join
– Optimierer-Steuerung
DB-Stammtisch 06/2006, 56
Hash Join
select t1.c1, t2.c2, t2.c1, t2.c3
from t1, t2
where t1.c2 = t2.c3
t2
t1
c1
24
32
11
47
65
c1
c2
c3
c4
1
2
3
4
5
6
88
77
66
55
44
33
ABC
BCA
DEF
XYZ
ABC
DEF
ZZ
YY
XX
WW
VV
TT
c2
ABC
XYZ
DEF
GHI
BCA
DB-Stammtisch 06/2006, 57
Hash Join
Hash
Tabelle
t2
c1
c2
1
2
3
4
5
6
7
88
77
66
55
44
33
…
c3 t1
c4
C6
1, ABC
2, BCA
CF
3, DEF
6, DEF
10B
4, XYZ
5, ABC
c1ABCc2 ZZ
BCA
YY
24
DEF ABCXX
32
XYZ XYZWW
11
ABC DEFVV
DEF GHITT
47
65
BCA
t1.c1
t1.c2
t2.c1
t2.c3
24
24
…
ABC
ABC
1
5
ABC
ABC
DB-Stammtisch 06/2006, 58
Sehr große Datenmengen
•
Beispiel:
–
–
–
–
select count(*), count(T1.price)
from titles T1, titles T2
where isnull(T1.price, 0) = isnull(T2.price, 0)
and rtrim(T1.title_id) <> rtrim(T2.title_id)
•
Kein Index, Join mit Ausdrücken und Ergebnis ist sehr groß
•
Vor Adaptive Server 15.0:
–
–
•
Der Optimierer wählt den nested loop
Benötigte Zeit 41,106 ms, ungefähr 3.1 Millionen I/O
Adaptive Server 15.0:
–
–
Der Optimierer wählt den Hash bzw. Merge Join
Beim Hash Join benötigt die Anfrage 320 ms mit 1248 I/O
DB-Stammtisch 06/2006, 59
Verwaltung großer Datenmengen mit
•
Adaptive Server IQ
– Neues zu IQ 12.6
•
Adaptive Server Enterprise 15 – Konzepte und
Funktionalitäten für große Datenmengen
– Partitionen
– Berechnete Spalten und Funktions-basierter Index
– Verbesserungen der Anfrage-Verarbeitung
• Arbeitstabellen
• Hash-Join
– Optimierer-Steuerung
DB-Stammtisch 06/2006, 60
Einstellen der Optimierungsziele und Gültigkeit
allrows_oltp
Zur schnellen Optimierung für OLTP wird nur eine
begrenzte Anzahl von Verfahren benutzt
e.g. nested loop join, keine Parallelisierung
allrows_mix
(default) generiert Pläne für gemischte Lastprofile
hauptsächlich allrows_oltp + merge joins, Parallelisierung
allrows_dss
generiert optimale Pläne für hoch-komplexe DSS
hauptsächlich allrows_mix + hash joins
Server:
sp_configure "optimization goal", 0, "allrows_oltp“
Session:
set plan optgoal allrows_dss
Query:
select * from A order by A.a
plan "(use optgoal allrows_oltp)"
DB-Stammtisch 06/2006, 61
Optimierungsaufwand bei großer Join-Anzahl
select * from T1, T2, ... T50
where T1.a = T2.a
AND T2.a = T3.a ...
AND T49.a = T50.a
•
Vor Adaptive Server 15.0:
die Optimierung der Join-Reihenfolge kann einen wesentlichen Zeitanteil benötigen
(~ TabAnz!, 12! = 479M)
set table count n
•
Adaptive Server 15.0
ermöglicht die Kontrolle über den Anteil der Optimierungszeit:
Server:
sp_configure "optimization timeout limit", 10
Session: set plan opttimeoutlimit 10
Query:
select * from A order by A.a
plan "(use opttimeoutlimit 10)“
Prozentanteil der Gesamtbearbeitungszeit, die der Optimierer zur
Erstellung des Planes verwenden darf (Standard: 10%)
DB-Stammtisch 06/2006, 62
Optimierer Kostenmodell ab ASE 15
•
Kostenschätzung im Optimierer:
– Kostenschätzung vor ASE 15:
• Logischer Zugriff: 2
• Physischer Zugriff: 18
– ASE 15: bessere Annäherung an die Realität:
• Logischer Zugriff : 2
• Physischer Zugriff : 25
• CPU - je Zeile, die gesucht oder verglichen wird: 0.1
DB-Stammtisch 06/2006, 63
Optimierungsaufwand - Beispiel
select *
from authors, titleauthor, titles_idpr, titles_pridtitl,
publishers, roysched, titles, salesdetail, sales, stores,
Optimization Timeout
Parse/Compile CPU Time Execution Time
discounts, blurbs
(%)
where titles.title_id
= roysched.title_id
AND
10titles.title_id = titles_idpr.title_id
1600 ms
10 ms
AND titles.title_id = titles_pridtitl.title_id
50titles.pub_id = publishers.pub_id
22,800 ms
3 ms
AND
AND blurbs.au_id = authors.au_id
AND titleauthor.au_id = authors.au_id
AND
titleauthor.title_id
= titles.title_id
• Zusätzlicher
Optimierungsaufwand
führt zu besseren
AND Ausführungsplänen.
titles.title_id = salesdetail.title_id
AND salesdetail.ord_num = sales.ord_num
AND
salesdetail.stor_id
sales.stor_id
• Die
Einsparungen bei der= Ausführung
sollten natürlich mit dem
AND zusätzlichen
sales.stor_id
= stores.stor_id
Optimierungsaufwand
abgewogen werden.
AND stores.stor_id = discounts.stor_id
Set option show brief
DB-Stammtisch 06/2006, 64
Automatisierung für Update Statistics
DB-Stammtisch 06/2006, 65
Archive Database Access (ADA)
– DBCC checks directly on database dumps (archives)
•
Ability to run DBCC directly on database dumps without having
the database fully loaded.
– Object Level Recovery
•
Customers have long requested the ability to retrieve data from
a dump that may have been accidentally deleted or corrupted.
•
Current method used by customers: reload of the entire
database which is time consuming
DB-Stammtisch 06/2006, 66
Archive Database Access (ADA)
Database Dump File
ASE
(acts as a DB device)
Archive Database
Real Database
Real Database
Real Database
Database Devices
DB-Stammtisch 06/2006, 67
ADA - Object Level Recovery
– Archive databases can be accessed with regular SQL, as
any other database
•
SELECT * INTO <table> FROM <Archive db>..<table>
•
Cross-server: use CIS (proxy tables) and SELECT INTO
DB-Stammtisch 06/2006, 68
Erreichte Effekte
Laufzeit
Laufzeit
Zugriffsanzahl
DB-Stammtisch 06/2006, 69
Query Processing – Die Änderungen
Parse Step
Transact-SQL via TDS
(sprocs stored in text
in syscomments table)
Parse Tree
Parsing
Normalization Step
Resolve Names
Normalize
Resolve Views, Aggregates &
Subqueries
Protection Map
Query Plan
Generation
Compile
Execution Engine
New in
15.0!
Execute
Access
Methods
Utilities
New in
15.0!
New in
15.0!
New in
15.0!
New in
15.0!
Query Tree
(“p-code”-like tree stored
in sysprocedures table)
Query Plan
( only resides in memory)
Managers
(buffer, lock, etc.)
Transaction
Management
Data and other Internal Resources
DB-Stammtisch 06/2006, 70