Mit Oracle 12.1.0.2 wurde die brandneue Datenbankoption namens inMemory eingeführt. Es gibt viele Blogs und Artikel, also will ich nicht zu sehr ins Detail gehen, was das das Konzept betrifft. Für mich klingt „inMemory“ ein wenig irreführend, denn immer wenn wir in der Oracle Datenbank arbeiten, sollten die meisten unsere Daten im Memory sein – stimmt’s? Aber es gibt eine andere Benennung dafür: Column Store. Also es speichert die Daten in Spalten, statt in Zeilen? Laut der Oracle Dokumentation ist dies nicht wahr, denn „The columnar format exists only in memory (Concepts Guide)“. Also ist das Konzept des inMemory, dass es in der SGA einen Bereich gibt und die Daten in diesem Bereich werden nicht mehr im Zeilenformat eingepflegt, wie im Buffer-Cache, sondern in einem Spalten-Format. Besonders Datawarehouses und Online Analytics System werden davon profitieren, aber da es keinen Unterschied in der Speicherung von Daten gibt, kann die Option einfach aktiviert und auch in OLTP Systemen verwendet werden. Für das Hauptkonzept würde ich empfehlen, den Oracle Concepts Guide und das Whitepaper „Oracle Database In-Memory“ zu lesen.
Das wahre Leben
Einige von meinen Kunden haben inMemory mehr oder weniger erfolgreich ausprobiert. Meistens war der Grund für einen nicht erfolgreichen Test, dass die Daten für inMemory nicht geeignet waren (z.B. kein Star-Schema, zu komplexe Queries, ungenügende Datenmenge). Aber ich hatte einen Termin mit einem Kunden, der hauptsächlich auf Business Intelligence Lösungen spezialisiert ist. Also perfekt für inMemory. Wir haben ein paar generelle Test in ihrer Umgebung gemacht und ohne irgendwelche Änderungen bei der Anwendung liefen die Queries 10 bis 20 mal schneller (und sogar mehr). Ich darf hier keine Kunden-Queries verwenden, aber ich habe eine Testumgebung in meinem eigenen Labor erstellt und hier sind einige Ergebnisse.
Beispiele
Die Queries basieren auf einem Star Flake Schema mit einer Fact-Tabelle namens „UMSAETZE“ mit ungefähr 145 Millionen Zeilen und vier Dimensionstabellen namens „ARTIKEL“, „REGION“, „ZEITEN“ und „KUNDEN“.
Die erste Query ist einfach dieses:
SELECT count(*) FROM umsatz;
Das zweite Query ist komplexer:
SELECT sum(u.umsatz) as gesamtumsatz,
a.artikelgruppe,
z.monat_des_jahres,
z.jahreszahl,
r.region,
k.plz,
k.ort
FROM region r
INNER JOIN umsaetze u ON (r.region_id = u.region_id)
INNER JOIN kunden k ON (k.kunden_id = u.kunden_id)
INNER JOIN artikel a ON (a.artikel_id = u.artikel_id)
INNER JOIN zeiten z ON (zeiten_id = zeit_id)
WHERE r.region = 'Nord'
AND z.jahreszahl in (2014,2015)
AND z.monat_des_jahres = 12
GROUP BY a.artikelgruppe, z.monat_des_jahres, z.jahreszahl, k.plz, k.ort, r.region
FETCH FIRST 10 PERCENT ROWS ONLY;
Um Tabellen zu erlauben in den Column Store eingepflegt zu werden, muss der Parameter inmemory_size gesetzt sein. Man muss beachten, dass der inMemory-Bereich zur SGA gehört, d.h. der Parameter sga_target oder memory_target muss ausreichend groß definiert sein, damit der Bereich zugewiesen werden kann. In meiner Testdatenbank war der sga_target ungefähr 10 GB, während die inmemory_size 5 GB war. Der inMemory-Bereich ist kein Cache, so wie ein Buffer-Cache, wenn also eine Tabelle zu groß ist oder der Bereich zu klein, könnten Tabellen zum Teil eingepflegt werden und eine Fehlermeldung wird in den Alert-Log geschrieben.
Die Tabellen einpflegen
Tabellen können mit dem „ALTER TABLE … INMEMORY“-Befehl angepasst werden. Obwohl dies der Standard ist, würde ich nicht empfehlen, ihn zu benutzen. Denn mit diesem Befehl wird eine Tabelle nur in den Bereich geladen, wenn sie das erste Mal verwendet wird (z.B. mit einem DML- oder einem SELECT-Befehl). Stattdessen empfehle ich, die Tabelle mit den wichtigsten Informationen zuerst laden und anschließend alle Tabellen, die damit zusammenhängen:
SQL> ALTER TABLE umsaetze INMEMORY PRIORITY CRITICAL; SQL> ALTER TABLE artikel INMEMORY PRIORITY HIGH; SQL> ALTER TABLE kunden INMEMORY PRIORITY HIGH; SQL> ALTER TABLE region INMEMORY PRIORITY HIGH; SQL> ALTER TABLE zeiten INMEMORY PRIORITY HIGH;
Mit diesen Befehlen wird die Tabelle UMSAETZE sofort geladen und immer wenn die Instanz neu gestartet wird. Nachdem die Tabelle vollständig ins Memory geladen wurde, wird der Rest der Tabellen eingepflegt, solange es genug Platz im inMemory-Bereich gibt.
Die Ansicht v$im_segments kann benutzt werden um zu überprüfen, dass die Tabellen eingepflegt wurden. Ich erteilte SELECT auf dem v$im_user_segments an meinen Test-User, sodass es keinen entsprechend berechtigten User mehr für die Bestätigung braucht.
SELECT inst_id, segment_name, inmemory_size, bytes, bytes_not_populated, populate_status FROM v$im_user_segments; SEGMENT_NAME INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_ -------------------- ------------- ---------- ------------------- --------- REGION 1179648 393216 0 COMPLETED KUNDEN 24313856 83886080 0 COMPLETED UMSAETZE 2710831104 6978273280 0 COMPLETED ZEITEN 1179648 131072 0 COMPLETED
Ein merkwürdiges Verhalten, das ich noch nicht erklären kann: Die Tabelle „ARTIKEL“ ist noch überhaupt nicht geladen. Ich habe es mit mehreren Optionen versucht und sogar mit „PRIORITY CRITICAL“ wird die Tabelle nicht gelistet. Da dies eine sehr kleine Tabelle ist (nur 300 Zeilen), beeinflusst sie das insgesamte Query-Ergebnis nicht.
Jetzt sind wir bereit unseren ersten Test zu machen:
1. Query 1 mit dem NO_INMEMORY Hinweis
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 11.32 39.68 308384 308455 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 11.32 39.68 308384 308455 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 105
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=308455 pr=308384 pw=0 time=39683926 us)
144500000 144500000 144500000 INDEX FAST FULL SCAN PK_UMSAETZE (cr=308455 pr=308384 pw=0 time=60586018 us cost=81707 size=0 card=144500000)(object id 92071)
2. Query 2 ohne den Hinweis
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.95 1.14 0 12 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.95 1.17 0 12 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 105
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=12 pr=0 pw=0 time=1144222 us)
144500000 144500000 144500000 TABLE ACCESS INMEMORY FULL UMSAETZE (cr=12 pr=0 pw=0 time=1130484 us cost=8708 size=0 card=144500000)
Ergebnis 1
Das Ergebnis ist ziemlich beeindruckend. Statt 40 Sekunden ohne InMemory, dauerte die Abfrage nur eine Sekunde.
Aber was ist mit einer komplexeren Query?
3. Query 2 mit dem NO_INMEMORY Hinweis
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 88 29.36 69.63 854299 849731 0 1304
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 90 29.41 69.68 854299 849731 0 1304
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 105
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1304 1304 1304 VIEW (cr=849731 pr=854299 pw=4619 time=69638236 us cost=268671 size=217524901 card=1587773)
13031 13031 13031 WINDOW BUFFER (cr=849731 pr=854299 pw=4619 time=69645196 us cost=268671 size=130197386 card=1587773)
13031 13031 13031 HASH GROUP BY (cr=849731 pr=854299 pw=4619 time=69609961 us cost=268671 size=130197386 card=1587773)
511356 511356 511356 HASH JOIN (cr=849731 pr=854299 pw=4619 time=56529021 us cost=238350 size=130197386 card=1587773)
374 374 374 TABLE ACCESS FULL ARTIKEL (cr=5 pr=0 pw=0 time=115 us cost=3 size=5984 card=374)
511356 511356 511356 HASH JOIN (cr=849726 pr=854299 pw=4619 time=56394671 us cost=238343 size=104793018 card=1587773)
1000000 1000000 1000000 TABLE ACCESS FULL KUNDEN (cr=9683 pr=9678 pw=0 time=54447 us cost=2745 size=21996920 card=999860)
511356 511356 511356 HASH JOIN (cr=840043 pr=839971 pw=0 time=43368955 us cost=229777 size=69862012 card=1587773)
1351 1351 1351 TABLE ACCESS FULL REGION (cr=41 pr=0 pw=0 time=556 us cost=13 size=13734 card=1526)
3128202 3128202 3128202 HASH JOIN (cr=840002 pr=839971 pw=0 time=85763697 us cost=229748 size=222288150 card=6351090)
32 32 32 TABLE ACCESS FULL ZEITEN (cr=14 pr=0 pw=0 time=154 us cost=6 size=616 card=56)
144500000 144500000 144500000 TABLE ACCESS FULL UMSAETZE (cr=839988 pr=839971 pw=0 time=41869421 us cost=229378 size=3468000000 card=144500000)
4. Query 2 ohne einen Hinweis
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.31 0.37 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 88 1.83 2.74 4650 26 0 1304
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 90 2.14 3.11 4650 26 0 1304
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 105
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1304 1304 1304 VIEW (cr=26 pr=4650 pw=4619 time=2735009 us cost=47289 size=217524901 card=1587773)
13031 13031 13031 WINDOW BUFFER (cr=26 pr=4650 pw=4619 time=2736637 us cost=47289 size=130197386 card=1587773)
13031 13031 13031 HASH GROUP BY (cr=26 pr=4650 pw=4619 time=2728219 us cost=47289 size=130197386 card=1587773)
511356 511356 511356 HASH JOIN (cr=26 pr=4650 pw=4619 time=2316748 us cost=16968 size=130197386 card=1587773)
374 374 374 TABLE ACCESS INMEMORY FULL ARTIKEL (cr=5 pr=0 pw=0 time=28765 us cost=1 size=5984 card=374)
511356 511356 511356 HASH JOIN (cr=21 pr=4650 pw=4619 time=2211160 us cost=16964 size=104793018 card=1587773)
1000000 1000000 1000000 TABLE ACCESS INMEMORY FULL KUNDEN (cr=3 pr=0 pw=0 time=72471 us cost=225 size=21996920 card=999860)
511356 511356 511356 HASH JOIN (cr=18 pr=0 pw=0 time=951774 us cost=10917 size=69862012 card=1587773)
1351 1351 1351 TABLE ACCESS INMEMORY FULL REGION (cr=3 pr=0 pw=0 time=169990 us cost=4 size=13734 card=1526)
3128202 3128202 3128202 HASH JOIN (cr=15 pr=0 pw=0 time=645427 us cost=10897 size=222288150 card=6351090)
32 32 32 JOIN FILTER CREATE :BF0000 (cr=3 pr=0 pw=0 time=14294 us cost=4 size=616 card=56)
32 32 32 TABLE ACCESS INMEMORY FULL ZEITEN (cr=3 pr=0 pw=0 time=14084 us cost=4 size=616 card=56)
3128202 3128202 3128202 JOIN FILTER USE :BF0000 (cr=12 pr=0 pw=0 time=227502 us cost=10529 size=3468000000 card=144500000)
3128202 3128202 3128202 TABLE ACCESS INMEMORY FULL UMSAETZE (cr=12 pr=0 pw=0 time=188217 us cost=10529 size=3468000000 card=144500000)
Ergebnis 2
Wieder eine fantastische Performance-Verbesserung: 70 Sekunden ohne inMemory und nur 3 (!) Sekunden mit inMemory.
Als erstes Ergebnis sieht inMemory sehr vielversprechend aus. Der Kunde war beeindruckt und wir haben die entsprechenden Änderungen in der Vorproduktionsdatenbank gemacht. Leider war das eine RAC Datenbank – und keine Exadata.
In meinem nächsten Blog geht es um die Herausforderungen, wenn man inMemory mit RAC verwendet.