Oracle 11g introdujo la compresión avanzada con el fin de reducir la utilización de recursos y la manipulación de grandes volúmenes de datos. Permite una sensible reducción del storage requerido para datos relacionales o estructurados (tablas), datos no estructurados (archivos) o datos de respaldo (backups).
El nuevo feature OLTP Table Compression usa un algoritmo diseñado para trabajar con aplicaciones OLTP. Dicho algoritmo trabaja eliminando valores duplicados a nivel de bloque. El ratio de compresión esperado es de 2 a 3 usando OLTP Compression. Lo más novedoso es que con este feature se puede leer la información comprimida sin necesidad de descomprimirla por lo tanto no existe degradación de rendimiento y hasta puede mejorar la performance debido a una reducción de I/O ya que se necesitará acceder menos bloques para obtener las mismas filas sumado con la consiguiente reducción de buffer cache requerido. De todas formas a mi me gusta ver para creer y por lo tanto les voy a mostrar mis pruebas para que uds saquen sus propias conclusiones, además de incentivarlos a tomar como práctica habitual testear siempre antes de implementar.
No tengo a mano en estos momentos una base R2 de 11g asi que mis pruebas se van a basar en R1. En R2 cambio la sintaxis pero la semántica de las operaciones es la misma que en R1. Por si alguno quisiera probar mi test en R2, y tiene pereza de consultar el manual SQL Reference, les paso las diferencias:
COMPRESS FOR ALL OPERATIONS (11gR1) = COMPRESS FOR OLTP (11gR2)
COMPRESS FOR DIRECT_LOAD OPERATIONS (11gR1) = COMPRESS BASIC (11gR2) (*)default
Para la prueba voy a crear 3 tablas:
T : Sin Compresión
T_C_DSS : Compresión DSS o compresión básica (el mismo tipo de compresión de
versiones anteriores)
T_C_OLTP: Compresión avanzada (11g+)
Conectado a:
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,y char(30),z date);
Tabla creada.
rop@ROP11G> create table t_c_dss (x int,y char(30),z date) compress;
Tabla creada.
rop@ROP11G> create table t_c_oltp (x int,y char(30),z date) compress for all operations;
Tabla creada.
rop@ROP11G> select table_name,pct_free,compression,compress_for from user_tables
2 where table_name in ('T','T_C_DSS','T_C_OLTP');
TABLE_NAME PCT_FREE COMPRESS COMPRESS_FOR
------------------------------ ---------- -------- ------------------
T 10 DISABLED
T_C_DSS 0 ENABLED DIRECT LOAD ONLY
T_C_OLTP 10 ENABLED FOR ALL OPERATIONS
Consultando en el catálogo se puede ver el tipo de partición. Observar que el pctfree en dss es 0 ya que no se esperan cambios.
Ahora voy a insertarles 5M de filas en forma aleatoria pero buscando un forma de que se repitan muchas veces valores en las columnas para que se aproveche la compresión
La columna x insertará valores unicos, la columna y insertará letras A y B y la columna z insertará fechas de hasta 10 dias posteriores al test.
rop@ROP11G> set timing on
rop@ROP11G> set autotr on
rop@ROP11G> insert into t
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;
5000000 filas creadas.
Transcurrido: 00:02:57.65
Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1350848739
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T | | | |
| 2 | COUNT | | | | |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | |
| 4 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=5000000)
Estadísticas
----------------------------------------------------------
4918 recursive calls
304059 db block gets
67349 consistent gets
5 physical reads
292595868 redo size
875 bytes sent via SQL*Net to client
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 <= 5000000;
5000000 filas creadas.
Transcurrido: 00:02:48.43
Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1350848739
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T_C_DSS | | | |
| 2 | COUNT | | | | |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | |
| 4 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=5000000)
Estadísticas
----------------------------------------------------------
4644 recursive calls
276921 db block gets
60593 consistent gets
0 physical reads
290874948 redo size
875 bytes sent via SQL*Net to client
696 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
5000000 rows processed
rop@ROP11G> commit;
Confirmación terminada.
Transcurrido: 00:00:00.04
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;
5000000 filas creadas.
Transcurrido: 00:08:58.00
Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1350848739
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T_C_OLTP | | | |
| 2 | COUNT | | | | |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | |
| 4 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=5000000)
Estadísticas
----------------------------------------------------------
7402 recursive calls
450484 db block gets
93862 consistent gets
3 physical reads
945961224 redo size
877 bytes sent via SQL*Net to client
697 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
5000000 rows processed
Los tiempos de inserción convencional para cada tablas fueron:
T = 2m 57s
T_C_DSS = 2m 48s
T_C_OLTP = 8m 54s
El tiempo de inserción sobre la tabla oltp fue 3 veces mayor al de la tabla dss y la tabla común, pero lo mas llamativo es la cantidad de redo usado:
Redo Insert en T = 292595868 ~= 279Mb
Redo Insert en T_C_DSS = 290874948 ~= 277Mb
Redo Insert en T_C_OLTP = 945961224 ~= 902Mb
También se observa que la cantidad de redo es de mas de 3 veces.
Busqué en metalink y la nota "COMPRESS FOR ALL OPERATIONS generates lot of redo [ID 829068.1]" declara que es esperable bastante más consumo de redo en las operaciones dml sobre una tabla con compresión avanzada (CA).
De todas formas, tarda mas pero veamos si comprimió y cuanto:
rop@ROP11G> 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');
SEGMENT_NAME BLOCKS MB
------------------------------ ---------- ----------
T 35456 277
T_C_DSS 31744 248
T_C_OLTP 11264 88
El factor de compresión sobre la table T_C_OLTP fue importante comparado con las otras dos tablas. Tambien observamos que la relación de 3 se sigue manteniendo ya que la tabla oltp aloca 3 veces menos espacio que las tablas con compresión dss y sin compresión que alocaron espacio similar. Parece que sacrificando mayor consumo de redo y más tiempo de procesamiento del insert tuvo sus resultados, no?.
La próxima prueba será analizar los inserts directos. Recordemos que este tipo de insert es muy usado para carga masiva , en especial en sistemas Datawarehouse (ETL).
Voy a testear la inserción de 1M de filas:
rop@ROP11G> insert /*+ APPEND */ into t
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 <= 1000000;
1000000 filas creadas.
Transcurrido: 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 <= 1000000
rop@ROP11G> /
1000000 filas creadas.
Transcurrido: 00:00:32.01
rop@ROP11G> commit;
Confirmación terminada.
Transcurrido: 00:00:00.07
rop@ROP11G> ed
Escrito file afiedt.buf
1 insert /*+ APPEND */ 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 <= 1000000
rop@ROP11G> /
1000000 filas creadas.
Transcurrido: 00:00:30.01
rop@ROP11G> commit;
Confirmación terminada.
Transcurrido: 00:00:00.01
Con el insert directo los tiempos fueron similares:
T = 29s
T_C_DSS = 32s
T_C_OLTP = 30s
y el espacio alocado:
rop@ROP11G> ed
Escrito file afiedt.buf
1 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 BLOCKS MB
------------------------------ ---------- ----------
T 48768 381
T_C_DSS 33792 264
T_C_OLTP 13312 104
De los resultados de arriba vemos que el insert directo incremento el espacio alocado de cada segmento de la siguiente manera:
SEGMENT_NAME deltha en Mb
------------------------------ -------
T 104 (381-277)
T_C_DSS 16 (264-248)
T_C_OLTP 16 (104-88)
De estos resultados se deduce que la compresión avanzada y la basica funcionaron igual para los insert directos, ambas solo tuvieron que alocar 16Mb adicionales para acomodar 1M de filas nuevas. Sin embargo la tabla si compresión tuvo que alocar un 25% extra.
Otro punto que me interesa testear son los update's y ver como se comporta este tipo de operación en cada caso. Para probar esto voy a cambiar la columna en 50000 filas.
rop@DESA11G> update t set y='C' where y = 'B' and rownum <= 50000;
50000 filas actualizadas.
Transcurrido: 00:00:01.96
Plan de Ejecución
----------------------------------------------------------
Plan hash value: 3603919313
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 50000 | 1562K| 11537 (3)| 00:02:19 |
| 1 | UPDATE | T | | | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| T | 2443K| 74M| 11537 (3)| 00:02:19 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=50000)
3 - filter("Y"='B')
Note
-----
- dynamic sampling used for this statement
Estadísticas
----------------------------------------------------------
258 recursive calls
1667 db block gets
1436 consistent gets
1028 physical reads
7656052 redo size
847 bytes sent via SQL*Net to client
574 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.
rop@DESA11G> ed
Escrito file afiedt.buf
1* update t_c_dss set y='C' where y = 'B' and rownum <= 50000
rop@DESA11G> /
50000 filas actualizadas.
Transcurrido: 00:00:01.84
Plan de Ejecución
----------------------------------------------------------
Plan hash value: 2179303604
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 50000 | 1562K| 9285 (3)| 00:01:52 |
| 1 | UPDATE | T_C_DSS | | | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| T_C_DSS | 2512K| 76M| 9285 (3)| 00:01:52 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=50000)
3 - filter("Y"='B')
Note
-----
- dynamic sampling used for this statement
Estadísticas
----------------------------------------------------------
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 <= 50000
rop@DESA11G> /
50000 filas actualizadas.
Transcurrido: 00:01:09.89
Plan de Ejecución
----------------------------------------------------------
Plan hash value: 2618608701
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 50000 | 1562K| 3791 (8)| 00:00:46 |
| 1 | UPDATE | T_C_OLTP | | | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| T_C_OLTP | 2814K| 85M| 3791 (8)| 00:00:46 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=50000)
3 - filter("Y"='B')
Note
-----
- dynamic sampling used for this statement
Estadísticas
----------------------------------------------------------
411 recursive calls
6153098 db block gets
4894807 consistent gets
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 roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
50000 rows processed
rop@DESA11G> commit;
Confirmación terminada.
Transcurrido: 00:00:00.01
Los tiempos del update de la columna y en cada caso fueron:
Tiempo Redo Db block gets
------ ---- -------------
T 1s 7Mb 1667
T_C_DSS 1s 15Mb 51518
T_C_OLTP 1m 9s 39Mb 6153098
Los tiempos en la tabla oltp para el update masivo son muy superiores con respecto a los tiempos de la misma operacion sobre las otras tablas. Intenté probar de realizar el mismo update pero con 1M de filas y si bien para las tablas t y dss los tiempos fueron de menos de 2 minutos para el caso de la tabla oltp no terminó y pasadas 2 horas tuve que matar la sesión. Lo probé 3 veces en dias distintos y en los tres casos tuve que suspender la ejecución.
En el ultimo test voy a ver como se comportan los select's. Para ver detalle voy a activar el trace 10046. La sentencia que armé recorre toda la tabla:
rop@DESA11G> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Sesión modificada.
Transcurrido: 00:00:00.07
rop@DESA11G> ed
Escrito file afiedt.buf
1 select z,count(*),max(z)
2 from t
3* group by z
rop@DESA11G> /
Z COUNT(*) MAX(Z)
--------- ---------- ---------
18-FEB-10 666293 18-FEB-10
17-FEB-10 665982 17-FEB-10
20-FEB-10 667869 20-FEB-10
16-FEB-10 666656 16-FEB-10
23-FEB-10 665526 23-FEB-10
19-FEB-10 666827 19-FEB-10
22-FEB-10 665960 22-FEB-10
21-FEB-10 666494 21-FEB-10
24-FEB-10 668393 24-FEB-10
9 filas seleccionadas.
Transcurrido: 00:00:09.89
rop@DESA11G> ed
Escrito file afiedt.buf
********************************************************************************
select z,count(*),max(z)
from t
group by z
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 2 2 0 0
Execute 1 0.00 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
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
------- ------ -------- ---------- ---------- ---------- ---------- ----------
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
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
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 82
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)
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
direct path read 110 0.00 0.00
SQL*Net message from client 2 0.02 0.02
Los tiempos en resolver la consulta fueron:
T = 9.57s
T_C_DSS = 10.04s
T_C_OLTP = 9.42s
El select sobre la tabla oltp fue el que menos tiempo arrojó, demostrando que no hubo overhead por el tema de la compresión.
Como conclusión general se ve que para operaciones masivas los tiempos y el consumo de redo sobre la tabla oltp son importantes y hay que tenerlos en cuenta. Tambien consideremos que este mecanismo, tal cual su nombre lo infiere, esta pensado para bases oltp en donde no es común el cambio masivo, aunque tampoco es tan infrecuente en ciertos casos, ya que muchas bases hibridas se comportan como oltp (por ejemplo durante el dia) y por la noche se usan como DSS con importante procesamiento batch que generalmente produce cambios masivos de datos.
En el paper oficial: "Advanced Compression with Oracle 11g R2" dice que las escrituras puntuales no se comprimen en cada operación y que la compresión de todo el bloque se realiza en forma batch cuando se alcanza un umbral en el bloque. Seguramente por ese motivo en mi test los tiempos fueron excesivos ya que al insertar o modificar muchas filas se alcanzó el umbral en varios bloques disparando la compresión en vivo y sumando tiempo al procesamiento de la sentencia.
A mi siempre me gusta testear a fondo cada nuevo feature para ver si lo puedo recomendar a mis clientes ya que no todo lo que brilla es oro y a veces existen restricciones que lo hacen inaplicable en ciertos negocios. Además hay que considerar el tema economico, la compresión avanzada, a diferencia de la compresión básica que viene habilitada para usar con la licencia de Enterprise Edition, se paga como un opcional aparte y por lo tanto es un factor no menor que merece se analizado antes de implementar este nuevo feature.
.JPG)
Muy útil. Gracias por compartirlo.
ResponderEliminarHola Pablo, en primer lugar felicitarte por el estudio presentado que me ha servido de gran ayuda.
ResponderEliminarPero si me lo permites, quisiera plantearte una duda.
Como se comporta la compresión OLPT con campos tipo BLOB, estos datos BLOB son ficheros pdf generados por aplicaciones web.
POr lo que hemos podido experimentar parece que la compresión es casi nula. No sabemos si esto es inviable o es que necesitamos meter mas registros para comenzar a ver resultados. Actualmente hemos metido 2500 registros.
Muchas gracias de antemano.
Hola Nacho, sé que ya pasó un tiempo desde tu consulta, pero te recomiendo leer el siguiente documento donde se menciona a la deduplicación de los archivos que se guardan en columnas BLOB y su compresión.
ResponderEliminarEste es el link: http://www.oracle.com/technetwork/es/database/enterprise-edition/documentation/tutorial-oracle-database-11gr2-1704974-esa.pdf.
Saludos.
Interesante ejercicio. Sencillo, ilustrativo y muy clarificador!!
ResponderEliminarGracias.
Muchas gracias, muy interesante y esclarecedor, antes de llevarlo a cabo en nuestros sistemas.
ResponderEliminar