PDF-Version
Werbung
DOAG Konferenz Nürnberg 2007 Tabellen Tuning oder Pimp my Table Impressum Oracle Schulung (SQL, DBA, PL/SQL, Security, 11g, Tuning, Backup & Recovery u.v.m.) Oracle Consulting & Support Oracle Entwicklung & Lizenzvertrieb Marco Patzwahl MuniQSoft GmbH Grünwalder Weg 13 a D-82008 Unterhaching b. München Telefon: E-Mail Internet: MuniQSoft GmbH +49 (0)89 679090-40 [email protected] www.muniqsoft.de DOAG 2007 www.plsql.de Tabellentuning Seite 2 Einleitung Dieser Vortrag soll die evtl. noch unbekannten Seiten beim Tabellentuning beleuchten Der Fokus liegt hier auf Speicherparametern, Block-Größen und diversen Tricks MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 3 Tabellen-Speicherparameter Beim Erstellen einer Tabelle können diverse Speicherparameter angegeben werden So kann der Benutzer z. B. folgende Parameter einstellen INITIAL, NEXT (Bei Locally Managed TBS vom Tablespace abhängig) MINEXTENTS, MAXEXTENTS PCTINCREASE, PCTUSED, FREELISTS und FREELIST GROUPS (bei SEGMENT MANAGEMENT AUTO Tablespace von Oracle gesetzt) PCTFREE (7.0 – 11.1) MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 4 SQL Syntax für relationale Tabellen CREATE TABLE [schema.]<tab_name> (<column datatype> [ default <expr>] [<col_constraint>], <column datatype> [ default <expr>] [<col_constraint>] ,...) [TABLESPACE <tablespace_name>} [STORAGE ( INITIAL n[K|M] NEXT n[K|M] MINEXTENTS n MAXEXTENTS n PCTINCREASE n)] [PCTFREE n] [PCTUSED n] [LOGGING | NOLOGGING] [CACHE | NOCACHE] [COMPRESS|NOCOMPRESS] /* ab 9.2 */; MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 5 Wahl des Spaltentyps Welche Länge wird zur Speicherung einer Spalte verwendet ? Datentyp MuniQSoft GmbH Speicherlänge DATE 7 TIMESTAMP 11 ROWID 10 CHAR(100):='A' 100 VARCHAR2(100):='A' 1 NVARCHAR2(100):='A' 1 NUMBER:=123456.78 5 NUMBER(12,2):=123456.78 5 FLOAT:=123456.78 5 BINARY_FLOAT:=123456.78 4 BINARY_DOUBLE:=123456.78 8 DOAG 2007 Tabellentuning Seite 6 Zeilenaufbau Jede Zeile im Block setzt sich aus dem Row-Header und den jeweiligen Spaltenwerten zusammen, wobei vor der Spalte die jeweilige Länge des Spaltenwerts steht. Bei VARCHAR2-Spalten wird für die Speicherung von bis zu 250 Byte langen Werten ein Byte benötigt, darüber drei Bytes. Block Nr. 4711 MuniQSoft GmbH Row-Header Länge Wert Länge Wert Länge Länge Wert 3 Byte 4 Test 5 Marco 0 3 BMW DOAG 2007 Tabellentuning Seite 7 Spalten Reihenfolge der Spalten Bei unserem Test mit 50 CHAR(200) Spalten war kein Unterschied zwischen Spalte 1 und 50 bei Updates zu beobachten Spalten die oft NULL Werte enthalten sollten ganz am Ende stehen Spaltendatentypen VARCHAR2 und NUMBER sind gleich schnell, wenn sie Zahlen aufnehmen sollen MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 8 Vor und Nachteile des Tabellentyps Vorteile Relationale per- Flexibel, sehr viele Datentypen und manente Tabelle Optionen möglich. Nachteile langsam Partitionierte Tabelle Administration der Abfrage auf einzelne Partitionen Partitionen notwendig schneller. Teile der Tabelle können mit DROP/TRUNCATE gelöscht werden. Index Organisierte Tabelle Ausgabe bereits in sortierter Reihenfolge. Anzahl der Indizes beschränkt. Nachträgliche Inserts & Updates ungünstig Temporäre Tabelle Schnellster Tabellentyp. Wird auf dem Temp-Tablespace angelegt. Transaktionsverlauf nicht in den Redologs => Kein Recovery im Fehlerfall! Cluster Tabelle Tabellen die mit Join verknüpft werden, können hier im gemeinsamen Block gespeichert werden => schneller Auch wenn nur eine Tabelle benutzt wird, wird die andere „mitgezogen“ => Overhead MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 9 Wahl des Tablespace Möglichst einen eigenen Tabellen-Tablespace benutzen (Nicht SYSTEM oder SYSAUX) Speicherparameter so weit möglich auf diesem Tablespace einheitlich wählen (INITIAL, NEXT) Ab 8.1.6: Locally Managed Tablespaces bevorzugen Ab 9i: SEGMENT SPACE MANAGEMENT AUTO Option bei Tablespaces benutzen MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 10 Speicherparameter INITIAL & NEXT Wenn auf der Tabelle häufig Full Table Scans durchgeführt werden, ist es besser, sie in einem Extent anzulegen (wenn nur eine Platte zur Verfügung steht) Es muss dann ausgerechnet werden, wie groß die Tabelle ist (oder sein wird) Beispiel: 1 Million Zeilen à 100 Bytes = 100 MB Wenn die Tabelle nicht immer im Ganzen gelesen wird, ist es günstiger, alle Extents im Tablespace gleich groß zu machen, also INITIAL=NEXT Bei Locally Managed Tablespaces wird die Angabe der Parameter ignoriert und stattdessen die Bitmap Einheit verwendet (Default 64K) MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 11 Speicherparameter PCTFREE/ PCTUSED Statische Tabellen Æ PCTFREE klein (0 – 5%) Dynamische Tabellen Æ PCTFREE groß (10-30%) Wenn zu viel "Luft" in den Blöcken ist, weil PCTFREE zu groß gewählt wurde, werden unnötigerweise mehr Blöcke gelesen oder geschrieben PCTUSED sollte ca. 60% betragen. Ab 9i kann der Parameter weggelassen werden, wenn der Tablespace mit der Option SEGMENT SPACE MANAGEMENT AUTO angelegt wurde MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 12 Speicherparameter FREELISTS Die FREELISTS ist eine Übersicht der noch für INSERTOperationen verfügbaren Blöcke (1 = benutzt, 0 = frei) Wenn zu viele INSERTS in einem Block gleichzeitig aktiv sind, kann es zu Wartezuständen kommen Passen Sie deshalb hier die Anzahl der FREELISTs an die Anzahl der parallel laufenden INSERTs auf ein Objekt an CREATE TABLE EMP(a VARCHAR2(200)) STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 PCTINCREASE 0 FREELISTS 16); ALTER TABLE emp STORAGE (FREELISTS 16); MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 13 FREELISTS ab 9i Ab 9i werden die FREELISTs von Oracle automatisch verwaltet 00 => 75% frei im Block 01 => 50% frei im Block 10 => 25% frei im Block 11 < 25% frei im Block Ab 9i kann der Parameter weggelassen werden, wenn der Tablespace mit der Option SEGMENT SPACE MANAGEMENT AUTO angelegt wurde MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 14 Speicherparameter FREELIST GROUPS Eigentlich für Real Application Cluster konzipiert, jedoch oft auch bei Single-Instanzen von Vorteil sind die FREELIST GROUPS. Hier werden komplette Blöcke nach dem Segment Header für die FREELISTS reserviert. Das führt zu einer weiteren Reduzierung von Wartezuständen bei vielen gleichzeitigen INSERTs. CREATE TABLE t(c VARCHAR2(200)) STORAGE (INITIAL 1M PCTINCREASE 0 FREELIST GROUPS 2 FREELISTS 4); Ab 9i kann der Parameter weggelassen werden, wenn der Tablespace mit der Option SEGMENT SPACE MANAGEMENT AUTO angelegt wurde. MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 15 Speicherparameter BUFFER_POOL Wenn eine Tabelle (oder ein Index) mit diesem Attribut ausgestattet ist, bekommt sie Ihren eigenen Speicherbereich im Database Buffer Cache (DBBC) CREATE TABLE test (d date) STORAGE (buffer_pool keep); ALTER TABLE test STORAGE (buffer_pool recycle); CREATE INDEX ind ON test(d) STORAGE (buffer_pool keep); ALTER INDEX ind STORAGE (buffer_pool recycle); Hinweis: Für UNDO-Segmente ist die Buffer-Pool Klausel nicht erlaubt MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 16 Speicherparameter BUFFER_POOL Für die privilegierten Tabellen muss dann noch ein entsprechender Keep- bzw. Recycle-Speicherbereich neben dem Default-DBBC eingerichtet werden: Ab Version 9i ALTER SYSTEM SET db_keep_cache_size=16m; ALTER SYSTEM SET db_recycle_cache_size=4m; MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 17 Speicherparameter CACHE/NOCACHE Tabellen länger im Cache halten: ALTER TABLE emp CACHE; Tabellen an das Ende der LRU-Liste setzen: ALTER TABLE emp NOCACHE; Mittels Hint kann eine Tabelle auch an den Anfang bzw. an das Ende der LRU Liste gesetzt werden: SELECT /*+ CACHE(scott.emp) */ * FROM scott.emp; SELECT /*+ NOCACHE(scott.emp) */ * FROM scott.emp; MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 18 Speicherparameter NOLOGGING Hier werden diverse Operationen der Tabelle nicht mehr vollständig im Redolog protokolliert werden Folgende Operationen werden nicht mehr protokolliert: Direct load mittels SQL*Loader Direct load mittels INSERT (INSERT /*+ APPEND */ INTO <tab> SELECT …) CREATE TABLE ... AS SELECT ... (CTAS) CREATE INDEX ... ALTER TABLE ... MOVE PARTITION und SPLIT PARTITION ALTER INDEX ... SPLIT PARTITION ALTER INDEX ... REBUILD und REBUILD PARTITION INSERT, UPDATE, und DELETE auf non-internal LOBs in NOCACHE NOLOGGING MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 19 Speicherparameter COMPRESS Diese Funktion ist ab Version 9.2 verfügbar Es werden jedoch nur beim ersten Laden der Daten in die Tabelle mehrfache Einträge in den Blöcken komprimiert CREATE TABLE <tab>(...) TABLESPACE <tbs> COMPRESS AS SELECT * FROM <tab>; Normale Inserts oder Updates werden bis Version 11g nicht komprimiert abgespeichert Workaround (auch für nachträgliches Einschalten): ALTER TABLE <tab> MOVE TABLESPACE <tbs> COMPRESS; MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 20 Tabelleninhalte komprimieren Bei einer Testtabelle mit 2.4 GB Volumen wurde die Tabelle durch COMPRESS auf 1.6 GB reduziert Blockheader Freier Platz Symbol-Tabelle Tabellendaten MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 21 Tabelleninhalte komprimieren SELECT sollte etwas schneller werden, da weniger I/O benötigt wird INSERT/UPDATE benötigen bei gleichen Spaltenwerten länger, da die Symbol-Tabelle aufgebaut und ständig geprüft werden muss DELETE benötigt ebenfalls länger Ein SHRINK ist auf einer komprimierten Tabelle nicht möglich MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 22 Größe der Tabelle minimieren Beispiel: Tabelle dba_source mit verschiedenen Parametern Durchschnittliche Zeilenlänge: 120 Byte Blockgröße 8K Blockgröße 16K Blockgröße 4K Nutzbarer Bereich (ca.) 6,9K 14K 3,3K PCTFREE 10 NOCOMPRESS 50M 42M 42M PCTFREE 0 NOCOMPRESS 46M 36M 38M PCTFREE 0 COMPRESS 37M 26M 29M MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 23 Tabellen-Tuning (Constraints) Constraints ausschalten (DISABLE) beschleunigt UPDATE/ INSERT & Import: ALTER TABLE <tab> DISABLE CONSTRAINT <cons>; Achtung: Sollte ein Primary Key oder Unique Key Constraint ausgeschalten werden, wird der dazugehörige Index gelöscht! Ausnahme: ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX /* ab 9i */; Einschalten kann man einen Constraint wieder mittels: ALTER TABLE <tab> ENABLE CONSTRAINT <cons>; Sollte der Index gelöscht worden sein (s.o.) wird er neu angelegt! Achtung: Ohne Constraints kann die Applikationslogik zerstört werden ! MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 24 Tabellen-Tuning (Trigger) Trigger ausschalten beschleunigt UPDATE/ INSERT/ DELETE & Import: ALTER TABLE <tab> DISABLE ALL TRIGGERS; Einschalten aller Trigger einer Tabelle wieder mittels: ALTER TABLE <tab> ENABLE ALL TRIGGERS; Achtung: Durch das Ausschalten der Trigger kann die Applikationslogik zerstört werden ! Statement Trigger sind schneller als Row-Trigger MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 25 Tabellen-Tuning (Indizes) Bei größeren INSERTs ist es meist schneller, die Indizes der Tabelle zuerst zu löschen und später dann wieder neu anzulegen Jedoch ist während der INSERTs dann kein Indexzugriff von anderen Sessions möglich => Full Table Scan Indizes können bei Bedarf mit den Optionen NOLOGGING, COMPRESS und ONLINE schnell erzeugt werden Jedoch sollten auch die "Nebenwirkungen" beachtet werden MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 26 Tabellen-Tuning Chained Rows vermeiden (Abspeicherung in mehreren Satzstücken, die in verschiedenen Datenblöcken liegen, da Satzgröße länger als der Block ist (Æ Chained Rows)) Æ Blockgröße vergrößern (bis 9i nur durch Neuanlegen der DB möglich). Tablepartitioning (ab Version 8 mit kostenpflichtiger Partitioning Option) zum Aufteilen der Tabellen auf verschiedene Tablespaces (auf verschiedenen Platten). MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 27 Locks reduzieren Wenn eine Tabelle einen Foreign Key besitzt, sollte darauf ein Index gesetzt werden, denn wenn vor 8.1.7 in der Mastertabelle ein UPDATE oder ein DELETE auf den PK durchgeführt wird, sperrt Oracle die komplette Child-Tabelle Auch ein Update auf alle Spalten führt zu diesem Effekt (Oracle Forms macht das als Standard) Bei Joins zwischen Primary Key und Foreign Key Spalten sollten auch die Foreign Key Spalten indiziert sein MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 28 NULL Werte Tabellenspalten, in denen NULL-Werte vorkommen können, sind durch einen Index-Zugriff nicht auffindbar Ausnahme: Bitmap Index (Enterprise Edition) Das bedeutet, dass eine Abfrage auf NULL (... WHERE comm IS NULL) immer zu einem Full Table Scan führt Alternativ kann die Spalte mit einem Default-Eintrag versehen werden. z. B. 'NA', 'NN'‚ '-1' CREATE TABLE test (nr NUMBER DEFAULT '-1'); MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 29 NULL Werte Nachteile eines Default-Wertes NULL-Werte benötigen weniger Platz in der Spalte (in den letzten Spalten sogar 0 Bytes) Evtl. sind mehr Blöcke nötig, um alle Daten aufzunehmen Index wird größer, da NULL Werte nicht indiziert werden Vorteile Es sind keine Outer-Joins mehr notwendig. Equi-Joins sind hier ausreichend Tabelleninhalt ist häufig für Laien besser verständlich MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 30 DROP TABLE beschleunigen Das Löschen von Objekten kann sehr lange Zeit in Anspruch nehmen, da u. U. viele Extents frei gegeben werden müssen. Dadurch wird CPU verbraucht. Bei großen Tabellen kann der Löschvorgang in mehrere Teile zerlegt werden, die an verschiedenen Tagen ausgeführt werden. MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 31 DROP TABLE beschleunigen (f) Beispiel: Tabelle BIG_EMP ist 1GB groß (= 131.072 * 8KB Extents) [TRUNCATE TABLE BIG_EMP DROP STORAGE; -- Dauert z. B. 4 Stunden] TRUNCATE TABLE BIG_EMP REUSE STORAGE; -- HWM wird versetzt ALTER TABLE BIG_EMP DEALLOCATE UNUSED KEEP 750M; ALTER TABLE BIG_EMP DEALLOCATE UNUSED KEEP 500M; ALTER TABLE BIG_EMP DEALLOCATE UNUSED KEEP 250M; DROP TABLE BIG_EMP; MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 32 DROP COLUMN optimieren (ab 8i) Es gibt zwei Verfahren, eine Spalte zu ‚löschen‘: Auf unbenutzt setzen (unused). Hier wird die Spalte nur aus dem DD ausgetragen, aber nicht phys. gelöscht. Dies ist die schnellere Variante ALTER TABLE <tab> SET UNUSED COLUMN <col>; Spalte aus der Tabelle physikalisch löschen ALTER TABLE EMP DROP COLUMN ENAME CHECKPOINT 256; Durch die Checkpoint Angabe werden die Rollbacksegmente nicht so sehr beansprucht. Trotzdem kann es mehrere Stunden dauern, bis die Transaktion abgeschlossen wurde ! ALTER TABLE DROP UNUSED COLUMNS; Löscht alle auf UNUSED gesetzten Spalten MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 33 Tuning von DML Befehlen: Allgemeines Achten, Sie darauf, dass die Redolog-Dateien nicht zu klein sind Hinweise dazu stehen in der Alert.log: "Checkpoint not complete" bedeutet u. U. sie sind zu klein Wenn die Archivierung ausgeschalten werden kann (Testsysteme!!!) spart dies eine Menge Zeit Der Database Buffer Cache sollte ausreichend groß dimensioniert sein MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 34 Tuning von DML-Befehlen: INSERT Erhöhung des Parameters FREELISTS / FREELIST GROUPS (bis 9i) Erhöhung des Parameters INITRANS ALTER TABLE <owner>.<tab> INITRANS 10; Löschen der Indizes DROP INDEX <owner>.<index>; Danach wieder Neuaufbau des Index Ausschalten der Constraints ALTER TABLE <owner>.<tab> DISABLE CONSTRAINT <constraint>; Danach wieder Einschalten des Constraints, wenn noch möglich Erhöhung des Database Buffer Caches MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 35 Tuning von DML-Befehlen: INSERT Verwenden Sie den APPEND Hint INSERT /*+ APPEND */ INTO <owner>.<table> SELECT * FROM <owner>.<table>; Verwenden Sie den PARALLEL Hint: ALTER SESSION ENABLE PARALLEL DML; INSERT /*+ PARALLEL (<table>, <parallel degree>) */ INTO <owner>.<table> SELECT * FROM <owner>.<table>; COMMIT; Tuning von DML-Befehlen: UPDATE Erhöhung des Parameters FREELISTS / FREELIST GROUPS Erhöhung des Parameters INITRANS ALTER TABLE <owner>.<tab> INITRANS 5; Löschen der Indizes DROP INDEX <owner>.<index>; Danach wieder Neuaufbau des Index Ausschalten der Constraints ALTER TABLE <owner>.<tab> DISABLE CONSTRAINT <constraint>; Danach wieder Einschalten des Constraints, wenn noch möglich Nur die Spalten updaten, die sich wirklich ändern UPDATE emp SET sal=sal; MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 37 Tuning von DML-Befehlen: UPDATE Parallelisieren Sie den UPDATE: ALTER SESSION ENABLE PARALLEL DML; UPDATE /*+ PARALLEL (<tab>,<parallel degree>) */ <owner>.<tab> SET <col>=<value>; Beispiel: ALTER SESSION ENABLE PARALLEL DML; UPDATE /*+ PARALLEL (big,2) */ scott.big SET owner='#'; COMMIT; Tuning von DML-Befehlen: DELETE DELETE in kleinere Teile zerlegen delete from <tab> where rownum<10000; commit; delete from <tab> where rownum<10000; commit; Wenn Tabelle zum großen Teil gelöscht wird, vorher Indizes + Constraints ausschalten Wenn Tabelle komplett gelöscht wird, kann auch ein TRUNCATE TABLE Befehl benutzt werden. Es ist aber dann kein Rollback mehr möglich MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 39 Tuning von DML-Befehlen: DELETE Parallelisieren Sie den DELETE: ALTER SESSION ENABLE PARALLEL DML; DELETE /*+ PARALLEL (<tab>,<parallel degree>) */ FROM <owner>.<tab>; Beispiel: ALTER SESSION ENABLE PARALLEL DML; DELETE /*+ PARALLEL (big,2) */ FROM scott.big; COMMIT; Performance Vergleich (11g) Tabelle mit 2.5 Millionen Zeilen (290MB) im Locally Managed Tablespace (Archivierung ausgeschalten) Befehl Ohne Speicherparameter (1) Nologging(2) Mit Speicherparameter(3) Leere Table + INSERT /*+ APPEND (4) Temporäre Table (5) Parallel 2 (6) Table Compress (7) CREATE TABLE AS SELECT * 18.1s 17.2s 17.3s 17.8s 17.0s 18.3s 17.7s 18.5s 15.9s 15.5s 23.9s 18.0s 19.0s 14.6s 14.9s 16s 23.6s 23.1s UPDATE 2m6s 3m14s 2m44s 2m55s 1m7s 5m51s (1) CREATE TABLE big2 AS SELECT * from big; (5) CREATE GLOBAL TEMPORARY TABLE big2 (2) CREATE TABLE big2 NOLOGGING AS SELECT ...; ON COMMIT PRESERVE ROWS; (3) CREATE TABLE big2 STORAGE (INITIAL 8M INSERT INTO big2 SELECT * FROM big; NEXT 8M PCTINCREASE 0) AS SELECT * FROM big; (6) CREATE TABLE big PARALLEL 2 AS SELECT …; (4) CREATE TABLE big2 AS SELECT * FROM big (7) CREATE TABLE big COMPRESS AS SELECT …; WHERE 1=2; INSERT /*+ APPEND */ INTO big2 SELECT * FROM big; MuniQSoft GmbH DOAG 2007 Tabellentuning Seite 41 Impressum Oracle Schulung (SQL, DBA, PL/SQL, Security,…) Oracle Consulting & Support Oracle Entwicklung & Lizenzvertrieb Marco Patzwahl MuniQSoft GmbH Grünwalder Weg 13 a D-82008 Unterhaching b. München Telefon: Fax: E-Mail: Internet: MuniQSoft GmbH +49 (0)89 679090-40 +49 (0)89 679090-50 [email protected] www.muniqsoft.de DOAG 2007 Tabellentuning Seite 42
