Montag, Februar 04, 2013

Bestimmung der compression von Tabellen

Heute wurde im OTN Forum die Frage gestellt: woran kann man erkennen, ob die Inhalte einer Partition komprimiert sind? Ich denke, das ist eine ziemlich interessante Frage, und eine, auf die Jonathan Lewis in einem Kommentar zu einem seiner Artikel zum Thema compression auf der AllThingsOracle-Seite vor kurzem eine kompakte Antwort gegeben hat:
Q: Is there a way to tell if the data is actually compressed?
A: In effect, no. 
You might be able to make a reasonable guess - check the average rows per block (num_rows / blocks), then cross-check with the avg_row_len (which reports the uncompressed row length, even when the rows are compressed). You could also use dbms_rowid to convert rowids to file and block numbers and check the number of rows per block individually - because you might have a table where half the data was compressed and half wasn't. 
In principle there is a method, mentioned originally in the 9.2 manuals I think. Column spare1 of table sys.seg$ has two bits set to show whether or not a segment is compressed and whether it holds any compressed data, so you could query seg$ (joined to various other data dictionary tables) to check. The tests are: bitand(spare1, 2048) = 2048 to check if the segment is defined with compression, and bitand(spare1, 4096) = 4096 to check if any of the data is compressed - unfortunately when I did a quick check of bit 4096 in a recent version of Oracle I found that it got set as soon as bit 2048 got set. Even it it worked, of course, all you could learn from a single bit was that some of the data in the table was compressed, you'd still have no idea about how much was compressed.
Auf Anhieb könnte man meinen, dass der Fall viel einfacher läge, denn schließlich enthält dba_tab_partitions eine Spalte COMPRESSION (und eine weiter Spalte COMPRESS_FOR). Aber darin steht nur der für folgende Operationen gültige Status: zum aktuellen Zustand gibt es keine Aussage. Dazu ein Beispiel:

drop table test_part_compress;

create table test_part_compress ( 
    startdate date
  , col1 number
  , col2 varchar2(100) 
)
partition by range (startdate) (
    partition p1 values less than (to_date('01.01.2013','dd.mm.yyyy'))
  , partition p2 values less than (to_date('01.02.2013','dd.mm.yyyy'))
) tablespace test_ts;

insert into test_part_compress
select to_date('31.12.2012', 'dd.mm.yyyy') + mod(rownum, 2) startdate
     , mod(rownum, 10) col1
     , lpad('*', 100, '*') col2
  from dual
connect by level <= 10000;

exec dbms_stats.gather_table_stats(user, 'test_part_compress')

select partition_name
     , num_rows
     , blocks
     , compression
     , compress_for
  from dba_tab_partitions
 where table_name = 'TEST_PART_COMPRESS';

PARTITION_NAME                   NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1                                   5000         84 DISABLED
P2                                   5000         84 DISABLED

alter table test_part_compress modify partition p1 compress;

exec dbms_stats.gather_table_stats(user, 'test_part_compress')

select partition_name
     , num_rows
     , blocks
     , compression
     , compress_for
  from dba_tab_partitions
 where table_name = 'TEST_PART_COMPRESS';

PARTITION_NAME                   NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1                                   5000         84 ENABLED  BASIC
P2                                   5000         84 DISABLED

alter table test_part_compress move partition p1;

exec dbms_stats.gather_table_stats(user, 'test_part_compress')

select partition_name
     , num_rows
     , blocks
     , compression
     , compress_for
  from dba_tab_partitions
 where table_name = 'TEST_PART_COMPRESS';

PARTITION_NAME                   NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1                                   5000          8 ENABLED  BASIC
P2                                   5000         84 DISABLED

alter table test_part_compress modify partition p1 nocompress;

select partition_name
     , num_rows
     , blocks
     , compression
     , compress_for
  from dba_tab_partitions
 where table_name = 'TEST_PART_COMPRESS';

PARTITION_NAME                   NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1                                   5000          8 DISABLED
P2                                   5000         84 DISABLED

Die Ergebnisse zeigen, dass die Angabe der COMPRESS oder NOCOMPRESS-Eigenschaft zunächst keine direkte Wirkung auf das vorliegende Segment hat: erst ein folgendes MOVE führt zur physikalischen Reorganisation. Das bedeutet dann auch, dass man im Fall der Partition P1 am Ende des Tests nicht ohne Weiteres bestimmen kann, ob ihr Inhalt komprimiert ist. Zur Bestimmung bleiben also die Vorschläge des Herrn Lewis. Dabei lasse ich die Prüfungen zur Größe außen vor, denn im Test ist der Unterschied zwischen 8 Blocks und 84 Blocks ausreichend deutlich:

select partition_name
     , header_file
     , header_block
  from dba_segments
 where segment_name = 'TEST_PART_COMPRESS';

PARTITION_NAME                 HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
P1                                       5       147272
P2                                       5       147104

select block#
     , blocks
     , bitand(spare1, 2048) compression_defined
     , bitand(spare1, 4096) compressed
  from sys.seg$ t
 where file# = 5
   and BLOCK# in (147104, 147272)

BLOCK#     BLOCKS COMPRESSION_DEFINED COMPRESSED
------ ---------- ------------------- ----------
147104         88                   0          0
147272         16                   0       4096

Das scheint also exakt die gewünschte Information zu liefern. Eine andere Variante zur Bestimmung nennt Randolf Geist in seinem Beitrag zum OTN-Thread: die Verwendung der Funktion dbms_compression.get_compression_type:

select subobject_name
     , data_object_id
  from dba_objects
 where object_name = 'TEST_PART_COMPRESS'
   and subobject_name is not null;

SUBOBJECT_NAME                 DATA_OBJECT_ID
------------------------------ --------------
P1                                     101978
P2                                     101977

select dbms_rowid.rowid_relative_fno(rowid) file_nr
     , dbms_compression.get_compression_type(user, 'TEST_PART_COMPRESS', rowid) compression_type
     , count(*) cnt
  from test_part_compress
 group by dbms_rowid.rowid_relative_fno(rowid)
        , dbms_compression.get_compression_type(user, 'TEST_PART_COMPRESS', rowid);

OBJECT COMPRESSION_TYPE        CNT
------ ---------------- ----------
101978                2       5000
101977                1       5000

Zumindest für den aktuell erreichten Zustand einer komprimierten Partition, deren Status aktuell NOCOMPRESS ist, sind die Tests demnach aussagekräftig: Für P1 wird der COMPRESSION_TYPE = 2 angegeben, der anscheinend für Basic und OLTP compression steht (sagt jedenfalls Kerry Osborne). Der Vollständigkeit halber hier noch ein paar weitere Schritte in die eingeschlagene Richtung:

-- move nach NOCOMPRESS-Definition
alter table test_part_compress move partition p1;
exec dbms_stats.gather_table_stats(user, 'test_part_compress')

PARTITION_NAME                   NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1                                   5000         74 DISABLED
P2                                   5000         84 DISABLED

-- beide Partitionen liefern jetzt COMPRESSION_TYPE = 1 (keine compression)
OBJECT COMPRESSION_TYPE        CNT
------ ---------------- ----------
101979                1       5000
101977                1       5000

BLOCK#     BLOCKS COMPRESSION_DEFINED COMPRESSED
------ ---------- ------------------- ----------
147096         80                   0          0
147104         88                   0          0

-- erneute COMPRESS-Definition
alter table test_part_compress modify partition p1 compress;
exec dbms_stats.gather_table_stats(user, 'test_part_compress')

PARTITION_NAME                   NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
P1                                   5000         74 ENABLED  BASIC
P2                                   5000         84 DISABLED

-- der COMPRESSION_TYPE bleibt 1, was plausibel ist, da die Daten ja tatsächlich nicht komprimiert sind
OBJECT COMPRESSION_TYPE        CNT
------ ---------------- ----------
101979                1       5000
101977                1       5000

-- aber seg$ behauptet, dass die Partition jetzt als COMPRESSed definiert ist und ihre Sätze komprimiert sind,
-- was nicht den Tatsachen entspricht
BLOCK#     BLOCKS COMPRESSION_DEFINED COMPRESSED
------ ---------- ------------------- ----------
147096         80                2048       4096
147104         88                   0          0

Auf Anhieb scheint dbms_compression.get_compression_type im gegebenen Fall also die brauchbarere Information zu liefern - und immerhin gibt es überhaupt Möglichkeiten, die unzureichende Aussage aus dba_tab_partitions zu ergänzen.

Keine Kommentare:

Kommentar veröffentlichen