DBA Hub

📋Steps in this guide1/3

DBMS_COMPRESSION Enhancements in Oracle Database 12c Release 1 (12.1.0.2)

Check out the enhancements to the DBMS_COMPRESSION package in Oracle Database 12c Release 1 (12.1.0.2).

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Setup

The examples in this article use the table defined below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id                NUMBER,
  code              VARCHAR2(20),
  description       VARCHAR2(50),
  clob_description  CLOB,
  created_date      DATE,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_Date)
(PARTITION tab1_part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION tab1_part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE users);

CREATE INDEX tab1_code_idx ON tab1(code) LOCAL;

INSERT INTO tab1
SELECT level,
       CASE
         WHEN MOD(level,2)=0 THEN 'CODE1'
         ELSE 'CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN 'Description for CODE1'
         ELSE 'Description for CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN 'CLOB description for CODE1'
         ELSE 'CLOB description for CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN TO_DATE('01/07/2015','DD/MM/YYYY')
         ELSE TO_DATE('01/07/2016','DD/MM/YYYY')
       END
FROM   dual
CONNECT BY level <= 100000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
2

GET_COMPRESSION_RATIO

In addition to tables and partitions, the procedure can now estimate the impact of different levels of compression on LOBs and indexes. It's also capable of estimating the impact of compression on the contents of the in-memory column store. The constants for the possible compression types, row sample sizes and object types are shown here . The following example shows the effect of advanced compression on a table, using all rows in the table as a sample size. The following example shows the effect of low-level advanced compression on an index partition, using the minimum sample size. It's also possible to test all indexes for a table in a single call. The procedure returns a collection of records holding the data for each index. The following example shows the effect of compression on a CLOB in a table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
SET SERVEROUTPUT ON
DECLARE
  l_blkcnt_cmp    PLS_INTEGER;
  l_blkcnt_uncmp  PLS_INTEGER;
  l_row_cmp       PLS_INTEGER;
  l_row_uncmp     PLS_INTEGER;
  l_cmp_ratio     NUMBER;
  l_comptype_str  VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'USERS',
    ownname         => 'TEST',
    objname         => 'TAB1',
    subobjname      => NULL,
    comptype        => DBMS_COMPRESSION.comp_advanced,
    blkcnt_cmp      => l_blkcnt_cmp,
    blkcnt_uncmp    => l_blkcnt_uncmp,
    row_cmp         => l_row_cmp,
    row_uncmp       => l_row_uncmp,
    cmp_ratio       => l_cmp_ratio,
    comptype_str    => l_comptype_str,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_allrows,
    objtype         => DBMS_COMPRESSION.objtype_table
  );

  DBMS_OUTPUT.put_line('Number of blocks used (compressed)       : ' ||  l_blkcnt_cmp);
  DBMS_OUTPUT.put_line('Number of blocks used (uncompressed)     : ' ||  l_blkcnt_uncmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (compressed)   : ' ||  l_row_cmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' ||  l_row_uncmp);
  DBMS_OUTPUT.put_line('Compression ratio                        : ' ||  l_cmp_ratio);
  DBMS_OUTPUT.put_line('Compression type                         : ' ||  l_comptype_str);
END;
/
Number of blocks used (compressed)       : 1325
Number of blocks used (uncompressed)     : 1753
Number of rows in a block (compressed)   : 74
Number of rows in a block (uncompressed) : 55
Compression ratio                        : 1.3
Compression type                         : "Compress Advanced"

PL/SQL procedure successfully completed.

SQL>

SET SERVEROUTPUT ON
DECLARE
  l_blkcnt_cmp    PLS_INTEGER;
  l_blkcnt_uncmp  PLS_INTEGER;
  l_row_cmp       PLS_INTEGER;
  l_row_uncmp     PLS_INTEGER;
  l_cmp_ratio     NUMBER;
  l_comptype_str  VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'USERS',
    ownname         => 'TEST',
    objname         => 'TAB1_CODE_IDX',
    subobjname      => 'TAB1_PART_2015',
    comptype        => DBMS_COMPRESSION.comp_index_advanced_low,
    blkcnt_cmp      => l_blkcnt_cmp,
    blkcnt_uncmp    => l_blkcnt_uncmp,
    row_cmp         => l_row_cmp,
    row_uncmp       => l_row_uncmp,
    cmp_ratio       => l_cmp_ratio,
    comptype_str    => l_comptype_str,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_minrows,
    objtype         => DBMS_COMPRESSION.objtype_index
  );

  DBMS_OUTPUT.put_line('Number of blocks used (compressed)       : ' ||  l_blkcnt_cmp);
  DBMS_OUTPUT.put_line('Number of blocks used (uncompressed)     : ' ||  l_blkcnt_uncmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (compressed)   : ' ||  l_row_cmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' ||  l_row_uncmp);
  DBMS_OUTPUT.put_line('Compression ratio                        : ' ||  l_cmp_ratio);
  DBMS_OUTPUT.put_line('Compression type                         : ' ||  l_comptype_str);
END;
/
Number of blocks used (compressed)       : 78
Number of blocks used (uncompressed)     : 120
Number of rows in a block (compressed)   : 641
Number of rows in a block (uncompressed) : 417
Compression ratio                        : 1.5
Compression type                         : "Compress Advanced Low"

PL/SQL procedure successfully completed.

SQL>

SET SERVEROUTPUT ON
DECLARE
  l_index_cr      DBMS_COMPRESSION.compreclist;
  l_comptype_str  VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'USERS',
    ownname         => 'TEST',
    tabname         => 'TAB1',
    comptype        => DBMS_COMPRESSION.comp_index_advanced_low,
    index_cr        => l_index_cr,
    comptype_str    => l_comptype_str,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_lob_maxrows
  );

  FOR i IN l_index_cr.FIRST .. l_index_cr.LAST LOOP
    DBMS_OUTPUT.put_line('----');
    DBMS_OUTPUT.put_line('ownname      : ' || l_index_cr(i).ownname);
    DBMS_OUTPUT.put_line('objname      : ' || l_index_cr(i).objname);
    DBMS_OUTPUT.put_line('blkcnt_cmp   : ' || l_index_cr(i).blkcnt_cmp);
    DBMS_OUTPUT.put_line('blkcnt_uncmp : ' || l_index_cr(i).blkcnt_uncmp);
    DBMS_OUTPUT.put_line('row_cmp      : ' || l_index_cr(i).row_cmp);
    DBMS_OUTPUT.put_line('row_uncmp    : ' || l_index_cr(i).row_uncmp);
    DBMS_OUTPUT.put_line('cmp_ratio    : ' || l_index_cr(i).cmp_ratio);
    DBMS_OUTPUT.put_line('objtype      : ' || l_index_cr(i).objtype);
  END LOOP;
END;
/
----
ownname      : TEST
objname      : TAB1_PK
blkcnt_cmp   : 223
blkcnt_uncmp : 223
row_cmp      : 448
row_uncmp    : 448
cmp_ratio    : 1
objtype      : 2
----
ownname      : TEST
objname      : TAB1_CODE_IDX
blkcnt_cmp   : 155
blkcnt_uncmp : 238
row_cmp      : 645
row_uncmp    : 420
cmp_ratio    : 1.5
objtype      : 2

PL/SQL procedure successfully completed.

SQL>

SET SERVEROUTPUT ON
DECLARE
  l_blkcnt_cmp    PLS_INTEGER;
  l_blkcnt_uncmp  PLS_INTEGER;
  l_lobcnt        PLS_INTEGER;
  l_cmp_ratio     NUMBER;
  l_comptype_str  VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'USERS',
    tabowner        => 'TEST',
    tabname         => 'TAB1',
    lobname         => 'CLOB_DESCRIPTION',
    partname        => NULL,
    comptype        => DBMS_COMPRESSION.comp_lob_high,
    blkcnt_cmp      => l_blkcnt_cmp,
    blkcnt_uncmp    => l_blkcnt_uncmp,
    lobcnt          => l_lobcnt,
    cmp_ratio       => l_cmp_ratio,
    comptype_str    => l_comptype_str,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_lob_maxrows
  );

  DBMS_OUTPUT.put_line('Number of blocks used (compressed)       : ' ||  l_blkcnt_cmp);
  DBMS_OUTPUT.put_line('Number of blocks used (uncompressed)     : ' ||  l_blkcnt_uncmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (compressed)   : ' ||  l_lobcnt);
  DBMS_OUTPUT.put_line('Number of lobs sampled                   : ' ||  l_cmp_ratio);
  DBMS_OUTPUT.put_line('Compression type                         : ' ||  l_comptype_str);
END;
/
Number of blocks used (compressed)       : 67
Number of blocks used (uncompressed)     : 61
Number of rows in a block (compressed)   : 4927
Number of lobs sampled                   : .9
Compression type                         : "Compress High"

PL/SQL procedure successfully completed.

SQL>
3

GET_COMPRESSION_TYPE

The function now has an extra parameter to optionally specify a partition name to limit the scope of the function. For more information see: - DBMS_COMPRESSION - DBMS_COMPRESSION : Estimate the Potential Benefits of Table Compression Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SELECT rowid,
       CASE DBMS_COMPRESSION.get_compression_type ('TEST', 'TAB1', rowid, 'TAB1_PART_2015')
         WHEN 1     THEN 'COMP_NOCOMPRESS'
         WHEN 2     THEN 'COMP_ADVANCED'
         WHEN 4     THEN 'COMP_QUERY_HIGH'
         WHEN 8     THEN 'COMP_QUERY_LOW'
         WHEN 16    THEN 'COMP_ARCHIVE_HIGH'
         WHEN 32    THEN 'COMP_ARCHIVE_LOW'
         WHEN 64    THEN 'COMP_BLOCK'
         WHEN 128   THEN 'COMP_LOB_HIGH'
         WHEN 256   THEN 'COMP_LOB_MEDIUM'
         WHEN 512   THEN 'COMP_LOB_LOW'
         WHEN 1024  THEN 'COMP_INDEX_ADVANCED_HIGH'
         WHEN 2048  THEN 'COMP_INDEX_ADVANCED_LOW'
         WHEN 1000  THEN 'COMP_RATIO_LOB_MINROWS'
         WHEN 4096  THEN 'COMP_BASIC'
         WHEN 5000  THEN 'COMP_RATIO_LOB_MAXROWS'
         WHEN 8192  THEN 'COMP_INMEMORY_NOCOMPRESS'
         WHEN 16384 THEN 'COMP_INMEMORY_DML'
         WHEN 32768 THEN 'COMP_INMEMORY_QUERY_LOW'
         WHEN 65536 THEN 'COMP_INMEMORY_QUERY_HIGH'
         WHEN 32768 THEN 'COMP_INMEMORY_CAPACITY_LOW'
         WHEN 65536 THEN 'COMP_INMEMORY_CAPACITY_HIGH'
       END AS compression_type
FROM   test.tab1 PARTITION (tab1_part_2015)
WHERE  rownum <= 5;

ROWID              COMPRESSION_TYPE
------------------ ---------------------
AAAX4aAAaAAABuSAAA COMP_NOCOMPRESS
AAAX4aAAaAAABuSAAB COMP_NOCOMPRESS
AAAX4aAAaAAABuSAAC COMP_NOCOMPRESS
AAAX4aAAaAAABuSAAD COMP_NOCOMPRESS
AAAX4aAAaAAABuSAAE COMP_NOCOMPRESS

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!