DOAG Vortrag 30. März 2004 / Folie 3 Peter van Garsel
Werbung
CREATE | ALTER SEGMENTS Erfahrungsbericht Oracle9i R2 Features im Bereich Data Warehousing eWorkIng GmbH Peter van Garsel Bad Homburg Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 1 Wer ist eWorkIng GmbH? • • • • Freiberufler ab 1996 GmbH Gründung 2000 Oracle Certified Professional 8i und 9i Mehrjährige Erfahrungen in EntwicklungProduktion data warehousing (Internet Provider) Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 2 Schlüsselworte • 24 * 7 Betrieb • COMPRESS • [CREATE | ALTER SEGMENT] [TABLE | INDEX] • DBMS_METADATA, DBMS_REDEFINITION • DBMS_SPACE • DBMS_STATS • MAINTAINCE Peter van Garsel • • • • • • • • MONITORING MOVE Oracle9i PCTFREE PERFORMANCE TEMPORAY TABLES UTL_FILE V$OBJECT_USAGE DOAG Vortrag 30. März 2004 / Folie 3 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 4 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? Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 5 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. Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 6 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 7 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. Ausnahme ALTER [TABLE | INDEX] segment_name DEALLOCATE_UNUSED_CLAUSE (8) Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 9 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 10 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 11 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 12 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 13 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 14 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 15 DATA_SEGMENT_COMPRESSION (9iR2) 1 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 16 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 ... Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 17 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 18 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 19 DATA_SEGMENT_COMPRESSION (9iR2) 5 Erfahrungen TPC – H / TPC - R Normal. Schema Not Compressed [GByte] Compressed [GByte] 115,0 120 90 60 82,0 79,0 49,0 30 17,0 14,0 0 Lineitem Peter van Garsel Orders Entire DB DOAG Vortrag 30. März 2004 / Folie 20 DATA_SEGMENT_COMPRESSION (9iR2) 6 Erfahrungen TPC – H / TPC - R Star Schema Not Compressed [GByte] Compressed [GByte] 55,0 60 45 30 27,0 18,8 15 8,6 18,0 6,5 7,5 1,9 0 Daily Sales Peter van Garsel Weekly Sales Weekly Aggr. Entire DB DOAG Vortrag 30. März 2004 / Folie 21 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] Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 22 Monitoring TABLE SEGMENTS 2 • Data Dictionary USER_TAB_MODIFICATIONS Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 23 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!!! VARCHAR2 END_MONITORING, Noch nicht gefüllt!? Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 24 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_usage ou where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo# Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 25 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 Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 26 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 free blocks in an object (table,index, or cluster). • SPACE_USAGE Returns information about free blocks in a bitmapped segment. Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 27 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. CDROM [Edition Oracle], Addison-Wesley, 3-8273-1559-X • B03, Kyte, Thomas Effective Oracle by design, 2003, 0-07-223065-7 • B04, Kyte Thomas ORACE ONE TO ONE… Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 28 HINTs / Links • L01 www.google, Suchbegriff, "oracle compress table benchmark" • L02 www.otn, Suchbegriff, table compression o9ir2_compression_twp von 2002 o9ir2_compression_twp von 2003 • L03 www.tpc.org • L04 OTN, Suchbegriff "Secrets of Oracle9i Database Decision Speed" - Table Compression In Action By Meikel Poess and Hermann Baer Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 29 Vielen Dank / Qs & As eWorkIng GmbH Peter van Garsel Bad Homburg Mail: [email protected] Peter van Garsel DOAG Vortrag 30. März 2004 / Folie 30
