Monitoreo de Indexes

Esta es una tarea muy importante ya que esto puede generar muchos consumos de CPU y de I/O. Con esto en mente tiene sentido poder identificar y eliminar cuales índices no son usados y así evitar un consumo de recursos inútiles.

Por lo que Oracle nos facilita esta tarea, colocando un parámetro en los índices para que este se encargue de decirnos si los índices se usan o no. Cabe destacar que esta tarea debe hacer en un periodo de tiempo significativo. Para poder tener una evolución real.

La sentencia que se utilizan para activar o desactivar el monitoreo son las siguientes:

ALTER INDEX <> MONITORING USAGE;
ALTER INDEX <> NOMONITORING USAGE;

Para hacer una activación masiva del monitoreo pueden usar el siguiente script:

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL monitoring.sql

SELECT 'ALTER INDEX "' i.owner '"."' i.index_name '" MONITORING USAGE;'
FROM dba_indexes i
WHERE owner = UPPER('&1')
AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

SPOOL OFF

SET PAGESIZE 18
SET FEEDBACK ON

@ monitoring.sql

Para hacer una desactivación masiva del monitoreo pueden usar el siguiente script:

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL temp.sql

SELECT 'ALTER INDEX "' i.owner '"."' i.index_name '" NOMONITORING USAGE;'
FROM dba_indexes i
WHERE owner = UPPER('&1')
AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

SPOOL OFF

SET PAGESIZE 18
SET FEEDBACK ON

@temp.sql

Para consulta de los resultados del monitoreo puede usar el siguiente Script

SET VERIFY OFF
SET LINESIZE 200

SELECT table_name,
index_name,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));

Porque no fucionan los indices.....

A veces cuando hacemos Query's, utilizamos algunas funciones dentro de los Where (NVL, DECODE etc). Esto provoca que algunas consulta se tornen muy lentas aqui les tengo un ejemplo: **********************************************************************

SELECT COUNT (1) cuotas,
SUM (a.mon_pagado_principal) principal,
SUM (a.mon_pagado_interes) intereses,
SUM (a.mon_pagado_intanticipado) int_anticipado,
SUM (a.mon_pagado_intvencido) int_vencido,
SUM (a.mon_pagado_moratorios) int_moratorios,
SUM (a.mon_polizas) polizas, SUM (a.mon_fondo_mutual) fondo_mutual,
SUM (a.mon_otros) otros, SUM (a.mon_efectivo) total
FROM cr_histrec a
WHERE a.cod_compania = :b1
AND a.fec_recibo > :b2
AND a.fec_recibo <= getdate AND a.cod_estado IS NULL

AND a.num_operacion =:a

AND a.ind_recibo = NVL (:b10, a.ind_recibo) <==== Se utiliza la funcion NVL

AND a.ind_recibo != NVL (:b11, a.ind_recibo) <==== Se utiliza la funcion NVL ********************************************************************* Haciendo que el explain plain tenga los siguientes valores Cost 8 CPU Cost 60,100 IO Cost 8 Cardinality 1

Al usar este tipo de funciones hacemos que el motor de la base de datos no utilice los indices adecuados incrementando los costos de procesamiento.

Debido a esto me dedique a investigar como resolver este problema y encontre una posible solucion que la cual es una vez utilizada la funcion NVL, utilizar las funciones To_number o to_char para comprar contra la misma clase de datos.

**********************************************************************
SELECT COUNT (1) cuotas,
SUM (a.mon_pagado_principal) principal,
SUM (a.mon_pagado_interes) intereses,
SUM (a.mon_pagado_intanticipado) int_anticipado,
SUM (a.mon_pagado_intvencido) int_vencido,
SUM (a.mon_pagado_moratorios) int_moratorios,
SUM (a.mon_polizas) polizas, SUM (a.mon_fondo_mutual) fondo_mutual,
SUM (a.mon_otros) otros, SUM (a.mon_efectivo) total
FROM cr_histrec a
WHERE a.cod_compania = :b1
AND a.fec_recibo > :b2
AND a.fec_recibo <= getdate AND a.cod_estado IS NULL AND a.num_operacion =:a AND a.ind_recibo = to_number(NVL (:b10, a.ind_recibo)) <==== Se utiliza la funcion TO_NUMBER AND a.ind_recibo != to_number(NVL (:b11, a.ind_recibo)) <==== Se utiliza la funcion TO_NUMBER

**********************************************************************

Obteniendo una mejora en el Explain Plain con los siguientes valores:

Cost
4
CPU Cost
30,077
IO Cost
4
Cardinality
1