Herramienta para diagnosticar problemas de performance (Oracle Performance Viewer Freeware)

(Ahora podes bajar la ultima versión del utilitario desde http://www.oramdq.com/oracle-performance-viewer/)

Hace ya varios años que trabajo con bases de datos Oracle y en todo ese tiempo fui acumulando scripts que me facilitaron diversas tareas de administración, mantenimiento, monitoreo, etc. Ya que ultimamente me vengo dedicando a temas de performance, me di cuenta que en ese tipo de actividad es importante analizar la mayor cantidad de información en el menor tiempo posible, recordemos que Oracle es una base altamente instrumentada y que versión tras versión se van agregando mas métricas (Oracle Wait Interface). A partir de 10g tenemos el repositorio AWR que almacena historial detallado de la actividad de la base.

El AWR resulta de mucha utilidad como baseline o punto de comparación cuando nos encontramos con una base que esta con problemas de rendimiento, detectados o bien por la activación de alarmas o peor aún, cuando el usuario final persive la demora y realiza el reclamo. Cualquiera que trabaje como DBA de bases productivas, ni hablar si son muy criticas, tendrá que responder rapidamente a su superior evaluando el escenario, diagnosticando y proponiendo o activando cursos de acción en forma inmediata. A veces los problemas no se detectan con facilidad y empezar a correr scripts por separado puede resultar un tanto lento.

Para facilitarme la tarea, programé un pequeño utilitario en C# donde agregué varias de las consultas que utilizo diariamente, sumado a todo el poder gráfico que me permite entre otras cosas analizar la historia, filtrar convenientemente, exportar a excel la grilla, explotar la información con doble click sobre la celda, etc. Abajo copié algunas pantallas para mostrarles como esta pensada la aplicación y obviamente me interesa compartirla en forma gratuita con quien le interesa y asi poder mejorarla.


La pantalla principal es una MDI con 4 paneles, el de arriba a la izquierda tiene una estructura de árbol con todos los reportes disponibles hasta el momento, clasificados según cierto criterio. el panel de abajo a la izquierda tiene un resumen de la base de datos donde esta conectada la app. el panel de arriba a la derecha tiene la grilla con los resultados, y el panel de abajo a la derecha tiene la consulta que se ejecutó para llenar la grilla.



La pantalla 2 muestra los criterios de filtrado para llenar la grilla de acuerdo al tipo de reporte que se este ejecutando:



La pantalla 3 muestra la grilla resultado. En el ejemplo se ejecutó el reporte de historial de DB Time:



La pantalla 4 muestra la salida del reporte de historial de los 5 eventos de espera mas importantes:



La pantalla 5 muestra las sesiones actuales de la base (información actual)



Con doble click sobre una fila de la grilla se abre otro form con detalle de la sesion seleccionada. En el panel donde aparece el texto de la sentencia que esta ejecutando la sesión se puede clickear el boton derecho y ver y seleccionar "Detalle.." para ver el historial de ejecución de dicha sentencia.



La siguiente pantalla muestra todos los forms abiertos puestos en mosaico horizontal (menu Ventanas)



La pantalla 8 es el resultado de la ejecucion del reporte de Tablespaces



Con doble click sobre la fila de la grilla se abre un nuevo form con detalle del tablespace:



La pantalla 10 muestra los posibles filtros que se pueden realizar para analizar el historial de sentencias TOP.



La pantalla 11 muesta las sentencias TOP que cumplen los filtros definidos anteriormente:



Con un click sobre la celda que tiene el SQLID se copia el valor y luego presionando el boton de REFRESCAR se "pastea" el valor copiado para ver historial de la sentencia:



Todas las grillas de resultado se pueden exportar a excel, se pueden filtrar y todas las consultas se puede copiar (botón derecho sobre el panel donde esta el texto de la consulta y seleccionar COPIAR).

Mi idea fue mostrarles algunas pantallas para que vean la funcionalidad que intenté darle a mi aplicación. Obviamente hay varios reportes mas que no estan detallados en el blog, pero invito a quien quiera evaluar mi programita (se llama OraPerfViewer.exe y pesa alrededor de 150kb) que solo me escriba a: rovedop@gmail.com y les enviaré el ejecutable que solo requiere windows y un framework .NET instalado (creo que la mayoria de las windows lo traen instalado por default). Por el momento no es RAC-Aware, pero en el futuro lo será y corre sobre versiones 10g en adelante.

Es una primera versión, y seguramente tenga varios bugs y cosas que mejorar, pero estoy seguro que compartiendo y discutiendo ideas se mejoran las cosas.

Reporte para obtener métricas de SO desde AWR

Como podemos hace para detectar problemas de cpu o i/o sin tener que pedir ayuda al grupo de Sistemas Operativos?, suena complicado, no?. Bueno... a partir de 10g una opción, por lo menos para ir teniendo un primer panorama, es ver si esta pasando algo con la caja donde reside nuestra base de datos consultando en el repositorio AWR. Para exteriorizar estas métricas existe la vista historica: DBA_HIST_OSSTAT, que en 10g contiene lo siguiente:

STAT_NAME
-------------------------------------------
BUSY_TIME
AVG_IDLE_TIME
NUM_CPUS
AVG_BUSY_TIME
OS_CPU_WAIT_TIME
VM_IN_BYTES
AVG_USER_TIME
AVG_SYS_TIME
LOAD
SYS_TIME
RSRC_MGR_CPU_WAIT_TIME
IDLE_TIME
USER_TIME
PHYSICAL_MEMORY_BYTES
IOWAIT_TIME
AVG_IOWAIT_TIME
VM_OUT_BYTES

y en 11g R1 se agregan las siguientes:

TCP_SEND_SIZE_DEFAULT
TCP_RECEIVE_SIZE_DEFAULT
TCP_RECEIVE_SIZE_MAX
NUM_CPU_SOCKETS
TCP_SEND_SIZE_MAX
NUM_CPU_CORES


Abajo, les copio un script que armé para ver algunas métricas importantes. El reporte toma como unico parametro la cantidad de horas hacia atras que quiero analizar.

set line 120
set pagesize 9999
set verify off
accept horas prompt "Ingrese cantidad de horas hacia atras que desea reportar: "

col snap format a20

select unique to_char(snap,'DD-MON-YYYY HH24') snap,
       avg_idle_time-lead(avg_idle_time) over (partition by st order by snap desc) avg_idle_time,
       avg_user_time-lead(avg_user_time) over (partition by st order by snap desc) avg_user_time,
       avg_sys_time-lead(avg_sys_time) over (partition by st order by snap desc) avg_sys_time,
       avg_iowait_time-lead(avg_iowait_time) over (partition by st order by snap desc) avg_iowait_time,
       os_cpu_wait_time-lead(os_cpu_wait_time) over (partition by st order by snap desc) os_cpu_wait_time
from
(select s.end_interval_time snap,
        s.startup_time st,
       max(decode(stat_name,'AVG_IDLE_TIME',value,null)) AVG_IDLE_TIME,
       max(decode(stat_name,'AVG_USER_TIME',value,null)) AVG_USER_TIME,
       max(decode(stat_name,'AVG_SYS_TIME',value,null)) AVG_SYS_TIME,
       max(decode(stat_name,'AVG_IOWAIT_TIME',value,null)) AVG_IOWAIT_TIME,
       max(decode(stat_name,'OS_CPU_WAIT_TIME',value,null)) OS_CPU_WAIT_TIME
from dba_hist_osstat os,
     dba_hist_snapshot s
where s.snap_id = os.snap_id
group by s.end_interval_time,s.startup_time)
where snap > sysdate-&horas/24
order by snap desc
/


SNAP         AVG_IDLE_TIME AVG_USER_TIME AVG_SYS_TIME AVG_IOWAIT_TIME OS_CPU_WAIT_TIME
25-FEB-10 09 211854       108399     40156  99439  2087400
25-FEB-10 08 271995       61131     27296  97923  1199800
25-FEB-10 07 236760       85951     31938  84592  1489500
25-FEB-10 06 180172       130112     50168  90475  2281500
25-FEB-10 05 182995       123247     54623  105254  2274500
25-FEB-10 04 193166       116204     51902  127028  2197000
25-FEB-10 03 195871       119849     45241  130806  2126400
25-FEB-10 02 236610       85916     37782  160617  1574500

Obviamente, para tener un diagnostico preciso de uso de recursos de SO lo ideal es pedir a los grupos encargados de la administración del SO, networking o storage reportes detallados historicos de actividad, pero si queremos tener una primera foto en forma rapida de lo que esta pasando con solo tener acceso al repositorio de la base, podemos usar la consulta de arriba o cualquier variación (se puede agregar columnas para reportar uso de memoria virtual, tráfico de red, etc) de esta para tener una primera impresión.

Monitorear el estado de las estadisticas de los segmentos referenciados en una sesión

Cualquiera que haya trabajado con bases de datos Oracle habrá experimentado alguna vez problemas de performance. La gran mayoria de estas degradaciones en los tiempos de los procesos o aplicaciones se deben a falta o desactualización de las estadísticas de los segmentos referenciados en las sentencias ejecutadas. Para los que cada tanto tenemos la tarea de analizar problemas de rendimiento, nos resulta necesario partir por identificar la sesión o sesiones instanciadas por el proceso o aplicacion con el problema. En el caso de procesos batch los operadores nos suelen suministrar los sid de las sesiones para poder generar una traza de ejecución o monitoreo online. El siguiente script muestra los segmentos con estadísticas STALE o EMPTY dado un sid de sesión:

set line 150
set pagesize 9999
set verify off

col owner format a15
col segment_name format a30
col type format a5

ACCEPT sid PROMPT "Ingrese el SID a analizar: "


select sq.sql_id,
       sq.object_type type,
       st.owner owner,
       st.table_name segment_name,
       st.partition_name partition_name ,
       st.subpartition_name subpartition_name,
       st.last_analyzed last_analyzed 
from v$session se,
     v$sql_plan sq,
     dba_tab_statistics st
where se.sql_id = sq.sql_id
  and se.sid = &sid
  and sq.OBJECT_OWNER = st.owner 
  and sq.OBJECT_NAME = st.table_name 
  and sq.object_type = 'TABLE'
  and sq.object_owner not in ('SYS','SYSTEM')
  and nvl(st.stale_stats,'YES') = 'YES'  
union all
select sq.sql_id,
       sq.object_type type,
       si.owner owner,
       si.table_name segment_name,
       si.partition_name partition_name ,
       si.subpartition_name subpartition_name,
       si.last_analyzed last_analyzed 
from v$session se,
     v$sql_plan sq,
     dba_ind_statistics si
where se.sql_id = sq.sql_id
  and se.sid = &sid
  and sq.OBJECT_OWNER = si.owner 
  and sq.OBJECT_NAME = si.index_name 
  and sq.object_type = 'INDEX'
  and sq.object_owner not in ('SYS','SYSTEM')
  and nvl(si.stale_stats,'YES') = 'YES'  
/

set verify on

Si quisieramos ver todas los segmentos con estadisticas nulas o viejas de todas las tablas referenciadas actualmente podemos usar la siguiente variante mas generica del script anterior:

set line 150
set pagesize 9999

col owner format a15
col segment_name format a30
col type format a5

select sq.sql_id,
       sq.object_type type,
       st.owner owner,
       st.table_name segment_name,
       st.partition_name partition_name ,
       st.subpartition_name subpartition_name,
       st.last_analyzed last_analyzed 
from v$sql_plan sq,
     dba_tab_statistics st
where sq.OBJECT_OWNER = st.owner 
  and sq.OBJECT_NAME = st.table_name 
  and sq.object_type = 'TABLE'
  and sq.object_owner not in ('SYS','SYSTEM')
  and nvl(st.stale_stats,'YES') = 'YES'  
union all
select sq.sql_id,
       sq.object_type type,
       si.owner owner,
       si.table_name segment_name,
       si.partition_name partition_name ,
       si.subpartition_name subpartition_name,
       si.last_analyzed last_analyzed 
from v$sql_plan sq,
     dba_ind_statistics si
where sq.OBJECT_OWNER = si.owner 
  and sq.OBJECT_NAME = si.index_name 
  and sq.object_type = 'INDEX'
  and sq.object_owner not in ('SYS','SYSTEM')
  and nvl(si.stale_stats,'YES') = 'YES'  
order by sql_id
/

Como monitorear el espacio temporal consumido

Es común que DBAs que recien se inician se preocupen cuando en un reporte de estado de tablespaces se muestra al tablespace temporal al 100% de utilización, es decir sin espacio disponible. En ese punto es importante aclarar que la alocación y dealocación de espacio temporal es distinta para los tablespace temporales respecto a los tablespaces de datos y por lo tanto si bien el reporte muestra un tablespace lleno en realidad no implica que no pueda utilizarse. Para obtener el espacio libre real armé la siguiente consulta:

select t2."TempTotal" "TempTotal (Mb)",
       t1."TempUsed" "TempUsed (Mb)",
       t2."TempTotal" - t1."TempUsed" "TempFree (Mb)"
  from (select nvl(round(sum(tu.blocks * tf.block_size) / 1024 / 1024, 2), 0) "TempUsed"
          from v$tempseg_usage tu, dba_tablespaces tf
         where tu.TABLESPACE = tf.tablespace_name) t1,
       (select round(sum(bytes) / 1024 / 1024, 2) "TempTotal"
          from dba_temp_files) t2

Con esta sentencia se puede ir monitoreando el espacio disponible. Puede resultar conveniente su utilización para hacer seguimiento de procesos batch que hayan cancelado por falta de espacio temporal. Es bastante frecuente que un proceso se quede sin espacio cuando las sentencias que lo componen generan un plan que requiere demasiado agrupamiento, agregacion, hashing, producto cartesiano, etc. Si faltaran indices o estadisticas en los segmentos involucrados se podría armar un plan erroneo que requiera mas espacio temporal que el necesario. Si las sentencias estuvieran bien escritas, los objetos con estadisticas actualizadas y con el esquema de indexacion adecuado podria estar subestimado el espacio temporal y se necesite un redimensionamiento.