La semana pasada tuve que aplicar el parche 11.2.0.2 sobre un equipo de desarrollo. Cuando entré a metalink y busqué el parche que aplicaba a mi SO (Solaris SPARC) me llamó la atención el tamaño del parche. Los ultimos parches que instalé recuerdo que no pesaban mucho mas de 1Gb. El parche de 11.2.0.2 sobre la plataforma que necesitaba pesa 5.1Gb!, y para AIX mas de 6Gb. Recien una vez que leí la documentación entendí el porque. El tema es que Oracle cambió la politica de aplicación de parches a partir de 11.2.0.2. Ahora no son mas incrementales, sino totales y ademas contienen todo el bundle, es decir el server, cliente, gateway, grid, etc.
Recuerdo que en 9i venia todo junto, si querias instalar solo un cliente necesitabas bajarte 3 archivos que contenian todo, lo cual resultaba engorroso. En 10g independizaron las instalaciones de cliente, server, grid, etc. Ahora parece que nuevamente hay que bajar todo y luego elegir en la instalación lo que necesitamos.
Es obligatorio usar un home separado para la instalación, al ser total no se puede parchear sobre el home actual. En mi caso ese nuevo requisito no me molesta ya que siempre considero como una buena practica instalar los parches sobre una copia en un home nuevo, para minimizar riesgos por si el parche falla a la mitad de la instalación y la vuelta atrás requiere respaldar los binarios anteriores. Si estan muy cortos de espacio, se complica un poco crear un home separado asi que en ese caso habrá que bajar las bases, respaldar los binarios, borrarlos e instalar el nuevo home.
La instalación sobre Solaris SPARC no tuvo contratiempos. Tuve que upgradear un Oracle 11g R1 (11.1.0.7) y un Oracle 11g R2 (11.2.0.1). Ambas actualizaciones se realizaron perfectamente y sin ningun contratiempo.
Mostrando entradas con la etiqueta 11g R2. Mostrar todas las entradas
Mostrando entradas con la etiqueta 11g R2. Mostrar todas las entradas
miércoles, 17 de noviembre de 2010
viernes, 12 de febrero de 2010
Compresión avanzada de Tablas en 11g (OLTP Compression)
En esta nota la idea es mostrarles algunas de las primeras pruebas que realicé sobre compresión de tablas. Uno de los features mas fervientemente presentados por Oracle en su última versión (11g) es justamente la compresión avanzada. Desde 9i se puede comprimir tablas pero con ciertas restricciones. En 9i y 10g la compresión de tablas la podriamos llamar básica ya que tiene varias limitaciones y la compresión solo aplica para un restringido set de operaciones (por ejemplo para el insert directo) lo que lo hace bastante util para sistemas DW pero no tanto para las bases OLTP.
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:
Para la prueba voy a crear 3 tablas:
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.
Los tiempos de inserción convencional para cada tablas fueron:
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:
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:
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:
Con el insert directo los tiempos fueron similares:
y el espacio alocado:
De los resultados de arriba vemos que el insert directo incremento el espacio alocado de cada segmento de la siguiente manera:
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.
Los tiempos del update de la columna y en cada caso fueron:
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:
Los tiempos en resolver la consulta fueron:
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.
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.
viernes, 22 de enero de 2010
Concatenar valores de una columna en 11g R2 (usando la función LISTAGG)
Varias veces me han preguntado como hacer para concatenar los valores de una columna en una sola fila agrupados por otra cierta columna. Para eso se necesita un operador de concatenación tal como existe el operador SUM() o el AVG() para sumar o sacar el promedio de un conjunto de columnas con cierto criterio de agrupamiento. Para solucionar esto lo que siempre sugeria era crear el operador STRAGG, que es una función que pueden encontrar en la pagina asktom.oracle.com. Abajo voy a crear la función para mostrarles como funciona:
En el ejemplo concatené los empleados en una sola fila agrupados por el departamento al cual pertenecen. Si tuvieran que concatenar mas valores y no le alcanza con varchar2 tambien pueden encontrar en el sitio de Tom una variante para usar CLOB, aunque es bastante mas lenta.
Ahora bien, en 11g R2 hubo una actualizacion importante de la funciones analiticas, tambien llamadas Analytic Functions II, y una de estas nuevas funciones (LISTAGG)se puede utilizar para hacer lo mismo que stragg en forma nativa evitando tener que crear el tipo stragg y demás. Les muestro un ejemplito para listar los tipos de trabajo que hay en cada departamento:
Como siempre digo, es importante leer los manuales cada vez que se libera una nueva versión, sobre todo el "Oracle New Features" que es un resumen de las nuevas caracteriticas. Me sucede a menudo que veo codigos o formas de administración antiguas que insumen muchas horas para hacer lo mismo que ya esta resuelto en forma nativa o mas simple a partir de una nueva versión.
rop@ROP92> create or replace type string_agg_type as object
2 (
3 total varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT string_agg_type ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN string_agg_type,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT string_agg_type,
22 ctx2 IN string_agg_type)
23 return number
24 );
25 /
Type created.
rop@ROP92> create or replace type body string_agg_type
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5 return number
6 is
7 begin
8 sctx := string_agg_type( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT string_agg_type,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.total := self.total || ',' || value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN string_agg_type,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := ltrim(self.total,',');
28 return ODCIConst.Success;
29 end;
30
31 member function ODCIAggregateMerge(self IN OUT string_agg_type,
32 ctx2 IN string_agg_type)
33 return number
34 is
35 begin
36 self.total := self.total || ctx2.total;
37 return ODCIConst.Success;
38 end;
39 end;
40 /
Type body created.
rop@ROP92>
rop@ROP92> CREATE or replace
2 FUNCTION stragg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5 /
Function created.
rop@ROP92>
rop@ROP92> select deptno, stragg(ename)
2 from scott.emp
3 group by deptno
4 /
DEPTNO STRAGG(ENAME)
-------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
En el ejemplo concatené los empleados en una sola fila agrupados por el departamento al cual pertenecen. Si tuvieran que concatenar mas valores y no le alcanza con varchar2 tambien pueden encontrar en el sitio de Tom una variante para usar CLOB, aunque es bastante mas lenta.
Ahora bien, en 11g R2 hubo una actualizacion importante de la funciones analiticas, tambien llamadas Analytic Functions II, y una de estas nuevas funciones (LISTAGG)se puede utilizar para hacer lo mismo que stragg en forma nativa evitando tener que crear el tipo stragg y demás. Les muestro un ejemplito para listar los tipos de trabajo que hay en cada departamento:
rop@ROP112> select deptno, listagg(job,',') within group (order by job) jobs
2 from (select distinct deptno, job from scott.emp)
3 group by deptno
4 order by deptno
5 /
DEPTNO JOBS
---------- ------------------------------
10 CLERK,MANAGER,PRESIDENT
20 ANALYST,CLERK,MANAGER
30 CLERK,MANAGER,SALESMAN
Como siempre digo, es importante leer los manuales cada vez que se libera una nueva versión, sobre todo el "Oracle New Features" que es un resumen de las nuevas caracteriticas. Me sucede a menudo que veo codigos o formas de administración antiguas que insumen muchas horas para hacer lo mismo que ya esta resuelto en forma nativa o mas simple a partir de una nueva versión.
Suscribirse a:
Entradas (Atom)
.JPG)