POR QUE MI QUERY REALIZA UN FULL SCAN A PESAR DEL ÍNDICE

Algunos desarrolladores consideran que generando índices se resuelven la mayor parte de los problemas de desempeño en sus querys. Generan planes de ejecución buscando al supuesto culpable de todos sus males: la sentencia 'full scan'.  Incluso algunas herramientas  lo muestran en rojo, para que sea más fácil identificarlo.

Una vez que el desarrollador detecta el 'full scan'  procede a la generación del indice, normalmente de tipo b-tree, que es el default de Oracle. Una vez generado, observan con molestia que se sigue realizando el 'full scan' a la tabla.

El siguiente ejercicio tiene como objetivo generar un índice y observar su comportamiento. Previo a su generación, vamos a realizarnos  las siguientes preguntas básicas:

 1.- Del total de registros de la tabla, cuantos va a recuperar el query?

 2.- Cuantos valores distintos contiene el campo o campos sobre el cual se va a generar el índice?

 3.- Contiene valores nulos el campo a indexar?

 4.- El campo a indexar contiene los datos ordenados?

 5.- Verificar que los siguientes parametros a nivel instancia que afectan la ejecución de indices,  contengan   los valores por default:

• OPTIMIZER_INDEX_COST_ADJ=100.- El valor por default es 100. Si se disminuye este valor, Oracle considera que tiene 'menor costo' el utilizar un índice y favorece su ejecución. Lo recomendable es modificar este valor a nivel sesión, no a nivel instancia.
• OPTIMIZER_INDEX_CACHING=0.- El valor por default es 0. Si asignamos un valor,  el indice tendra la informacion en cache y se ejecutara más rápido. Es recomendable modificarlo a nivel sesión, no instancia.

Para verificar los valores apropiados para estos parámetros se puede revisar la guía de performance OPDG  (Oracle Performance Diagnostic Guide) del sitio de soporte oficial de Oracle.

Iniciamos el ejercicio creando la tabla TEST la cual va a contener un solo campo de tipo númerico

SQL> CREATE TABLE TEST ( campo1 number);

Table created.

Insertamos 100 mil registros únicos en la tabla TEST, ordenados de forma ascendente por medio del siguiente bloque pl/sql:

SQL>  DECLARE
                vcuenta NUMBER := 100000;
              BEGIN
                 FOR i IN 1..100000 LOOP
                   vcuenta  := vcuenta+1;
                   INSERT INTO TEST values (vcuenta);
                 END LOOP;
                   COMMIT;
          END;
   /

PL/SQL procedure successfully completed. 

Validamos la ejecución del bloque PL/SQL. Se tienen 100 mil registros únicos iniciando con el valor 100,000 y finalizando con el registro 200,000   

SQL> SELECT min(campo1), max(campo1), count(*) FROM test;

   MIN(CAMPO1) MAX(CAMPO1)   COUNT(*)
-----------            -----------                       ----------
     100001          200000                         100000

Generamos el índice idx_test1 en el campo campo1

    
SQL> CREATE INDEX idx_test1 ON test (campo1);

Index created.

Actualizamos estadísticas de la tabla TEST con el package 'DBMS_STATS' con los parámetros básicos,

incluyendo la opción 'cascade=> TRUE' para que se actualicen las estadísticas del índice:

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TEST', cascade=> TRUE);

PL/SQL procedure successfully completed.

Actualizamos las estadísticas del índice recien generado IDX_TEST1 (ya habiamos dado la opción cascade, por si las dudas)

SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>'TEST', indname=> 'IDX_TEST1');

PL/SQL procedure successfully completed.

Verificamos las estadísticas actualizadas de la tabla 'TEST' en la tabla del sistema 'dba_tables'

SQL> SELECT num_rows, last_analyzed, sample_size

           FROM dba_tables WHERE table_name = 'TEST';

  

   NUM_ROWS   LAST_ANA  SAMPLE_SIZE
----------                --------                  -----------
    100000           29/04/14           100000    

Verificamos las estadísticas del índice en la tabla del sistema dba_indexes.  Esta consulta nos puede indicar, de forma muy básica, que tan probable es que nuestro índice funcione correctamente, verificando los siguientes puntos:

1.- NUM_ROWS .- El índice contiene los rowids del total de registros de la tabla : 100,000. El rowid es la  dirección física donde se encuentra el registro guardado. El índice va a recuperar estos rowids.
               
2.- LAST_ANALIZED .- Contiene la fecha de la última actualización de estadísticas para el índice

3.- SAMPLE_SIZE .- Contiene el tamaño muestra utilizado por el 'package' DBMS_STATS para calcular las estadísticas.  Lo ideal es que este valor sea igual al campo 'NUM_ROWS'. En este caso tenemos un tamaño muestra apropiado de 100,000 registros, que el mismo que el el 'NUM_ROWS'

4.- DISTINCT_KEYS.- Tal vez sea uno de los campos más importantes para validar la viabilidad del índice. En este caso tenemos 100,000 valores distintos, que es lo más apropiado. Si por ejemplo se tuvieran 100,000 mil registros, de los cuales 50,000  contienen el valor 1 y los otros 50 mil restantes el valor de 2, tendriamos un valor de DISTINCT_KEYS igual a 2, por lo que seguramente el índice no funcionaria.

5.- CLUSTERING_FACTOR .- En este caso ingresamos 100,000 registros de forma ascendente 1,2,3,4,5 sucesivamente, por lo que los datos se encuentran ordenados fisicamente en la tabla, lo que facilita las búsquedas. El valor contenido en este campo, de 152  indica un buen grado de ordenamiento en la tabla. Entre menor sea el valor de este campo, mejor ordenamiento físico se tendrá.
                      

SQL> SELECT num_rows, last_analyzed, sample_size, distinct_keys, clustering_factor FROM dba_indexes where index_name ='IDX_TEST1';

  NUM_ROWS         LAST_ANA        SAMPLE_SIZE        DISTINCT_KEYS    CLUSTERING_FACTOR
----------                      --------                    -----------                      -------------                       -----------------
    100000                29/04/14               100000                       100000                            152   
   

Con el índice generado vamos a realizar una primera consulta que va a recuperar el 100% de los registros de la tabla.

El siguiente plan de ejecución muestra que se está utilizando él índice 'IDX_TEST1'. Se esta realizando un 'INDEX FAST FULL SCAN', el cual permite contar el total de registros, por medio del mismo índice, sin necesidad de contar los registros en la tabla. Este tipo de 'barrido' o 'scan' es muy útil cuando se recupera un volumen importante de información  ya que utiliza lecturas 'multibloque' y puede ser invocado por el hint 'index_ffs'.  En este momento se está realizando un 'FULL SCAN'

  1* SELECT count(*) FROM test WHERE campo1 between 100000 AND 200000

  COUNT(*)
----------
    100000

Execution Plan
----------------------------------------------------------
Plan hash value: 2671621383

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     5 |    63   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_TEST1 |   100K|   488K|    63   (2)| 00:00:01 |
-----------------------------------------------------------------------------------

Vamos a realizar otra consulta para recuperar ahora el 30% del total de registros y como nos muestra el plan de ejecución, se sigue realizando un 'FULL SCAN'
   
SQL> SELECT count(*) FROM test WHERE campo1 between 100000 AND 130000;

  COUNT(*)
  ----------
     30000

Execution Plan
----------------------------------------------------------
Plan hash value: 2671621383

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     5 |    63   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_TEST1 | 30000 |   146K|    63   (2)| 00:00:01 |

-----------------------------------------------------------------------------------           

Ahora modificamos el query para que recupere el 27% del total de registros. En este momento se observa en el plan de ejecución cambio de un 'INDEX FAST FULL SCAN' a un 'INDEX RANGE SCAN' por lo que en este punto es donde el índice estaría cumpliendo su verdadera funcionalidad, de retornar un  número limitado de registros de forma eficiente.   
   
No es una regla escrita, pero si se va a recuperar entre el 1 y el 25% del  total de registros de una tabla (en este ejercicio funciono hasta el 27% del total), el índice será utilizado, sin necesidad de realizar un 'FULL SCAN' y tendremos a nuestro desarrollador contento.

En este punto se deben considerar factores como cardinalidad, selectividad y valor asignado al parámetro 'DB_FILE_MULTIBLOCK_READ_COUNT'

Para valores mayores, el manejador prefiere realizar un 'FULL SCAN' con lecturas 'multibloque' ignorando lecturas de índice de tipo 'INDEX SCAN' y 'RANGE SCAN' principalmente.
   
SQL> SELECT count(*) FROM test WHERE campo1 between 100000 AND 127000;

  COUNT(*)
----------
     27000

Execution Plan
----------------------------------------------------------
Plan hash value: 3196887443
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     5 |    62   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TEST1 | 27000 |   131K|    62   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Conclusiones

Previo a la generación de un índice, es necesario considerar distintos factores como son el número de registros que va a retornar, cardinalidad y selectividad entre otros. Si un query va a retornar un aproximado del 30% o más de registros y generamos un índice, es probable que este no sea invocado para búsquedas puntuales de tipo 'INDEX SCAN' y 'RANGE SCAN' principalmente.  La tabla del sistema 'DBA_INDEXES' nos muestra información básica del índice. Si requerimos información adicional de un índice, la podremos obtener de la vista del sistema 'INDEX_STATS'

José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com


5532439143 Ciudad de México

GENERANDO LOS TRES TIPOS PRINCIPALES DE JOIN: HASH, NESTED Y MERGE

Oracle al igual que otros manejadores de bases de datos utiliza principalmente tres tipos de join entre tablas:

• HASH
• NESTED
• MERGE

 El objetivo es identificar que tipo de sentencias SQL generan estos joins.

1.- HASH JOIN

El primer ejercicio es realizar una sentencia SQL que genere un 'HASH JOIN' para lo cual vamos a crear dos tablas:  'PRUEBA1' y 'PRUEBA2' con un solo campo llamado 'campo1' de tipo númerico, sin ningún índice.

 

La tabla 'PRUEBA1' va a contener únicamente un registro. La tabla 'PRUEBA2' va a contener 7 registros. 

Una vez insertados los registros se realiza un join por el campo campo1 entre la tabla 'PRUEBA1' y 'PRUEBA2'. Se ejecuta el join regresando únicamente un registro

El plan de ejecución muestra que el join de las dos tablas produce tres operaciones: Una operación padre que es el HASH JOIN con el id 1 y dos hijos con los id 2 y 3 con accesos secuenciales a las tablas 'PRUEBA1' y 'PRUEBA2'  

 ID 1.- HASH JOIN

           ID 2.- TABLA ACCESS FULL PRUEBA1

           ID 3.- TABLA ACCESS FULL PRUEBA2

El HASH es un metodo eficiente para ejecutar un join cuando se realiza un acceso secuencial o 'full scan' entre las tablas involucradas. Este metodo unicamente requiere  un ‘barrido’ o 'scan'  por cada tabla, para recuperar todos los registros.

2.- NESTED JOIN

A diferencia del 'HASH JOIN', el 'NESTED JOIN' va a realizar un ‘barrido’ o 'scan' de la tabla secundaria, por cada registro de la tabla principal en el join. Por ser este tipo de join mas costoso, el optimizador lo utiliza cuando va a recuperar un volumen relativamente pequeño de registros y generalmente involucra uno o más índices.

Para el ejercicio de un 'NESTED JOIN', vamos a ocupar las mismas tablas del ejercicio anterior:  'PRUEBA1' y 'PRUEBA2', utilizando el mismo query.  Se van a generar dos índices, 'IDX_PRUEBA1' utlizando el campo1 de la tabla 'PRUEBA1' y el índice 'IDX_PRUEBA2' utilizando el campo1 de la tabla 'PRUEBA2'.

Actualizamos estadisticas de las tablas PRUEBA1 y PRUEBA2 con el 'package' dbms_stats.

Se ejecuta el query anterior, donde se realiza un join entre las tablas 'PRUEBA1' y 'PRUEBA2' por los campos 'CAMPO1'.

A diferencia del ejercicio anterior, donde se genero un 'HASH JOIN', el manejador detecto los nuevos indices 'IDX_PRUEBA1' e 'IDX_PRUEBA2' generando un 'NESTED LOOP', donde se unen las dos tablas a traves de los registros recuperados por los índices. La operación padre 'NESTED LOOPS' con el id 1, va a ser el padre de las operaciones 'INDEX FULL SCAN' con el ID 2 y el 'INDEX RANGE SCAN' ID 3. 

3.- MERGE JOIN

Este realiza dos operaciones

1.- Va a realizar un ordenamiento o ‘sort’ de los datos de las tablas involucradas en el join, conocidas como ‘inner’ y ‘outer’ tabl

2.- Una vez ordenados los datos, se realiza una comparación registro por registro de las dos tablas considerando los siguientes criterios:

o    Si la columna del inner join  <     columna del outer join,  se avanza un registro en la tabla del inner join

o    Si la columna del inner join =  columna del outer join, se leen los registros que hicieron match

o    Si la columna del inner join  >     columna del outer join, se avanza un registro en la tabla del outer join

El realizar el ‘sort’ de las tablas involucradas, es la parte más demandante en cuanto a recursos. Una vez ordenados los datos, la busqueda se realiza de forma relativamente rapida. Este tipo de join se da generalmente cuando se agregan operadores mayor (>), menor (<) y diferente (¡=)

Se muestra un ejemplo para la generación de un 'MERGE JOIN'. Se van a crear dos tablas ‘PRUEBA1’ y ‘PRUEBA2’ con los mismos datos, cada una con 9438 registros cada una, a partir de la tabla de sistema 'DBA_EXTENTS'.

Se va a generar un join entre las tablas 'PRUEBA1' y 'PRUEBA2' agregando el operador '<'  en la claúsula 'WHERE a.extent_id < b.extent_id', para forzar la generación del 'MERGE JOIN'.

El plan de ejecución en el ID 1 muestra un 'HASH UNIQUE', originado por el select ‘DISTINCT a.extent_id’. Se muestra el 'MERGE JOIN' con el id 2. Este genero dos ‘hijos’:

o    'SORT JOIN' con el id 3 para ordenar los datos de la tabla ‘PRUEBA1’

o    'SORT JOIN' con el id 5 para ordenar los datos de la tabla ‘PRUEBA2’

El ordenamiento de los datos de las dos tablas, fue el proceso más costoso, como lo muestra el ID 5 ‘SORT JOIN’ que requirio 26M para ser realizado. 

Una vez ordenados los datos, los ‘barridos’ o 'scans'  de la tabla, requirieron  un número menor de  recursos como lo muestra la columna con el id 4 ‘TABLE ACCESS FULL’ con apenas 9437 KBS y la columna con el ID 6 ‘TABLE ACCESS FULL’  con 9438 KBS.

 

Conclusiones

Identificando el tipo de query podremos asignar los recursos de memoria de una forma más eficiente. Si tenemos un query de tipo 'NESTED JOIN' la instancia estará utilizando principalmente memoria 'SGA'. Si tenemos un query de tipo 'HASH JOIN' el tipo de memoria utilizadada será principalemnte 'PGA', que es un recurso mucha más limitado que la memoria 'SGA'. Si detectamos un tipo  join tipo 'MERGE JOIN', sabremos que se esta ejecutando una sentencia con un consumo elevado de recursos,  principalmente para la parte del 'SORT', en particular para el parámetro 'sort_area_size'.  Se pueden utilizar, entre otras, como auxiliares en el monitoreo de la memoria ocupada por estos tipos de join las vistas dinámicas del sistema 'v$sgastat', 'v$pgastat', 'v$systat' y los campos  'pga_used_mem', 'pga_alloc_mem', 'pga_freeable_mem', y 'pga_max_mem' de la vista 'v$process'.

José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com

5532439143 Ciudad de México

EFECTO DE ESTADISTICAS NO ACTUALIZADAS UTILIZANDO ORACLE CBO

A partir de la versión 9i  de Oracle se introduce el optimizador basado en costos o 'Cost Based Optimizer' o CBO que sustituye al optimizador basado en reglas o 'Rule Based Optimizer' o RBO que estuvo vigente durante casi una decada.  El CBO  busca el plan de ejecución que requiera el menor número de recursos (cpu, memoria y disco) de la instancia.  Para lograrlo, requiere de estadísticas confiables.  Cuando estas se actualizan, ya sea de una tabla o de un índice, el manejador conoce entre otros, el número de registros de la tabla, valores nulos, máximos, mínimos, frecuencias, valores distintos, cardinalidad y valores únicos. En este articulo no se incluye el uso de histogramas para aquellas tablas que tengan una distribución de datos no uniforme.

Una forma relativamente sencilla de verificar si las estadísticas se encuentran actualizadas (no necesariamente correctas) es por medio de la tablas del sistema dba_tables y dba_indexes.  Estas contienen entre otros los campos num_rows, sample_size y last_analyzed

El campo num_rows contiene el número de registros que va a considerar el CBO al momento de generar el plan de ejecución y no necesariamente reflejan el número de registros que contiene la tabla, aunque lo ideal es que sea lo más aproximado o igual.  Si realizamos un  'select count(*)' a una tabla y validamos que el resultado es el mismo que contenido en el campo num_rows de la tabla dba_tables, sabremos que el manejador tiene información confiable en cuanto al número de registros de la tabla.  

El campo sample_size nos va a servir también, como punto para validar que tan confiables son las estadísticas. Este nos indica el tamaño muestra utilizando durante la recopilación de estadísticas por medio del package DBMS_STATS.  Lo ideal es que el valor del campo sample_size de las tablas del sistema dba_tables y dba_indexes sea igual o lo más cercano  al total de registros de la tabla, es decir que el manejador conto el total de registros de la tabla.  Este conteo se realiza por medio de la opción ESTIMATE_PERCENT del package DBMS_STATS.  Si colocamos la opción ESTIMATE_PERCENT=>100, tendremos la certeza de que el manejador conto el total de registros de la tabla para actualizar estadísticas. Sin embargo, en ocasiones, es demasiado costoso utilizar esta opción al 100%,  sobre todo en tablas con un número muy elevado de registros. 

Se tiene la alternativa de utilizar la opción 'estimate_percent => dbms_stats.auto_sample_size' del package DBMS_STATS  para que Oracle realice un cálculo basado en una muestra,  sin necesidad de realizar un conteo total de registros.  Para conocer que valor es la más adecuado de la opción ESTIMATE_PERCENT para calcular el tamaño muestra, se puede consultar la nota 390374.1 de la guía 'Oracle Performance Diagnostic Guide (OPDG) del sitio de soporte de Oracle.

El campo last_analyzed de las tablas dba_indexes, dba_tables indica la última vez que las estadísticas fueron actualizadas, ya sea para una tabla o un índice. Si se tienen tablas con operaciones DDL (principalemnte inserts, deletes y updates) de forma continua, pero la fecha last_analyzed no se encuentra actualizada, el CBO no podra generar el mejor plan de ejecución.  Este campo también se encuentra en la tablas del sistema dba_tab_partitions y dba_ind_partitions, para validar la fecha de actualización de estadísticas en tablas e índices particionados.

El siguiente ejercicio tiene como objetivo mostrar el efecto negátivo en el CBO cuando no cuenta con estadísticas actualizadas. Se van a generar dos tablas: ‘TEST1’ y ‘TEST2’ las cuales van a contener únicamente el campo ‘CAMPO1’ de tipo numérico.

Se generan los índices IDX_TEXT1 en la tabla TEST1 e IDX_TEXT2 en la tabla TEST2

Insertamos únicamente un registro en la tabla ‘TEST1’ y actualizamos estadísticas. En la tabla ‘dba_tables’ se muestra que para fines del optimizador, se tiene un registro.

Insertamos en la  tabla ‘TEST2’  20,000,004 registros y actualizamos estadísticas. La tabla ‘dba_tables muestra las estadísticas actualizadas de forma correcta:

Ahora vamos a insertar los 20 millones de registros de la tabla ‘TEST2’ a la tabla ‘TEST1’, sin actualizar las estadísticas de esta última, para que el optimizador ‘crea’ que la tabla tiene únicamente un registro, cuando realmente tiene 20 millones:

Ejecutamos un query, el cual va a realizar el join de las tablas ‘TEST1’ y ‘TEST2’ recuperando todos los registros de ambas tablas. Como el optimizador tiene estadísticas erróneas para la tabla ‘TEST1’, ‘cree’ que únicamente es necesario recuperar un registro de dicha tabla para realizar el join, como lo indica la columna E-Rows, cuando, realmente necesita recuperar 20 millones de registros. Por lo que de forma errónea utiliza el índice IDX_TEXT1,  ya que estima recuperar un número limitado de registros. 

Las estadísticas erróneas sobre la tabla ‘TEST1’ afectan todo el plan de ejecución, ya que en lugar de utilizar un ‘HASH JOIN’ que sería el indicado,  ya que se están recuperando todos los registros de las dos tablas, intenta realizar un acceso indexado, por medio de un ‘NESTED JOIN’,  tanto de la tabla ‘TEST1’ como de la tabla ‘TEST2’. 

Recordando que los índices son efectivos cuando se recupera un número limitado de registros. En este caso se está recuperando el 100% de la información de ambas tablas, por lo que los índices ‘IDX_TEST1’ e ‘IDX_TEST2’ en lugar de mejorar el tiempo de respuesta, lo perjudican.

Ahora, vamos a actualizar las estadísticas de la tabla ‘TEST1’, para que el optimizador tenga información real del número de registros. Una vez actualizadas las estadísticas con el package ‘DBMS_STATS’, reiniciamos la memoria.

Con las estadísticas actualizadas, ejecutamos nuevamente el mismo query.  El tiempo de respuesta disminuyo de 2.46 a 1.06 minutos.  Antes de la actualización de estadísticas sobre la tabla ‘TEST1’ se tenía un ‘NESTED JOIN’, el cual fue sustituido por un ‘HASH JOIN’. El nuevo plan muestra que con las estadísticas actualizadas,  el acceso vía índices ‘IDX_TEXT1’ e ‘IDX_TEXT2’ fue sustituido por ‘TABLE ACCESS FULL’ para ambas tablas, que en este caso en particular es más eficiente.

Conclusiones

Si las tablas e índices de una instancia de Oracle no cuentan con estadísticas actualizadas, el optimizador basado en costos o CBO, no tiene los insumos para generar el plan de ejecución más eficiente. Es importante distinguir las tablas históricas de las transaccionales para determinar que metodo y periocidad en la actualización de estadísticas es el más eficiente. Las últimas versiones de Oracle cuentan con algoritmos que le permiten a partir de una muestra pequeña o 'sample size' de la tabla, determinar de forma muy precisa las estadísticas de tablas e índices.   

José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com


5532439143 Ciudad de México

EFECTO DEL PARAMETRO PGA_AGGREGATE_TARGET

Manejo de memoria PGA 

La documentación de Oracle, nos muestra que la memoria de la instancia se encuentra dividida en dos áreas: ‘SGA’ (System Global Area) y’ PGA’ (Private Global Area).- En teoría es muy fácil distinguir estos dos tipos de memoria.  La ‘SGA’ se encuentra dedicada a recibir la información proveniente de los ‘datafiles’, entre otros, en un área llamada ‘DATABASE BUFFER CACHE’, que normalmente ocupa el mayor tamaño de la memoria física de la instancia. La memoria ‘PGA’, es una memoria privada, donde se tienen información de las sesiones residentes en la base de datos y también es utilizada para operaciones de ‘sort’ y ‘hash’. Sin embargo, en el momento de realizar un monitoreo de las sentencias ‘SQL’, no es tan fácil distinguir en cuál de estas dos memorias se está ejecutando una consulta. Se va a realizar el ejercicio con los siguientes valores de memoria asignada: Para la memoria ‘SGA’ se tiene un valor máximo de 400 mb y 40 mb para la ‘PGA’, siendo este valor un poco más bajo de lo recomendable.  El objetivo del ejercicio es que el  ‘query’ utilice únicamente memoria ‘PGA’ y el efecto del parámetro PGA_AGGREGATE_TARGET en la asignación de los recursos de memoria:

La siguiente pantalla muestra el valor de la memoria que pretendemos que utilice Oracle, que es la 'PGA'. Incialmente se tienen asignados 40mb por medio del parámetro 'PGA_AGGREGATE_TARGET'

 

Una vez identificado el valor asignado a la memoria ‘PGA’, se va a realizar un ejercicio utilizando las tablas ‘test1’ y ‘test2’. La tabla ‘test1’ tiene un solo campo de tipo númerico llamado ‘campo1’ con 30 millones de registros, con valores únicos. La tabla ‘test2’ es una copia exacta de la tabla ‘test1’. El siguiente ‘query’ va a generar un ‘hash join’ entre las dos tablas. Una vez ejecutado,  deberá realizar un barrido completo de todos los registros de las dos tablas, que en teoría deberán ser alojados en memoria ‘PGA’. El ‘query’ realizó el conteo de los 30 millones de registros por cada tabla. El valor inicial de la memoria PGA de únicamente 40 MB, parece no ser suficiente para alojar los 345 MB que inicialmente requirió el ‘HASH JOIN’, como se muestra en el plan de ejecución:

La siguiente pantalla muestra la memoria ‘SGA’ ocupada. Se  tenían 87.42 MB libres previos a la ejecución del ‘query’. Una vez ejecutado este, la memoria ‘SGA’ disponible disminuyo a 81.37 MB. El ‘query’ ocupo únicamente 6 MB de esta memoria, como lo muestra la vista dinámica ‘v$sgastat’.

Ahora vamos a verificar si realmente se ocupo la memoria ‘PGA’ por medio del ‘HASH JOIN’ del ‘query’. Previo a su ejecución, se habían procesado 20.19 MB de memoria ‘PGA’, como se muestra en la columna ‘bytes processed’ de la vista dinámica ‘V$PGASTAT’

Posterior a la ejecución del query, se tuvo un importante incremento en el consumo de memoria PGA con 828.91 MB !. Esto nos indica que el ‘HASH JOIN’ si ocupo memoria ‘PGA’ y que el valor inicial de 40MB asignados por el parámetro PGA_AGGREGATE_TARGET fue insuficiente. Sin embargo a Oracle no  le  ‘importo’ mucho el valor definido, ya que asigno aproximadamente 800 MB más de los declarados.

Qué efecto tiene en el manejador que el valor asignado a la PGA sea insuficiente?  La consulta fue ejecutada en 3.56 minutos y requirió 235,138 de lecturas a disco.

Vamos a modificar los valores tanto de las memorias ‘SGA’ y ‘PGA’ para verificar si existe una mejora en la ejecución del ‘query’. Se va a incrementar el valor de la memoria ‘SGA’ de 400mb a 1800mb, reiniciando la instancia:

De le misma forma vamos a incrementar la memoria ‘PGA’ (que en este caso es utilizada principalmente por el ‘select count(*) de los 30 millones de registros). Oracle recomienda asignar el 80% de memoria física a la  ‘SGA’ y el 20% restante para la memoria ‘PGA’ en ambientes transaccionales. Para ambientes Datawarehouse se recomienda valores de 70% para la memoria ‘SGA’ y 30 % de memoria para la ‘PGA’. Continuando con el ejercicio, vamos a configurar la memoria ‘PGA’ un poco más alta de lo normal, con 800 mb y 1800 mb asignados a la ‘SGA’.

Una vez incrementada la memoria, se ejecuto nuevamente el ‘query’ con la sentencia ‘select count’ de los 30 millones de registros. La ejecución del ‘query’ observó el siguiente comportamiento (aqui les debo la imagen con el  plan de ejecución :-( ):

1. El tiempo de ejecución disminuyo de 3.56 a 1.50 minutos
2. Las lecturas a disco disminuyeron de 235,138 a 190,220
3. Los 'sorts' en memoria se incrementaron de 4 a 12 lo que ayudo a mejorar el tiempo de ejecución

 Conclusiones

El parámetro 'PGA_AGGREGATE_TARGET' no impone un límite en cuanto a la memoria ´PGA´asignada. Sin embargo, si este se encuentra configurado correctamente, el manejador podrá realizar de forma más eficiente las recuperaciones de disco a memoria. Para asignar un valor correcto a este parámetro, se pueden utilizar como insumo los 'advisors' contenidos en los reportes 'STATSPACK' y 'AWR' y la vista dinámica del sistema 'v$pga_target_advice'.  En la recien liberada versión Oracle 12c se introduce un nuevo parámetro que al parecer va a limitar la memoria 'PGA':  pga_aggregate_limit'.



José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com

5532439143 Ciudad de México