REAL-TIME SQL MONITORING – PARTE I

Real-time SQL Monitor, es un excelente feature disponible desde Oracle 11.2 en adelante, y permite identificar problemas de performance en tiempo de ejecución, permitiendo facilmente diagnosticar sentencias con alto consumo de recursos, long queries, eventos de espera, y queries con paralelismo.

sqlmonitor2

Este feature es el reemplazo de la  antiguamente muy usada vista v$session_longops, la cual servía para monitorear querys de larga ejecución.

Requisitos para usar SQL Monitor:

  • Requiere licencia Diagnostic+Tuning pack, por lo cual el parámetro control_management_pack_access debe estar seteado a Diag+Tuning pack
  • El parámetro statistics_level debe estar seteado al menos en TYPICAL|ALL

¿Todas las sentencias ejecutadas en la Base de datos son Monitoreadas por SQL Monitor?

Como es obvio, por default, no todas las sentencias ejecutadas en la BD son monitoreadas por SQL Monitor, para que una sentencia sea monitoreada debe cumplirse alguno de los siguientes criterios:

  • Sentencias con paralelismo
  • Queries con tiempo de ejecución mayor a 5 segundos de CPU time
  • Queries con tiempo de ejecución mayor a 5 segundos de espera en I/O
  • Aquellas sentencias que explicitamente han sido seteadas con el hint /*+ MONITOR */

¿De donde podemos generar/consultar SQL Monitor report?
– Enterprise Manager
– EM Database Express
– SQL Developer
– Via command line

Vistas en las que esta basada:

En oracle 11g:  En esta version era posible consultar SQL Monitor a traves de las vistas [G]v$sql_monitor [G]v$sql_plan_monitor
En oracle 12c: A partir de esta versión tambien es posible consultar SQL monitor a través de las vistas [G]v$sql_monitor_sesstat, [G]v$sql_monitor_statname, dba_hist_reports(retención de SQL Monitor persistente en disco) y dba_hist_reports_details.

sql_monitor1

Limitaciones y parámetros ocultos (hidden parameters):

Existen limitantes a tener en cuenta si tenemos una BD con mucha carga de trabajo o complejos planes de ejecución.

  • Por default, Oracle limita el numero de sentencias SQL que seran monitoreadas a 20 x CPU_COUNT. Se puede incrementar el valor limite, seteando el parámetro oculto _sqlmon_max_plan pero se debe ser cuidadoso por que esto podría incrementar la cantidad de memoria total usada por SQL Monitor en el Shared Pool, teniendo como resultado que la información de  SQL Monitor sea obsoleta y fuera de memoria mas rápido.
  • SQL Monitor solo monitoreará aquellas sentencias SQL cuyo plan de ejecución tiene menos de 300 lineas, si queremos que SQL Monitor tome en cuenta nuestros planes de ejecución mayor a 300 lineas, podemos cambiar el parametro oculto _sqlmon_max_planlines y así incrementar este límite.Nuevamente se corre el riesgo que la información de  SQL Monitor sea obsoleta y fuera de memoria mas rápido.
  • Se puede incrementar o reducir el valor del umbral de 5 segundos, para que mas sentencias sean consideradas en el monitoreo de SQL Monitor.Esto puede ser cambiado seteando el parámetro oculto _sqlmon_threshold.

Debido a posibles efectos negativos de estos parámetros ocultos es recomendable setearlos a nivel de sesión hasta estar seguros de su impacto.

 

PERFORMANCE TUNING : ACTIVE SESSION HISTORY -ASH -PARTE I

INTRODUCCIÓN:

Los problemas de Performance en una Base de datos Productiva,son quizá,los tipo de problema mas críticos, complejos y personalmente considero que son los mas emocionantes de resolver!!!.

¿Cuantas veces nos ha pasado en nuestro día a día como DBAs, que nos han pedido analizar e indicar a detalle que ocasionó un cuello de botella en nuestras BDs en un punto en el pasado?,probablemente de hace unas horas,días o incluso semanas atrás.

Active Session History(ASH), es una excelente herramienta para analizar problemas de performance que suceden en tiempo real,así como también en un punto en el pasado.

ASH brinda la mayoría de información disponible en trace files pero sin necesidad de generar trace files,esta siempre esta disponible.Usar ASH es extremadamente simple, por que no tienes que generar eventos e ir a la ruta en el servidor a revisar las trazas generadas, basta con consultar la vista v$active_session_history.

Cabe resaltar que Active Session History, es un feature disponible desde Oracle 10g release 1 y su uso requiere licencia Diagnostic Pack.

ash-architecture-and-advanced-usage-rmoug2014-7-6381

Cada segundo, Active Session History sensa la base de datos y recolecta importante información de las sesiones activas(sesiones inactivas no son capturadas) y pobla el ASH Buffer,el cual se puede consultar desde la vista v$active_session_history.

  • SESSION:session_id,session_serial#,user_id(puede ser usado contra la dba_users para obtener el username),session_type,session_state,qc_session_id,qc_instance_id
  • WAIT:event,event_id,event#,seq#,p1,p2,p3
  • SQL: sql_id,sql_child_number,sql_plan_hash_value,sql_opcode
  • OBJECT:current_obj#,current_file#,current_block#
  • APPLICATION: Program,Module,action,client_id,service_hash

ash

La información disponible en la v$active_session_history es poblada en base a la información disponible de las vistas v$session y v$session_wait y permite obtener información importante propia de la sesion, wait, sentencia SQL,objeto,aplicacion:

image002

RETENCIÓN:

La retención de active session history, es dependiente de la retención del AWR.

Oracle escribe la información de memoria a disco, desde v$active_session_history hacia la tabla wrh$active_session history, a la cual podemos consultar a través de la vista dba_hist_active_sess_history. Esta acción la realiza cada hora antes de cada snapshot del AWR o cuando el Ash buffer este al 2/3 de su capacidad en el SGA, cabe resaltar que no toda la información baja a disco, solo 1 de cada 10 entradas.

Calcular Retención en dias de ASH:

 select sysdate - a.sample_time ash,
 sysdate - s.begin_interval_time snap,
 c.RETENTION
 from sys.wrm$_wr_control c,
 (
 select db.dbid,
 min(w.sample_time) sample_time
 from sys.v_$database db,
 sys.Wrh$_active_session_history w
 where w.dbid = db.dbid group by db.dbid
 ) a,
 (
 select db.dbid,
 min(r.begin_interval_time) begin_interval_time
 from sys.v_$database db,
 sys.wrm$_snapshot r
 where r.dbid = db.dbid
 group by db.dbid
 ) s
 where a.dbid = s.dbid
 and c.dbid = a.dbid;

retention_ash

SELECT min(sample_time) min_sample_time, max(sample_time) max_sample_time FROM V$ACTIVE_SESSION_HISTORY;

PARÁMETROS:

_ash_size
_ash_enable
_ash_sampling_interval
_ash_disk_write_enable
_ash_disk_filter_ratio

referencias: