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;

 

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ámetro	Descripción	Ejemplo
COMPUTE	Calcula las estadísticas con todos los registros de la tabla	ANALYZE TABLE tabla COMPUTE STATISTICS;
ESTIMATE	Calcula 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;
DELETE	Permite 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ámetro	Descripción
USUARIO_PROPIETARIO	Es el usuario propietario de las tablas a realizar el análisis de estadísticas
METODO_ANALISIS	Tiene 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_ESTIMAR	Se 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_ESTIMAR	Se 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ámetro	Descripción
METODO_ANALISIS	Tiene 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_ESTIMAR	Se 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_ESTIMAR	Se 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.

Recuperar espacio inutilizado de los segmentos SHRINK SPACE

En una base de datos transaccional nos encontraremos con tablas que son afectadas con transacciones insert, update y delete. Las transacciones que nos puede dejar bloques sub-utilizados (Fragmentado) son el delete y update.

Para recuperar este espacio antes de la version 10G de Oracle las técnicas utilizadas eran:

• Exportar la tabla, truncar la tabla incluido el storage y finalmente importar los datos.
• Con la ayuda de DML y DDL: CREATE TABLE MI_TABLA_TMP AS SELECT * FROM MI_TABLA, DROP TABLE MI_TABLA y finalmente RENAME TABLE MI_TABLA_TMP TO MI_TABLA

Ya desde la version 10G existe la utilidad SHRINK que nos ayuda mucho con la tarea de recuperar espacio de bloques fragmentados.

 Para explicar como nos va ayudar la utilidad SHRINK primero veamos una pequeña explicación de la estructura de almacenamiento y bloques de datos.

Definición de la Estructura de Almacenamiento
Primero entendamos como esta diseñada la estructura logica de alacenamiento de Oracle.

Estructura Lógica y Física de almacenamiento

Vemos que un tablespace es un conjunto de segmentos, un segmento es un conjunto de extensiones y una extension es un conjunto de bloques( la minima expresión lógica de almecenamiento).

Bloque de Datos
Nuestro estudio se centrará en el comportamiento de los bloques de datos, por lo que debemos entender su estructura, para ello a continuación presentaré de manera breve su estructura:

Estructura del Bloque de Datos Oracle

 Son cinco partes fundamentales que conforman un bloque de datos:

1. Cabecera: En esta sección del bloque se almacena la información genera y de control del bloque como: Tipo de segmento (de tabla, indice, lob, rollback, cluster, etc) e información de la ubicación del bloque, número de sincronización.
2. Directorio de Tabla: Contiene información de la tabla relaciona a la fila almacenada al bloque.
3. Directorio de Fila: Contiene información de las filas almacendas en el bloque (dirección para cada fila de la tabla almacenada en el bloque).
4. Espacio Libre: Esta sección esta destinada para los nuevos datos o actualizaciones de datos que requieran otorgar (Siempre y cuando exista disponivilidad de espacio en el bloque). Para el caso de una actualización esta puede incrementar o descrementar el espacio disponible del bloque.
5. Datos: Esta es la sección en donde se almacena la los datos de las tablas o indices. Los datos de un registro puede encontrarse la necesidad de que ocupen mas de un bloque, esto puede suceder por dos situciones que son: Encadenamiento y Migracion de Filas.

Ahora si con la breve explicación de as esctructuras fisicas y lógicas que Oracle maneja ahora entendamos que es lo que sucede cuando se crea una tabla. Cuando se crea una tabla, a esta se le indica que en que tablespace se almacenará y de manera automatica se asigna a un segmento llamado segmento de tabla y para los indices es igual con un numero inicial de extensiones.

A medida que se insertan datos y se va agotando el espacio asignado a la tabla en el segmento, automaticamente se va asignando al segmentos mas extensiones para dar mas espacio disponible para los datos nuevos.

En la siguiente vista vemos los segmentos y extents asignado a las tabla o indice:

• select * from dba_segments where segment_name='nombre tabla o indice';
• select * from dba_extents where segment_name='nombre tabla o indice';

Donde segment_name es el nombre de la tabla o indice.

Comportamiento de una tabla que subre insert y delete
En el siguiente ejercicio veremos como se comporta el almacenamiento asignado a una tabla realizando varias transacciones insert y delete.

 drop table mi_tabla purge;
----------------------------------------------------------
--Creamos la tabla
----------------------------------------------------------create table mi_tabla
(
    numero number,
    es_par varchar2(1)
)
PCTFREE 0
tablespace ts_datos;

--Realicemos un analisis del espacio utilizado, para esto
--usaremos el procedimiento P$ANALIZA_ESPACIO que dará un
--resumen del almacenamiento
call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 8
KBytes: 64.00
USADO
Bloques: 3
KBytes: 24.00
LIBRE
Bloques: 5
KBytess: 40.00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 0
Registros KBytes: .00
ANALISIS
% SUB UTILIZADO: 100.00

Vemos que % SUB UTILIZADO nos da 100, pero no hay problema, ya que recien creamos tabla.

----------------------------------------------------------
--Ahora realicemos inserción de datos
----------------------------------------------------------
insert into mi_tabla
select rownum, mod(rownum,2)
from dual
connect by level<=1000000;
commit;
Un millon de registros insertados

--Realicemos un analisis del espacio utilizado
call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 1536
KBytes: 12,288.00
USADO
Bloques: 1536
KBytes: 12,288.00
LIBRE
Bloques: 0
KBytess: .00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 1000000
Registros KBytes: 9,765.63
ANALISIS
% SUB UTILIZADO: 20.53

Ahora vemos que el % SUB UTILIZADO es de 20.53, pero no es problema ya que es un valor aceptable considerando que existe entre un 10 y 15 % de espacio del bloque que es usado para controles internos de oracle. Vemos que la cantidad de registros es de un millon y el tamaño en KB de los registro es de 9,765.63

----------------------------------------------------------
--Ahora realicemos borrado de datos
----------------------------------------------------------
delete mi_tabla where es_par=0;
commit;
Quinientos mil registros borrados

--Realicemos un analisis del espacio utilizado
call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 1536
KBytes: 12,288.00
USADO
Bloques: 1536
KBytes: 12,288.00
LIBRE
Bloques: 0
KBytess: .00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 500000
Registros KBytes: 4,882.81
ANALISIS
% SUB UTILIZADO: 60.26

Nos damos cuenta que lo único que vario es la cantidad de registros y el el % SUB UTILIZADO de 60.26 (Alta fragamentación)

Ahora si ponemos en práctica el uso del SHRINK, pero tenemos dos formas de utilizarlo:

SHRINK SPACE COMPACT

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

--Previo a esto habilitamos el movimiento de filas en la tabla

--y realizamos el shrink space compact

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

alter table mi_tabla enable row movement;
alter table mi_tabla shrink space compact;

--Realicemos un analisis del espacio utilizado

call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------

ASIGNADO
Bloques: 1536
KBytes: 12,288.00
USADO
Bloques: 1536
KBytes: 12,288.00
LIBRE
Bloques: 0
KBytess: .00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 500000
Registros KBytes: 4,882.81
ANALISIS
% SUB UTILIZADO: 60.26

SHRINK SPACE

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

--Previo a esto habilitamos el movimiento de filas en la tabla

--y realizamos el shrink space

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

alter table mi_tabla enable row movement;
alter table mi_tabla shrink space;

--Realicemos un analisis del espacio utilizado

call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 768
KBytes: 6,144.00
USADO
Bloques: 764
KBytes: 6,112.00
LIBRE
Bloques: 4
KBytess: 32.00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 500000
Registros KBytes: 4,882.81
ANALISIS
% SUB UTILIZADO: 20.11

• SHRINK SPACE COMPACT Se compactan los bloques, dejando lleno los que estan a medio uso y dejando libre los demas bloques pero siguen asignados al segmento tabla o indice.

• SHRINK SPACE Se compactan los bloques, dejando lleno los que estan a medio uso y dejando libre los demas bloques y se libera la asignación al segmento de las extensiones libres.

Al realizar el SHRINK SPACE notamos que los valores de ASIGNADO, USADO y % SUB UTILIZADO disminuyeron, esto ya que se compactaron y liberaron del segmento los bloques libres.

Otras opciones del SHRINK
ALTER INDEX MI_INDICE SHRINK SPACE Tambien podeis aplicar el SHRINK en los indices.

ALTER TABLE MI_TABLA SHIRINK SPACE CASCADE. Realizas el SHRINK a todos los indices relacionados a las tablas.

Restricciones:
No puedes usarlo en tablas clusterizadas o que contengan columnas LONG.
No funciona en indices basados en función o indices bimap.
No puedes usar esta clausula para tabla comprimidas.

Consideraciones.
Tener cuidado al realizar el ALTER TABLE ENABLE ROW MOVEMENT ya que esto afecta a los triggers asociados a las tablas que hacen referencia a :OLD ya que las filas estan en movimiento.