Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Table compression

RE: Table compression

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Tue, 23 Mar 2004 08:55:31 -0500
Message-ID: <D91D9D5A73FC694BBC52F1EB26AD410F02780CBA@MSGBOSCLD2WIN.DMN1.FMR.COM>


Here is a test:
--

drop table test_delete_compress;
--

create table test_delete_compress noparallel compress as select * from some_table where rownum < 100001;
--

drop table test_delete_nocompress;
--

create table test_delete_nocompress noparallel nocompress as select * from test_delete_compress;
--

SQL> select segment_name, bytes
  2 from dba_segments
  3 where segment_name in ('TEST_DELETE_COMPRESS','TEST_DELETE_NOCOMPRESS');

SEGMENT_NAME                                  BYTES

---------------------------------------- ----------
TEST_DELETE_COMPRESS 7340032 TEST_DELETE_NOCOMPRESS 17825792

SQL> select count(*) from test_delete_compress;

  COUNT(*)


    100000

SQL> select count(*) from test_delete_nocompress;

  COUNT(*)


    100000

SQL> alter session set sql_trace = true;

Session altered.

SQL> delete from test_delete_compress;

100000 rows deleted.

SQL> delete from test_delete_nocompress;

100000 rows deleted.

Here is the trace file:


delete from test_delete_compress

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.01          0          1          0           0
Execute      1      3.92       4.03          0        403     102388      100000
Fetch        0      0.00       0.00          0          0          0           0

------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.92 4.05 0 404 102388 100000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26

Rows Row Source Operation
------- ---------------------------------------------------

      0 DELETE (cr=403 r=0 w=0 time=4037999 us)  100000 TABLE ACCESS FULL TEST_DELETE_COMPRESS (cr=403 r=0 w=0 time=154747 us)


delete from test_delete_nocompress

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          1          0           0
Execute      1      3.57       4.46          0       1090     105496      100000
Fetch        0      0.00       0.00          0          0          0           0

------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.57 4.47 0 1091 105496 100000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26

Rows Row Source Operation
------- ---------------------------------------------------

      0 DELETE (cr=1090 r=0 w=0 time=4468838 us)  100000 TABLE ACCESS FULL TEST_DELETE_NOCOMPRESS (cr=1090 r=0 w=0 time=154613 us)


Oracle 9.2.0.4 Solaris 8 Sun Fire 6800

Regards,

Waleed

-----Original Message-----

From: Tim Gorman [mailto:tim_at_sagelogix.com] Sent: Monday, March 22, 2004 11:34 PM
To: oracle-l_at_freelists.org
Subject: Re: Table compression

Waleed,

What I think generally has little to do with what I post, unless I specifically say as much. Excerpts from a test case...

SQL> delete from test_compress;

10000 rows deleted.

Elapsed: 00:00:12.20

Statistics


         16  recursive calls
      20699  db block gets
         96  consistent gets
          0  physical reads
    4921204  redo size
        788  bytes sent via SQL*Net to client
        798  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> delete from test_nocompress;

10000 rows deleted.

Elapsed: 00:00:01.55

Statistics


         16  recursive calls
      10367  db block gets
         45  consistent gets
          0  physical reads
    2539244  redo size
        791  bytes sent via SQL*Net to client
        800  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

This is an 8:1 ratio in timing, gathered on a Sun E450 running Solaris 8 over Sun JBOD disk. I get similar (8:1) timing results on my little ol' Mac OS X laptop too. I got 6:1 results on a Sun E15000 running Solaris 9 over EMC, but I don't have those test results handy, so I'll just say so for now... :-)

Hope this helps...

-Tim

on 3/22/04 8:47 AM, Khedr, Waleed at Waleed.Khedr_at_FMR.COM wrote:

> Don't know why you think deletion in compression tables will be that slow.
> 
> I expect deletion speed not be affected by the compression.
> Also conventional inserts should no be affected since it will not be
> compressed.
> 
> On the other hand I know that updates are really slow and usually the update
> ends up with tons of chained rows that make FTS a nightmare. When I need to
> update, I take these steps: uncompress, update, compress.
> 
> Regards,
> 
> Waleed
> -----Original Message-----
> From: Tim Gorman [mailto:tim_at_sagelogix.com]
> Sent: Monday, March 22, 2004 9:03 AM
> To: oracle-l_at_freelists.org
> Subject: Re: Table compression
> 
> 
> LeRoy,
> 
> Expect performance penalties of 2x on times for "bulk" inserts, 3x-4x
> penalty on "conventional" inserts and updates, and up to 6x-8x penalty on
> deletes.  This is an option that shares a lot of design characteristics with
> bitmap indexes, in that you love them for the queries but hate them during
> DML.
> 
> As with bitmap indices, the benefits of compression come out only during
> certain query operations.  Queries using full table scans are much, MUCH
> faster (due simply to fewer I/Os) and queries using indexed scans are a wash
> (no difference).
> 
> Excerpts from some test timings on a small subset of a fact table copied
> into (unindexed and nonparallel) compressed and non-compressed copies:
> 
> SQL> select  sum(bytes)/1048576 alloc_mb,
> 2          sum(blocks) alloc_blocks,
> 3          count(distinct partition_name) nbr_partitions,
> 4          count(*) nbr_exts
> 5  from    user_extents
> 6  where   segment_name = 'COMPRESS_TEST';
> 
> ALLOC_MB ALLOC_BLOCKS NBR_PARTITIONS   NBR_EXTS
> ---------- ------------ -------------- ----------
>  194.875        24944             51        538
> 
> SQL> select count(*) from compress_test;
> 
> COUNT(*)
> ----------
>  6443786
> 
> 1 row selected.
> 
> Elapsed: 00:00:07.40
> 
> SQL> select  sum(bytes)/1048576 alloc_mb,
> 2          sum(blocks) alloc_blocks,
> 3          count(distinct partition_name) nbr_partitions,
> 4          count(*) nbr_exts
> 5  from    user_extents
> 6  where   segment_name = 'NONCOMPRESS_TEST';
> 
> ALLOC_MB ALLOC_BLOCKS NBR_PARTITIONS   NBR_EXTS
> ---------- ------------ -------------- ----------
> 792.0625       101384             51       1110
> 
> SQL> select count(*) from noncompress_test;
> 
> COUNT(*)
> ----------
>  6443786
> 
> 1 row selected.
> 
> Elapsed: 00:00:56.48
> 
> In this case, a 4:1 compression ratio on space but a 7:1 compression ratio
> on FULL table scan performance.  A little less I/O goes a long way, and look
> ma! no caching!
> 
> The reason that there is little to no performance penalty on queries is
> because Oracle's method of compression is not similar to the algorithm's
> used by "zip" or "compress" or "zcat", etc.  Rather, Oracle adds a layer of
> indirection to a list of distinct values within the block, so that rows
> become a set of pointers to this list (called a "symbol table") within the
> block.  Little-to-no additional cycles to "uncompress".  This style of
> "compression" pays off when there is low cardinality and lots of repeated
> data values, such as the case with fact tables in a dimensional data model.
> 
> So this is an incredibly useful option for data warehouses but you have to
> use bulk loads (a.k.a. inserts via direct-path, append, etc) which, in most
> circumstances, means using partitioning and various forms of the EXCHANGE
> PARTITION load technique...
> 
> Hope this helps...
> 
> -Tim
> 
> 
> 
> 
> 
> on 3/19/04 1:58 PM, LeRoy Kemnitz at lkemnitz_at_uwsa.edu wrote:
> 
>> I read that the select runs just as fast if not faster when compared to
>> uncompressed tables.
>> 
>> 
>> 
>> Goulet, Dick wrote:
>> 
>>> Yeah, but what's the penalty during reads???
>>> 
>>> Dick Goulet
>>> Senior Oracle DBA
>>> Oracle Certified 8i DBA
>>> 
>>> -----Original Message-----
>>> From: Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM]
>>> Sent: Friday, March 19, 2004 3:48 PM
>>> To: 'oracle-l_at_freelists.org'
>>> Subject: RE: Table compression
>>> 
>>> 
>>> I use it, works great, 60% savings.
>>> 
>>> Once the table/partition gets flagged "compress", any direct load will be
>>> compressed.
>>> 
>>> You will get ora-600 if trying to do parallel direct load.
>>> 
>>> Also can't add a column to a compressed table.
>>> 
>>> Waleed
>>> 
>>> -----Original Message-----
>>> From: LeRoy Kemnitz [mailto:lkemnitz_at_uwsa.edu]
>>> Sent: Friday, March 19, 2004 3:41 PM
>>> To: Oracle List
>>> Subject: Table compression
>>> 
>>> 
>>> 
>>> I am looking into doing some table compression on my warehouse database
>>> to free up some space on the os.  I am running 9.2.0.4 on Unix 5.1.  The
>>> compression is about 2.5:1 on my tables.  The documentation says the
>>> bulk insert time will be doubled  but the single inserts, updates, and
>>> deletes are going to be a wash.  Does anyone use compression?  Are there
>>> any problems you notice in the use of it?     I have also read that the
>>> table will need to be re-compressed after the bulk inserts.  Any
>>> alternative ideas about getting this done?
>>> 
>>> Thanks in advance,
>>> 
>>> LeRoy
>>> 
>>> ----------------------------------------------------------------
>>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>> ----------------------------------------------------------------
>>> To unsubscribe send email to:  oracle-l-request_at_freelists.org
>>> put 'unsubscribe' in the subject line.
>>> --
>>> Archives are at http://www.freelists.org/archives/oracle-l/
>>> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>>> -----------------------------------------------------------------
>>> ----------------------------------------------------------------
>>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>> ----------------------------------------------------------------
>>> To unsubscribe send email to:  oracle-l-request_at_freelists.org
>>> put 'unsubscribe' in the subject line.
>>> --
>>> Archives are at http://www.freelists.org/archives/oracle-l/
>>> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>>> -----------------------------------------------------------------
>>> ----------------------------------------------------------------
>>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>> ----------------------------------------------------------------
>>> To unsubscribe send email to:  oracle-l-request_at_freelists.org
>>> put 'unsubscribe' in the subject line.
>>> --
>>> Archives are at http://www.freelists.org/archives/oracle-l/
>>> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>>> -----------------------------------------------------------------
>>> 
>>> 
>>>  
>>> 
> 
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Tue Mar 23 2004 - 07:56:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US