|
18c: Primärschlüsseloptimierung mit Scalable Sequences
Für Primärschlüssel legt die Oracle Datenbank immer einen Index an und bei streng aufsteigenden Werten, durch Sequences erzeugt, entsteht bei hoher Einfügelast schnell Contention auf den Indexblöcken mit den höchsten Schlüsselwerten. Contention bedeutet, dass die Benutzer warten müssen und die Performance sinkt. Ab Oracle 18c gibt es eine neue und sehr elegante Lösung für dieses Problem: Scalable Sequences. Dieser Tipp zeigt, wie Sie die INSERT-Performance auf Tabellen mit Primärschlüssel einfach und schnell steigern können.
Datenbanktabellen sollen nach der Theorie der relationalen Datenbanktechnik einen Primärschlüssel besitzen, der keine semantische, also inhaltliche Bedeutung hat. Aus diesem Grund werden in der Regel dazu aufsteigende numerische Werte verwendet, die bei Oracle Datenbanken durch eine Sequence erzeugt werden. Bei Oracle Datenbanken wird für jeden Primärschlüssel (wie auch beim UNIQUE Constraint) auch ein Index erstellt, der intern als B*-Baum realisiert wird. Im Index gibt es Verzweigungsknoten, die zu den Blattknoten führen. Diese Blattknoten enthalten dann die Schlüsselwerte und Rowid-Informationen, um einen direkten Zeilenzugriff zu ermöglichen.
Wenn mehrere Zeilen in eine Tabelle eingefügt werden, bekommt der Primärschlüssel bei Verwendung einer Sequence immer einen Wert, der nahe am vorangegangenen erzeugten Wert liegt, ja nach in der Sequence definiertem Inkrement. Das Inkrement ist typischerweise 1. Dieses ist bzgl. I/O sicherlich günstig, da die Indexblöcke, in denen gearbeitet wird, alle im Hauptspeicher, also dem Buffer Cache, liegen. Allerdings gibt es auch beim Zugriff auf Oracle Blöcke im Hauptspeicher dann Wartesituationen, wenn viele parallele Datenbanksitzungen zugreifen. Obwohl also noch Hauptspeicher frei wäre "tummeln" sich alle Datenbanksitzungen in wenigen Oracle Blöcken und behindern sich gegenseitig.
Um dieses Verhalten zu entzerren wurden in der Vergangenheit die REVERSE KEY Indizes eingeführt. Dabei werden die Schlüsselwerte umgekehrt und damit auf die gesamte Breite des Index verteilt. Allerdings ergeben sich damit Einschränkungen bei der Nutzung des Index und auch leichte Performanceverluste, weil bei jedem Zugriff der Schlüsselwert erst noch einmal bearbeitet werden muß.
Ab Oracle 18c wird diese Thematik durch einen neuen Lösungsansatz adressiert: Statt eine strikt aufsteigende Zahlenfolge im Index anders abzubilden, wird die Zahlenfolge so abgeändert, dass sie einerseits nicht mehr strikt aufsteigend ist, aber dennoch immer noch einzigartige Werte liefert. Dazu wird der normalen Sequencezahl eine sechsstellige Zahl vorangestellt, die aus der Session ID und Serial# der Datenbanksitzung gebildet wird. Beide Werte werden dabei jeweils mit einem Hashalgorithmus in eine dreistellige Zahl abgebildet und dann zu der sechsstelligen Zahl zusammengeführt. Jede Datenbanksitzung arbeitet für sich also wieder mit strikt aufsteigenden Werten, während parallele Datenbanksitzungen in einem anderen Zahlenbereich operieren. Jede Datenbanksitzung für sich arbeitet im Index also mit den gleichen Blöcken, behindert aber die anderen Datenbanksitzungen nicht.
Die Lösung wird also auf der Ebene der Sequence erreicht, indem diese als SCALABLE erstellt oder darin umgewandelt wird. Dabei gibt es zwei Varianten: EXTEND und NOEXTEND. Bei NOEXTEND wird mit der Option MAXVALUE die Gesamtbreite der Sequence-Werte angegeben, von der sechs Ziffern für die Datenbanksitzung verwendet werden. Bei EXTEND bekommen die Sequencewerte die Breite von 6+MAXVALUE und die Werte hinter den ersten sechs Ziffern werden mit führenden Nullen aufgefüllt.
Die erzeugten Zahlen sehen dann also so aus:
xxxxxxsssssss - x Ziffer aus der Sessioninformation - s Ziffer aus dem fortlaufenden Sequencewert
Betrachten wir also zwei Sessions, eine mit dem Prefix 123456 und die andere mit dem Prefix 234567, dann könnten die erzeugten Sequencewerte so aussehen:
Session1 (Sequence prefix 123456) 1234560001 1234560002 Session2 (Sequence Prefix 234567) 2345670003 2345670004 Session1 1234560005 Session2 2345670006 :
Damit verteilt sich die Aktivität im Primärschlüsselindex auf mehrere Indexblöcke, wie das folgende vereinfachte Schaubild zeigt. Das Dreieck symbolisiert dabei den B*-Baum, in dem die Suche über Verzweigungsknoten hin zu den Blattknoten erfolgt. Die Blattknoten enthalten den Schlüsselwert und die dazugehörige Rowid.
Einfache Beispiele zeigen das Verhalten von Scalable Sequences:
Erstellen Sie die neue Sequence gewohnt mit CREATE SEQUENCE, aber nun mit der neuen Option SCALE:
CREATE SEQUENCE seq_buchung SCALE EXTEND MAXVALUE 10;
SELECT sid,serial# FROM v$session where sid=sys_context('userenv','sid');
SID SERIAL#
52 40785
SELECT seq_buchung.nextval FROM dual;
NEXTVAL
--------
10105201
10105202
Angenommen, der gleiche Datenbankbenutzer startet eine zweite Datenbanksitzung und ruft die Sequence auf, so ist das Ergebnis:
SELECT sid,serial# FROM v$session where sid=sys_context('userenv','sid');
SID SERIAL#
285 58046
SELECT seq_buchung.nextval FROM dual;
NEXTVAL
--------
10128503
10128504
:
10128510
SELECT seq_buchung.nextval FROM dual;
ORA-08004: sequence SEQ_BUCHUNG.NEXTVAL exceeds MAXVALUE and cannot be instantiated
Die Werte der Sequence werden also von 1 bis 10 hochgezählt und dann links um einen Hashwert basierend auf den IDs der Datenbanksitzungen erweitert. Im Index werden sich diese beiden Datenbanksitzungen also weniger gegenseitig blockieren, als wenn man einen normalen Index verwenden würde. Nach zehn Werten ist aber Schluß, denn MAXVALUE wurde erreicht. Um das Maximum des Zahlenbereiches ausnutzen zu können, lassen Sie MAXVALUE einfach weg:
CREATE SEQUENCE seq_buchung SCALE EXTEND; SELECT seq_buchung.nextval FROM dual; NEXTVAL ---------------------------------- 1010520000000000000000000000000001
Die maximale Breite bei EXTEND sind 28 Ziffern plus die führenden sechs Ziffern für die Datenbanksitzung. Bei der Option NOEXTEND beinhaltet die Breite von 28 Ziffern die ersten sechs Ziffern für die Datenbanksitzung.
CREATE SEQUENCE seq_buchung SCALE NOEXTEND; SELECT seq_buchung.nextval FROM dual; NEXTVAL ---------------------------- 1010520000000000000000000001
Wenn Sie einen Wert für MAXVALUE angeben, muß dieser mindestens 1000000 betragen, denn
CREATE SEQUENCE seq_buchung SCALE NOEXTEND MAXVALUE 100000; SELECT seq_buchung.nextval FROM dual; ORA-64603: NEXTVAL cannot be instantiated for SEQ_BUCHUNG. Widen the sequence by 1 digits or alter sequence with SCALE EXTEND. CREATE SEQUENCE seq_buchung SCALE NOEXTEND MAXVALUE 1000000; SELECT seq_buchung.nextval FROM dual; NEXTVAL ------- 1014721
Umwandlung bestehender Sequences
Eine Sequence, die nicht mit der Option SCALE erstellt wurde, kann nachträglich zu einer Scalable Sequence umgewandelt werden. Auch der Weg zurück ist theoretisch möglich, jedoch wird der Maximalwert der Sequence, die mit SCALE definiert wurde, als Startwert für die NOSCALE Sequence verwendet wird, woraus sich einige Einschränkungen ergeben, die im folgenden Beispiel deutlich werden.
Zunächst wird eine einfache Sequence erstellt mit
CREATE SEQUENCE seq_buchung; SELECT seq_buchung.nextval FROM dual; NEXTVAL ------- 1 2
Die einfache Sequence wird jetzt in eine SCALE Sequence mit NOEXTEND umgewandelt:
ALTER SEQUENCE seq_buchung SCALE NOEXTEND MAXVALUE 1000000; SELECT seq_buchung.nextval FROM dual; NEXTVAL ------- 1014723 1014724
Wenn die Sequence jetzt wieder zurück in NOSCALE umgewandelt wird, dann geht es mit einem neuen nächsten Sequencewert weiter:
ALTER SEQUENCE seq_buchung NOSCALE MAXVALUE 10000000; SELECT seq_buchung.nextval FROM dual; NEXTVAL ------- 1999995 1999996
Jetzt wird die Sequence in SCALE EXTEND umgewandelt und die Breite der Sequencewerte steigert sich nochmals, da dem letzten Wert die sechs Ziffern der Datenbanksitzung vorangestellt wird:
ALTER SEQUENCE seq_buchung SCALE EXTEND; SELECT seq_buchung.nextval FROM dual; NEXTVAL -------------- 10147201999997 10147201999998 :
Wenn nun die Sequence mit der MAXVALUE Option auf das Maximum verbreitert wird
ALTER SEQUENCE seq_buchung SCALE EXTEND MAXVALUE 1000000000000000000000000000; SELECT seq_buchung.nextval FROM dual; NEXTVAL 1014720000000000000000000002000002
kann man die Sequence zwar wieder als NOSCALE definieren.
ALTER SEQUENCE seq_buchung NOSCALE;
Aber neue Sequencewerte können nicht mehr erzeugt werden, denn die maximale Breite wurde erreicht.
SELECT seq_buchung.nextval FROM dual; ORA-08004: sequence SEQ_BUCHUNG.NEXTVAL exceeds MAXVALUE and cannot be instantiated
Data Dictionary
Im Data Dictionary können Sie über zwei neue Spalten in der View xxx_SEQUENCES nachsehen, ob eine Sequence SCALABLE ist und wenn ja, ob sie mit EXTEND oder NOEXTEND angelegt wurde:
DESC user_sequences Name Null? Type ----------------------------------------- -------- ---------------------------- SEQUENCE_NAME NOT NULL VARCHAR2(128) MIN_VALUE NUMBER MAX_VALUE NUMBER INCREMENT_BY NOT NULL NUMBER CYCLE_FLAG VARCHAR2(1) ORDER_FLAG VARCHAR2(1) CACHE_SIZE NOT NULL NUMBER LAST_NUMBER NOT NULL NUMBER SCALE_FLAG VARCHAR2(1) EXTEND_FLAG VARCHAR2(1) SESSION_FLAG VARCHAR2(1) KEEP_VALUE VARCHAR2(1) SELECT scale_flag,extend_flag FROM user_sequences WHERE sequence_name='SEQ_BUCHUNG'; S E - - Y Y
Mit einer Scalable Sequence entzerren Sie den konkurrierenden Zugriff auf die Indexblöcke eines Primärschlüssels, da jede Datenbanksitzung einen eigenen Zahlenbereich bekommt, indem die ersten sechs Ziffern der Sequencewerte aus Identifikationsinformationen der Datenbanksitzung gebildet werden. Bereits bestehende Sequences können nachträglich in diesen neuen Modus umgewandelt werden. Scalable Sequences können immer dann verwendet werden, wenn ein Primärschlüssel nicht strikt aufsteigend sein muß und keine semantische Bedeutung hat.
Diese Funktionalität ist Basisbestandteil jeder Oracle Datenbank ab der Version 18c.
Weitere Informationen
Zurück zum Anfang des Artikels
Zurück zur Community-Seite