this note, the idea is to show some of the first tests I made on table compression. One of the features most strongly presented by Oracle in its latest version (11g) is just the advanced compression. From 9i tables can be compressed but with certain restrictions. In 9i and 10g compression might call the basic tables as it has several limitations and compression only applies to a restricted set of operations (eg for direct insert) which makes it quite useful for DW systems but not for OLTP databases. Oracle 11g introduced
advanced compression to reduce resource use and handling of large volumes of data. Allows a significant reduction in required storage for relational or structured data (tables), unstructured data (files) or data backup (backups).
The new OLTP Table Compression feature uses an algorithm designed to work with OLTP applications. This algorithm works by eliminating duplicate values \u200b\u200bat the block level. The expected compression ratio is 2 to 3 using OLTP Compression. The most novel feature is that this information can be read without having to decompress compressed so there is no performance degradation and may even improve the performance due to a reduction of I / O as they need to access fewer blocks to obtain they joined ranks with the consequent reduction in required buffer cache. Anyway I like to see to believe and therefore I will show my evidence to you guys are to draw their own conclusions, as well as encourage them to take practice test ever before deploying.
I have no hand in this moment the 11g R2 database so my tests will be based on R1. R2 change in the syntax but the semantics of operations is the same as in R1. In case anyone wanted to try my test in R2, and is too lazy to consult the SQL Reference manual, I pass the differences:
COMPRESS FOR ALL OPERATIONS (11gR1) = COMPRESS FOR OLTP (11gR2) DIRECT_LOAD COMPRESS FOR OPERATIONS (11gR1) COMPRESS = BASIC (11gR2) (*) default
For the test I will create 3 tables:
T: No Compression
T_C_DSS: basic compression or compression DSS (the same type of compression
earlier)
T_C_OLTP: Advanced Compression (11g +)
Connected to: Oracle Database 11g
Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
rop @ ROP11G> create table t (x int, char (30), z date);
Table created.
rop @ ROP11G> create table t_c_dss (x int, char (30), z date) compress;
Table created.
rop @ ROP11G> create table t_c_oltp (x int, char (30), z date) compress for all Operations;
Table created.
rop @ ROP11G> select table_name, pct_free, compression, compress_for from user_t
2 WHERE table_name in ('T', 'T_C_DSS', 'T_C_OLTP');
PCT_FREE5000000 filas creadas.
TABLE_NAME COMPRESS COMPRESS_FOR
-------- ---------------------- ---------- -------- ----------
-------- T 10
T_C_DSS 0 ENABLED DISABLED DIRECT LOAD ONLY
T_C_OLTP 10 ENABLED FOR ALL OPERATIONS
Consulting in the catalog you can see the partition type. Note that the PCTFREE in dss is 0 since no changes are expected.
Now I will insert 5M rows at random but looking for a way to be repeated many times in the columns values \u200b\u200bto take advantage
compression column x inserted unique values, the column and insert letters A and B and the column z dates inserted up to 10 days after the test.
rop @ ROP11G> set timing on
rop @ ROP11G> set @ Car train on
rop ROP11G> insert into t select rownum
2, 3
chr (64 + trunc (dbms_random.value (1.3)))
4 trunc (sysdate) + trunc (dbms_random.value (1.10))
5 from dual 6 connect by rownum
5000000 rows created. After
: 00:02:57.65
Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1350848739
-------------------------------------------------------------------------------
BY WITHOUT FILTERING 690 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
5000000 rows processed rop@ROP11G> commit;
Confirmación terminada.
Transcurrido: 00:00:00.06
rop@ROP11G> insert into t_c_dss
2 select rownum ,
3 chr(64+trunc(dbms_random.value(1,3))),
4 trunc(sysdate)+trunc(dbms_random.value(1,10))
5 from dual
6 connect by rownum
Transcurrido: 00:02:48.43
Plan de Ejecución
----------------------------------------------------------rop @ ROP11G> commit;
Plan hash value: 1350848739
----------------------------------------------------------------------------------
client 3 sorts (memory)
0 sorts (disk) 5000000 rows processed
Confirmation
completed.
Elapsed: 00:00:00.04I searched metalink and the note "COMPRESS FOR ALL OPERATIONS
rop @ ROP11G> insert into t_c_oltp
2 select rownum,
3 chr (64 + trunc (dbms_random.value (1.3))),
4 trunc (sysdate) + trunc (dbms_random.value (1.10))
5 from dual 6 connect by rownum
5000000 rows created.
Elapsed: 00:08:58.00 Execution Plan
-----------------------------------
----------------------- Plan hash value: 1350848739
-----------------------------------------------------------------------------------
3 sorts (memory)
0 sorts (disk) 5000000 rows processed
conventional insertion times for each table were:
T = 2m 57s 2m 48s
T_C_DSS
T_C_OLTP = 8m 54s
The insertion time of the OLTP table was 3 times greater than the DSS table and the common table, but more striking is the amount of redo used:
Redo Insert T = 292595868 ~ 279Mb
= Redo Insert in T_C_DSS = 290874948 ~ 277Mb
= Redo Insert in T_C_OLTP = 945961224 ~ = 902Mb
is also noted that the amount of redo is more than 3 times.
Generates lot of redo [ID 829068.1]
states that consumption is expected rather more redo dml operations on a table with advanced compression (CA).
T_C_OLTP
Anyway, it takes more but lets see if and how much compressed:
rop @ ROP11G> select segment_name, blocks, round (bytes/1024/1024, 2) 2 Mb from user_segments
WHERE segment_name in ('T' 'T_C_DSS', 'T_C_OLTP');
segment_name
MB
BLOCKS ------------------------------ ----- ----- ---------- T 35456 277
T_C_DSS 31744 248 11 264 88
The compression on the table T_C_OLTP was significant compared with the other two tables. Also note that the ratio of 3 is still maintained as the OLTP table allocates 3 times less space than tables compressed and uncompressed dss similar space that went wild. It seems that sacrificing more consumption and more time redo the insert was processing the results, no?.1 insert / * + APPEND * / into t_c_oltp
The next test will analyze the direct inserts. Remember that this type of insert is used to bulk load, especially in systems Datawarehouse (ETL). I will test
inserting 1M rows:
rop @ ROP11G> insert / * + APPEND * / into t select rownum
2, 3
chr (64 + trunc (dbms_random.value (1.3))), trunc
4 (sysdate) + trunc (dbms_random.value (1.10))
5 from dual 6 connect by rownum
1000000 rows created.
Elapsed: 00:00:29.37
1 insert / * + APPEND * / into t_c_dss
2 select rownum,
3 chr (64 + trunc (dbms_random.value (1.3))),
4 trunc (sysdate ) + trunc (dbms_random.value (1.10))
5 from dual 6 connect by rownum
* rop @ ROP11G> /
1000000 rows created.
Elapsed: 00:00:32.01
rop @ ROP11G> commit;
Confirmation
completed.
Elapsed: 00:00:00.07
rop @ ROP11G> ed
Written afiedt.buf file
2 select rownum,
3 chr (64 + trunc (dbms_random.value ( 1.3))),
4 trunc (sysdate) + trunc (dbms_random.value (1.10)) from dual
5 6 * connect by rownum
rop @ ROP11G> /
1000000 rows created.
Elapsed: 00:00:30.01
rop @ ROP11G> commit;
Confirmation
completed.
Elapsed: 00:00:00.01
With direct insert times were similar:
T_C_DSS T = 29s = 32s = 30s
T_C_OLTP
and space crazy:
rop @ ROP11G> Written
ed afiedt.buf
1 file select segment_name, blocks, round (bytes/1024/1024, 2) Mb from user_segments
2 * WHERE segment_name in ('T', 'T_C_DSS', 'T_C_OLTP') @ rop
ROP11G> /
segment_name MB
BLOCKS ------------------------------ ---------- -
--------- T 48768 381
T_C_DSS 33792 264 13312 104
T_C_OLTP
From the above results we see that the increased direct insert crazy space of each segment as follows:
1436 consistent gets
segment_name ---------- Deltha in
Mb
-------------------- ------- T 104 (381-277)
T_C_DSS 16 (264-248)
T_C_OLTP 16 (104 - 88)
From these results it follows that the basic and advanced compression worked well for direct insert, both only had to allocate an additional 16MB to accommodate new rows 1M. However the table if compression had to allocate an extra 25%.
Another point that interests me is the update's test and see how the behavior of this type of operation in each case. To test this I will change the column at 50000 rows.
rop @ DESA11G> update t set and = 'C' where y = 'B' and rownum
50000 rows updated.
Elapsed: 00:00:01.96 Execution Plan
-----------------------------------
----------------------- Plan hash value: 3603919313
------------------- --------------------------------------------------
-------
Note
-----
- dynamic sampling used for this statement
Estadísticas
----------------------------------------------------------
258 recursive calls
1667 db block gets
1028 physical reads
7656052 redo size 
847 bytes sent via SQL*Net to client
574 bytes received via SQL*Net from client 847 bytes sent via SQL*Net to client 6 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
50000 rows processed
rop@DESA11G> commit;
Confirmación terminada.
rop@DESA11G> ed
Written fillet afiedt.buf
t_c_dss September 1 * update y = 'C' where y = 'B' and rownum
Rop DESA11G @> /
----------------------------------- -----------------------
50000 rows updated.
Elapsed: 00:00:01.84 Execution Plan
Plan hash value: 2179303604
------------------- -------------------------------------------------- ----------
257 recursive calls
51518 db block gets
827 consistent gets
890 physical reads
15677976 redo size
867 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
50000 rows processed
rop@DESA11G> commit;
Confirmación terminada.
Transcurrido: 00:00:00.01
rop@DESA11G> ed
Escrito file afiedt.buf
1* update t_c_oltp set y='C' where y = 'B' and rownum
rop@DESA11G> /
50000 rows updated.
Elapsed: 00:01:09.89 Execution Plan
Misses in library cache during parse: 1
----------------------------------- -----------------------
Plan hash value: 2618608701
------------------- -------------------------------------------------- -----------
1567 physical reads 40901448 redo size 868 bytes
Sent via SQL * Net to client 581
Bytes Received via SQL * Net from client 6 SQL * Net <= 5000000;
roundtrips to / from client
7 sorts (memory)
0 sorts (disk)
50000 rows processed
rop @ DESA11G> commit;
Confirmation
completed.
Elapsed: 00:00:00.01
update times of the column and in each case were:
Db block gets Redo Time ------ ---- ------
-------
T 1s 1s T_C_DSS 7Mb
1667 51518
15Mb T_C_OLTP 1m 9s 39Mb
<=5000000)
6153098 times the OLTP table for mass update are much higher compared to the times of the same operation on other tables. I tried to try to make the same update but with 1M rows and tables, but ty for dss times were less than 2 minutes to the case of OLTP table and did not end after 2 hours I had to kill the session. I tried 3 times on different days and in the three cases I had to stay the execution.
In the last test I'll see how they behave the selectmen's. For details go to activate the 10046 trace. The sentence I built runs along the table:
rop @ DESA11G> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8 ';
Session altered.
Elapsed: 00:00:00.07
rop @ DESA11G> ed
Written afiedt.buf
1 file select z, count (*), max (z) t
2 from 3 * group by z @ DESA11G
rop > / Z
COUNT (*) MAX (Z)
--------- ---------- --------- 18-FEB-10 666 293 18
-FEB-10 17-FEB-10 665 982 17-FEB-10
20-FEB-10 667 869 20-FEB-10
16-FEB-10 666 656 16-FEB-10
23-FEB-10 665 526 23-FEB -10
19-FEB-10 666 827 19-FEB-10 <= 5000000;
22-FEB-10 665 960 22-FEB-10
21-FEB-10 666 494 21-FEB-10
24-FEB-10 668 393 24-FEB-10
9 rows selected.
Elapsed: 00:00:09.89
Rop DESA11G @> ed
Written fillet afiedt.buf
*************************** ************************************************** ***
select z, count (*), Max (z) t
the group by z
call count cpu elapsed
disk query current rows ------- ------
- ------ ---------- ---------- ---------- ---------- ---- ------
Parser 1 0.02 0.01 2 2 0 0 1 0.00
Run 0.00 0 0 0 0
Fetch 2 10.33 9.55 41379 42190 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 10.35 9.57 41381 42192 0 9 <=5000000)
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 82
Rows Row Source Operation
------- ---------------------------------------------------
9 HASH GROUP BY (cr=42190 pr=41379 pw=0 time=0 us cost=11949 size=44228052 card=4914228)
6000000 TABLE ACCESS FULL T (cr=42190 pr=41379 pw=0 time=29090 us cost=11414 size=44228052 card=4914228)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.00
SQL*Net message to client 2 0.00 0.00
reliable message 1 0.00 0.00
enq: KO - fast object checkpoint 1 0.00 0.00
direct path read 334 0.00 0.02
SQL*Net message from client 2 0.02 0.02
********************************************************************************
select z,count(*),max(z)
from t_c_dss
group by z
call count cpu elapsed disk query current rows <= 5000000;
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 10.71 10.02 33071 33680 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 10.72 10.04 33073 33682 0 9
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 82
Rows Row Source Operation
------- ---------------------------------------------------
9 HASH GROUP BY (cr=33680 pr=33071 pw=0 time=0 us cost=9723 size=46852029 card=5205781)
6000000 TABLE ACCESS FULL T_C_DSS (cr=33680 pr=33071 pw=0 time=0 us cost=9155 size=46852029 card=5205781)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
reliable message 1 0.00 0.00
enq: KO - fast object checkpoint 1 0.00 0.00 <=5000000)
direct path read 270 0.01 0.02
SQL*Net message from client 2 0.02 0.02
********************************************************************************
select z,count(*),max(z)
from t_c_oltp
group by z
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 9.82 9.42 12852 13635 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 9.83 9.42 12854 13637 0 9
Optimizer mode: ALL_ROWS
Parsing user id: 82
Elapsed times include waiting on following events:
Rows Row Source Operation ------- ---------------------------------------------------
9 HASH GROUP BY (cr=13635 pr=12852 pw=0 time=0 us cost=4271 size=50894532 card=5654948)
6000000 TABLE ACCESS FULL T_C_OLTP (cr=13635 pr=12852 pw=0 time=0 us cost=3651 size=50894532 card=5654948)
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00times to resolve the consultation were:
reliable message 1 0.00 0.00 enq: KO - fast object checkpoint 1 0.00 0.00
direct path read 110 0.00 0.00
SQL*Net message from client 2 0.02 0.02
T = 9.57s = 10.04s
T_C_DSS T_C_OLTP = 9.42s
The OLTP select on the table was thrown the least time, showing that there was no overhead on the issue of compression.
overall conclusion is that bulk of time and redo consumption on the OLTP table are important and need to be taken into account. Also consider that this mechanism, as is its name infers, is designed for OLTP databases where there is common massive change, although it is not that unusual in certain cases, as many hybrid bases behave as OLTP (eg during the day) and at night are used as DSS with significant batch processing, which usually brings massive changes in data.
The official paper:
"Advanced Compression with Oracle 11g R2" says
specific scripts are not compressed in each operation and the compression of the entire block is performed in a batch when a threshold is reached in the block. Surely that is why in my test times were excessive and that many rows to insert or modify the threshold was reached in several blocks firing live compression and adding processing time to the sentence.
I always like to thoroughly test each new feature to see if I can recommend to my clients because not everything that glitters is gold and sometimes there are restrictions that make it inapplicable in certain businesses. We also have to consider the economic issue, advanced compression, unlike basic compression is enabled for use with the Enterprise Edition license, you pay as a separate optional and therefore is a factor no less deserving is analyzed before to implement this new feature.
0 comments:
Post a Comment