viernes, 30 de marzo de 2012

Monitoreo de indices

El uso de los índices es de gran ayuda para mejorar el rendimiento de la aplicaciones el momento de filtrar los datos, pero, como podemos saber si los índices que tenemos en nuestra base de datos están verdaderamente siendo usados por nuestras aplicaciones?
¿Porque puede pasar que los índices no se usen?
La aplicación sea adquirida y no desarrollada. Esto implica que ya viene con sus índices pre-establecidos.
El funcionamiento o políticas de la empresa cambien lo que también afecta al funcionamiento de nuestras aplicaciones.
La creación de un nuevo índice sea más óptima en costo con el que ya existía. Ya que el CBO tomará el índice con menor costo para su planificación.

Para todos estos casos, esto nos generaría tener índices inútiles que ocupan espacio y hacen más costos las operaciones de insert, delete y update.
Oracle introdujo desde la versión 10g el monitoreo de índices lo cual nos permite determinar que índices se usan o no se usan.

Acción
Sentencia
Activar monitoreo
ALTER INDEX indice_01 MONITORING USAGE;
Desactivar monitoreo
ALTER INDEX indice_01 NOMONITORING USAGE;

EJEMPLO
/*Creamos una tabla*/
CREATE TABLE mi_tablas
AS   SELECT * FROM all_tables;

/*Creamos el indice*/
CREATE INDEX mi_indice_01    ON mi_tablas (table_name);


Veamos la información
/*Vemos la informacion del indice*/  
SELECT iu.name owner, io.name index_name, T.name table_name,
       DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') MONITORING,
       DECODE(bitand(ou.flags, 1), 0, 'NO', NULL, NULL, 'YES') used,
       ou.start_monitoring,
       ou.end_monitoring
FROM    sys.obj$ io,
        sys.obj$ T,
        sys.ind$ i,
        sys.USER$ iu,
        sys.USER$ tu,
        sys.object_usage ou,
        dba_segments s
WHERE  io.owner# = iu.USER#
AND    i.obj# = io.obj#
AND    io.obj# = ou.obj# (+)
AND    T.obj# = i.bo#
AND    T.owner# = Tu.USER#
AND    i.type# not in (4, 8, 9)
AND    iu.name = user --Usuario dueño del objeto
AND    bitand(io.flags, 128) <> 128
AND     S.SEGMENT_NAME=io.name
AND     S.OWNER=iu.name

.
Descripción de las columnas
OWNER
Dueño del índice
INDEX_NAME
Nombre del índice
TABLE_NAME
Tabla asociada al índice
MONITORING
YES/NO si está o no siendo monitoreado
USED
YES/NO si fue usado o no el índice
START_MONITORING
Fecha desde que inició el monitoreo
END_MONITORING
Fecha que finalizó el monitoreo

Vemos que en la columna MONITORING el valor es NO lo cual indica que el índice no está siendo monitoreado.
Ahora activemos el monitoreo del índice.
/*Activar el monitoreo del indice*/
ALTER INDEX mi_indice_01 MONITORING USAGE;


Ejecutamos la consulta para ver información del índice


Aquí vemos que en la columna MONITORING ya indica que el índice está siendo siendo monitoreado, en la columna USED en cambio indica NO lo cual indica que desde que se activo el monitoreo no se ha hecho uso del índice.
Ahora ejecutemos una consulta usando como filtro el campo de la tabla asociada al índice.
/*Selecionamos los datos usando el indice*/
select * from mi_tablas where table_name='DATO';

Veamos la consulta de información del índice.


Ya con esto vemos que cuando ejecutamos una consulta que hace uso del campo asociado al índice el monitoreo registra que el índice fue usado de tal manera que ya podemos determinar si utilizo o no.

El tiempo que monitoreemos los índices dependerá de la frecuencia de uso de los objetos, como por ejemplo si tenemos consultas que solo se hacen uso a fines, en este escenario debemos esperar hasta que todos los procesos o consultas se ejecuten durante el periodo. Con esto garantizaremos que si eliminamos el índice estemos 100% seguro de que no se use por ningún proceso o consulta.

martes, 27 de diciembre de 2011

PCTFREE y PCTUSED


PCTFREE y PCTUSED son parámetros que nos permite especificar comportamientos de almacenamiento de los bloques de datos. Se los configura a nivel de tabla durante la creación o posterior a la creación.
PCFREE: Especifica cuál es la cantidad de espacio que se deberá dejar libre en el bloque para las futuras actualizaciones que se vayan a realizar en los registros previamente almacenados en el bloque. Esto quiere decir que Oracle insertará registros en el bloque hasta que se llegue al % especificado en el PCFREE para las futuras actualizaciones de los registros que se encuentran en el bloque y tamaño puede aumentar, con esto se evita la migración de datos.
PCTUSED: Especifica cuanto espacio disponible debe de haber para realizar nuevas inserciones después de que el bloque llego a su máxima capacidad definida por el PCTFREE. Supongamos que tenemos un PCTFREE de 10% para una tabla y un PCTUSED de 60%, cuando realizamos inserciones se realizaran hasta llegar a 10% libre del bloque, pero que pasa después de que realizamos borrados de filas, este bloque queda disponible para inserciones de nuevos registros? Pues el bloque se pondrá disponible para inserciones hasta que se llegue un valor usado definido por el PCTUSED, es decir cuando llegue a un 60%

En una base de datos es muy importante poder establecer este parámetro al momento de crear una tabla o tener en cuenta poder modificarlo según la evaluación que le demos a la tabla. Que deberíamos considerar para definir el PCTFREE? Según el tipo de transacciones SQL(Insert, delete o update) que se le aplique a la tabla.
  • Si es una tabla log, histórica que solo se aplican transacciones Insert debería tener un PCTFREE de 0%.
  • Si es una tabla que sirve como data warehouse el PCTFREE deberá configurarse con 0%.
  • Si es una tabla transaccional que sufre de inserts, update y delete deberá configurarse un PCTFREE superior a 0%.
En el caso de tablas transaccionales que sufren de inserts, updates y deletes el PCTFREE deberá configurarse con un valor de acuerdo a un previo análisis que dependerá de la cantidad de columnas que se actualizaran posteriores a la inserción y del tipo de dato. Supongamos que tenemos una estructura de "Analisis_Credito" el cual es llenada por un analista de ventas para poder dar crédito a un cliente, en principio esta se llena con información básica que luego es actualizada por un flujo de trabajo que va desde el análisis, verificación y aprobación durante este flujo se actualizaron datos de comentarios, referencias, teléfono y otros como vemos un %0% de esta información es llenada posterior a la creación del registro, por lo que se debería considerar un PCTFREE de 60%.
Cuando se crea la tabla por default el PCTFREE es de 10%, entonces si ya sabemos que tipo de información se va almacenar, podemos establecer al momento o después de la crear la tabla.
EJEMPLO
Supongamos que tenemos que realizar una estructura que almacene todos los días el listado de los objetos de base de datos, esta tabla sólo se ingresaran datos, no se realizarán actualizaciones ni borrados, por lo que el PCTFREE debe de ser 0%. Crearemos una tabla HIS_OBJETOS_PCTFREE_0 (Definiremos 0%) y HIS_OBJETOS (no definimos nada).
SQL> --PCTFREE 0
SQL> create table HIS_OBJETOS_PCTFREE_0 PCTFREE 0
2 as
3 select * from all_objects;
Tabla creada.
SQL>
SQL> --PCTFREE SIN DEFINIR (Defecto 10%)
SQL> create table HIS_OBJETOS
2 as
3 select * from all_objects;
Tabla creada.
SQL>
SQL> select segment_name, bytes/1024/1024 mb, blocks
2 from dba_segments where segment_name like 'HIS_OBJETOS%'
3 and segment_type='TABLE';
-------------------------------------
SEGMENT_NAME MB BLOCKS
-------------------------------------
HIS_OBJETOS_PCTFREE_0 6 768


HIS_OBJETOS 7 896


Observemos que los MB de la tabla HIS_OBJETOS_PCTFREE_0 es de 6 y los bloques usados de 768, mientras que HIS_OBJETOS los MB usados es de 6 y los bloques 896.
Imaginen estos valores con tablas que tienen millones de registros, la cantidad de espacio que optimizaríamos además de mejorar el número de aciertos en consultas ya que entraran mas registros en un bloque con PCTFREE de 0 que uno superior a cero.

lunes, 14 de noviembre de 2011

Particionamiento


El Particionamiento es una técnica utilizada para distribuir el almacenamiento de tablas, índices y vistas materializadas en más de estructura de almacenamiento que se administren de forma independiente. Esta división de almacenamiento es lo que llamamos Particionamiento.

¿Cuál es el beneficio de particionar?
Ya hemos escuchado el dicho "Divide y vencerás", pues eso es lo que lograremos con el Particionamiento, logrando mejorar el rendimiento, disponibilidad y mejor manejabilidad de los datos en la base de datos.

Oracle ofrece esta utilidad desde la versión 8i y desde que se introdujo con cada nueva versión han aplicado mejoras y nuevas técnicas de particionamiento.

En la imagen se muestra la tabla 1 que no esta particionada y la tabla 2 que se muestra particionada. La tabla 1 tiene se guarda en una sola partición mientras que la tabla 2 se almacena en tres particiones que agrupan la información por mes.

Este particionamiento se lo realiza utilizando una clave de particionado (Partitioning Key) que es la que identifica a que parte de la partición se almacenara el dato, en el ejemplo anterior la tabla particionada usa como clave de particionamiento el campo MES para direccionar a que partición se almacenara el dato. Esta llave sir durante la inserción o actualización del registro.

Oracle ofrece tres técnicas básicas de particionamiento según su la necesidad de la organización y es totalmente transparente para las aplicaciones que accedan a los datos y estas son:

Range Partitioning.- Esta técnica usa un rango de valores para determinar a qué partición de datos se almacenara el registro. Este es el método más común de particionar los datos y se usa a menudo como clave de partición los campos fechas.

Método de particionamiento Range
List Partitioning.- Para esta técnica se define una lista de valores para la clave de particionado, la ventaja de este particionamiento es que podemos agrupar y organizar los datos que tengan una relación común, como por ejemplo podemos definir como clave de partición el dato de PAIS.

Método de particionamiento List
Hash Partitioning.- Esta se basa en un algoritmo hash sobre la columna que se definió como clave de partición, esto es una técnica que distribuye a los datos en las particiones de manera equitativa y es usado cuando no se encuentra claros criterios de particionados.

Método de particionamiento Hash
A partir de esta tres técnica básica se definieron dos técnica mas que no son nada más que la combinación de las anteriores y estas son:

Composite Partitioning.- Esta es una técnica que se compone de las tres técnicas básicas anteriormente mencionadas, con esto podemos seleccionar para un primer nivel de partición una técnica y para el otro nivel de partición otra técnica, las combinaciones que podemos realizar son:
  • Range-Range Partitioning
  • Range-Hash Partitioning
  • Range-List Partitioning
  • List-Range Partitioning
  • List-Hash Partitioning
  • List-List Partitioning

Método de particionamiento Composite

En la imagen que se muestra dos ejemplos particionamiento compuesto Range-Hash y Range-List.

Ejemplo del uso de la partición Range
Como describimos hace un momento, esta técnica se basa en que se asigna la partición según el rango de valores en que coincida la clave de partición. Supongamos que tenemos una tabla de ventas con información del 2009 al 2011 y deseamos realizar el particionamiento tipo rango utilizando como llave la fecha, definiendo para cada año una partición.
CREATE TABLE pos_ventas
(
id_venta NUMBER,
fecha DATE,
valor NUMBER
)
PARTITION BY RANGE (fecha)
(
PARTITION pos_ventas_p1 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')) TABLESPACE ts_datos2009,
PARTITION pos_ventas_p2 VALUES LESS THAN (TO_DATE('01/01/2011','DD/MM/YYYY')) TABLESPACE ts_datos2010,
PARTITION pos_ventas_p3 VALUES LESS THAN (TO_DATE('01/01/2012','DD/MM/YYYY')) TABLESPACE ts_datos2011
);


Lo que realizamos es crear particiones por años, para esto seleccionamos como clave de particionamiento al campo fecha y se definió para cada partición un tablespace, con esto logramos una clara distribución de los datos.
Cada una de estas particiones se las puede administrar de manera independiente, ¿a que me refiero? Que podemos trabajarlos como estructuras independientes como si fueran tablas diferentes. Podemos establece definiciones de storage para cada partición, borrar una partición (Solo afectara a los datos contenidos dentro de la partición) y truncar la partición.
Si queremos ver que datos están contenidos en alguna de las particiones de la tabla Oracle nos proporciona la forma de hacerlo usando la clausula PARTITION(Nombre de la partición):
SELECT * FROM pos_ventas PARTITION(pos_ventas_p1);


Ejemplo del uso de la partición List
Este método se caracteriza por que la clave de particionamiento se define por una lista de valores. Tenemos una tabla de ventas, pero queremos realizar la división por el código de almacén de tal manera que tengamos los agrupemos por sector norte, sur y centro.
CREATE TABLE pos_ventas
(
id_venta NUMBER,
id_almacen VARCHAR2(5),
fecha DATE,
valor NUMBER
)
PARTITION BY LIST (id_almacen)
(
PARTITION pos_norte VALUES ('10','20','30') TABLESPACE ts_datos_nor,
PARTITION pos_sur VALUES ('40','50') TABLESPACE ts_datos_sur,
PARTITION pos_centro VALUES ('90','120','130','150') TABLESPACE ts_datos_cen
);


Vemos que las agrupaciones de las particiones dependerán de la lista de datos que se defina en la sección VALUES y que también podemos definir tablespaces o no para cada una de las particiones.


Ejemplo del uso de la partición Hash
Esta es una forma de distribución equitativa de datos definiendo un campo como llave de particionado para que la función Hash nos indique a que partición irá el dato.
Tenemos la tabla detalle de ventas y vamos a usar id_producto como clave de particionamiento para la función hash.
CREATE TABLE pos_ventas_detalles
(
id_venta NUMBER,
id_detalle_venta NUMBER,
id_producto VARCHAR2(20),
cantidad number,
valor number
)
PARTITION BY HASH (id_producto) PARTITIONS 4;


De esta manera definimos que se crearan hasta 4 particiones para la tabla POS_VENTAS_DETALLES y la clave de particionado esta definida por el campo ID_PRODUCTO, de esta manera distribuiremos de manera equitativa los datos. Con esto de manera implícita se crearan las particiones hasta un máximo de 4 particiones, pero también podemos especificar de manera especifica el nombre de las particiones con sus respectivos tablespaces.
CREATE TABLE pos_ventas_detalles
(
id_venta NUMBER,
id_detalle_venta NUMBER,
id_producto VARCHAR2(20),
cantidad number,
valor number
)
PARTITION BY HASH (id_producto)
(
PARTITION pos_vta_det_p1 TABLESPACE ts_datos_01,
PARTITION pos_vta_det_p2 TABLESPACE ts_datos_02
);
    
¿Cuando se necesita particionar una tabla?
Hay varios criterios a considerar para determinar si se necesita particionar una tabla:
  • El tamaño de la tabla supera los 2G de tamaño.
  • La tabla contiene información histórica que tiende a ser modificada, un ejemplo es en la facturación donde se genera mucha información por año y mucha de esta información se mantiene para análisis de los históricos, dicha información podríamos dividirla en dos particiones una histórica y otra actual.
En algunas ocasiones me ha tocado revisar base de datos cuyas técnicas de particionamiento es el uso de programación para traspasar datos de una tabla transaccional a una histórica y esta dependerá de que una persona sea la encargada de ejecutar el proceso, lo cual es un desperdicio ya que con el uso del particionamiento de Oracle esta tarea se la define una vez y es transparente para los usuarios. Solo tenemos que realizar un buen plan de distribución para aplicarla en nuestro particionamiento.


¿Cuál es la mejor técnica de particionamiento?
No hay repuesta especifica para esta pregunta, ya que se tomará la que mejor se adapte a nuestro requerimientos es como cuando jugamos al golf(solo en play station lo he jugado) seleccionamos el palo que se adapte al terreno en el que nos encontramos.

viernes, 4 de noviembre de 2011

¿Cómo reemplazar el uso de Sinónimos?


Una de las desventajas del uso de sinónimos privados es que ocupa mas espacio en el diccionario del SYS y en memoria, ya que para cada usuario de base de datos se deberá crear los sinónimos privados para que este pueda ver los objetos sin necesidad de escribir la ruta absoluta.
¿Ruta absoluta? Con esto quiero indicar que no debemos anteponer el nombre del esquema propietario seguido de un punto y el nombre del objeto, pero para entenderlo mejor veamos un ejemplo.
Vamos a crear una tabla BD_USUARIOS con el usuario SISOWN.
C:\>sqlplus sisown@orcl
Introduzca la contrase±a:
SQL> create table bd_usuarios as select * from dba_users;
Tabla creada.
SQL> select count(*)cantidad from bd_usuarios;
CANTIDAD
----------
23

Creamos al usuario PRUEBA_1 con los permisos de consulta a la tabla BD_USUARIOS.
SQL> create user prueba_1 identified by hola;
Usuario creado.
SQL> grant connect to prueba_1;
Concesi¾n terminada correctamente.
SQL> grant create synonym to prueba_1;
Concesi¾n terminada correctamente.
SQL> grant select on bd_usuarios to prueba_1;
Concesi¾n terminada correctamente.
SQL> connect prueba_1/hola
Conectado.
SQL> select count(*)cantidad from bd_usuarios;
select count(*)cantidad from bd_usuarios
*
ERROR en lÝnea 1:
ORA-00942: la tabla o vista no existe
SQL> select count(*)cantidad from sisown.bd_usuarios;
CANTIDAD
----------
23

Al conectarnos con PRUEBA_1 y realizar la consulta a la tabla BD_USUARIOS nos da error de que la tabla o vista no existe ya que a nivel de usuario PRUEBA_1 no existe la tabla, pero en la segunda consulta lo realizamos anteponiendo el nombre del usuario propietario SISOWN.BD_USUARIOS y no nos da error la consulta.

El error se presenta por que al realizar la consulta lo primero que se hace es ver si la tabla existe a nivel en el esquema PRUEBA_1 y al no encontrarlo se produce el error.
Para evitar ubicar el nombre del esquema antes del objeto en toda la programación tenemos 3 opciones:
  1. Utilizar sinónimos privados
  2. Utilizar sinónimos públicos
  3. Cambiar la variable de sesión CURRENT_SCHEMA
Utilizar sinónimos privados
Los sinónimos privados son objetos que pertenecen a cada usuario de la base y son alias que le damos a los objetos de base de datos y nos evitaría llamar a los objeto por rutas absolutas.

C:\>sqlplus prueba_1@orcl
Introduzca la contrase±a:
SQL> select count(*)cantidad from bd_usuarios;
select count(*)cantidad from bd_usuarios
*
ERROR en lÝnea 1:
ORA-00942: la tabla o vista no existe
SQL> create or replace synonym bd_usuarios for sisown.bd_usuarios;
Sin¾nimo creado.
SQL> select count(*)cantidad from bd_usuarios;
CANTIDAD
----------
23

Vemos como creando el sinónimo privado ya no se necesita de ubicar ruta absoluta (Esquema y objetos) para poder llamar a la tabla BD_USUARIOS. Esto deberá definirse para cada usuario de todos los objetos (Tablas, procedimientos, vistas, secuencias, etc) del usuario propietario 

Utilizar sinónimos públicos
Los sinónimos públicos a diferencias de los privados es que son de dominio público, es decir que solo se necesita definir una sola vez y todos para que sean visto por los demás usuarios, la ventaja de esto es que no debemos definirlo por cada usuario sino una sola vez.

C:\>sqlplus sisown@orcl
Introduzca la contrase±a:
SQL> create or replace public synonym bd_usuarios for sisown.bd_usuarios;
Sin¾nimo creado.
SQL> connect prueba_1/hola
Conectado.
SQL> select count(*)cantidad from bd_usuarios;
CANTIDAD
----------
23

Cambiar la variable de sesión CURRENT_SCHEMA
Esta manera es más eficiente que las dos anteriores, ya que no se necesita de crear objetos adicionales, solo se debe de indicar a la sesión de usuario que esquema de datos se va usar(Es decir en donde primero debe de buscar el objeto). La variable de sesión que modificamos es la CURRENT_SCHEMA la cual indica hacia que esquema se debe de buscar los objetos. Por default el CURRENT_SCHEMA es igual al nombre de la sesión de usuario.

C:\>sqlplus prueba_1@orcl
Introduzca la contrase±a:
SQL> select count(*)cantidad from bd_usuarios;
select count(*)cantidad from bd_usuarios
*
ERROR en lÝnea 1:
ORA-00942: la tabla o vista no existe
SQL> alter session set current_schema=SISOWN;
Sesi¾n modificada.
SQL> select count(*)cantidad from bd_usuarios;
CANTIDAD
----------
23

 Si me preguntaran cual es la mejor método a implementar, pues les daría el siguiente orden: Cambiar CURRENT_SCHEMA, Creación de sinónimos públicos y creación de sinónimos.

¿Por qué es mejor alterar el CURRENT_SCHEMA?
Es mejor porque no se necesita la creación de objetos en el diccionario del SYS, además de que en la memos ocupación de la Library Cache(Ya que por cada usuario no se cargaria metadata del objeto sinonimo) lo que es una ventaja por que dejamos más espacio disponible en la shared pool.

Si tenemos un ambiente de base de datos con 100 usuarios y esos usuario hacen uso de un promedio de 3,000 objetos de un esquema propietario (Tablas, vistas, procedimientos, secuencias, etc), para esto tendriamos que crear 300,000 sinonimos y considerar que cada vez que se cree un usuario el tiempo que nos tomará crear todos los sinonimos para este usuario.

Ademas otro punto en contra de los sinónimos es que cada vez que realices un cambio en los objetos del esquema propietario ya se estructural o de progrmación, los sinonimos relacionados a estos objeto se invalidan por lo que hay que recrearlos para evitar problemas.

¿Se puede hacer de manera automática el cambio del CURRENT_SCHEMA?
Si no deseas cambiar código de programación en tu sistema y deseas que esto se haga de manera automática en la base de datos lo que puede usar es un trigger de base de de datos AFTER LOGON:
CREATE OR REPLACE TRIGGER SISOWN.DB_CONTROL_SESION
AFTER LOGON
ON DATABASE
DECLARE
/***********************************************************
Desarrollado por : Víctor Endara
Objetivo : Cambiar de manera automática la el CURRENT_SCHEMA
************************************************************/
BEGIN
  if user not in ('SYS','SYSTEM','SYSAUX','SYSMAN') then
   execute immediate 'alter session set current_schema=SISOWN';
  end if;
  EXCEPTION
  WHEN OTHERS THEN
   RAISE;
END ;
/

miércoles, 19 de octubre de 2011

TOP SQL


Saludos,

En ocasiones no basta solo con realizar una buena (me refiero a que use buenas practicas SQL o que use los índices correctos) consulta SQL eficiente y cuyo costo no se alto, puede ser que durante una arranque inicial de un sistema , las consultas SQL no se comportan nada anormal ya que el rendimiento y tiempo de respuesta es eficiente, pero que pasará luego de 1, 2, 3 o 12 meses? Nos reportan los usuarios que cada mes el reporte o sistema se pone más lento?

Eso pasa en todo sistema que arranca desde cero o que se implementa nuevos módulos o funcionalidades que requieren crea tablas, agregar columnas o crear nuevas consultas. Es por ello que se necesita siempre de un constante monitoreo de dichas consultas SQL que se están ejecutando o ejecutaron en la base de datos para posterior a esto realizar un afinamiento que mejore el desempeño.

 ¿Cómo podemos monitorear los SQL pesados?
Podemos ayudarnos de herramientas o scripts para obtener esta información:
  • Oracle Enterprise Manager
  • TOAD SGA Trace
  • Oracle SQL Developer
  • Scripts SQL
Oracle Enterprise Manager:
El Enterprise manager proporciona una utilidad para el análisis de los TOP SQL que se han ejecutado y analizar la sentencia.


Aquí nos proporciona una lista de los SQL que se han ejecutado que tienen mayor consumo.
Esta utilidad se encuentra dentro del Enterprise Manager de Oracle Data Base.

 TOAD SGA Trace
Es una utilidad integrada al TOAD que nos permite visualizar los SQL almacenados en el área SQL del Oracle. Aquí se muestra información estadística sobre los SQL ejecutados, pero ¿cómo podemos determinar los SQL pesados? yo siempre me baso en tres columnas para encontrarlos:
  • Hit Rate.- Es un valor porcentual que va desde 0 hasta 100. Este valor representa una relación de lecturas de bloques consistentes (Lecturas/Escrituras en la SGA, para ser mas exacto en la Buffer Cache) vs las lecturas físicas (Lecturas/Escrituras de bloques de datos en disco) que se realizan al ejecutar un SQL. Lo óptimo es que este valor este entre 85% y 100%, ya que eso nos indicaría que la sentencia trabaja más sobre los bloques de datos de la Buffer Cache que sobre los bloques de datos de los datafile almacenados en disco, si el valor retornado es menor a 85 es necesario analizar la sentencia para ver que se puede optimizar.
  • Buffer Gets.- Es la cantidad de bloques de datos recuperados del área Buffer Cache de la SGA para que la sentencia SQL necesitó para poder elaborarse.
  • Disk Reads.- Es la cantidad de bloques de datos recuperados de los datafiles para que la sentencia SQL necesitó para poder elaborarse.

Toad es una herramienta pagada muy buena herramienta, pero puedes usar la versión de prueba que puedes descargar del siguiente link Toad 10
 Oracle SQL Developer
Esta es una herramienta de Oracle para desarrolladores SQL y PL/SQL y puedes descargarla de manera gratuita desde la siguiente link OracleSqlDeveloper
Esta herramienta ofrece una opción de informes relacionados con la base de datos y que incluye informes de rendimientos SQL que los listará por:
  • SQL Principal por CPU.- Por el consumo de CPU que realice la ejecución de esta sentencia.
  • SQL Principal por Ejecuciones.- Por la cantidad de veces que se ha ejecutado.
  • SQL Principal por Esperas.- Por el tiempo que ha demorado en ejecutarse el SQL.
  • SQL Principal por Lecturas de Disco.- Por la cantidad de lecturas de bloques a disco que se realiza.
  • SQL Principal por Obtenciones en Buffer.- Por la cantidad de lecturas de bloques a la Buffer Cache
  • SQL Principal por Obtenciones en Buffer/Procesos.- Por la cantidad de lecturas de bloques a la Buffer Cache con relación al procesamiento




Scripts
La manera más artesanal de verificarlo es por medio de los scripts. A continuación script que les servirá para obtener los SQL con alto consumo.
SQL con Hit Rate inferior al 85% y con mas de 1000 bloques leídos en disco, ordenados de manera ascendente por el Hit Rate
SELECT sql_text, disk_reads, buffer_gets, module,
(buffer_gets - disk_reads) / buffer_gets hit_rate
FROM v$sqlarea
WHERE buffer_gets != 0
AND (buffer_gets - disk_reads) / buffer_gets < .85
AND disk_reads > 1000
ORDER BY 4;


SQL con mas de 1000 bloques leídos en disco, ordenados de manera descendente por el campo Disk_Reads
SELECT sql_text, disk_reads, buffer_gets, module,
(buffer_gets - disk_reads) / buffer_gets hit_rate
FROM v$sqlarea
WHERE buffer_gets != 0
AND disk_reads > 1000
ORDER BY 4;

 

lunes, 17 de octubre de 2011

Ejecución de las Estadísticas


Saludos,

Algo importante en considerar en una base de datos es mantener actualizado las estadísticas de tablas e índices. Y como me diría un amigo ¿Y esto pa que nos sirve? Sirve de mucho al CBO (Optimizador Basado en Costes) al momento de seleccionar el plan de ejecución con menor costo para ejecutar las consultas SQL.

CBO (Cost Based Optimizer)
Es un método interno del motor de base de datos que selecciona de varios planes de ejecución el menos costoso. Antes de ejecutar una sentencia SQL esta pasa por un análisis que genera varios planes de ejecución cuya selección dependerá de que costo. Este método fue introducido desde la versión 7i. A diferencia del método antecesor RBO (Optimizador Basado Reglas) el CBO busca un plan de ejecución de le represente menor costo a la base de datos. Para poder evaluar el costo de una ejecución este método se ayuda de la información de las tablas alimentadas por las estadísticas.

RBO (Rule Based Optimizer)
Este método se basa en realizar un plan de ejecución basado en las reglas de accesos y no estima costo de ejecución por lo que no necesita de las estadísticas. RBO es un método que Oracle va a desaparecer y no recomienda su uso.

¿Cómo ejecutamos las estadísticas?
 Bueno, existen varias formas de ejecutar las estadisticas y a continuación se las indicaré:

ANALISIS POR OBJETO
--Estadísticas por tabla y los índices relacionados a la tabla
ANALYZE TABLE tabla COMPUTE|ESTIMATE|DELETE STATISTICS
--Estadísticas solo por tabla
ANALYZE TABLE tabla COMPUTE|ESTIMATE|DELETE STATISTICS FOR TABLE
--Estadísticas solo de los índices relacionadas a la tabla
ANALYZE TABLE tabla COMPUTE|ESTIMATE|DELETE STATISTICS FOR INDEX
--Estadísticas por índice
ANALYZE INDEX indice COMPUTE|ESTIMATE|DELETE STATISTICS
--Estadísticas por cluster
ANALYZE CLUSTER cluster COMPUTE|ESTIMATE|DELETE STATISTICS


ParámetroDescripciónEjemplo
COMPUTECalcula las estadísticas con todos los registros de la tabla
ANALYZE TABLE tabla COMPUTE STATISTICS;
ESTIMATECalcula las estadísticas con una muestra de filas basadas en cantidad o porcentaje. Es recomendable usarlo cuando la tabla es muy grande y la estadística demora mucha en realizarse.
--Estimación por porcentaje
ANALYZE TABLE tabla ESTIMATE STATISTICS SAMPLE porcentaje_filas PERCENT;
--Estimación por filas
ANALYZE TABLE tabla ESTIMATE STATISTICS SAMPLE cantidad_filas ROWS;
DELETEPermite el borrado de las estadísticas
ANALYZE TABLE tabla DELETE STATISTICS;


ANALISIS POR ESQUEMA
--Estadistica a todas las tablas de un esquema por medio de la función ----UTILITY
DBMS_UTILITY.ANALYZE_SCHEMA(
USUARIO_PROPIETARIO,
METODO_ANALISIS,
FILAS_A_ESTIMAR,
PORCENTAJE_A_ESTIMAR);


ParámetroDescripción
USUARIO_PROPIETARIOEs el usuario propietario de las tablas a realizar el análisis de estadísticas
METODO_ANALISISTiene dos opciones: COMPUTE y ESTIMATE.
Si el parámetro es COMPUTE el análisis es por todos los registros de la tabla y los siguientes dos parámetros no se usan.
Si el parámetro es ESTIMATE se debe de indicar uno de los dos parámetros siguientes, si el análisis se lo realiza tomando una determinada muestra de registros.
FILAS_A_ESTIMARSe usa si el método a usar es ESTIMATE se define la cantidad de registros para tomar como muestra para realizar el análisis.
PORCENTAJE_A_ESTIMARSe usa si el método a usar es ESTIMATE se define el porcentaje de registros para tomar como muestra para realizar el análisis.


ANALISIS A TODA LA BASE
--Estadistica a todas las tablas de la base
DBMS_UTILITY.ANALYZE_DATABSE(
METODO_ANALISIS,
FILAS_A_ESTIMAR,
PORCENTAJE_A_ESTIMAR);


ParámetroDescripción
METODO_ANALISISTiene dos opciones: COMPUTE y ESTIMATE.
Si el parámetro es COMPUTE el análisis es por todos los registros de la tabla y los siguientes dos parámetros no se usan.
Si el parámetro es ESTIMATE se debe de indicar uno de los dos parámetros siguientes, si el análisis se lo realiza tomando una determinada muestra de registros.
FILAS_A_ESTIMARSe usa si el método a usar es ESTIMATE se define la cantidad de registros para tomar como muestra para realizar el análisis.
PORCENTAJE_A_ESTIMARSe usa si el método a usar es ESTIMATE se define el porcentaje de registros para tomar como muestra para realizar el análisis.




Ejemplo
Este ejemplo va ilustrar la forma en que las estadísticas ayudan a mejorar el método CBO para la selección del plan con menor costo.
Creamos la tabla clientes con índices por identificación y tipo_identificacion
CREATE TABLE clientes
(
id_cliente NUMBER,
identificacion VARCHAR2(20),
tipo_identificacion VARCHAR(20),
id_provincia VARCHAR2(5),
nombres VARCHAR2(60),
apellidos VARCHAR2(60),
PRIMARY KEY (id_cliente)
);


--Creamos dos indices uno por identificacion y otra por el id_provincia
CREATE INDEX ind_iden_cliente ON clientes(identificacion);


CREATE INDEX ind_id_pro_cliente ON clientes(id_provincia);


Insertamos 300 registros
Insert Into Clientes
(Id_Cliente, Identificacion, Tipo_Identificacion, Id_Provincia, Nombres, Apellidos )
Select Rownum Id_Cliente, Trim(To_Char(Rownum,'000000000')) Identificacion, Mod(Rownum,2)+1Tipo_Identificacion, Mod(Rownum,4) Id_Provincia, 'Nombre '||Rownum Nombres, 'Apellido '||Rownum Apellidos
From Dual
Connect By Level <= 300;


Commit;


Revisamos el plan de ejecución se la siguiente consulta sin la estadística ejecutada:
Select * From Clientes Where Id_Provincia='1';

Plan de ejecución con el SQLPLUS
Plan de ejecución con el TOAD

Ahora ejecutemos el plan de ejecución pero antes ejecutemos la estadística a la tabla Clientes.
ANALYZE TABLE clientes COMPUTE STATISTICS;

Plan de ejecución con el SQLPLUS
Plan de ejecución con el TOAD
Ahora vemos que el plan de ejecución se va por el indice y no por acceso full a la tabla

¿Cuando ejecutar las Estadisticas?No existe un periodo estándar la realizar la ejecución de las estadísticas, esto dependerá de la transaccionalidad y cambios en los datos que se realicen en la base, puede ser cada mes, quincena, semana o diario, en mi caso tengo programado la ejecución de las estadísticas cada quincena y en horario nocturno para que no afecte a los usuarios.

Es calro que si las estadisticas no son reales o aproximadas, el metodo CBO no estará seleccionando el plan de ejecución correcto.

Conclusión
Hemos encontrado que el CBO (Optimizador Basado en Costes) funciona de manera óptima siempre y cuando tengamos actualizados las estadísticas de tablas e índices para que al momento de elegir el plan de ejecución menos costoso este se base en estadísticas reales.