data segment compression

8/9/2019 Data Segment Compression

1/46

1 juliandyke.com 2005 Julian Dyke

Data SegmentCompression

Julian Dyke

Independent ConsultantWeb Version


2/46

juliandyke.com 2005 Julian Dyke2

Agenda1. Introduction

2. What is data compression?

3. Data segment compression

1. Functionality

2. Syntax3. Implementation

4. Performance

4. Conclusion


3/46


What is data compression? Data is

compressed when it is written to a block

decompressed when it is read from the block

Compression requires less data storage to hold the compressed data

more CPU to compress and decompress the data


4/46


Why use data compression? Data compression

Increases number of rows in each block

Reduces number of blocks required to store data

For a full table scan reduces number of logical(and probably physical) I/Os required

For a table access by ROWID increasesprobability that block is already in buffer cache

Improves buffer cache hit ratio

Potentially reduces backup and recovery times


5/46


When does Oracle use compression? Oracle compresses some data types including

VARCHAR2

NUMBER

RAW

Oracle does not compressDATE

CHAR

Compression is often achieved by using length byte(s)

trimming unused characters/bytes


6/46


When does Oracle use compression? Oracle also compresses

Length bytes in table blocks

Length bytes in index blocks

NULL values

NULL values at end of each row

Index branch blocks (suffix compressed) Index leaf blocks (optionally prefix compressed)

In addition some data structures implicitly

compress data IOTs

Index Clusters


7/46


Data Segment Compression Introduced in Oracle 9.2

Intended for

DSS environments

Read-only tables

Not intended forOLTP environments

Environments with any DML activity subsequentto data loading

Data is compressed at block level Direct path load must be used


8/46


Restrictions Data segment compression cannot be used

with

IOTs

IOT overflow segments

IOT mapping tables

Index clustered tables

Hash clustered tables

Hash partitions

Hash / list subpartitionsExternal Tables


9/46


Block Level Compression Compression is applied at block level

Blocks will only be compressed if data is sufficiently large to fill the block

rows have low enough cardinality

Columns will be reordered within each block toachieve optimal compression ratios

A segment may contain compressed and uncompressed blocks

blocks compressed on different columns


10/46


Direct Path Load Direct path load bypasses much on the work

done by conventional load

Direct path load

reserves extents from above HWM formats rows into blocks

inserts blocks back into table

adjusts HWM

No other transactions can be active on the tablewhilst load is in progress


11/46


Direct Path Load In Oracle 9.2 the following statements can use

direct path loads

CREATE TABLE AS SELECT

INSERT /* + APPEND */

ALTER TABLE MOVE

In addition the following features can use directpath loads

Materialized View RefreshSQL*Loader

Online reorganisation


12/46


Creating New Tables Tables are compressed using COMPRESS

clause

CREATE TABLE t1(

c01 NUMBER,c02 VARCHAR2(30)

)COMPRESS;

Default is for tables to be uncompressed This is equivalent to using the NOCOMPRESS

clause


13/46


Successful Compression If conditions are met then these statements

create compressed data blocks

CREATE TABLE t2 COMPRESS ASSELECT * FROM t1;

CREATE TABLE t2 COMPRESS ASSELECT * FROM t1 WHERE ROWNUM < 1;

INSERT /*+ APPEND */ INTO t2SELECT * FROM t1;


14/46


Unsuccessful Compression These statements will not create compressed

data blocks

CREATE TABLE t2 ASSELECT * FROM t1 WHERE ROWNUM < 1;

INSERT INTO t2 VALUES ('DBA_TABLES',46);

CREATE TABLE t2 COMPRESS ASSELECT * FROM t1 WHERE ROWNUM < 1;

INSERT INTO t2

SELECT * FROM t1;


15/46


Altering Existing Tables Compression can be specified for an existing

table

ALTER TABLE t1 COMPRESS;

Existing blocks are not compressed New blocks will be compressed if direct path

load is used

Similarly

ALTER TABLE t1 NOCOMPRESS;

disables compression for new data blocks, butdoes not change existing data blocks


16/46


Moving Existing Tables Tables can be moved using

ALTER TABLE t1 MOVE COMPRESS;

This command

Creates a new segmentUses direct load to copy and compress blocks

Drops old segment


17/46


Data Dictionary Views Not modified in Oracle 9.2

Data segment compression flag is missing fromALL_TABLES

DBA_TABLES

USER_TABLES

Data segment compression is recorded bysetting a bit in TAB$.SPARE1

Affects Hkan factor maximum number ofrows that can be held on block


18/46


Data Dictionary Views In Oracle 9.2.0.1 the following will list all

compressed tables in the database

SELECTu.name AS owner,o.name AS table_name

FROMsys.tab$ t,sys.obj$ o,sys.user$ u

WHERE BITAND (t.spare1, 131072) = 131072

AND o.obj# = t.obj#AND o.owner# = u.user#;


19/46


Tablespace Defaults Data segment compression can be specified at

tablespace level

CREATE TABLESPACE ts01 DEFAULT COMPRESS;

All new objects created will have compression

enabled Data segment compression can also be

specified for existing tablespaces

ALTER TABLESPACE ts01 DEFAULT COMPRESS;


20/46


Data Dictionary Views In Oracle 9.2.0.1 the DBA_TABLESPACES view

was not been updated to include data segmentcompression

Use the following to identify tablespaces withcompression enabled

SELECT nameFROM sys.ts$WHERE BITAND (flags, 64) = 64;


21/46


Partitioned Tables Data segment compression can also be used

with range or list partitioned tables

CREATE TABLE t1 (c01 NUMBER, c02 VARCHAR2(200))PARTITION BY RANGE (c01)(

PARTITION p1 VALUES LESS THAN (100),PARTITION p2 VALUES LESS THAN (200)

)COMPRESS;

Oracle 9.2 cannot compress hash or compositepartitioned tables


22/46


Partitioned Tables Can also create a table with some partitions

compressed and others uncompressed

CREATE TABLE t1 (c01 NUMBER, c02 VARCHAR2(200))PARTITION BY RANGE (c01)(

PARTITION p1 VALUES LESS THAN (100) COMPRESS,PARTITION p2 VALUES LESS THAN (200))COMPRESS;

ALTER TABLE t1ADD PARTITION p3VALUES LESS THAN (300) COMPRESS;

Compression can also be specified for new

partitions added to an existing table


23/46


Partitioned Tables Existing partitions can be specified as

compressed/uncompressed using

ALTER TABLE t1 MODIFY PARTITION p1 COMPRESS;ALTER TABLE t1 MODIFY PARTITION p1 NOCOMPRESS;

These do not affect existing rows

ALTER TABLE t1 MOVE PARTITION p1 COMPRESS;

This creates new segment,

copies and compresses all the rows

drops old segment

An existing uncompressed partition can becompressed using


24/46


Data Dictionary Views DBA_PART_TABLES.DEF_COMPRESSION

contains

NONE Compression never enabled

ENABLED Compression enabled at table level

DISABLED Compression has been enabled at tablelevel and subsequently disabled

N/A Partitioned IOT

DBA_TAB_PARTITIONS.COMPRESSION

contains

ENABLED Compressed enabled for partition

DISABLED Otherwise


25/46


Nested Tables Compression can be specified for storage table

of a nested table

CREATE TABLE t1 (c1 NUMBER, c2 TY2)NESTED TABLE c2 STORE AS t2 COMPRESS;

In Oracle 9.2. DBA_NESTED_TABLES has notbeen updated to indicate that the storage tablehas been compressed


26/46


Materialized Views Compression can be specified for materialized

views

CREATE MATERIALIZED VIEW mv1COMPRESSBUILD IMMEDIATEENABLE QUERY REWRITEAS

SELECT c1, c2, SUM (c3)FROM t1GROUP BY c1, c2;


27/46


Materialized Views Compression can be also specified for existing

materialized views

ALTER MATERIALIZED VIEW mv1 COMPRESS;

Data will be compressed the next time thematerialized view is refreshed

e.g

EXECUTE dbms_mview.refresh ('MV1');


28/46


SQL*Loader SQL*Loader can create data segment

compressed blocks using direct path loads

Specified using the parameter

DIRECT = TRUE

Conventional loads using SQLLDR do notgenerate compressed blocks


29/46


PCTFREE Default value ofPCTFREE for compressed

tables is 0

Can be overridden manually e.g.

CREATE TABLE t1 (c01 NUMBER)COMPRESS PCTFREE 10;

In general the default behaviour is preferable


30/46


Compression Ratios Compression ratios vary with

number of rows

number of columns

cardinality of rows

Compression ratios can be improved by sortingtable on low cardinality columns prior toloading

Can also be improved by using larger blocksizes


31/46


Compression Ratios For example loading SALES table from sales

history demo schema

$ORACLE_HOME/demo/schema/sales_history

BlockSize

UncompressedSize (Blocks)

CompressedSize (Blocks)

Ratio %

2048 18777 13433 71.5

4096 8983 6106 68.08192 4398 2850 64.7

16384 2179 1353 62.0

Table contains 1016271 rows


32/46


Implementation Each compressed block contains two tables

Symbol table contains one row for eachindividual column value or set of column values

Row table one row for each row in block

Each column in row table can be a reference to the symbol table if column is

compressed

column value if column is not compressed

Compression is performed at block-level only no inter-block references


33/46


Data Segment Compression

Block Common Header

Compressed Data Header

Row directory

Free Space

Symbol Table

Table directory

Data Header

Transaction Header

Row Data

Tail

20 bytes

24 bytes + 24 bytes per ITL entry

14 bytes

16 bytes (variable)

8 bytes

2 bytes per row

4 bytes


34/46


Compressed Block Header Compressed blocks include an extra header.

Header length is variable

Depends on

number of columns

order in which they are compressed Example of header from block dump

r0_9ir2=0x0

mec_kdbh9ir2=0x1r1_9ir2=0x0

76543210flag_9ir2=------OC

fcls_9ir2[5]={ 0 32768 32768 32768 32768 }perm_9ir2[5]={ 0 1 4 2 3 }


35/46


Length Bytes Column length bytes behave differently in

compressed blocks

200 AND < 250 length is value - 200250 (0xFA) length is contained in next two bytes

251 (0xFB) reference is contained in next two bytes


36/46


Length Bytes Examples

Byte(s) - Hex Bytes (s) Decimal Value

C9 201 1

CA 202 2

CB 203 3CC 204 4

F8 248 48

F9 249 49

FA 00 32 250 00 50 50FA 00 33 250 00 51 51

FA 0F 9F 250 15 159 3999

FA 0F A0 250 15 160 4000


37/46


Example Monaco Grand Prix Winners 1993-2002

Year Driver Team

1993 Ayrton Senna McLaren

1994 Michael Schumacher Benetton

1995 Michael Schumacher Benetton1996 Olivier Panis Ligier

1997 Michael Schumacher Ferrari

1998 Mika Hakkinen McLaren

1999 Michael Schumacher Ferrari

2000 David Coulthard McLaren2001 Michael Schumacher Ferrari

2002 David Coulthard McLaren


38/46


Example - Uncompressed Data Block

2001 FerrariMichael Schumacher




1998 Mika Hakkinen McLaren

1993 Ayrton Senna McLaren

1994 BenettonMichael Schumacher

1996 Olivier Panis Ligier1995 Michael Schumacher Benetton


RowData


39/46


Example - Compressed Data Block

RowData

SymbolTable

1993Ayrton Senna4

1996Olivier PanisLigier

19943 2

19953 2

19971 2

20024 020011 2

20004 0

19991 2

19984 Mika Hakkinen

SymbolTable

RowData

2 David Coulthard0

3 Ferrari1

5 Michael Schumacher2

Benetton3 2

4 4 McLaren


40/46


Performance Performance tests

Cost of inserting compressed rows

Cost of selecting compressed rows

Tested using

Oracle 9.2.0.1Sun Ultra Enterprise 450 4 CPUs

8192 byte block size

Test data adapted from sales history demo

$ORACLE_HOME/demo/schema/sales_history

SALES table contains 1016271 rows


41/46


Inserting Compressed Rows Loading the entire file into an empty table

Compressed Blocks Elapsed TIme(Secs)

CPU Time(Secs)

No 4398 31.77 4.13

Yes 2850 71.08 43.86

Compressed data is 35% smaller

Reduction in logical and physical I/O more than

offset by increase in CPU time to compressblocks

Statistics from V$SYSSTAT


42/46


Selecting Compressed Rows Selecting all rows from table

Compressed Blocks Elapsed TIme(Secs)

CPU Time(Secs)

No 4398 3.41 2.77

Yes 2850 3.78 3.53

Reduction in logical and physical I/O more than

offset by increase in CPU time to compressblocks

Statistics from trace file

SELECT SUM (quantity_sold) FROM sales;


43/46


Caveat Updating rows is VERY expensive

Rows must be decompressed before they canbe updated

In this example the ALL_OBJECTS viewcontained 7253 rows

CREATE TABLE t1 PCTFREE 0 ASSELECT owner, object_name,subobject_name, object_idFROM all_objects;

creates a 28 block table

The same statement with a COMPRESS clausecreates a 23 block table


44/46


Caveat The statement

UPDATE t2 SET owner = owner;

After the update statement is executed the table

contains 79 blocks

Once blocks are decompressed, rollback willnot recompress them

Use read-only tablespaces to preventinadvertent updates

decompresses all blocks

Deletes do not display this characteristic


45/46


Conclusions DSS / read only feature

Good compression ratios

Only works with direct path load

High CPU usage

High elapsed times Updates are disproportionately expensive

Documentation is weak

Data dictionary views need enhancing


46/46

juliandyke com 2005 Julian Dyke46

Thank you for your interest

For more information and to provide feedbackplease contact me

My e-mail address is:

[email protected]

My website address is:

www.juliandyke.com

data segment compression

Documents