miércoles, 28 de enero de 2009

Probando SQL Result Cache

A partir de Oracle 11g se agregaron varias funcionalidades interesantes. Una de ellas es lo que se denomina SQL Result Cache. Con este nuevo feature se pueden acelerar sentencias que utilizan tablas con tasa de cambio nula o muy baja, por ejemplo tablas de referencia. La idea de este mecanismo es guardar el resultado de la ejecución de la sentencia para que las subsiguientes ejecuciones lo reutilicen.

Voy a realizar un test con una tabla de esquema de ejemplo HR. Si bien la tabla del ejemplo es muy pequeña y no se va a notar la diferencia en los tiempos, sirve para mostrar el caching de resultados y el cambio de planes.


rop@ROP111> ed
Escrito file afiedt.buf

1 select department_id,
2 count(1) cnt,
3 avg(salary) avg_sal,
4 max(hire_date) max_hire
5 from hr.employees
6* group by department_id
rop@ROP111> /

DEPARTMENT_ID CNT AVG_SAL MAX_HIRE
------------- ---------- ---------- ---------
100 6 8600 07-DIC-99
30 6 4150 10-AGO-99
1 7000 24-MAY-99
20 2 9500 17-AGO-97
70 1 10000 07-JUN-94
90 3 19333.3333 13-ENE-93
110 2 10150 07-JUN-94
50 45 3475.55556 08-MAR-00
40 1 6500 07-JUN-94
80 34 8955.88235 21-ABR-00
10 1 4400 17-SEP-87
60 5 5760 07-FEB-99

12 filas seleccionadas.


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
0 SELECT STATEMENT 11 165 4 (25) 00:00:01
1 HASH GROUP BY 11 165 4 (25) 00:00:01
2 TABLE ACCESS FULL EMPLOYEES 107 1605 3 (0) 00:00:01
--------------------------------------------------------------------------------


Estadísticas
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
6 physical reads
0 redo size
946 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed


Vemos que el plan realiza un full scan sobre la tabla y requiere de 7 lecturas logicas y 6 fisicas.

Ahora voy a editar la sentencia y le agrego mediante un hint la directiva para que utilice result cache.


rop@ROP111> ed
Escrito file afiedt.buf

1 select /*+ result_cache */ department_id,
2 count(1) cnt,
3 avg(salary) avg_sal,
4 max(hire_date) max_hire
5 from hr.employees
6* group by department_id
rop@ROP111> /

DEPARTMENT_ID CNT AVG_SAL MAX_HIRE
------------- ---------- ---------- ---------
100 6 8600 07-DIC-99
30 6 4150 10-AGO-99
1 7000 24-MAY-99
20 2 9500 17-AGO-97
70 1 10000 07-JUN-94
90 3 19333.3333 13-ENE-93
110 2 10150 07-JUN-94
50 45 3475.55556 08-MAR-00
40 1 6500 07-JUN-94
80 34 8955.88235 21-ABR-00
10 1 4400 17-SEP-87
60 5 5760 07-FEB-99

12 filas seleccionadas.


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 11 165 4 (25) 00:00:01
1 RESULT CACHE 3kcg089wfhwtj35k7d8fm6b9xc
2 HASH GROUP BY 11 165 4 (25) 00:00:01
3 TABLE ACCESS FULL EMPLOYEES 107 1605 3 (0) 00:00:01
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=4; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache */ department_id,
count(1) cnt,
avg(salary) avg_sal,
max(hire_date) max_hire
from hr.employees
grou"



Estadísticas
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
940 bytes sent via SQL*Net to client
324 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed

rop@ROP111>



Con la primera ejecución quedó almacenado el resultado, cambió el plan pero seguimos viendo que aun hay 7 lecturas lógicas .

Ahora vamos a ver sucede en la próxima ejecución:


rop@ROP111> /

DEPARTMENT_ID CNT AVG_SAL MAX_HIRE
------------- ---------- ---------- ---------
100 6 8600 07-DIC-99
30 6 4150 10-AGO-99
1 7000 24-MAY-99
20 2 9500 17-AGO-97
70 1 10000 07-JUN-94
90 3 19333.3333 13-ENE-93
110 2 10150 07-JUN-94
50 45 3475.55556 08-MAR-00
40 1 6500 07-JUN-94
80 34 8955.88235 21-ABR-00
10 1 4400 17-SEP-87
60 5 5760 07-FEB-99

12 filas seleccionadas.


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 11 165 4 (25) 00:00:01
1 RESULT CACHE 3kcg089wfhwtj35k7d8fm6b9xc
2 HASH GROUP BY 11 165 4 (25) 00:00:01
3 TABLE ACCESS FULL EMPLOYEES 107 1605 3 (0) 00:00:01
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=4; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache */ department_id,
count(1) cnt,
avg(salary) avg_sal,
max(hire_date) max_hire
from hr.employees
grou"



Estadísticas
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
946 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed

rop@ROP111>


Como vemos cambió el plan denotando el uso del valor almacenado previamente. Además hay que notar que las lecturas lógicas (consistent gets) ahora son 0. De esta forma vemos que este nuevo feature de 11g puede resultar muy atractivo para acelerar las sentencias sobre tablas estáticas o con cambios poco frecuentes.
En el caso de que cambie alguna tabla subyacente se invalidará el resultado almacenado y se volverá a ejecutar normalmente realizandose el caching para la subsiguiente ejecución, de forma de mantener la consistencia de los resultados.
SQl Result Cache es un nuevo cache en la SGA, tal como es el buffer cache. Ademas se agregan nuevas parametrización que gobiernan el comportamiento de caching de sql's, tales como result_cache_mode, result_cache_max_Result, etc.
Este nuevo mecanismo puede aplicarse también a funciones PL/SQL haciendo que se almacene el valor de retorno y provocando que en las subsiguientes ejecuciones no se ejecute ninguna línea de código de la función y simplemente se retorne el valor resultado previamente cacheado.

lunes, 26 de enero de 2009

Como monitorear el espacio temporal consumido

Es común que DBAs que recien se inician se preocupen cuando en un reporte de estado de tablespaces se muestra al tablespace temporal al 100% de utilización, es decir sin espacio disponible. En ese punto es importante aclarar que la alocación y dealocación de espacio temporal es distinta para los tablespace temporales respecto a los tablespaces de datos y por lo tanto si bien el reporte muestra un tablespace lleno en realidad no implica que no pueda utilizarse. Para obtener el espacio libre real armé la siguiente consulta:


select t2."TempTotal" "TempTotal (Mb)",
t1."TempUsed" "TempUsed (Mb)",
t2."TempTotal" - t1."TempUsed" "TempFree (Mb)"
from (select nvl(round(sum(tu.blocks * tf.block_size) / 1024 / 1024, 2), 0) "TempUsed"
from v$tempseg_usage tu, dba_tablespaces tf
where tu.TABLESPACE = tf.tablespace_name) t1,
(select round(sum(bytes) / 1024 / 1024, 2) "TempTotal"
from dba_temp_files) t2


Con esta sentencia se puede ir monitoreando el espacio disponible. Puede resultar conveniente su utilización para hacer seguimiento de procesos batch que hayan cancelado por falta de espacio temporal. Es bastante frecuente que un proceso se quede sin espacio cuando las sentencias que lo componen generan un plan que requiere demasiado agrupamiento, agregacion, hashing, producto cartesiano, etc. Si faltaran indices o estadisticas en los segmentos involucrados se podría armar un plan erroneo que requiera mas espacio temporal que el necesario. Si las sentencias estuvieran bien escritas, los objetos con estadisticas actualizadas y con el esquema de indexacion adecuado podria estar subestimado el espacio temporal y se necesite un redimensionamiento.

martes, 20 de enero de 2009

Usando OUTLINES en 10g

Hace unas semanas estuve trabajando en una migración de 8i a 10g para un banco. Si bien se había realizado un análisis intensivo de impacto en las sentencias Top hubo algunas que no se consideraron y luego de la migración con la carga online se comenzaron a degradar. Dado que la reescritura de las sentencias implicaba un cambio de código mayor se optó por usar outlines para fijar el plan a dos de las sentencias con problemas.
Antes de mostrarles un ejemplo de uso me gustaría aclarar que desde 10g se pueden usar advisors, en particular el dbms_sqltune que generan propuestas de mejoras de sentencias. Una de las propuestas podria ser agregar un indice, una vista materializada o un profile. Los profiles son muy interesantes ya que pueden verse como una evolución de los outlines o los hints. Con los profiles lo que se hace es darle mayor información al optimizador para que conozca mas sobre al correlación de los datos y proponga un mejor plan de ejecución. Ya en otro nota explicaré mas detalle de los profiles.
Ahora les voy a mostrar un ejemplo de uso de outlines.

Primero, cree una tabla T copia de la dba_objects, luego hice un update a la T del campo object_id y cambie el 90% de las filas poniendo como object_id = 99. Con esa distribucion, el optimizador por costos en all_rows (default en 10g) va por un full scan y por regla va por el indice. Es claro que dado que tiene que procesar el 90% de las filas el path mas adecuado deberia ser por FullScan. Lo que hice es forzar al optimizador CBO de 10g para que vaya por indice igual. Abajo les pegue el detalle:



SQL*Plus: Release 10.2.0.4.0 - Production on Mar Ene 20 10:38:35 2009

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


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

-- Primero muestro cual es el plan elegido por el cbo de 10g

rop@DESA10G> set autotr trace exp
rop@DESA10G> select count(distinct object_name) from t where object_id = 99;

Plan de Ejecución
----------------------------------------------------------

-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 777 |
| 1 | SORT GROUP BY | | 1 | 79 | |
| 2 | TABLE ACCESS FULL| T | 16985 | 1310K| 777 |
-----------------------------------------------------------

-- Preparo la sesion para que use RBO y por lo tanto use otro plan

rop@DESA10G> alter session set optimizer_mode = rule;

-- Ahora activo para grabar el outline el plan generado con RBO

rop@DESA10G> alter session set USE_STORED_OUTLINES = true;

Sesión modificada.

rop@DESA10G> select count(distinct object_name) from t where object_id = 99;

Plan de Ejecución
----------------------------------------------------------

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 53 |
| 1 | SORT GROUP BY | | 1 | 79 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1649 | 127K| 53 |
| 3 | INDEX RANGE SCAN | T_IDX | 660 | | 1 |
----------------------------------------------------------------------

-- Como vemos se eligio ir por el indice

rop@DESA10G> alter session set USE_STORED_OUTLINES = false;

Sesión modificada.

-- Vuelvo a poner el modo del optimizador en su valor default

rop@DESA10G> alter session set optimizer_mode = all_rows;

Sesión modificada.

rop@DESA10G> select count(distinct object_name) from t where object_id = 99;

Plan de Ejecución
----------------------------------------------------------

-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 777 |
| 1 | SORT GROUP BY | | 1 | 79 | |
| 2 | TABLE ACCESS FULL| T | 16985 | 1310K| 777 |
-----------------------------------------------------------

-- Vemos que usa el full scan

- Ahora activamos el outline

rop@DESA10G> alter session set USE_STORED_OUTLINES = true;

Sesión modificada.

rop@DESA10G> select count(distinct object_name) from t where object_id = 99;

Plan de Ejecución
----------------------------------------------------------

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 53 |
| 1 | SORT GROUP BY | | 1 | 79 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1649 | 127K| 53 |
| 3 | INDEX RANGE SCAN | T_IDX | 660 | | 1 |
----------------------------------------------------------------------

Podemos observar que se uso el plan guardado en los outlines

rop@DESA10G> set autotr off
rop@DESA10G> select * from dba_outlines;

NAME OWNER CATEGORY USED
------------------------------ ------------------------------ ------------------------------ ------
TIMESTAMP VERSION
--------- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SIGNATURE COMPATIBLE ENABLED FORMAT
-------------------------------- ------------ -------- ------
SYS_OUTLINE_09012010432243201 ROP DEFAULT USED
20-ENE-09 10.2.0.1.0
select count(distinct object_name) from t where object_id = 99
C7ABDCAEF65EDD55840EBEC9D9AC70C3 COMPATIBLE ENABLED NORMAL

-- En la consulta anterior vemos que se uso el outline almacenado con el plan fijado.

lunes, 12 de enero de 2009

Agregado de columnas con valor default (revisión de nuevo mecanismo interno en 11g)

Hace un tiempo me pidieron estimar el tiempo que llevaría agregar una columna a una tabla de 800 millones de registros. Cuando me hicieron esa pregunta inmediatamente les consulté si la columna a agregar iba a ser null ó not null con un valor default. Eso marcaba una gran diferencia ya que agregar una columna con un valor default implica que internamente ser ejecute un update con el consiguiente consumo de redo y undo además del lockeo DML propio del update. Desafortunadamente la versión de la base era 10g así que tuve que realizar una estimación en un ambiente de test similar. La operación demoró unas horas.
Ahora en 11g esa misma operación es casi instantánea ya que en lugar de actualizarse el valor default de la columna por cada fila se agrega una entrada en el diccionario de datos para almacenar el valor default. Cuando se realiza una consulta de la tabla y la columna en una fila determinada no tiene valor Oracle sabe que debe obtener ese valor del catalogo.

Voy a comparar este cambio de comportamiento interno comparando la misma operación entre bases 10g y 11g


Primero voy a crear una tabla T y luego la voy a ir copiando a si misma para si generar un par de millones de filas


rop@TEST10G> create table t as select * from dba_objects;

Tabla creada.

rop@TEST10G> insert into t select * from t;

92631 filas creadas.

rop@TEST10G> /

185262 filas creadas.

rop@TEST10G> /

370524 filas creadas.

rop@TEST10G> /

741048 filas creadas.

rop@TEST10G> /

1482096 filas creadas.

rop@TEST10G> select count(1) from t;

COUNT(1)
----------
2964192

rop@TEST10G> commit;

Confirmación terminada.


rop@TEST10G> set timing on
rop@TEST10G> alter table t add x varchar2(2) null;

Tabla modificada.

Transcurrido: 00:00:00.17

Vemos que agregar un columna x null demoró 17 decimas de segundo.


rop@TEST10G> alter table t add y varchar2(2) default 'E' not null;

Tabla modificada.

Transcurrido: 00:04:30.61

En cambio al agregar otra columna con valor default se demoró 4 minutos y medio.

Ahora voy a realizar lo mismo en 11g:


rop@TEST10G> conn rop@rop111
Introduzca la contraseña: ********
Conectado.
rop@ROP111> set timing on

Transcurrido: 00:01:29.95
rop@ROP111> alter table t add x varchar2(2) null;

Tabla modificada.

Transcurrido: 00:00:00.93
rop@ROP111> alter table t add y varchar2(2) default 'E' not null;

Tabla modificada.

Transcurrido: 00:00:00.20
rop@ROP111>


Como vemos el tiempo de agregado de la columna X fue similar al tiempo en 10g. Por el contrario el tiempo insumido en agregar la columna Y fue instantaneo comparado con los 4 minutos y medio que demoró en 10g.

Ahora comprobamos que efectivamente se haya agregado la columna con el valor 'E'


rop@ROP111> select count(1) from t where Y = 'E';

COUNT(1)
----------
2964192

Transcurrido: 00:00:08.65


Para mayor detalle se puede consultar la documentación oficial online en: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables006.htm#i1006666