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: p.garsel@eworking.de