viernes, 6 de marzo de 2009

Script para analizar tendendencia de crecimiento de tablespaces

A continuación les voy a pasar un script que permite monitorear como van llenandose los tablespaces y ademas obtiene una proyección de crecimiento a futuro. A mi me sirve para minimizar el riesgo de que se queden sin espacio los tablespaces (no suelo usar autoextend) y así poder realizar realocaciones o resizing en forma anticipada.


Rem
Rem Tendencia_Crecimiento_x_Tablespace
Rem
Rem NOMBRE
Rem Tendencia_Crecimiento_x_Tablespace.sql
Rem
Rem DESCRIPCION
Rem Reporte para mostrar como fue creciendo un tablespace por hora, dia,
Rem semana y mes. Tambien realiza proyecciones de crecimiento por semana -
Rem mes y muestra STATUS (Aplica para 10+)
Rem
Rem
Rem provedo 04/03/09 -- Creado
Rem
set line 150
col "%Used" format a10
col "%Proy_1s" format a10
col "%Proy_1m" format a10
col tsname format a20
select tsname,
round(tablespace_size*t2.block_size/
1024/1024,2) TSize,
round(tablespace_usedsize*t2.block_size/1024/1024,2) TUsed,
round((tablespace_size-tablespace_usedsize)*t2.block_size/1024/1024,2) TFree,
round(val1*t2.block_size/1024/1024,2) "Dif_1h",
round(val2*t2.block_size/1024/1024,2) "Dif_1d",
round(val3*t2.block_size/1024/1024,2) "Dif_1s",
round(val4*t2.block_size/1024/1024,2) "Dif_1m",
round((tablespace_usedsize/tablespace_size)*100)||'%' "%Used",
round(((tablespace_usedsize+val3)/tablespace_size)*100)||'%' "%Proy_1s",
round(((tablespace_usedsize+val4)/tablespace_size)*100)||'%' "%Proy_1m",
case when ((((tablespace_usedsize+val3)/tablespace_size)*100 < 80) and
(((tablespace_usedsize+val4)/tablespace_size)*100 < 80)) then 'NORMAL'
when ((((tablespace_usedsize+val3)/tablespace_size)*100 between 80 and 90)
or
(((tablespace_usedsize+val4)/tablespace_size)*100 between 80 and 90))
then 'WARNING'
else 'CRITICAL' end STATUS
from
(select distinct tsname,
rtime,
tablespace_size,
tablespace_usedsize,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime rows 1 preceding) val1,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime rows 24 preceding) val2,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime rows 168 preceding) val3,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime rows 720 preceding) val4
from (select t1.tablespace_size, t1.snap_id, t1.rtime,t1.tablespace_id,
t1.tablespace_usedsize-nvl(t3.space,0) tablespace_usedsize
from dba_hist_tbspc_space_usage t1,
dba_hist_tablespace_stat t2,
(select ts_name,sum(space) space
from recyclebin group by ts_name) t3
where t1.tablespace_id = t2.ts#
and t1.snap_id = t2.snap_id
and t2.tsname = t3.ts_name (+)) t1,
dba_hist_tablespace_stat t2
where t1.tablespace_id = t2.ts#
and t1.snap_id = t2.snap_id) t1,
dba_tablespaces t2
where t1.tsname = t2.tablespace_name
and rtime = (select max(rtime) from dba_hist_tbspc_space_usage)
and t2.contents = 'PERMANENT'
order by "Dif_1h" desc,"Dif_1d" desc,"Dif_1s" desc, "Dif_1m" desc


Un ejemplo de la salida resultado de correr el script es la siguiente:


TSNAME TSIZE TUSED TFREE Dif_1h Dif_1d Dif_1s Dif_1m %Used %Proy_1s %Proy_1m STATUS
-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
SYSAUX 580 551 29 .13 -3.31 -17.81 -17.81 95% 92% 92% CRITICAL
TS_TEL 61440 41097.13 20342.88 0 748.63 -2776 -2776 67% 62% 62% NORMAL
DESA_TS 7552 3278.56 4273.44 0 12 14.19 14.19 43% 44% 44% NORMAL
USERS 23205 720.44 22484.56 0 .06 .25 .25 3% 3% 3% NORMAL
SYSTEM 3620 1067.75 2552.25 0 0 2.06 2.06 29% 30% 30% NORMAL
TS_DATA 10240 10220.44 19.56 0 0 0 0 100% 100% 100% CRITICAL
SCH_DATA 900 899.69 .31 0 0 0 0 100% 100% 100% CRITICAL
EXAMPLE 100 68.19 31.81 0 0 0 0 68% 68% 68% NORMAL
ROP 1024 0 1024 0 0 0 0 0% 0% 0% NORMAL
TS_INDEX 4096 506.31 3589.69 0 0 0 0 12% 12% 12% NORMAL
SCH_INDEX 100 6.94 93.06 0 0 0 0 7% 7% 7% NORMAL


Ahora voy a describir las columnas del reporte:

TSNAME : Nombre del Tablespace
TSIZE : Espacio total del Tablespace en Mb (no toma en cuenta autoextend del
tablespace)
TUSED : Espacio utilizado (Mb)
TFREE : Espacio Libre (Mb)
Dif_1h : Diferencia entre el espacio alocado hace 1 hora y el espacio actual
(Mb).
Dif_1d : Diferencia entre el espacio alocado hace 1 dia y el espacio actual
(Mb).
Dif_1s : Diferencia entre el espacio alocado hace 1 semana y el espacio actual
(Mb).
Dif_1m : Diferencia entre el espacio alocado hace 1 mes y el espacio actual
(Mb).
%Used : Porcentaje de Uso actual del tablespace.
%Proy_1s : Porcentaje de Uso Proyectado a 1 semana adelante.
%Proy_1m : Porcentaje de Uso Proyectado a 1 mes adelante.
STATUS : Status de alocación: menor al 80% es normal, entre 80% y 90% es
warning y mayor al 90% es critical.

El script toma en cuenta que el AWR guarda 1 mes de historia y corre cada 1 hora.

35 comentarios:

  1. Que tal Pablo, estaba revisando la salida de tu reporte, y en verdad me parecio sorprendente la informacion que puedes obtener de el, sin embargo cuando intente probarlo en mi pc, al parecer el script no esta completo.. me pregunto si podrias postearlo completo, o en su defecto enviarmelo a mi correo fernando.bernal@oracle.com

    ResponderEliminar
  2. Fernando, ahi lo postee completo, gracias por avisar. Aparentemente se cortó en el proceso de copy/paste. Espero te sea de utilidad el script. Cuentame luego como te funcionó.

    ResponderEliminar
  3. Pablo me parecio muy interesante el script, me funcionó perfectamente, me es de gran ayuda ya que utilizo alto particionamiento en mis bases. espero ver nuevos articulos suyos.

    Javier

    ResponderEliminar
  4. por favor pablo...estoy realizando mi tesis sobre oracle(diagnosticos y alertas sobre forms) y me seria de gran ayuda que me mandaras a mi tambien el script completo..para revisar la data en el tiempo.

    MIL GRACIAS por el apoyo. mi correo es df_britoi@hotmail.com

    ResponderEliminar
  5. Si haces copy/paste te lo copia bien. De todas formas, ahora acorté las lineas para que se vean completas.

    ResponderEliminar
  6. Hola Tengo una duda, sobre tu script que pasa cuando se aumenta el espacio en un tbs, como se maneja eso????......Gracias!!!!

    ResponderEliminar
  7. Pudedes enviarmelo a mi tambien completo? Llevo tiempo intentando encontrar un script parecido y no me lo copia entero. Mi email: flopez@eso.org

    ResponderEliminar
  8. Hola, podes obtenerlo desde mi herramienta para analizar performance. La podes bajar desde: http://www.oramdq.com/oracle-performance-viewer/

    ResponderEliminar
  9. Hola me podrias enviar tu script completo quisiera probarlo para obtener un dimencionamiento de crecimiento para mis tablespace

    mi correo es jorge_210686@hotmail.com

    ResponderEliminar
  10. Muy buen aporte muchas gracias me fue de mucha ayuda.
    Alejandro Mora.

    ResponderEliminar
  11. Estimado

    El script funciona solo para proyectar 1 semana, pero es bastante bueno de todas formas...
    Saludos

    ResponderEliminar
    Respuestas
    1. Buenas !!...funciona perfecto el script....fijate que tu retencion de awr sea de 30 dias al menos (y no el default de 7 dias) : select RETENTION FROM DBA_HIST_WR_CONTROL;
      Saludos
      Pablo Sinisgalli

      Eliminar
  12. Hola
    Tambien me podrias ayudar en enviarme el script completo.
    este es mi correo bonbiox-j@hotmail.com
    Gracias

    Eres un master

    ResponderEliminar
  13. HOla, me surge una duda... ¿cómo podría obtener el crecimiento de un TBS en un intervalo de tiempo concreto? Quiero decir, por ejemplo, comprobar el crecimiento del tbs "x" en el intervalo de tiempo entre

    Hora Inicio: 08/11/2012 15:25:00
    Hora Fin: 08/11/2012 17:24:40

    Muchas gracias!

    ResponderEliminar
  14. Hola me podrias proporcionar el script completo porfavor mi correo es felipealv_16@hotmail.com

    GRACIAS

    ResponderEliminar
  15. Exelente script, me ayudara de una manera extraordinaria, muchas gracias por tu valioso aporte

    ResponderEliminar
  16. Hola podrías pasarme tu script a mi mail jomelendezhanda@gmail.com, además quisiera saber si tienes una guia de como configurar alertas y envios vía mail desde oracle, muchas gracias.

    ResponderEliminar
  17. me puedes enviar a mi tab el scrip porfavor, me seria de gran ayuda. Un saludo Ricardo


    ubunturga@gmail.com

    ResponderEliminar
  18. habrá algo parecido para sql server?

    ResponderEliminar
  19. Hola por favor me puedes pasar el scripts completo quisiera probarlo y creo es urgente .
    Gracias por tu ayuda
    Saludos.

    damly7@hotmail.com

    ResponderEliminar
  20. Podrias por favor compartirme el quey completo.

    Al correo gera_humer@hotmail.com

    ResponderEliminar
  21. Pablo me puedes ayudar con el quey completo a klevereduardo01@gmail.com

    ResponderEliminar
  22. me puedes enviar el script completo por favor. Un saludo Ricardo
    jsandim@gmail.com

    ResponderEliminar
  23. Hola

    Podrias enviarme el script

    fverdejo@gmx.com

    gracias

    ResponderEliminar
  24. Buenas, Si al ejecutar el script, el resultado es no rows selected que significa. Estoy haciendo algo mal o es algo mas.
    Gracias de Antemano

    ResponderEliminar
    Respuestas
    1. Hola. Pudiste solucionar el problema? A mi me pasa lo mismo.

      Eliminar
  25. hola buenas tardes estaba valiando la info me intereza el script indicado saludos

    orgerany@gmail.com

    ResponderEliminar
  26. Hola! muy interesate el script. Pero como habria que modificarlo para que tenga en cuenta el autoextend? Gracias.

    ResponderEliminar
  27. Muchisimas gracias!
    Muy util tu script. :)

    ResponderEliminar
  28. buenos días, como podria agregar el file_name para que nos muestre el directorio donde fue creado el datafile.

    ResponderEliminar
  29. Hola pablo, Buenas, Si al ejecutar el script, el resultado es no rows selected que significa. Estoy haciendo algo mal o es algo mas.
    Gracias de Antemano.
    Me podrias por favor enviar el script completo. Mil gracias. germangonzalezfranco@gmail.com

    ResponderEliminar
  30. Hola buenos dias podrias colaborarme enviando el script completo por favor

    ResponderEliminar
  31. carla_patricia_th@hotmail.com

    ResponderEliminar
  32. Hola Pablo, muy buena tarde .... igualmente podrias compartirme el script completo al correo antonio.martinez.jaml@gmail.com, el que copie del foro me da el mismo porcentaje para la proyeccion de 1s y de 1m ... espero me puedas ayudar ... Saludos.

    ResponderEliminar