CREATE - doag.org Rhein... · Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 19...

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


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)


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


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


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?


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.


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


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)


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


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


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


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


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


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


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



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 ...



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



Einsatzgebiete• Sinnvoller Einsatz bei redundaten Daten• SQL for AGGREGATION GROUP BY, ROLLUP,

CUBE …• MATERIALIZED VIEWs• OLTP Bereich für “Historische” Daten



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]



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]


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]


Monitoring TABLE SEGMENTS 2

• Data Dictionary USER_TAB_MODIFICATIONS


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!?


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#


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


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.


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…


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


Vielen Dank / Qs & As

eWorkIng GmbH Peter van Garsel

Bad HomburgMail: [email protected]

CREATE - doag.org Rhein... · Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 19...

Documents

Transcript of CREATE - doag.org Rhein... · Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 19...