Licensed for Advanced Compression? Don’t forget the network

January 8, 2018January 22, 2018 Connor McDonald compression, dba, performance, sqlnet

We often think of Advanced Compression being exclusively about compressing data “at rest”, ie, on some sort of storage device. And don’t get me wrong, if we consider just that part of Advanced Compression, that still covers a myriad of opportunities that could yield benefits for your databases and database applications:

Heat maps
Automatic Data Optimization
XML, JSON and LOB compression (including de-duplication)
Compression on backups
Compression on Data Pump files
Additional compression options on indexes and tables
Compressed Flashback Data Archive storage
Storage snapshot compression

However, if you are licensed for the option, there are other things that you can also take advantage of when it comes to compression of data on the network.

In 12c, Advanced Network Compression is part of the Advanced Compression option. Obviously, the modern data centre typically co-locates application servers and database servers within a high speed high bandwidth network, but once data needs to be accessed over less optimal networks, for example, users running ad-hoc queries to their desktops, then compressing data across the wire can yield benefits.

Trust me, when you live in Perth, Western Australia, any kind of improvement to access data across the network is a good thing! Smile

Here’s an example of the option in action. Here’s my source table – which I’ve deliberately created with lots of potential for compression:


SQL create table comp_Test
  2  as select rownum x, rpad('x',1000,'x') y from dual
  3  connect by level <= 100000;

Table created.

Every row is almost a replica of the others, and column Y is 1000 repeated characters. So it is an artificial but perfect candidate to see the compression in action. To ensure that the network transmission of the data is a impactful factor here, I’ve located this table in a “database server” (my spare laptop) accessed via a slow wireless network from my other machine. We can see the impact of the network by starting with the default settings

Default settings


C:\temp>sqlplus scott/tiger@pdb122

SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 27 13:48:05 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Wed Dec 27 2017 13:48:26 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> set timing on
SQL> set arraysize 1000
SQL> set feedback only
SQL> select * from scott.comp_test;

100000 rows selected.

Elapsed: 00:00:24.68

So a touch more than 1000 bytes * 100000 rows yields the following session level statistics


SQL> @netstat

NAME                                                              VALUE
------------------------------------------------------------ ----------
bytes sent via SQL*Net to client                              102138497

Now I’ll activate compression by adding the following parameters to sqlnet.ora on client and server.


SQLNET.COMPRESSION=on
SQLNET.COMPRESSION_LEVELS=(low,high)
SQLNET.COMPRESSION_THRESHOLD=1024

and repeat the test in the a fresh connection. To see if we can potentially use compression, we can check V$SESSION_CONNECT_INFO


SQL> select sid, network_service_banner from V$SESSION_CONNECT_INFO 
  2  where sid = sys_context('USERENV','SID')
  3  @pr
==============================
SID                           : 324
NETWORK_SERVICE_BANNER        : Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.2.0.1.0 - Production
==============================
SID                           : 324
NETWORK_SERVICE_BANNER        : Oracle Advanced Network Compression Service for 64-bit Windows: Version 12.2.0.1.0 - Production
==============================
SID                           : 324
NETWORK_SERVICE_BANNER        : NTS Authentication service adapter for 64-bit Windows: Version 2.0.0.0.0 - Production
==============================
SID                           : 324
NETWORK_SERVICE_BANNER        : Encryption service for 64-bit Windows: Version 12.2.0.1.0 - Production
==============================
SID                           : 324
NETWORK_SERVICE_BANNER        : Crypto-checksumming service for 64-bit Windows: Version 12.2.0.1.0 - Production

or if you enable a SQL*Net trace, you’ll entries like this in the log files


nlstddp_dump_ptable:   SQLNET.COMPRESSION_LEVELS = HIGH
nlstddp_dump_ptable:   SQLNET.COMPRESSION = ON

Compression enabled


SQL> set timing on
SQL> set arraysize 1000
SQL> set feedback only
SQL> select * from scott.comp_test;

100000 rows selected.

Elapsed: 00:00:01.93

You can see the huge difference in elapsed time. Unfortunately, as far I can ascertain, the session network statistics are derived after decompression, because the bytes transferred still reflect the original sizes


SQL> @netstat

NAME                                                              VALUE
------------------------------------------------------------ ----------
bytes sent via SQL*Net to client                              102114310

We can also take advantage of the larger SDU sizes available in 12c. Now my sqlnet.ora looks like this:


DEFAULT_SDU_SIZE=1048576
SQLNET.COMPRESSION=on
SQLNET.COMPRESSION_LEVELS=(low,high)
SQLNET.COMPRESSION_THRESHOLD=1024

and then the test can be repeated.

Compression on, larger SDU


SQL> set timing on
SQL> set arraysize 1000
SQL> set feedback only
SQL> select * from scott.comp_test;

100000 rows selected.

Elapsed: 00:00:00.98

(* – elapsed times are averaged across a number of executions)

There is a whitepaper on Advanced Compression with more details here, so if you have the license, make sure you take a look to see if there are some benefits for you to maximize the return on your investment.

Index compression–quick tip

June 2, 2017July 24, 2017 Connor McDonald compression, indexes, tablespaces

If you’re appropriately licensed and want to use advanced index compression, you can take advantage of the setting a tablespace to automatically add compression as a default at a nominated level in the database. From the docs:

Here is an example of that in action. We’ll set our tablespace default accordingly


SQL> create tablespace demo
  2  datafile 'C:\ORACLE\ORADATA\DB122\DEMO.DBF'
  3  size 100M
  4  default index compress advanced high;

Tablespace created.

Now we’ll create a table and an index on it in that tablespace


SQL> column index_name format a10
SQL> column tablespace_name format a15
SQL> create table t tablespace demo as
  2  select *
  3  from dba_objects;

Table created.

SQL> create index t_idx on t(owner) tablespace demo;

Index created.

SQL> select index_name,compression
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION  
---------- ------------- 
T_IDX      ADVANCED HIGH   

1 row selected.

You can see that even though we did not specify any kind of compression for the index, it picked up the setting from the tablespace. Thus existing DDL scripts you have for indexes et al will not need to be changed.

Similarly, rebuilding the index does not lose the setting


SQL> alter index t_idx rebuild tablespace demo;

Index altered.

SQL> select index_name,compression
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION   
---------- ------------- 
T_IDX      ADVANCED HIGH 

1 row selected.

You can see what the defaults are for a tablespace by querying DBA_TABLESPACES as per normal


SQL> select tablespace_name,compress_for,index_compress_for
  2  from user_tablespaces where tablespace_name = 'DEMO';

TABLESPACE_NAME COMPRESS_FOR                   INDEX_COMPRES
--------------- ------------------------------ -------------
DEMO                                           ADVANCED HIGH

Index compression–working out the compression number

August 5, 2015March 29, 2017 Connor McDonald compression, dba, indexes

Richard Foote did a series of informative posts on Index Compression which concludes that there is whole lot of positives about index compression, and very little negatives. But obviously one critical thing is choosing the right number of leading columns to compress. Is it just “take a guess?” . Luckily, Oracle has taken the guesswork out of it.

The ANALYZE command on an index can let you find the optimial compression count. When you do an ANALYZE INDEX command, two critical columns are populated:

SQL> desc index_stats
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 HEIGHT                                 NUMBER
 BLOCKS                                 NUMBER
 NAME                                   VARCHAR2(128)
 PARTITION_NAME                         VARCHAR2(128)
 LF_ROWS                                NUMBER
 LF_BLKS                                NUMBER
 LF_ROWS_LEN                            NUMBER
 LF_BLK_LEN                             NUMBER
 BR_ROWS                                NUMBER
 BR_BLKS                                NUMBER
 BR_ROWS_LEN                            NUMBER
 BR_BLK_LEN                             NUMBER
 DEL_LF_ROWS                            NUMBER
 DEL_LF_ROWS_LEN                        NUMBER
 DISTINCT_KEYS                          NUMBER
 MOST_REPEATED_KEY                      NUMBER
 BTREE_SPACE                            NUMBER
 USED_SPACE                             NUMBER
 PCT_USED                               NUMBER
 ROWS_PER_KEY                           NUMBER
 BLKS_GETS_PER_ACCESS                   NUMBER
 PRE_ROWS                               NUMBER
 PRE_ROWS_LEN                           NUMBER
 OPT_CMPR_COUNT                         NUMBER
 OPT_CMPR_PCTSAVE                       NUMBER

Lets look at an example – we’ll create a table with 10 columns with all sorts of interesting distributions

SQL> drop table T purge;

Table dropped.

SQL> create table T as
 2   select
 3     rownum r01,
 4     mod(rownum,10) r02,
 5     mod(rownum,100) r03,
 6     mod(rownum,1000) r04,
 7     trunc(rownum/10) r05,
 8     trunc(rownum/100) r06,
 9     trunc(dbms_random.value(1,100)) r07,
10     trunc(dbms_random.value(1,1000)) r08  ,
11     trunc(mod(rownum,100)/10) r09,
12     mod(trunc(rownum/10),100) r10
13  from
14    ( select 1 from dual connect by level <= 1000 ),
15    ( select 1 from dual connect by level <= 1000 )
16  /

Table created.

We’ve used a technique described here https://www.youtube.com/watch?v=UonikfFgEyM to generate 1,000,000 rows.

Now with some SQL trickery, we can generate every possible combination of 3 column indexes for this table.

SQL> with inds as ( select rownum r from dual connect by level <= 10 )
  2  select  '(r'||i1.r||',r'||i2.r||',r'||i3.r||')' ix_cols
  3  from inds i1, inds i2, inds i3
  4  where i1.r != i2.r
  5  and   i2.r != i3.r
  6  and   i1.r != i3.r
  7  /

IX_COLS
---------------------------------------------------------------
(r1,r2,r3)
(r1,r2,r4)
(r1,r2,r5)
(r1,r2,r6)
(r1,r2,r7)
(r10,r9,r4)
...
...
(r10,r9,r5)
(r10,r9,r6)
(r10,r9,r7)
(r10,r9,r8)

So, now we’ll create a table called IND_STATS to hold the results of our ANALYZE command, because every time you issue ANALYZE INDEX the INDEX_STATS view is cleared.

SQL> create table IND_STATS as select name, OPT_CMPR_COUNT,  OPT_CMPR_PCTSAVE from index_stats where 1=0;

Table created.

And now we’ll just loop around all 720 potential indexes and work out the optimal compression.

SQL> begin
  2  for i in (
  3    with inds as ( select rownum r from dual connect by level <= 10 )
  4    select 'IX_'||i1.r||'_'||i2.r||'_'||i3.r ix_name,
  5           '(r'||to_char(i1.r,'fm00')||',r'||to_char(i2.r,'fm00')||',r'||to_char(i3.r,'fm00')||')' ix_cols
  6    from inds i1, inds i2, inds i3
  7    where i1.r != i2.r
  8    and   i2.r != i3.r
  9    and   i1.r != i3.r
 10  )
 11  loop
 12    dbms_application_info.set_client_info(i.ix_name);
 13    begin
 14      execute immediate 'drop index IX';
 15    exception
 16      when others then null;
 17    end;
 18    execute immediate 'create index IX on t '||i.ix_cols;
 19    execute immediate 'analyze index IX validate structure';
 20    insert into IND_STATS select i.ix_cols, OPT_CMPR_COUNT,  OPT_CMPR_PCTSAVE from index_stats;
 21    commit;
 22  end loop;
 23  end;
 24  /

PL/SQL procedure successfully completed.

So now we can take a look at the compression recommendations for all of our potential 3 column indexes. (Note: I’m not suggesting you would actually index all 720 combinations ! Smile )

SQL> select * from ind_stats;

NAME                                     OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------------------------------------- -------------- ----------------
(r01,r02,r03)                                         0                0
(r01,r02,r04)                                         0                0
(r01,r02,r05)                                         0                0
(r01,r02,r06)                                         0                0
(r01,r02,r07)                                         0                0
(r01,r02,r08)                                         0                0
(r01,r02,r09)                                         0                0

...
...


(r02,r06,r05)                                         2               24
(r02,r06,r07)                                         2               26
(r02,r06,r08)                                         2               25
(r02,r06,r09)                                         2               26
(r02,r06,r10)                                         2               26
(r02,r07,r01)                                         2               26
(r02,r07,r03)                                         3               43
(r02,r07,r04)                                         3               39
(r02,r07,r05)                                         2               26
(r02,r07,r06)                                         2               28
(r02,r07,r08)                                         2               28
(r02,r07,r09)                                         3               43
(r02,r07,r10)                                         3               37
(r02,r08,r01)                                         2               29
(r02,r08,r03)                                         3               39

You can see that some indexes probably wont benefit at all from compression, whereas for others, ALL columns become candidates for compression and might save nearly half the size of index.

So you dont need to guess at the compression for your indexes – you can work it out using ANALYZE.

Compressed partitions are not compressed tables

August 26, 2013December 20, 2017 Connor McDonald compression, dba, partitioning

So…you have got a big table and you’re lucky enough to have a partitioning license. So far, so good.

Then you read about using compression to make your older, static data faster and smaller and you thought … “Cool!”

Let’s see that in action – first here is my uncompressed partition table

SQL> create table T ( x date, y int, z varchar2(50) )
  2  PARTITION BY RANGE (x) INTERVAL (INTERVAL '60' DAY)
  3    (PARTITION p1 VALUES LESS THAN (TO_DATE('01/01/2012','dd/mm/yyyy')  )
  4   )
  5  /

Table created.

SQL> insert /*+ APPEND */ into T
  2  select sysdate - 1000+rownum/100, trunc(rownum/1000), rpad('x',50)
  3  from dual
  4  connect by level  commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T'
  4  order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             DISABLED   101.838046
SYS_P241                       DISABLED           80
SYS_P242                       DISABLED           80
SYS_P243                       DISABLED           80
SYS_P244                       DISABLED           80
SYS_P245                       DISABLED           80
SYS_P246                       DISABLED           80
SYS_P247                       DISABLED           80
SYS_P248                       DISABLED           80
SYS_P249                       DISABLED           80
SYS_P250                       DISABLED           80
SYS_P251                       DISABLED         17.5

and now I’ll compress all of the partitions

SQL> begin
  2  for i in (
  3  select partition_name,  compression
  4  from dba_tab_partitions
  5  where table_name = 'T' )
  6  loop
  7    execute immediate 'alter table t modify partition '||i.partition_name||' compress';
  8    execute immediate 'alter table t move partition '||i.partition_name;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T'
  4  order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             ENABLED    450.170455
SYS_P241                       ENABLED    206.896552
SYS_P242                       ENABLED    206.896552
SYS_P243                       ENABLED    206.896552
SYS_P244                       ENABLED    206.896552
SYS_P245                       ENABLED    206.896552
SYS_P246                       ENABLED    206.896552
SYS_P247                       ENABLED    206.896552
SYS_P248                       ENABLED    206.896552
SYS_P249                       ENABLED    206.896552
SYS_P250                       ENABLED    206.896552
SYS_P251                       ENABLED    20.2631579

12 rows selected.

so I give myself a nice big pat on the back and sit and bask in the glow of a job well done 🙂

But wait….I then want to merge a couple of those partitions…Let’s see what happens

SQL> col x new_value p1
SQL> select partition_name from user_tab_partitions
  2  where partition_position = 4;

PARTITION_NAME
------------------------------
SYS_P243

SQL> col x new_value p2
SQL> select partition_name from user_tab_partitions
  2  where partition_position = 5;

PARTITION_NAME
------------------------------
SYS_P244

SQL> alter table T merge partitions &&p1, &&p2 into partition NEW_PAR;

Table altered.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T';

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
SYS_P251                       ENABLED    20.2631579
SYS_P248                       ENABLED    206.896552
SYS_P245                       ENABLED    206.896552
NEW_PAR                        DISABLED   91.6030534
P1                             ENABLED    450.170455
SYS_P242                       ENABLED    206.896552
SYS_P246                       ENABLED    206.896552
SYS_P241                       ENABLED    206.896552
SYS_P247                       ENABLED    206.896552
SYS_P250                       ENABLED    206.896552
SYS_P249                       ENABLED    206.896552

11 rows selected.

Uh oh….the act of merging the two compressed partitions has resulted in an uncompressed partition. Now I would be forced to do an “alter table move partition” if I wanted to get those compression benefits back. That’s not fun 😦

Now let’s repeat the exercise, but this time…I knew in advance that I might want to compress that table sometime in the future

SQL> drop table T purge;

Table dropped.

SQL> create table T ( x date, y int, z varchar2(50) )
  2  PARTITION BY RANGE (x) INTERVAL (INTERVAL '60' DAY)
  3    (PARTITION p1 VALUES LESS THAN (TO_DATE('01/01/2012','dd/mm/yyyy')  )
  4   )
  5   compress
  6  /

Table created.

SQL> insert /*+ APPEND */ into T
  2  select sysdate - 1000+rownum/100, trunc(rownum/1000), rpad('x',50)
  3  from dual
  4  connect by level  commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T'
  4  order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             ENABLED    450.170455
SYS_P261                       ENABLED    206.896552
SYS_P262                       ENABLED    206.896552
SYS_P263                       ENABLED    206.896552
SYS_P264                       ENABLED    206.896552
SYS_P265                       ENABLED    206.896552
SYS_P266                       ENABLED    206.896552
SYS_P267                       ENABLED    206.896552
SYS_P268                       ENABLED    206.896552
SYS_P269                       ENABLED    206.896552
SYS_P270                       ENABLED    206.896552
SYS_P271                       ENABLED    20.2631579

12 rows selected.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> col x new_value p1
SQL> select partition_name x from user_tab_partitions
  2  where partition_position = 4;

X
--------------------------------------------------------------------------------------------------------------------------------
SYS_P263

SQL> col x new_value p2
SQL> select partition_name x from user_tab_partitions
  2  where partition_position = 5;

X
--------------------------------------------------------------------------------------------------------------------------------
SYS_P264

SQL> alter table T merge partitions &&p1, &&p2 into partition NEW_PAR;

Table altered.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T';

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             ENABLED    450.170455
SYS_P271                       ENABLED    20.2631579
SYS_P265                       ENABLED    206.896552
SYS_P266                       ENABLED    206.896552
SYS_P270                       ENABLED    206.896552
SYS_P267                       ENABLED    206.896552
SYS_P269                       ENABLED    206.896552
SYS_P262                       ENABLED    206.896552
SYS_P268                       ENABLED    206.896552
NEW_PAR                        ENABLED           300
SYS_P261                       ENABLED    206.896552

11 rows selected.

Well look at that !

It’s at this point, you’re probably thinking that this is a bug, or that you get “one shot” at this otherwise you’re doomed to not get compression.

But that’s not the case – it’s just a nuance of the way Oracle is interpreting your requests. The MERGE PARTITION command is in effect creating a NEW partition, hence the new partition takes on the default compression clause at table level. So, in the uncompressed example above, if I simply did

SQL> alter table T compress;

Table altered.

before I merge the partitions, then the resulting partition is indeed compressed.

Alternatively, although I can’t find an obvious reference in the documentation that this is permitted, the following also works:

SQL> alter table T merge partitions &&p1, &&p2 into partition NEW_PAR compress;

Table altered.

So to wrap up – be careful when manipulating partitions when dealing with compression – you might just end up with some uncompressed ones.

Learning is not a spectator sport

Connor McDonald's Oracle blog

compression

Licensed for Advanced Compression? Don’t forget the network

Index compression–quick tip

Index compression–working out the compression number

Compressed partitions are not compressed tables