Gedanken zum Datenbanktuning
Es kommt immer wieder es vor, dass (Oracle-) Datenbankanwendungen „zu
langsam" sind. Dann wird versucht herauszufinden woran es liegt und wenn
man Glück hat findet sich eine Idee das verursachende SQL-Statement so
zu ändern, dass die Anwendung „schnell genug" wird. Es wird bei diesem
ganzen Vorgang oft übersehen, dass die Ursachen(n) für die schlechte
Performance nicht erst im SQL-Statement begründet ist, sondern viel
früher ihren Ursprung hat.
Die Planung einer performanten Datenbankanwendung beginnt mit der
Planung der Datenbank. Um eine Datenbank planen zu können sollten die
Anforderungen an die mit ihr zu realisierenden Anwendungen bekannt sein.
Einige der Fragen, die für die Planung relevant sind:
\
Ist es eine ERP-Anwendung oder ein Datawarehouse?\
Gibt es Tabellen mit sehr unterschiedlichen Satzlängen?\
Wird mit der Anwendung hauptsächlich abgefragt oder Daten bearbeitet?\
Gibt es mehrere Anwendungen, die mit den Daten arbeiten sollen und haben
diese Anwendungen unterschiedliche Anwendungsmuster?
Mit den gesammelten Anforderungen kann die Physik der Datenbank geplant
werden.
::: {.wp-block-spacer style="height: 36px;"}
:::
Tablespaces und Blockgrößen {#h-tablespaces-und-blockgr-en}
Die Blockgröße(n) der Tablespaces sollten an die Anforderungen der
Anwendung angepasst werden. Zur Erinnerung: Oracle liest immer nur ganze
Blöcke und nicht einzelne Datensätze. Falls es in den Anforderungen an
die einzelnen Tabellen große Unterschiede gibt sollten die Tabellen auf
Tablespaces mit geeigneten unterschiedlichen Blockgrößen verteilt
werden.
Kleine Blockgrößen (2KB/4KB) eignen sich für kurze Satzlängen auf die
oft einzeln zugegriffen wird. Sie eignen sich nicht für lange Satzlängen
und wenn oft auf große Mengen an Datensätzen zugegriffen wird.
Große Blockgrößen (8KB -- 32KB) eignen sich für lange Satzlängen und
wenn große Datenmengen sequenziell abgefragt werden.
Falls wegen eines kleinen Datensatzes ein großer Block gelesen werden
muss oder falls wegen eines langen Datensätzes dieser auf mehrere Blöcke
verteilt werden muss (chaining), so mindert dies die Performance der
späteren Anwendung.
Gegebenenfalls lohnt es sich Tablespaces mit unterschiedlichen
Blockgrößen anzulegen.
::: {.wp-block-spacer style="height: 36px;"}
:::
Tabellen {#h-tabellen}
Die Datensätze einer Tabelle werden in den Blöcken des zu verwendenden
Tablespaces gespeichert. Sobald ein Block eines Tablespace einer Tabelle
zugewiesen ist gehört er zu dieser Tabelle, auch dann, wenn alle
Datensätze in diesem Block gelöscht wurden (high water mark). Bei einem
Full Table Scan werden auch diese inzwischen leeren Blöcke gelesen und
kosten Zeit. Oracle bietet mehrere Möglichkeiten diese Blöcke
freizugeben ( export/import, alter table .. shrink space, usw...)
Oracle vermerkt alle leeren oder teilweise leeren Blöcke einer Tabelle
in die noch Datensätze geschrieben werden dürfen in einer Freelist.\
Mit den Speicherparametern pctfree und pctused (Prozentwerte) kann
angegeben werden, unter welchen Bedingungen ein Block auf die Freelist
gesetzt oder von dieser entfernt wird. Ist der Block zu mehr als pctfree
frei, so wird der Block auf die Freelist gesetzt. Ist der Block zu mehr
als pctused belegt, so wird er von der Freelist genommen.
::: {.wp-block-spacer style="height: 36px;"}
:::
Chaining {#h-chaining}
Falls ein Datensatz nicht in einen Block passt, so wird er über mehrere
Blöcke verteilt. Dann müssen um diesen einen Datensatz zu lesen alle
diese Blöcke nacheinander gelesen werden. (Ob in einer Tabelle solche
Datensätze existieren kann z.B. in user_tables in der Spalte chain_cnt
überprüft werden)
Zu Chaining kommt es, wenn große Datensätze in (zu) kleine Blöcke
geschrieben werden, oder wenn bestehende Datensätze beim Aktualisieren
verlängert werden (z.B. wenn ein varchar2-Wert verlängert wird). Falls
der Datensatz länger ist als die Blockgröße, so lässt sich chaining
nicht verhindern. In diesem Fall kann man darüber nachdenken die Tabelle
in einen Tablespace mit größerer Blockgröße zu verschieben.
Falls die Verlängerung eines Datensatzes aufgrund der
Anwendungsanforderungen vorhersehbar ist, so kann durch setzen eines
geeigneten Werts für pctused der Tabelle dafür gesorgt werden, dass in
den Blöcken genügend freier Platz gelassen wird, so dass eine
Verlängerung eines Datensatzes in den bestehenden Block passt. Für
Tabellen mit Datensätzen, die nur einmal geschrieben und dann nicht mehr
verändert werden, kann pctused hoch gewählt werden.
Falls Datensätze regelmäßig gelöscht und neu geschrieben werden, so
sollte pctfree so gewählt werden, dass ein neuer Datensatz in den noch
freien Platz eines bereits teilweise belegten Blocks passt:
::: {.wp-block-spacer style="height: 36px;"}
:::
Beispiel {#h-beispiel}
Blockgröße: 4096 Bytes\
mittlere Satzlänge eines Datensatzes (user_tables.avg_row_len): 400
Bytes
Zwei Bemerkungen:
- Diese mittlere Länge kann sich ändern.
- Viele Datensätze sind sicherlich länger als 400 Bytes.
Mit „select sum(c.DATA_LENGTH) from user_tab_columns c where
c.TABLE_NAME = \$NAME\$\
können wir die maximale Länge eines Datensatzes ermitteln.
\
Beispiel a) die mittlere Datensatzlänge wäre 600 Bytes.\
Mit pctfree = 15 (4096*0,15 = 614) sind wir auf der sicheren Seite.
Beispiel b) die mittlere Datensatzlänge wäre 6000 Bytes.\
Da wir die Verteilung der Datensatzlängen nicht kennen, müssen wir eine
Annahme treffen:\
pctfree = 20 (2096*0,20 = 819) sollte für die meisten Datensätze
ausreichend sein.
Wir tauchen ein bisschen tiefer ein. Falls wir mit verlässlichern Werten
arbeiten wollen, können wir uns mit etwas Aufwand bessere Kennzahlen
ermitteln:
dump() liefert uns einen String, der u.a. die Länge in Bytes des
Ausdrucks zurückgibt:
Z.B: select dump( 'ä' ) from dual;
DUMP('Ä')
Typ=96 Len=2: 195,164
Dies nutzen wir, um die Länge der einzelnen Spalten zu ermitteln und
diese aufzusummieren.
2 Anmerkungen:
- Jede Spalte benötigt ein zusätzliches Byte Speicherplatz. (Dies berücksichtigen wir nachfolgend)
- NULL-Spalten (auch mehrere) am Ende eines Datensatzes benötigen keinen Speicherplatz. (Dies berücksichtigen wir nachfolgend nicht!)
mit
select '+ coalesce( to_number( regexp_substr( dump( ' || rpad( column_name, 32, ' ' ) ||q'{ ), 'Len=(\d*):', 1, 1, 'x', 1 ) ), 0) +1}' as term
from user_tab_columns
where table_name = $tabellenname$
order by column_id;
können wir uns den schreibintensiven Teil des folgenden Statements
erzeugen lassen:
with len_ as
( select coalesce( to_number( regexp_substr( dump( ), 'Len=(\d):', 1, 1, 'x', 1 ) ), 0) +1
+ coalesce( to_number( regexp_substr( dump( ), 'Len=(\d):', 1, 1, 'x', 1 ) ), 0) +1 + …
+ coalesce( to_number( regexp_substr( dump( ), 'Len=(\d*):', 1, 1, 'x', 1 ) ), 0) +1 as bytes from $tabellenname$ )
select min(bytes), avg(bytes), max(bytes), stddev(bytes) from len_;
Hinweis: für sehr große Tabellen kann diese Auswertung lang dauern. In
diesem Fall sollte sie auf eine ausreichend große repräsentative Menge
begrenzt werden ( where rownum < X ).
Die Werte sind wegen Anmerkung 2 nicht 100% zuverlässig. Für eine
Abschätzung der Verteilung der Datensatzlängen sind sie aber
ausreichend. Unter der Annahme, dass die Längen normalverteilt sind,
kann die Mindestgröße für pctfree wie folgt bestimmt werden:
pctfree = 100 * ( avg + stddev ) / Blockgröße (für ~84% aller
Datensätze ausreichend)\
pctfree = oder 100 * ( avg + 2*stddev ) / Blockgröße (für ~98% aller
Datensätze ausreichend)
Siehe auch:
Der Beitrag Gedanken zum
Datenbanktuning
erschien zuerst auf virtual7 GmbH - Blog.
https://blog.virtual7.de/gedanken-zum-datenbanktuning/
#virtual7 #digitalisierung #deutschland #virtual #digitalezukunft #digital #zukunft #agile #Finance #OracleDatabase #Technology #Chaining #database #Datenbank #Oracle #Tuning