DOAG Vortrag 30. März 2004 / Folie 1Peter van Garsel
CREATE | ALTER SEGMENTSErfahrungsbericht Oracle9i R2
Features im Bereich Data Warehousing
eWorkIng GmbHPeter van Garsel
Bad Homburg
DOAG Vortrag 30. März 2004 / Folie 2Peter van Garsel
Wer ist eWorkIng GmbH?
• Freiberufler ab 1996 • GmbH Gründung 2000• Oracle Certified Professional 8i und 9i• Mehrjährige Erfahrungen in Entwicklung-
Produktion data warehousing (Internet Provider)
DOAG Vortrag 30. März 2004 / Folie 3Peter van Garsel
Schlüsselworte
• 24 * 7 Betrieb• COMPRESS• [CREATE | ALTER
SEGMENT] [TABLE | INDEX]
• DBMS_METADATA, DBMS_REDEFINITION
• DBMS_SPACE• DBMS_STATS• MAINTAINCE
• MONITORING• MOVE• Oracle9i• PCTFREE• PERFORMANCE• TEMPORAY TABLES• UTL_FILE• V$OBJECT_USAGE
DOAG Vortrag 30. März 2004 / Folie 4Peter van Garsel
Argenda• Business Tasks / www (Wie Wie Wie)
• CREATE TABLE AS SELECT
• DML / PARALLELES DML
• ALTER TABLE MOVE (8i)
• ALTER INDEX rebuild_clause (8i / 9i)
• TEMPORAY TABLES (8i)
• EXTERNAL TABLES (9i)
• INDEX KEY KOMPRESSION 8i / 9i)
• TABLE DATA_SEGMENT_KOMPRESSION (9iR2), TPC (H und R)
• TABLE | INDEX MONITORING
• ORACLE SUPPLIED PACKAGES
DOAG Vortrag 30. März 2004 / Folie 5Peter van Garsel
Business Tasks
Business Tasks definieren die erforderlichen Segmentoptionen / Segmentvarianten;
• Wie werden Daten generiert?• Wie werden Daten verändert?• Wie werden Daten genutzt?• Ist Option produktiv nutzbar?
DOAG Vortrag 30. März 2004 / Folie 6Peter van Garsel
CREATE TABLE AS SELECT 1
• Welche Eigenschaften soll das Segment haben? (Default Wert für PCTFREE 10 PCTUSED 40)
• INSERT /*+ APPEND*/ PARALLEL n, Operation startet 2 * n parallele Prozesse (Lese + Sortier Prozess). Jeder Sportierprozess generiert mindestens ein EXTENT. Die Ergebnisse der parallelen Prozesse (= N * EXTENTS) werden “einfach” zusammengeführt.
DOAG Vortrag 30. März 2004 / Folie 7Peter van Garsel
CREATE TABLE AS SELECT 2
• Views [USER | ALL | DBA ] _TABLES PCT_FREE; PCT_USED, DEGREE; PARALLEL, COMPRESSION …
• Es werden nur die aktuellen Werte angezeigt!ALTER TABLE my_table
hat keinen Einfluss auf Speicherung vorhandener Segs. DBMS_SPACE (8i) ØUNUSED_SPACE ØFREE_BLOCKS
DOAG Vortrag 30. März 2004 / Folie 8Peter van Garsel
Einfluß von DML – Operationen
• DML (INSERT / UPDATE / DELETE) Es existiert in der Regel kein automatisches Verkleinern von Tabellen. Nicht genutzte Extents werden nicht wieder für andere Segmente freigegeben.
• Ein FULL TABLE SCAN liest alle Segmente (Blöcke) einer Tabelle zur high water mark (HWM) unabhängig wie der Füllgrad der Blöcke ist.
AusnahmeALTER [TABLE | INDEX] segment_name
DEALLOCATE_UNUSED_CLAUSE (8)
DOAG Vortrag 30. März 2004 / Folie 9Peter van Garsel
Alter TABLE MOVE (8i)
• Änderung von TABLE / PARTITION Segmente via ALTER TABLE MOVE …
• Vollständiger Wiederaufbau / table comments, col. comments, grants ... bleiben erhalten
• Änderung Tablespace• Änderung storage_attributes (pct_free ...)• Parallel – Clause möglich
Randbedingungen• Rebuild Indizes erforderlich, Status unusable• Index organized Tables IOT - Online!• NICHT bei LOB - Columns möglich
DOAG Vortrag 30. März 2004 / Folie 10Peter van Garsel
ALTER INDEX rebuild_clause 8
Vorteile• Option Online Reorganisation möglich (8i)• Änderung physical_attributes_clause (pctfree…)• Änderung Tablespace• Index Statistiken können „just in time“ generiert werden• Komprimierung möglich (8i)
Randbedinungen• Rebuild nicht möglich für TEMPORARY TABLES• Rebuild pro Partition erforderlich
DOAG Vortrag 30. März 2004 / Folie 11Peter van Garsel
TEMPORAY Tables (8i)
Vorteile• Datensätze werden implizit einer Transaktion oder einer Session
zugeordnet.• Deutliche Reduktion Redo Log-Volumen
Randbedingungen• Keine partitionierten, geclusterten, IOT - Tables• Keine foreign key constraints• Keine Spalten mit nested table oder varray type.• Keine LOB clause; keine tablespace, storage_clause,
logging_clause, monitoring | nomonitoring, or lob_index_clause.• Keine parallelen Queries • Kein paralleles DML
DOAG Vortrag 30. März 2004 / Folie 12Peter van Garsel
EXTERNAL TABLE (9i) 1
• Zugriff auf Text – Files via SQL- Befehl; SELECT FROM WHERE GROUP BY …
• Syntax, Mischung von CREATE TABLE und SQL -Loader Controlfile
• Änderung der Datenquelle ALTER TABLE my_ext_tab directory location
• PARALLEL QUERY möglich• CACHE (HINT) Performancegewinn bei erneutem Lesen.
Randbedingungen• DML Befehle (INSERT / UPDATE / DELETE) nicht
möglich• CREATE INDEX nicht möglich
DOAG Vortrag 30. März 2004 / Folie 13Peter van Garsel
EXTERNAL TABLE (9i) 2
Hinweise• Views
- [ALL | DBA] _DIRECTORIES- [USER | ALL | DBA] _EXTERNAL_LOCATIONS- [USER | ALL | DBA] _TABLES
• ORACLE SUPP. Package UTL_FILE wurde um einige Features erweitert. FREMOVE, FCOPY FILE, …
• EXTERNAL PROCEDUREs können als (teilweiser) Ersatz von SH / Perl / - Scripten dienen.
• pro PARALLEL QUERY Prozeß ein logfile• Lesen aus komprimierten File möglich
DOAG Vortrag 30. März 2004 / Folie 14Peter van Garsel
INDEX KEY COMPRESSION 8i
Vorteile• Reduzierter Plattenplatzbedarf• Reduziertes Backup – Volumen / schnelleres Backup• Reduzierte DB_BLOCKS, effizientere Nutzung
Speicherstrukturen• Performancegewinn
Randbedingungen;• Nicht partitionierte und non – unique Indizes 8i
(aufgehoben bei 9i)• Unique Indizes mit mindestens zwei Spalten• Keine Verwendung für BITMAP Indizes möglich• Geringfügig höhere CPU - Last
DOAG Vortrag 30. März 2004 / Folie 15Peter van Garsel
DATA_SEGMENT_COMPRESSION (9iR2) 0
• CREATE TABLE test (d1 DATE)• ALTER TABLE test compress• ALTER TABLE test
ADD (d2 DATE)ORA-22856: cannot add columns to object tables Metalink Note:228082.1 9i R2 New Feature: Data
Segment Compression
DOAG Vortrag 30. März 2004 / Folie 16Peter van Garsel
DATA_SEGMENT_COMPRESSION (9iR2) 1
DOAG Vortrag 30. März 2004 / Folie 17Peter van Garsel
DATA_SEGMENT_COMPRESSION (9iR2) 2
Vorteile• Reduzierten Plattenplatzbedarf• Reduziertes Backup – Volumen / schnelleres
Backup• Reduzierten Speicherbedarf (buffer cache),
komprimierte Blöcke sind auch in SGA komprimiert
• Table (heap, partitioned (Range, List), nested) …• Performancegewinn bei DML & SELECT *
FROM ...
DOAG Vortrag 30. März 2004 / Folie 18Peter van Garsel
DATA_SEGMENT_COMPRESSION (9iR2) 3
Randbedingungen• Nicht für HASH partionierte Tables, HASH | LIST
SUBPARTITIONS• Nicht für index-organized tables• Nicht für overflow Segments• Nicht für external Tables• Geringfügig höhere CPU – Last
DOAG Vortrag 30. März 2004 / Folie 19Peter van Garsel
DATA_SEGMENT_COMPRESSION (9iR2) 4
Einsatzgebiete• Sinnvoller Einsatz bei redundaten Daten• SQL for AGGREGATION GROUP BY, ROLLUP,
CUBE …• MATERIALIZED VIEWs• OLTP Bereich für “Historische” Daten
DOAG Vortrag 30. März 2004 / Folie 20Peter van Garsel
DATA_SEGMENT_COMPRESSION (9iR2) 5
Erfahrungen TPC – H / TPC - R
Normal. Schema
79,0
17,0
115,0
49,0
14,0
82,0
0
30
60
90
120
Lineitem Orders Entire DB
Not Compressed [GByte] Compressed [GByte]
DOAG Vortrag 30. März 2004 / Folie 21Peter van Garsel
DATA_SEGMENT_COMPRESSION (9iR2) 6
Erfahrungen TPC – H / TPC - R
Star Schema
27,018,8
7,5
55,0
8,6 6,51,9
18,0
0
15
30
45
60
Daily Sales Weekly Sales Weekly Aggr. Entire DB
Not Compressed [GByte] Compressed [GByte]
DOAG Vortrag 30. März 2004 / Folie 22Peter van Garsel
Monitoring TABLE SEGMENTS 1
„Specify MONITORING if you want modification statistics to be collected on this table. These statistics are estimates of the number of rows affected by DML statements over a particular period of time. They are available for use by the optimizer or for analysis by the user.” [ORA03]ALTER TABLE my_table
[NOMONITORING | MONITORING]
DOAG Vortrag 30. März 2004 / Folie 23Peter van Garsel
Monitoring TABLE SEGMENTS 2
• Data Dictionary USER_TAB_MODIFICATIONS
DOAG Vortrag 30. März 2004 / Folie 24Peter van Garsel
MONITORING INDEX - Segments 1
• ALTER INDEX my_index [NOMONITORING|MONITORING]USAGE
• Data Dictionary v$object_usage• Columns
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING,Datentyp!!! VARCHAR2END_MONITORING, Noch nicht gefüllt!?
DOAG Vortrag 30. März 2004 / Folie 25Peter van Garsel
MONITORING INDEX - Segments 2
-- ----------------------------------------------------------- Auszug aus Metalink - Note 160712.1-- Viewing All Indexes Being Monitored Under Another User's-- --------------------------------------------------------
select io.name, t.name,decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),ou.start_monitoring,ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usageouwhere i.obj# = ou.obj#and io.obj# = ou.obj#and t.obj# = i.bo#
DOAG Vortrag 30. März 2004 / Folie 26Peter van Garsel
Oracle supplied packages (8i, 9i)) 1
• DBMS_STAT vs ANALYZE [TABLE | INDEX]• Oracle Documentation, recommends
DBMS_STAT [ORA06, page 138]• GATHER_TABLE_STATS, Änderungen
GATHER_INDEX_STATS mit 9i ist Parallelisierung möglich
DOAG Vortrag 30. März 2004 / Folie 27Peter van Garsel
Oracle supplied packages (8i, 9i)) 2
DBMS_SPACE (8?, 8i):• UNUSED_SPACE, Returns information about
unused space in an object (table, index, or cluster).
• FREE_BLOCKS, Returns information about freeblocks in an object (table,index, or cluster).
• SPACE_USAGE Returns information about free blocks in a bitmapped segment.
DOAG Vortrag 30. März 2004 / Folie 28Peter van Garsel
Hints / Bücher 1
• B01, Stürner, Günther, Oracle 8i, Der objekt-relationale Datenbank Server (Release 8.0 & 8.1), 2000, 12/2000. 583 Seiten, dbms publishing, 3-930124-01-7
• B02, Herrmann, Uwe / Lenz, Dierk / Unbescheid, Günter / Ahrends, Johannes, Oracle 9i für den DBA, Effizient konfigurieren, optimieren und verwalten, 2002, 736 Seiten. CD-ROM [Edition Oracle], Addison-Wesley, 3-8273-1559-X
• B03, Kyte, ThomasEffective Oracle by design, 2003, 0-07-223065-7
• B04, Kyte ThomasORACE ONE TO ONE…
DOAG Vortrag 30. März 2004 / Folie 29Peter van Garsel
HINTs / Links
• L01 www.google, Suchbegriff, "oracle compresstable benchmark"
• L02 www.otn, Suchbegriff, table compression o9ir2_compression_twp von 2002o9ir2_compression_twp von 2003
• L03 www.tpc.org• L04 OTN, Suchbegriff "Secrets of Oracle9i
Database Decision Speed" - Table CompressionIn Action By Meikel Poess and Hermann Baer
DOAG Vortrag 30. März 2004 / Folie 30Peter van Garsel
Vielen Dank / Qs & As
eWorkIng GmbH Peter van Garsel
Bad HomburgMail: [email protected]
Top Related