Reportes de Métricas de Carga y Tiempos de Respuesta de la Base de Datos (10g+)

A partir de 10g se agregaron vistas dinamicas e información historica para poder entender mejor y en forma mas rapida la actividad de la base de datos. Si bien los reportes de statspack y AWR tienen la información, estos se basan de los snapshots como referencia para analizar un intervalo. Generalmente los intervalos son de 1 hora (automatico y default en 10g+) y muchas veces hay que esperar al próximo snapshot para tener una idea de la actividad actual.

Con las nuevas vistas dinamicas se puede saber casi en tiempo real cual es la actividad de la base consultando las siguientes vistas dinamicas:

V$SYSMETRIC : Metricas mas recientes y menos recientes del ultimo minuto
(una muestra cada 15").
V$SYSMETRIC_HISTORY : Ultima hora de todas las muestras (elige una muestra por
minuto).
V$SYSMETRIC_SUMMARY : Resumen de la actividad de la ultima hora (maximos, minimos,
promedios y desviación standard).


y las vistas historias que persisten parte de la información de las vistas dinamicas
de la ultima hora (el proceso MMON se encarga de copiar parte de la información mas relevante de las vistas V$ a disco) y se externaliza el resultado con las siguientes vistas:

DBA_HIST_SYSMETRIC_HISTORY
DBA_HIST_SYSMETRIC_SUMMARY

Con esta información a disposición se obtiene una idea muy detallada de la actividad y el perfil de carga. Veamos una query que usa la vista sumarizada y retorna entre otros, los mismos datos que encontramos en los reportes statspack/awr en la parte "Load Profile" en la columa tabulada por segundo:

select metric_name,
  case (metric_id)
  when 2016 then round(minval/1024/1024,2)
  when 2058 then round(minval/1024/1024,2)
  else round(minval,2) end Min,
  case (metric_id)
  when 2016 then round(maxval/1024/1024,2)
  when 2058 then round(maxval/1024/1024,2)
  else round(maxval,2) end Max,
  case (metric_id)
  when 2016 then round(average/1024/1024,2)
  when 2058 then round(average/1024/1024,2)
  else round(average,2) end Avg,
  case (metric_id)
  when 2016 then round(standard_deviation/1024/1024,2)
  when 2058 then round(standard_deviation/1024/1024,2)
  else round(standard_deviation,2) end STDDEV,
  case (metric_id)
  when 2016 then 'Mbytes Per Second'
  when 2058 then 'Mbytes Per Second'
  else metric_unit end metric_unit
from v$sysmetric_summary
where metric_id in (2003,2026.2004,2006,2016,2018,2030,
               2044,2046,2058,2071,2075,2081,2123)
order by metric_id

METRIC_NAME                                                             MIN        MAX        AVG     STDDEV METRIC_UNIT
---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------
User Transaction Per Sec                                                  0      28.75       24.4       1.64 Transactions Per Second
Physical Writes Per Sec                                                   0      29.63      21.91       2.25 Writes Per Second
Redo Generated Per Sec                                                    0        .06        .05          0 Mbytes Per Second
Logons Per Sec                                                            0       1.18        .93        .08 Logons Per Second
Logical Reads Per Sec                                                     0    1015.72     592.05      75.19 Reads Per Second
Total Parse Count Per Sec                                                 0      52.75      28.29       4.33 Parses Per Second
Hard Parse Count Per Sec                                                  0       7.24       1.29        .88 Parses Per Second
Network Traffic Volume Per Sec                                            0        .03        .02          0 Mbytes Per Second
DB Block Changes Per Sec                                                  0     339.75     287.39      19.15 Blocks Per Second
CPU Usage Per Sec                                                         0      11.27       9.88        .51 CentiSeconds Per Second
User Rollback UndoRec Applied Per Sec                                     0         .3        .03        .07 Records Per Second
Database Time Per Sec                                                     0      72.48      26.67        8.1 CentiSeconds Per Second

Los datos anteriores se pueden obtener por transacción si se llegara a necesitar.

Ahora voy a mostrar como obtener las metricas basadas en percentiles, con ratios y porcentajes de las ultimas dos muestras del ultimo minuto. La mas reciente es de a lo sumo 15 segundos y la mas antigua es de a lo sumo 60 segundos.

select metric_name,
  round(value,2) value,
  metric_unit
from v$sysmetric
where metric_name like '%\%%' escape '\'
or metric_name like '%Percent%'
or metric_name like '%Ratio%'


METRIC_NAME                                                           VALUE METRIC_UNIT
---------------------------------------------------------------- ---------- ----------------------------------------------------------------
Buffer Cache Hit Ratio                                                95.75 % (LogRead - PhyRead)/LogRead
Memory Sorts Ratio                                                      100 % MemSort/(MemSort + DiskSort)
Redo Allocation Hit Ratio                                               100 % (#Redo - RedoSpaceReq)/#Redo
User Commits Percentage                                                 100 % (UserCommit/TotalUserTxn)
User Rollbacks Percentage                                                 0 % (UserRollback/TotalUserTxn)
Cursor Cache Hit Ratio                                               232.71 % CursorCacheHit/SoftParse
Execute Without Parse Ratio                                           63.74 % (ExecWOParse/TotalExec)
Soft Parse Ratio                                                      96.05 % SoftParses/TotalParses
User Calls Ratio                                                      33.48 % UserCalls/AllCalls
Host CPU Utilization (%)                                               4.63 % Busy/(Idle+Busy)
PX downgraded 1 to 25% Per Sec                                            0 PX Operations Per Second
PX downgraded 25 to 50% Per Sec                                           0 PX Operations Per Second
PX downgraded 50 to 75% Per Sec                                           0 PX Operations Per Second
PX downgraded 75 to 99% Per Sec                                           0 PX Operations Per Second
User Limit %                                                              0 % Sessions/License_Limit
Database Wait Time Ratio                                              42.12 % Wait/DB_Time
Database CPU Time Ratio                                               57.88 % Cpu/DB_Time
Row Cache Hit Ratio                                                   99.75 % Hits/Gets
Row Cache Miss Ratio                                                    .25 % Misses/Gets
Library Cache Hit Ratio                                                98.1 % Hits/Pins
Library Cache Miss Ratio                                                1.9 % Misses/Gets
Shared Pool Free %                                                    91.27 % Free/Total
PGA Cache Hit %                                                       99.89 % Bytes/TotalBytes
Process Limit %                                                        24.7 % Processes/Limit
Session Limit %                                                       16.86 % Sessions/Limit
Streams Pool Usage Percentage                                             0 % Memory allocated / Size of Streams pool
Buffer Cache Hit Ratio                                                96.18 % (LogRead - PhyRead)/LogRead
Memory Sorts Ratio                                                      100 % MemSort/(MemSort + DiskSort)
Execute Without Parse Ratio                                           64.59 % (ExecWOParse/TotalExec)
Soft Parse Ratio                                                      95.72 % SoftParses/TotalParses
Host CPU Utilization (%)                                               4.39 % Busy/(Idle+Busy)
Database CPU Time Ratio                                                15.8 % Cpu/DB_Time
Library Cache Hit Ratio                                               97.64 % Hits/Pins
Shared Pool Free %                                                    91.28 % Free/Total

Otra consulta que suelo usar es mas simple y solo me retorna el tiempo de respuesta general y el tiempo de respuesta por transacción, ambos en segundos, y asi se puede analizar rapidamente y detectar si algo esta pasando con la base. Yo tengo idea de los tiempos razonables para cada base y si veo algo que se dispara me doy cuenta mirando solo esos dos valores. Abajo muestro como es la consulta que utilizo y la salida de la misma.

select end_time,
round(max(decode(metric_id,2106,value/100,null)),4) "SQLRTime",
round(max(decode(metric_id,2109,value/100,null)),4) "RTime/Trx"
from v$sysmetric_history
where metric_id in (2106,2109)
and end_time > sysdate-10/24/60
group by end_time
order by end_time desc

END_TIME                   SQLRTime RTime/Trx
18/11/2010 12:15:34 p.m.    0.0013    0.0172
18/11/2010 12:14:33 p.m.    0.0063    0.0843
18/11/2010 12:13:33 p.m.    0.0087    0.1101
18/11/2010 12:12:33 p.m.    0.0039    0.1147
18/11/2010 12:11:34 p.m.    0.009    0.1214
18/11/2010 12:10:34 p.m.    0.0062    0.1145
18/11/2010 12:09:34 p.m.    0.0079    0.1102
18/11/2010 12:08:34 p.m.    0.0081    0.1167
18/11/2010 12:07:34 p.m.    0.0085    0.1112
18/11/2010 12:06:34 p.m.    0.0078    0.1141

Si quisiera ver la historia mas antigua o necesito armar un reporte historico sumarizado y/o agrupado por hora, dia, semana o mes se puede usar una vista historica (DBA_HIST_xxx).