LOB Komprimierung mit Oracle 11g Einführung

LOB Komprimierung mit Oracle 11g
Mathias Zarick . Consultant . Oktober 2009
Mit Oracle Database 11g ist es möglich, Large Objects (LOBs) komprimiert in der Datenbank
zu speichern. Durch dieses Feature aus der neuen kostenpflichtigen Advanced Compression
Option ist es möglich Speicherplatz zu sparen. Lohnt sich der Aufwand? Was bedeutet das für
die Application Performance? Dieser Artikel beleuchtet diese Fragen.
Einführung
Oracle hat die Implementierung der Large Objects (LOBs) in der Datenbank grundlegend
überarbeitet. Oracle verspricht mit dieser neuen Implementierung verbesserte Performance und
Verwaltung. Diese neuen LOBs sind ein weiteres Beispiel für die effektive Nutzung der
Architektur von lokalen Tablespaces mit Automatic Segment Space Management (ASSM). ASSM
Tablespaces sind daher auch eine Voraussetzug für das Anlegen dieser neuartigen LOBs, die
Oracle SecureFiles nennt.
SecureFiles
Die Nutzung von Oracle SecureFiles ist transparent für Anwendungen und APIs, die auf diese
LOBs zugreifen. Jegliche LOB Schnittstellen arbeiten wie auf den herkömmlichen LOBs, die es
natürlich auch noch gibt und jetzt BasicFiles heißen. Sowohl für BasicFiles als auch für
SecureFiles werden die drei Datentypen CLOB, NCLOB und BLOB unterstützt.
Dennoch gibt es auch Einschränkungen: Der LogMiner versteht SecureFiles mit 11gR1 noch
nicht, daher sind sie auf diesem Release nicht in Umgebungen mit Logical Standby Database
oder Streams einsetzbar. Diese Einschränkung fällt mit 11gR2, ab diesem Release kann der
LogMiner mit SecureFiles umgehen.
Oracle SecureFiles können im Gegensatz zu BasicFiles komprimiert, dedupliziert oder
verschlüsselt werden. Diese drei Technologien können orthogonal zueinander Anwendung
finden, d.h. keines der Features schließt ein anderes aus. Komprimierung und Deduplizierung
werden in diesem Artikel näher beleuchtet. Die Verschlüsselung, welche den SecureFiles wohl
den Namen gab, ermöglicht es, die LOBs basierend auf Transparent Data Encryption (TDE) zu
verschlüsseln.
SecureFiles bieten außerdem eine zusätzliche Logging Option FILESYSTEM_LIKE_LOGGING
mit welcher es nun möglich ist, Redo Informationen für Metadaten zu loggen, für Daten jedoch
nicht.
Nötige Lizenzierungen
Für die Verwendung von SecureFiles ohne Komprimierung, Deduplizierung oder
Verschlüsselung ist keine weitere Lizenz nötig. Sie sind sowohl in Standard Edition als auch in
Enterprise Edition verfügbar. Sobald jedoch eines der drei erwähnten Features verwendet wird,
ist die Enterprise Edition mit einer dazugehörigen Option notwendig. Für Komprimierung und
Deduplizierung ist das die Advanced Compression Option und für die Verschlüsselung die
Advanced Security Option. Bei einer kombinierten Anwendung werden entsprechend beide
Optionen fällig.
www.trivadis.com
Seite 1 / 15
Speicherung von LOBs
Um ein LOB zu speichern, verwendet Oracle auch für SecureFiles die gleichen
Datenstrukturen, ein LOB Segment und einen LOB Index. Der LOB Index wird benötigt, um auf
LOB Chunks (Erklärung folgt unten) aus dem LOB Segment zuzugreifen. LOBs können nach wie
vor inline (enable storage in row) oder auch out-of-line (disable storage in row) gespeichert
werden. Für LOBs, die out-of-line gespeichert werden, speichert Oracle einen LOB Locator in
der entsprechenden Row. Der LOB Locator bietet dann den Einstieg, um über den LOB Index
auf das LOB im LOB Segment zuzugreifen. Wird hingegen Inline Speicherung verwendet, so
können kleine LOBs mit in der Row also zusammen mit den anderen Spalten der Tabelle in
demselben Block gespeichert werden. Überschreitet das LOB jedoch eine Größe von ca. 4000
Bytes wird es immer komplett out-of-line gespeichert. Inline Speicherung empfiehlt sich nur für
kleine LOBs, die häufig zusammen mit den anderen Spalten der Tabelle gelesen werden. Gibt
es hingegen viele Full Table Scans auf der Tabelle, die die LOB Daten nicht brauchen, so
sollten sie unbedingt out-of-line gespeichert werden, um nicht ständig über unnötige Daten
hinweglesen zu müssen. Der Default ist nach wie vor „enable storage in row“.
Die Daten in den LOB Segmenten werden in Chunks unterteilt. Sie bilden eine kleinste Einheit
für den Zugriff und die Manipulation und müssen ein Vielfaches der Blocksize sein. Bei
BasicFiles können sie maximal 32K groß sein und sind immer gleich groß. Hier wird es mit
Oracle 11g spannend. Mit dem Zeitalter der SecureFiles sind diese Chunkgrößen nämlich
dynamisch und können auch weitaus größer als nur 32K werden. Der Parameter Chunk ist
jedoch nach wie vor auch für SecureFiles gültig, er dient jedoch nur noch der
Abwärtskompatibilität bzw. wird vom Oracle Server als Vorschlag erachtet.
Lesekonsistenz
Bezüglich Lesekonsistenz gibt es auch eine Neuerung im Vergleich zu BasicFiles. Der
PCTVersion Parameter, mit welchem es möglich war, eine Prozentzahl des Platzes des LOB
Segmentes für alte LOB Versionen anzugeben, ist für SecureFiles nicht mehr gültig1. Hier
kommt nur noch der Retention Parameter zum Einsatz. Bei diesem besseren Ansatz wird nicht
ein Prozentsatz sondern eine Zeitperiode definiert. Und dieser Parameter wird mit den
SecureFiles noch erweitert. Man kann neben dem Default AUTO, welches nur konsistentes
Lesen sicherstellt, auch MIN plus eine Zeitspanne in Sekunden angeben. Ein weiterer Wert für
Retention ist MAX. Alte LOB Versionen werden dann bis zu einer anzugebenen MAXSIZE
(Größe des Segments) gespeichert. Sind alle aktuellen Versionen der LOBs jedoch größer als
diese MAXSIZE wird keine alte Version mehr im Segment gespeichert. Keine alten Versionen
kann man ebenso mit der Einstellung NONE für Retention erreichen.
Caching
Beim Caching gibt es nichts Neues. Der Default ist nach wie vor NOCACHE, d.h. auf ein LOB
wird im Normalfall immer über direct physical reads bzw. writes zugegriffen, und es wird nicht
im Buffer Cache abgelegt. CACHE READS ist eine Option, die es erlaubt die Blöcke des LOBs
nur für das Lesen zu cachen, nicht für das Schreiben und CACHE cached die Blöcke
letztendlich in beiden Fällen.
Migration
Um Daten
Methoden.
möchte ich
export und
1
von BasicFiles zu SecureFiles zu migrieren, gibt es leider keine „einfachen“
Die Daten müssen wirklich bewegt werden. Als Möglichkeiten zur Migration
„Create table … as select …“, „insert into … select …“, „alter table … move lob“,
import, Data Pump und Online Table Redefinition mittels DBMS_REDEFINITION
Versucht man ein LOB als SecureFile mit dem PCTVersion Parameter anzulegen, so bekommt man
einen Fehler ORA-22853: invalid LOB storage option specification.
www.trivadis.com
Seite 2 / 15
nennen. Die letztgenannte ist die einzige, die es ermöglicht online Migrationen durchzuführen,
ohne zugreifende Applikationen zu behindern.
Syntax und init.ora Parameter
Die Syntax für die Anlage eines SecureFiles, ist hier schematisch dargestellt:
CREATE TABLE <TABELLE>
( <SPALTEN>,
<LOBSPALTE> BLOB,
...
)
<STORAGE KLAUSEL FUER TABELLENSEGMENT>
LOB (<LOBSPALTE>) STORE AS SECUREFILE <LOBSEGMENTNAME> (
TABLESPACE <TABLESPACENAME>
DISABLE STORAGE IN ROW
<STORAGE KLAUSEL FUER LOBSEGMENT>
)
;
Konkrete Beispiele werden im Laufe des Artikels folgen. Die vollständige Syntaxbeschreibung
finden Sie in der Oracle Dokumentation.
Sollten Sie keinen Zugriff auf die SQL Anweisungen haben, welche die Tabellen erstellen und
die Speicherungsmethode nicht nachträglich mühsam ändern wollen, dann sind vielleicht
folgende Parameter – welche Sie auch auf Session Ebene ändern können – interessant:
Name
DB_SECUREFILE
_KDLXP_LOBCOMPRESS
_KDLXP_LOBDEDUPLICATE
Bedeutung
NEVER
bedeutet,
dass
SecureFiles
mit
Fehler
verweigert werden.
PERMITTED bedeutet, dass
SecureFiles angelegt werden
können, es muss aber auch so
spezifiziert werden.
Bei ALWAYS werden wenn
möglich immer SecureFiles
angelegt.
Bei
IGNORE
werden
SecureFiles immer ignoriert
und BasicFiles angelegt.
Wenn
TRUE
wird
ein
SecureFile
immer
komprimiert
(medium
Kompression) unabhängig der
Spezifikation.
Wenn
TRUE
wird
ein
SecureFile immer dedupliziert
angelegt unabhängig der
Spezifikation.
Defaultwert
PERMITTED
FALSE
FALSE
BasicFile vs. SecureFile
Oracle verspricht mit den neuen SecureFiles – wie schon gesagt – eine verbesserte
Zugriffsperformance. Bei meinen Tests und Vergleichen von Performancewerten stellte ich fest,
dass die Relation stark von der Leistung der Hardware abhängig ist. In der folgenden Tabelle
sind Lese- und Schreibwerte eines 100 MB LOBs auf verschiedener Hardware
www.trivadis.com
Seite 3 / 15
zusammengestellt, installiert war jeweils Oracle 11.1.0.7.0. Oracle Blocksize war 8k und die
Chunksize des BasicFiles 8K. Das LOB stand auf dem Default LOGGING, die Datenbank war
im NOARCHIVELOG Modus. Für die LOBs wurde der Default NOCACHE2 verwendet.
Hardware Lesen, Vergleich Lesen eines LOBs Schreiben des LOBs
von 2 LOBs mittels 100 MB mittels mittels
dbms_lob.compare JDBC thin
dbms_lob.loadfromfile
(2 x 100MB)
A1
A2
B1
B2
BF
1,8 s
17,9 s
4,0 s
2,6 s
SF
1,2 s
5,1 s
11,5 s
1,5 s
BF
3,6 s
12,2 s
2,9 s
2,9 s
SF
3,2 s
4,9 s
3,6 s
3,5 s
BF
4,2 s
4,7 s
22,1 s
8,3 s
SF
1,5 s
4,1 s
79,0 s
75,3 s
Lesen
und
Schreiben, Kopie
eines
LOBs
innerhalb
des
selben Segments
mittels
insert
into
…
select …
BF
SF
5,2 s
3,6 s
15,2 s
6,3 s
12,5 s
79,7 s
6,7 s
76,3 s
Hardware:
A1: Linux x86, 2x Intel Xeon CPU 2.40GHz, I/O Durchsatz mit dd: 80 MB/s
A2: wie A1, Datenfiles jetzt auf nfs, I/O Durchsatz nun 40 MB/s
B1: Solaris 10, Sun Fire V210, 2x UltraSPARC-IIIi 1336 MHz, I/O Durchsatz mit dd: 48 MB/s.
B2: wie B1, Datenfiles jetzt auf einer ramdisk
BF .. BasicFile
SF .. SecureFile
Durch die Tests konnte ganz klar erkannt werden, dass BasicFiles lediglich Ansprüche an das
I/O Subsystem und kaum an die CPU stellen. Das ist bei SecureFiles ganz offensichtlich nicht
so. SecureFiles Performance ist stark von der CPU Leistung abhängig. SecureFiles können nur
mit starken CPUs die BasicFiles auf der Strecke lassen, was sie dann aber auch sehr beachtlich
machen. Ist die CPU hingegen schwach, so wird ein SecureFile Zugriff langsamer als ein
BasicFile Zugriff auf derselben Hardware. Daraus folgt, dass für den Einsatz von SecureFiles
adäquate Hardware verwendet werden muss, um auch den von Oracle prophezeiten
Performancegewinn zu erzielen.
Kurzum SecureFiles sind nur auf aktueller leistungfähiger Hardware schneller als BasicFiles.
LOB Komprimierung
Die LOB Komprimierung ermöglicht nun SecureFiles binär zu komprimieren. Dadurch kann der
Speicherplatz abhängig von Komprimierbarkeit reduziert werden. Es ist als ein Trade Off
zwischen Storage Reduzierung und CPU Nutzung zu verstehen, denn das Komprimieren und
Dekomprimieren verlangt natürlich etwas mehr CPU Ressourcen. Komprimierung kann in zwei
verschiedenen Stufen eingeschaltet werden: MEDIUM und HIGH. Sie steuert letztendlich die
Aggressivität des Komprimierungsalgorithmus, der auf ZLIB3 basiert. Die Komprimierung erfolgt
transparent für die Zugriffsschicht, d.h. bestehende Applikationen brauchen darauf nicht
angepasst zu werden. LOB Komprimierung lässt sich pro Segment also, pro Tabelle, Partition
2
In diesem Tests ging es um die native I/O Performance, daher wurde nicht gecached. Bei einem Caching
des LOBs würden die Ergebnisse natürlich abweichen.
3
Eine Session mit dem Debugger gdb zeigte die verwendete Oracle Kernel Funktion: kgcczlibdo.
www.trivadis.com
Seite 4 / 15
oder Subpartition einstellen. Ohne diese neue LOB Komprimierung stand bisher lediglich eine
manuelle Komprimierung auf Applikationsschicht oder in der Datenbank, z.B. durch das in 10g
eingeführte Package UTL_COMPRESS zu Verfügung.
Betrachten wir folgendes Beispiel: Ein „großes“ Textfile und ein Zip Archiv, welches dieses File
enthält werden betrachtet.
# ls -al
-rw-r--r--rw-r--r--
1 oracle
1 oracle
dba
dba
105000720 Jul 14 11:57 large_txt_file.txt
27063480 Jul 15 16:15 large_txt_file.zip
Die Textdatei ist ca. 100 MB groß. Das mit Standard zip gepackte Archiv ist ca. 26 MB groß. 74
Prozent des Storage wurden damit also eingespart. Jetzt wollen wir sehen, ob das mit der
Oracle Datenbank auch möglich ist. Dafür legen wir 4 Tabellen an. Das LOB Feld in der
Tabelle ist jedesmal anders gespeichert. Einmal als BasicFile, einmal als Standard SecureFile,
einmal als medium komprimiertes SecureFile und zuguterletzt noch als hoch komprimiertes
SecureFile. In diese LOBs laden wir dann die Textdatei und vergleichen die Größen:
Elapsed: 00:00:00.06
SQL> create table lobtest_sf
2
(
3
id number not null,
4
data blob
5 )
6
lob (data) store as securefile datablob_sf
(disable storage in row)
7 ;
SQL>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Table created.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
SQL> create table lobtest_comp_med
2
(
3
id number not null,
4
data blob
5 )
6
lob (data) store as securefile
datablob_comp_med (disable storage in row
compress medium)
7 ;
Elapsed: 00:00:05.88
Table created.
1 row created.
Elapsed: 00:00:00.03
SQL> create table lobtest_comp_high
2
(
3
id number not null,
4
data blob
5 )
6
lob (data) store as securefile
datablob_comp_high (disable storage in row
compress high)
7 ;
Elapsed: 00:00:06.54
SQL> insert into lobtest_comp_high
2 select * from lobtest_bf;
Table created.
Elapsed: 00:00:00.02
SQL> select segment_name,bytes from
user_segments
2 where segment_type = 'LOBSEGMENT';
SQL> create table lobtest_bf
2
(
3
id number not null,
4
data blob
5 )
6
lob (data) store as basicfile datablob_bf
(disable storage in row)
7 ;
Table created.
Elapsed: 00:00:00.04
SQL> insert into lobtest_bf (id,data) values
2 (1,empty_blob());
1 row created.
Elapsed: 00:00:00.00
declare
dest_loc
blob;
src_loc
bfile;
amount
integer;
begin
src_loc:=
bfilename('DIR','large_txt_file.txt');
select data into dest_loc
from lobtest_bf for update;
amount := dbms_lob.getlength(src_loc);
dbms_lob.open(src_loc,
dbms_lob.lob_readonly);
dbms_lob.open(dest_loc,
dbms_lob.lob_readwrite);
dbms_lob.loadfromfile(dest_loc,
src_loc, amount);
dbms_lob.close(dest_loc);
dbms_lob.close(src_loc);
end;
/
SQL> insert into lobtest_sf
2 select * from lobtest_bf;
1 row created.
Elapsed: 00:00:03.26
SQL> insert into lobtest_comp_med
2 select * from lobtest_bf;
1 row created.
Elapsed: 00:00:21.13
SQL> commit;
Commit complete.
SEGMENT_NAME
BYTES
------------------------------ ---------DATABLOB_BF
109051904
DATABLOB_SF
189005824
DATABLOB_COMP_MED
36962304
DATABLOB_COMP_HIGH
31719424
Elapsed: 00:00:00.13
www.trivadis.com
Seite 5 / 15
Die längeren Laufzeiten der Inserts erklären sich durch den zusätzlichen Aufwand der
Komprimierung.
Der eigentliche Platzbedarf in den Segmenten ist noch geringer, da in der obigen Query auch
leere vorreservierte Blöcke in den Extents mitgerechnet wurden. Der genaue Platzbedarf lässt
sich mit dem DBMS_SPACE Package ermitteln, welches dafür mit 11g einen neuen Aufruf der
Prozedur SPACE_USAGE bekommen hat. Dafür habe ich folgende PL/SQL Prozedur genutzt.
Die Prozedur erwartet als Eingabe den Segmentnamen und funktioniert für SecureFiles im
aktuellen Schema.
create or replace procedure lobsegmentsize(lobsegment_name varchar2)
as
segment_size_blocks number;
segment_size_bytes number;
used_blocks number;
used_bytes number;
expired_blocks number;
expired_bytes number;
unexpired_blocks number;
unexpired_bytes number;
begin
dbms_space.space_usage(user,lobsegment_name,'LOB',
segment_size_blocks,segment_size_bytes,used_blocks,used_bytes,
expired_blocks,expired_bytes,unexpired_blocks,unexpired_bytes);
dbms_output.put_line(lobsegment_name);
dbms_output.put_line('-----------------');
dbms_output.put_line('segment_size_bytes = '||segment_size_bytes);
dbms_output.put_line('used_bytes = '||used_bytes);
end;
/
SQL> exec lobsegmentsize('DATABLOB_SF');
DATABLOB_SF
----------------segment_size_bytes = 189005824
used_bytes = 106741760
PL/SQL procedure successfully completed.
SQL> exec lobsegmentsize('DATABLOB_COMP_MED');
DATABLOB_COMP_MED
----------------segment_size_bytes = 36962304
used_bytes = 32235520
PL/SQL procedure successfully completed.
SQL> exec lobsegmentsize(DATABLOB_COMP_HIGH');
DATABLOB_COMP_HIGH
----------------segment_size_bytes = 31719424
used_bytes = 27574272
PL/SQL procedure successfully completed.
Der Platzbedarf des hoch komprimierten SecureFiles liegt also auch bei ca. 26 MB und kommt
damit auch an die Rate von zip heran. Wir können also durchaus gute Platzersparnisse erzielen.
Wie gut sich Daten jedoch überhaupt komprimieren lassen hängt natürlich so wie bei jeder
Binärkomprimierung von ihrem Typ ab.
LOB Deduplizierung
Betrachtet man Komprimierungsalgorithmen im Allgemeinen, so gibt es immer ein wesentliches
Prinzip: Wiederholt auftauchende Werte, Sequenzen, Symbole etc. werden nicht wiederholt
gespeichert, sondern dedupliziert. Dieses Prinzip gibt es nun auch in der Oracle Datenbank für
ganze LOBs. Legt man eine Tabelle, Partition oder Subpartition entsprechend an, so werden
sich wiederholende LOBs nur einmal gespeichert. Das geschieht, indem beim Einfügen zur
Laufzeit eine Checksumme berechnet wird. Gibt es diese Checksumme schon im gleichen
www.trivadis.com
Seite 6 / 15
Segment, so wird nur noch eine Referenz auf das schon existierende LOB gespeichert. Die
Checksumme wird defaultmäßig mit SHA1 berechnet.4 Die Erkennung von Duplikaten kann nur
in demselben Segment erfolgen, d.h. es funktioniert nicht partitionsübergreifend und schon gar
nicht tabellenübergreifend. Dieses Feature ist besonders interessant für Dokumentenmanagementsysteme, in welchen Dokumente in Form von LOBs durch zum Beispiel
Multiversions- oder Multiusermanagement redundant gespeichert werden.
Betrachten wir noch einmal das Beispiel von oben:
SQL>
2
3
4
5
6
7
create table lobtest_dedup
(
id number not null,
data blob
)
lob (data) store as securefile datablob_dedup (disable storage in row deduplicate)
;
Table created.
Elapsed: 00:00:00.10
SQL> insert into lobtest_dedup select * from lobtest_sf;
1 row created.
Elapsed: 00:00:06.90
SQL> insert into lobtest_dedup select * from lobtest_sf;
1 row created.
Elapsed: 00:00:03.07
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> select count(*) from lobtest_sf;
COUNT(*)
---------1
Elapsed: 00:00:00.00
SQL> select count(*) from lobtest_dedup;
COUNT(*)
---------2
Elapsed: 00:00:00.01
SQL> select segment_name,bytes from user_segments
2 where segment_name in ('DATABLOB_SF','DATABLOB_DEDUP');
SEGMENT_NAME
-------------------DATABLOB_DEDUP
DATABLOB_SF
BYTES
---------151257088
189005824
Elapsed: 00:00:00.57
SQL> exec lobsegmentsize('DATABLOB_SF');
DATABLOB_SF
----------------segment_size_bytes = 189005824
used_bytes = 106741760
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL> exec lobsegmentsize('DATABLOB_DEDUP');
DATABLOB_DEDUP
----------------segment_size_bytes = 151257088
used_bytes = 106741760
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
4
Der hidden Parameter _kdlxp_dedup_hash_algo legt den Algorithmus fest. SHA2 und MD5 sind
ebenfalls verfügbar.
www.trivadis.com
Seite 7 / 15
Wir können sehen, dass das erneute Einfügen des LOBs, welches schon im Segment vorhanden
ist zu dem gewünschten Effekt führt. Dadurch sparen wir Speicherplatz. Zusätzlich gewinnen
wir in dem Falle eines Inserts wie hier auch an Performance, da „nur“ noch der SHA1 Hash
gespeichert werden muss.
Performance Einflüsse der LOB Komprimierung
LOB Komprimierung und LOB Deduplizierung können wie schon erwähnt einzeln oder auch
kombiniert Anwendung finden. Doch welchen Performance Overhead kosten diese Features?
Wie sollten die LOBs für beste Performance konfiguriert werden? Diese Fragen sollen nun
beleuchtet werden. Ich habe dafür Tests mit LOBs in verschiedenen Szenarien gemacht. Die
LOBs wurden dabei gelesen und geschrieben. Die LOBs wurden mit unterschiedlichen
Einstellungen in Bezug auf ihre Art der Komprimierung, des Caching, der Blocksize des
zugrundeliegenden Tablespaces angelegt.
Leseperformance
Zunächst ein Beispiel anhand einer kleinen „Tuning Session“: Ausgangspunkt ist eine Tabelle
documents mit einem BLOB.
SQL> desc documents
Name
----------------------------------------ID
VERSION
NAME
MIME_TYPE
BYTES
MODIFIED
DATA
Null?
-------NOT NULL
NOT NULL
NOT NULL
Type
---------------------------NUMBER
NUMBER
VARCHAR2(255)
VARCHAR2(30)
NUMBER
DATE
BLOB
Diese Tabelle hat für diesen Test lediglich einen Datensatz mit dem bekannten Textfile:
SQL> select * from documents;
ID
VERSION NAME
MIME_TYPE
BYTES MODIFIED
---------- ---------- -------------------- ------------------------------ ---------- --------1
1 large_txt_file.txt
text/plain
105000720 08-SEP-09
Folgender PL/SQL Block, welche das LOB liest, soll „getuned“ werden.
declare
lob_1 blob;
lob_2 blob;
length number;
begin
select data,dbms_lob.getlength(data) into lob_1,length from documents
where id=1 and version=1;
dbms_lob.createtemporary(lob_2,cache => &1);
dbms_lob.copy(lob_2,lob_1,length);
end;
/
Ca. 10 Sekunden dauert der Lauf für das BasicFile5 mit dem Default cache = false in dem
CREATETEMPORARY Aufruf. Das kann man doch vielleicht noch schneller schaffen: Man
ändert in der PL/SQL Prozedur in dem Aufruf von CREATETEMPORARY den Parameter cache
auf true, und nun sind es knapp 3 Sekunden. Der Grund dafür ist, dass temporary LOBs die
nicht gecached sind, durch I/O Operationen auf dem TEMP Tablespace realisiert werden, egal
5
Hardware: Linux x86, 2x Intel Xeon CPU 2.40GHz, I/O Durchsatz mit dd: 80 MB/s
www.trivadis.com
Seite 8 / 15
wie groß sie sind. In den Wait Events macht sich das durch vermehrtes „direct path read/write
temp“ bemerkbar. Cached temporary LOBs werden hingegen im Memory realisiert, was für
kleine bis mittelgroße LOBs anzustreben ist.
Okay, aber die I/Os auf das gelesene LOB sind auch noch alles direct path reads, d.h. die
gelesenen Blöcke werden nicht gecached. Cached man das Ganze, dann wäre wohl noch mehr
herauszuholen.
SQL> alter table documents modify lob (data) (cache reads);
Table altered.
Nein Irrtum – in meinen Tests verbessert es die Raten nicht.
Wie sieht es nun allerdings mit SecureFiles und komprimierten SecureFiles aus?
alter table documents move lob (data) store as securefile;
alter table documents modify lob (data) (nocache);
Gute 2 Sekunden dauert der PL/SQL Block auf dem SecureFile.
Bringt das Caching hier etwas?
alter table documents modify lob (data) (cache reads);
Nein – nicht wirklich. Die Werte für einen Lauf schwanken hier auf einmal zwischen 2 und 5
Sekunden.
Na gut – dann wird jetzt noch komprimiert:
alter table documents move lob (data) store as securefile (compress high);
alter table documents modify lob (data) (nocache);
Ob es dadurch schneller wird? Wohl kaum, wir haben ja den Overhead des Dekomprimierens.
In der Tat, es dauert ca. 5 Sekunden. Und bringt das Cachen hier etwas?
alter table documents modify lob (data) (cache reads);
Ja, es sind ca. 4 Sekunden! Auch schon im ersten Lauf ohne „Cache Warmup“. Aber warum?
Warum bringt es was bei komprimierten Daten? Diese Frage wird später beantwortet.
Zuguterletzt war da ja noch der Tablespace mit der größeren Blocksize:
alter table documents move lob (data) store as securefile (compress high tablespace users_16k);
alter table documents modify lob (data) (nocache);
Ja – das bringt auch etwas. Knapp 3 Sekunden läuft der PL/SQL Block nun.
Warum hat das Cachen auf der komprimierten Tabelle nun etwas gebracht?
In meinen Tests bemerkte ich, dass die Werte für „physical read bytes“ in der View V$SESSTAT
höher lagen, als das BLOB eigentlich groß war, und das obwohl ich erwartete, dass es durch die
Komprimierung ja kleiner sein muss. Das LOB ist im Segment defacto ja auch kleiner, wie wir
vorher schon gesehen haben. D.h. es kann nur so sein, dass gewisse Blöcke mehrfach gelesen
werden. Und diese „dunkle“ Ahnung hat sich bestätigt, als ich die Leseoperationen mit einem
Trace mittels DBMS_MONITOR.SESSION_TRACE_ENABLE untersucht habe. Folgendermaßen
sah das Lesen eines komprimierten LOBs aus:
www.trivadis.com
Seite 9 / 15
Lesen eines medium komprimierten LOBs
210000
209000
Blocknummer
208000
207000
206000
205000
204000
203000
Zeit
In der Grafik sind von den Multiblock I/Os Operationen (direct path read) jeweils der Startblock
angegeben. Der von Oracle gewählte Algorithmus zum Dekomprimieren erfordert offenbar das
wiederholte Lesen von Blöcken. Warum das so ist, konnte oder wollte Oracle mir in einem
laufenden Service Request bis heute nicht erklären. Nun, und durch das wiederholte Lesen der
Blöcke bringt das Cachen natürlich etwas. Folgendes Beispiel zeigt das deutlich, hier werden
zwei medium komprimierte LOB mittels DBMS_LOB.COMPARE verglichen:
SQL> REM eine zweite Row mit demselben LOB erzeugen
SQL> insert into documents select 1,2,name,mime_type,bytes,modified,data from documents;
1 row created.
SQL> commit;
Commit complete.
SQL> alter table documents move lob (data) store as securefile (compress medium);
Table altered.
SQL> REM neu connecten um v$mystat zu "initialisieren"
SQL> connect scott/tiger
Connected.
SQL> set serveroutput on timing on
SQL> column name format a30
SQL> column value format 999999999999999
SQL> REM Wie groß ist das LOB-Segment?
SQL> exec lobsegmentsize('DATABLOB');
DATABLOB
----------------segment_size_bytes = 86179840
used_bytes = 64462848
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
www.trivadis.com
Seite 10 / 15
SQL> REM der dbms_lob.compare Aufruf
SQL> declare
2
lob_1 blob;
3
lob_2 blob;
4
retval integer;
5 begin
6
select data into lob_1 from documents
7
where id=1 and version=1;
8
select data into lob_2 from documents
9
where id=1 and version=2;
10
retval := dbms_lob.compare(lob_1, lob_2);
11
if retval = 0 then
12
dbms_output.put_line('equal');
13
else
14
dbms_output.put_line('not equal');
15
end if;
16 end;
17 /
equal
PL/SQL procedure successfully completed.
Elapsed: 00:00:47.76
SQL> REM Wieviel wurde gelesen?
SQL> select name, value from v$mystat natural join v$statname where name = 'physical read bytes';
NAME
VALUE
------------------------------ ---------------physical read bytes
11993120768
Elapsed: 00:00:00.00
SQL> REM Und jetzt das LOB auf CACHE READS setzen
SQL> alter table documents modify lob (data) (cache reads);
Table altered.
Elapsed: 00:00:02.33
SQL> REM den Buffer Cache ausleeren um etwaige Seiteneffekte auszuschließen
SQL> conn / as sysdba
Connected.
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:01.59
SQL> REM neu connecten um v$mystat zu "initialisieren"
SQL> conn scott/tiger
Connected.
SQL> set serveroutput on
SQL> REM der dbms_lob.compare Aufruf
SQL> declare
2
lob_1 blob;
3
lob_2 blob;
4
retval integer;
5 begin
6
select data into lob_1 from documents
7
where id=1 and version=1;
8
select data into lob_2 from documents
9
where id=1 and version=2;
10
retval := dbms_lob.compare(lob_1, lob_2);
11
if retval = 0 then
12
dbms_output.put_line('equal');
13
else
14
dbms_output.put_line('not equal');
15
end if;
16 end;
17 /
equal
PL/SQL procedure successfully completed.
Elapsed: 00:00:18.10
SQL> REM Und wieviel wurde jetzt von Disk gelesen?
SQL> select name, value from v$mystat natural join v$statname where name = 'physical read bytes';
NAME
VALUE
------------------------------ ---------------physical read bytes
64438272
Elapsed: 00:00:00.00
Durch das Caching des LOBs wurden die physical reads von über 11 GB !! auf 61 MB reduziert.
Das entspricht einer I/O Reduktion um 99,5 Prozent !!!. Die Laufzeit hat sich um ca. 60 Prozent
verkürzt. Das sind enorme Faktoren.
www.trivadis.com
Seite 11 / 15
In folgender Tabelle sind die gemessenen Werte für verschiedene LOB Einstellungen
zusammengefasst:
LOB Einstellungen
NOCOMPRESS
KEEP_DUPLICATES
COMPRESS
MEDIUM
KEEP_DUPLICATES
COMPRESS HIGH
KEEP_DUPLICATES
NOCOMPRESS
DEDUPLICATE
COMPRESS
MEDIUM
DEDUPLICATE
COMPRESS HIGH
DEDUPLICATE
NOCACHE
Physical Zeit
LOB
Segment Reads in in s
MB
Größe
in MB
204
204
1,3
CACHE READS
Physical Zeit
Reads in in s
MB
I/O
Einsparung
in Prozent
Zeit
Einsparung
in Prozent
204
2,9
0
-123
61
11438
53,0
61
19,7
99,5
62,8
53
10553
48,7
53
18,6
99,5
61,8
102
12424
38,5
102
2,2
99,2
94,3
31
882
6,5
31
4,9
96,5
24,7
26
791
7,0
26
4,4
96,7
37,1
Die Einstellung „CACHE“ bei den LOBs ist nicht extra ausgewiesen, da sich bei lesenden
Operationen der Effekt von „CACHE“ nicht von dem von „CACHE READS“ unterscheidet.
Gut – diese fiktiven „Tuning“ Sessions bezogen sich lediglich auf das Lesen mittels DBMS_LOB.
Dennoch werden hier einige wichtige Grundsätze klar:
- Caching in der API oder am Client sind ein wesentlicher Aspekt.
- Caching von temporären LOBs (hier in DBMS_LOB.CREATETEMPORARY) steigert die
Performance, da TEMP Tablespace I/O-Operationen vermieden werden
- Eine größere Blocksize verbessert die Leseperformance von großen LOBs6. Außerdem ist
hier noch der Aspekt des separaten Caches anzubringen. Durch einen eigenen Cache,
der für das Betreiben von Tablespaces mit einer Blocksize verschieden der Datenbank
Default Blocksize notwendig ist, wird die Cache Hit Ratio des Default Buffer Cache
nicht durch etwaiges LOB Caching beeinflusst. Dadurch wird die Performance für
„normale“ Tabellen nicht in Gefahr gebracht.
- Caching am Server lohnt sich im Spezialfall des Lesens von komprimierten und/oder
deduplizierten LOBs – und das nicht nur bei wiederholtem Lesen.
Erstaunlich ist, dass auch die Deduplizierung diesen Effekt mit dem multiplen Lesen mit sich
bringt, welcher im Falle NOCOMPRESS noch relativ dramatisch ist, sich jedoch mit der
Komprimierung dann etwas relativiert, auch schon ohne Caching.
Ähnliche Performancekennzahlen und -trends wurden auch beim lesenden Zugriff mit JDBC
festgestellt, sowohl für JDBC OCI als auch für JDBC Thin Zugriffe. Daher kann ausgeschlossen
werden, dass es sich bei den oben gemessenen Werten „nur“ um ein Phänomen im
Zusammenhang mit der DBMS_LOB API handelt.
6
Vorsicht: Verwenden Sie keine Blocksize für LOBs die größer ist als die durchschnittliche Größe der
LOBs. Das verschwendet zu viel Platz.
www.trivadis.com
Seite 12 / 15
Schreibperformance
Wir machen mit dem oberen Beispiel weiter und wollen nun schließlich noch die
Schreibperformance untersuchen. Die 2 Datensätze aus der Tabelle werden dabei mittels Insert
in eine vorbereitete Tabelle kopiert und dabei die Zeit sowie die physical reads und writes
gemessen. Die Quelltabelle wird zunächst wieder auf Standard SecureFile zurückgestellt. Dann
wird die neue Table erstellt und die Daten hineinkopiert:
SQL> alter table documents move lob (data) store as securefile (nocache nocompress keep_duplicates);
Table altered.
Elapsed: 00:00:12.71
SQL> CREATE TABLE documents_comp_med
2
(
3
ID NUMBER NOT NULL ENABLE,
4
VERSION NUMBER NOT NULL ENABLE,
5
NAME VARCHAR2(255) NOT NULL ENABLE,
6
MIME_TYPE VARCHAR2(30),
7
BYTES NUMBER,
8
MODIFIED DATE,
9
DATA BLOB
10 )
11
LOB (DATA) STORE AS SECUREFILE DATABLOB_COMP_MED (DISABLE STORAGE IN ROW COMPRESS MEDIUM)
12 ;
Table created.
Elapsed: 00:00:00.06
SQL> REM neu connecten um v$mystat zu "initialisieren"
SQL> conn scott/tiger
Connected.
SQL> insert into documents_comp_med select * from documents;
2 rows created.
Elapsed: 00:00:26.32
SQL> commit;
Commit complete.
Elapsed: 00:00:00.11
SQL> select name, value from v$mystat natural join v$statname
2 where name in ('physical read bytes','physical write bytes','redo size');
NAME
VALUE
------------------------------ ---------physical read bytes
213950464
physical write bytes
64438272
redo size
240432
Elapsed: 00:00:00.01
SQL> alter table documents_comp_med modify lob (data) (cache);
Table altered.
Elapsed: 00:00:00.04
SQL> truncate table documents_comp_med;
Table truncated.
Elapsed: 00:00:04.72
SQL> REM neu connecten um v$mystat zu "initialisieren"
SQL> conn scott/tiger
Connected.
SQL> insert into documents_comp_med select * from documents;
2 rows created.
Elapsed: 00:00:33.02
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> select name, value from v$mystat natural join v$statname
2 where name in ('physical read bytes','physical write bytes','redo size');
NAME
VALUE
------------------------------ ---------physical read bytes
214130688
www.trivadis.com
Seite 13 / 15
physical write bytes
redo size
0
64597740
Elapsed: 00:00:00.01
Es ist also festzustellen, dass das Caching hier das Schreiben verzögert, also eher einen
negativen Effekt hat. Folgende Tabelle fasst die Performancedaten für diesen Lauf und für alle
anderen Arten von Tabellen zusammen. Quelle der beiden Datensätze war immer die gleiche
Tabelle mit dem Standard SecureFile. Da die Zeiten hier teilweise recht stark schwankten, habe
ich hier die durchschnittlichen Werte aus 10 Läufen notiert:
LOB Einstellungen der LOB
Zieltabelle
Segment
Größe in
MB
NOCOMPRESS
204
KEEP_DUPLICATES
COMPRESS MEDIUM 61
KEEP_DUPLICATES
COMPRESS
HIGH 53
KEEP_DUPLICATES
NOCOMPRESS
102
DEDUPLICATE
COMPRESS MEDIUM 31
DEDUPLICATE
COMPRESS
HIGH 26
DEDUPLICATE
NOCACHE
Physical
Redo
Writes in Size
MB
in MB
Ø
Zeit
in s
CACHE
Physical
Redo
Ø
Writes in Size in Zeit
MB7
MB
in s
204
0,6
8,6
0
206
66,6
61
0,3
24,0
0
62
34,2
53
0,2
70,4
0
53
66,3
102
0,3
7,9
0
103
36,5
31
0,1
26,9
0
31
22,9
26
0,1
63,6
0
26
58,2
Die Einstellung „CACHE READS“ bei den LOBs ist nicht extra ausgewiesen, da sich bei
schreibenden Operationen der Effekt von „CACHE READS“ nicht von dem von „NOCACHE“
unterscheidet. Alle LOBs standen auf normalem LOGGING, d.h. über die Redo Logs wurde
sichergestellt, dass sie wiederherstellbar / recoverable sind.
Diese Zahlen offenbaren nun recht interessante Erkenntnisse: Wie schon erwähnt – bei der
Tabelle mit dem medium komprimierten LOBs macht das Caching fürs Schreiben wenig Sinn.
Die Laufzeit erhöht sich im Schnitt um 10 Sekunden. Jedoch kippt dieses Bild bei Steigerung
der Komprimierungsrate und/oder bei der Hinzunahme von Deduplizierung, auch wenn es
nicht zu enormen Performancesteigerungen kommt. Jedoch Vorsicht: Die Raten für das
Schreiben in cached LOBs sind auch stark von der Performance des Log Writers abhängig, z.B.
von der Anzahl der Redo Log Member pro Redo Log Gruppe. In meinen Tests wurde nicht
gespiegelt. In Produktionsumgebungen sollte aber unbedingt gespiegelt werden, d.h. dort
relativiert sich das Bild wieder. Ein weiterer Aspekt ist, dass es sich hier um ein großes
Textdokument handelte, welches sehr stark komprimiert werden konnte.
Alles in allem denke ich, dass das Cachen von SecureFile LOBs schon beim Schreiben nicht
generell zu empfehlen ist. Es mag aber den einen oder anderen Ausnahmefall geben, bei
welchem es sich lohnt. Dieses wäre dann aber für die jeweilige Applikation in ausführlichen
Tests zu verifizieren.
Das Schreiben in LOBs mittels DBMS_LOB.LOADFROMFILE oder mit jdbc zeigte ähnliche
Resultate. Im Falle der Verwendung von „großen“ LOBs ist eine höhere Blocksize für die LOB
Tablespaces auch im Hinblick auf die Schreiboperationen zu empfehlen.
7
Im Falle „Cache“ wird nicht von der Server Session direkt in die Datafiles geschrieben. Das übernimmt
hier der Database Writer mit einer Verzögerung. Daher misst die Session keinerlei „physical write bytes“.
www.trivadis.com
Seite 14 / 15
Empfehlungen zum Setup von komprimierten LOBs
Folgende Empfehlungen für eine beste Performance ergeben sich nun unter anderem aus
meinen Tests:
- Verwenden Sie komprimierte LOBs nur auf aktueller leistungsfähiger Hardware.
- Verwenden Sie „enable storage in row“ nur, wenn in die Majorität der Queries auf der
Table das entsprechende LOB mit abfragt, ansonsten verwenden Sie „disable storage in
row“.
- Verwenden Sie eigene Tablespaces für die großen LOBs und geben Sie diesen eine
höhere Blocksize. Das hat auch den Vorteil, dass damit für die LOBs ein weiterer
eigener Buffer Cache entsteht.
- Setzen Sie die komprimierten LOBs auf CACHE READ.
- Setzen sie die Parameter für die LOB Retention nur so gering wie nötig.
- Erwägen Sie den Einsatz von FILESYSTEM_LIKE_LOGGING oder gar NOLOGGING
nachdem Sie sich über das Risiko von Nologging Operationen bewusst sind.
- Testen und verifizieren Sie Ihr Setup für die beste Performance.
Fazit
Oracle LOB Komprimierung und Deduplizierung sind eine gute Möglichkeit Speicherplatz zu
sparen. Oracle verspricht auf seiner Homepage8 auch die Performance zu verbessern. Zu
diesem Punkt muss man jedoch ganz klar sagen, dass es von vielen Faktoren abhängt, ob die
Performance wirklich besser wird. Sie kann unter gewissen Umständen auch schlechter werden.
Gute Tests bevor man das Feature in Produktion einsetzt sind daher wichtig. Immer viel Freude
an Ihren komprimierten LOBs wünscht
Mathias Zarick
Trivadis GmbH
Millennium Tower
Handelskai 94-96
A-1200 Wien
Tel.: +43 1 332 35 31 00
Fax: +43 1 332 35 34
[email protected]
www.trivadis.com
Kontaktieren Sie mich bitte, wenn Sie weitere Details oder Hilfe beim Setup von komprimierten
LOBs benötigen.
Quellenangabe
[1] Oracle, Oracle Database SecureFiles and Large Objects Developer's Guide, 11g Release 1,
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28393.pdf
[2] Oracle, Oracle Database PL/SQL Packages and Types Reference, 11g Release 1,
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419.pdf
8
http://www.oracle.com/database/advanced-compression.html
www.trivadis.com
Seite 15 / 15