- Gedanken zum Datenbanktuning - 31. März 2021
- Callgraphs for PL/SQL-Packages in Oracle - 26. März 2021
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.
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.
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.
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:
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:
