Oracle Database 11g: Script para la generación dinámica de reportes AWR
Los reportes de Automatic Workload Repository (AWR), son sin duda una de las mayores armas de las que disponemos para realizar “troubleshooting” en un ambiente de base de datos Oracle. El análisis de AWR, es una actividad habitual en la vida cotidiana de un DBA, de hecho, nosotros mismos (sobre todo al principio de nuestra carrera) hemos pasado gran cantidad de horas investigando y tratando de entender cada uno de los datos proporcionados por estos reportes.
El generador de AWR que se presenta en este artículo, fue desarrollado a partir de la necesidad de obtener la máxima información posible contenida en los informes generados. Es común encontrarse en situaciones donde el análisis de rendimiento, diagnóstico y ajuste se realiza el día después del incidente, por lo que AWR resulta ser de gran ayuda para estos casos.
Un típico gran error que enmascara información en el reporte AWR, es generarlo con un gran volumen de datos. Por ejemplo, generar un único reporte con toda la información acumulada entre 8 de la mañana a las 6 de la tarde. Esto termina enmascarando los datos, y a menudo puede ocultar valiosa información que apunte al verdadero problema causando perder además, mucho tiempo analizando el reporte y no obteniendo ningún resultado útil. Por lo tanto, en estos casos la recomendación sería generar varios reportes para cada intervalo de “snapshots” entre las 8 a.m. y 6 p.m., es aquí donde entra en juego el script generador de AWR que a diferencia del script proporcionado por Oracle (awrrp.sql) que apenas genera un único informe entre dos puntos en el tiempo. Nuestro generador puede producir varios reportes de AWR dentro del rango de “snapshot id” proporcionado. Tomando como ejemplo el reporte citado, con datos recolectados entre las 8 de la mañana y las 6 de la tarde, el script generador "awr" producirá un informe AWR por cada hora, esto sería uno para las 8 am, otro para las 9 am, otro para las 10am y así sucesivamente... hasta el snapshot id de las 18:00.
Solo 4 parámetros son requeridos para la ejecución del script:
1er parámetro: Tipo de informe que desea generar, html o texto.
2do parámetro: Cantidad de días para los cuales serán listados los snapshot id en la pantalla. Sólo para ver los snaps disponibles.
3er parámetro: El snapshot id inicial
4to parámetro: El snapshot id final
Veamos la ejecución del script gerador_awr.sql:
SQL> @gerador_awr
====>Showingthe AWR Settings Inverval/Retention
Minute Minute Days
Snapshot Interval Snapshot Retention Snapshot Retention
----------------- ------------------ ------------------
60 11520 8
====>Enteringthetypeof AWR Report: [html/text]: text
====>Enteringthenumberofdaysof snapshots listed: 1
DBID SNAPID BEGIN_TIME END_TIME
------------ ------ ---------------- -----------------
665418562 2626 15/12/2014 00:00 15/12/2014 01:00
2627 15/12/2014 01:00 15/12/2014 02:00
2628 15/12/2014 02:00 15/12/2014 03:00
2629 15/12/2014 03:00 15/12/2014 04:00
2630 15/12/2014 04:00 15/12/2014 05:00
2631 15/12/2014 05:00 15/12/2014 06:00
2632 15/12/2014 06:00 15/12/2014 07:00
2633 15/12/2014 07:00 15/12/2014 08:00
2634 15/12/2014 08:00 15/12/2014 09:00
2635 15/12/2014 09:00 15/12/2014 10:00
2636 15/12/2014 10:00 15/12/2014 11:00
2637 15/12/2014 11:00 15/12/2014 12:00
2638 15/12/2014 12:00 15/12/2014 13:00
2639 15/12/2014 13:00 15/12/2014 14:00
2640 15/12/2014 14:00 15/12/2014 15:00
2641 15/12/2014 15:00 15/12/2014 16:00
2642 15/12/2014 16:00 15/12/2014 17:00
2643 15/12/2014 17:00 15/12/2014 18:00
2644 15/12/2014 18:00 15/12/2014 19:00
2645 15/12/2014 19:00 15/12/2014 20:00
2646 15/12/2014 20:00 15/12/2014 21:00
2647 15/12/2014 21:00 15/12/2014 22:00
2648 15/12/2014 22:00 15/12/2014 23:00
2649 15/12/2014 23:00 16/12/2014 00:00
====>Enteringthe BEGIN SNAP ID : 2630
====>Enteringthe END SNAP ID : 2640
Generation AWR SNAPID ...2630 TO 2640
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
---------- -------------- --------- ---------------- ----------- ----
dbtst 665418562 dbtst1 1 11-Oct-14 19:57 11.2.0.4.0 YES
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- ---------------------- --------- -------- ----------
dm01dbadm01.pass Linux x86 64-bit 48 24 2 251.99
Snap Id Snap Time SessionsCurs/SessInstances
--------- -------------- --------------------------
Begin Snap: 2630 15-Dec-14 05:00:00 2,3684.9 2
EndSnap: 2631 15-Dec-14 06:00:03 2,3684.9 2
Elapsed: 60.04 (mins)
DB Time: 0.17 (mins)
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ ----------- --------------- --------- ---------
DB Time(s): 0.0 0.4 0.00 0.00
DB CPU(s): 0.0 0.4 0.00 0.00
Redosize (bytes): 634.1 95,187.8
Logicalread (blocks): 30.7 4,603.3
Blockchanges: 1.6 240.5
Physicalread (blocks): 0.0 1.3
Physicalwrite (blocks): 0.2 33.8
Read IO requests: 0.0 1.3
Write IO requests: 0.2 26.6
Read IO (MB): 0.0 0.0
Write IO (MB): 0.0 0.3
RAC GC blocksreceived: 0.2 32.7
RAC GC blocksserved: 0.1 21.8
Usercalls: 1.6 233.1
Parses (SQL): 1.0 151.2
Hard parses (SQL): 0.0 5.3
SQL WorkArea (MB): 0.1 16.5
Logons: 0.1 11.0
Executes (SQL): 1.7 249.7
Rollbacks: 0.0 0.0
Transactions: 0.0
InstanceEfficiencyPercentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 RedoNoWait %: 100.00
Buffer Hit %: 99.97 In-memorySort %: 100.00
output removido
output removido
...
...
output removido
output removido
.00 0 0 0 0
-----------------------------------------------------------------------
DynamicRemasteringStats DB/Inst: PREPROD/preprod1 Snaps: 2638-2639
No data exists for thissectionofthe report.
------------------------------------------------------
EndofReport
AWR beginsnap 2630 toendsnap 2631 generated, file: gen_awr_dbtst1_2630_2631_report.txt
AWR beginsnap 2631 toendsnap 2632 generated, file: gen_awr_dbtst1_2631_2632_report.txt
AWR beginsnap 2632 toendsnap 2633 generated, file: gen_awr_dbtst1_2632_2633_report.txt
AWR beginsnap 2633 toendsnap 2634 generated, file: gen_awr_dbtst1_2633_2634_report.txt
AWR beginsnap 2634 toendsnap 2635 generated, file: gen_awr_dbtst1_2634_2635_report.txt
AWR beginsnap 2635 toendsnap 2636 generated, file: gen_awr_dbtst1_2635_2636_report.txt
AWR beginsnap 2636 toendsnap 2637 generated, file: gen_awr_dbtst1_2636_2637_report.txt
AWR beginsnap 2637 toendsnap 2638 generated, file: gen_awr_dbtst1_2637_2638_report.txt
AWR beginsnap 2638 toendsnap 2639 generated, file: gen_awr_dbtst1_2638_2639_report.txt
SQL>
Al final se presentan 9 reportes de AWR entre el rango de snapshot indicado.
Y aquí podemos ver todos la reportes AWR generados localmente por la ejecución del script:
Flavios-MacBook-Pro:~ flaviosoares$ ll gen_awr_dbtst1_263*
-rw-r--r-- 113049600 Dec 16 00:10 gen_awr_dbtst1_2630_2631_report.txt
-rw-r--r-- 114753536 Dec 16 00:11 gen_awr_dbtst1_2631_2632_report.txt
-rw-r--r-- 113672192 Dec 16 00:11 gen_awr_dbtst1_2632_2633_report.txt
-rw-r--r-- 113213440 Dec 16 00:11 gen_awr_dbtst1_2633_2634_report.txt
-rw-r--r-- 114327552 Dec 16 00:11 gen_awr_dbtst1_2634_2635_report.txt
-rw-r--r-- 120291328 Dec 16 00:11 gen_awr_dbtst1_2635_2636_report.txt
-rw-r--r-- 125730816 Dec 16 00:11 gen_awr_dbtst1_2636_2637_report.txt
-rw-r--r-- 115408896 Dec 16 00:11 gen_awr_dbtst1_2637_2638_report.txt
-rw-r--r-- 113082368 Dec 16 00:11 gen_awr_dbtst1_2638_2639_report.txt
Notas del script:
El script fue probado en:
- Versiones de 11g/12c
- Single instance/RAC
- Es necesario permiso de lectura-escritura en el directorio actual de ejecución para el mismo.
- El mismo detecta automáticamente si hubo un reinicio de la instancia entre el rango de snapshots proporcionado y advierte sobre este hecho.
- En el caso de querer ejecutar el script en un ambiente Windows, se obtendrá un error a la hora de la eliminación de los archivos temporales RUN_gen_awr_report.sql y store_set_saved.sql.
Código gerador_awr.sql:
-----------------------------------------------------------------------------------------------------------
-- Header: gerador_awr.sql 03-dez-2014.22:15 $ FSX Scripts - Flavio Soares X Scripts
--
-- Filename: gerador_awr.sql
--
-- Version: v1
--
-- Purpose: Gera AWR dinamicos sequencialmente, no formato escolhido a partir de um range de SNAPID fornecido.
--
-- Modified:
--
-- Notes: O gerador_awr.sql foi testado no Oracle 11g/12c, Single e RAC Instance
-- !!Para a execução do script é necessário permissão de leitura/Escrita no diretório atual da execução!!
--
-- Usage: SQL> @gerador_awr
--
-- Others:
--
-- Author: Flavio Soares
-- Copyright: (c) Flavio Soares - http://flaviosoares.com - All rights reserved.
-------------------------------------------------------------------------------------------------------
SET TERMOUT OFF
STORE SET store_set_saved.sql
SET TERMOUT ON
SET FEEDBACK OFF SERVEROUTPUT ON SQLBL ON LINES 5000 VERIFY OFF PAGES 2000
PROMPT ====> Showing the AWR Settings Inverval/Retention
COLUMN awr_env_interval_minutes HEADING "Minute|Snapshot Interval"
COLUMN awr_env_retention_minutes HEADING "Minute|Snapshot Retention"
COLUMN awr_env_retention_days HEADING "Days|Snapshot Retention"
SELECT
EXTRACT( day FROM snap_interval) *24*60+
EXTRACT( hour FROM snap_interval) *60+
EXTRACT( minute FROM snap_interval ) awr_env_interval_minutes,
EXTRACT( day FROM retention) *24*60+
EXTRACT( hour FROM retention) *60+
EXTRACT( minute FROM retention ) awr_env_retention_minutes,
((
EXTRACT( day FROM retention) *24*60+
EXTRACT( hour FROM retention) *60+
EXTRACT( minute FROM retention )
)/60/24)
awr_env_retention_days
from dba_hist_wr_control;
PROMPT
PROMPT
ACCEPT _gen_wr_type PROMPT '====> Entering the type of AWR Report: [html/text]: ' DEFAULT 'html'
PROMPT
PROMPT
ACCEPT _gen_wr_days PROMPT '====> Entering the number of days of snapshots listed: ' DEFAULT 8
COLUMN gen_wr_startup_time HEADING STARTUP_TIME FORMAT a28
COLUMN gen_wr_begin_time HEADING BEGIN_TIME FORMAT a28
COLUMN gen_wr_end_time HEADING END_TIME FORMAT a28
COLUMN gen_wr_snap_id HEADING SNAP_ID FORMAT 999999
COLUMN gen_wr_dbid HEADING DBID FORMAT 99999999999 NEW_VALUE _gen_awr_dbid
COLUMN gen_wr_inst_id HEADING INST_ID FORMAT 999999 NEW_VALUE _gen_awr_inst_id
COLUMN gen_wr_inst_name HEADING INSTANCE_NAME FORMAT a15 NEW_VALUE _gen_awr_inst_name
BREAK ON gen_wr_dbid SKIP 1 ON gen_wr_inst_id SKIP 1 ON gen_wr_inst_name SKIP 1 ON gen_wr_startup_time SKIP 1
SELECT
s.dbid gen_wr_dbid,
s.instance_number gen_wr_inst_id,
i.instance_name gen_wr_inst_name,
TO_CHAR(s.startup_time, 'DD/MM/YYYY HH24:MI') gen_wr_startup_time,
s.snap_id gen_wr_snap_id,
TO_CHAR(s.begin_interval_time, 'DD/MM/YYYY HH24:MI') gen_wr_begin_time,
TO_CHAR(s.end_interval_time, 'DD/MM/YYYY HH24:MI') gen_wr_end_time,
s.error_count
FROM dba_hist_snapshot s, v$instance i
WHERE end_interval_time >= sysdate - TO_NUMBER(&_gen_wr_days)
AND i.instance_number = s.instance_number
ORDER BY gen_wr_dbid, gen_wr_inst_id, gen_wr_snap_id;
ACCEPT _gen_wr_begin PROMPT '====> Entering the BEGIN SNAP ID : '
PROMPT
ACCEPT _gen_wr_end PROMPT '====> Entering the END SNAP ID : '
SET TERMOUT OFF
COLUMN gen_awr_report_inst_restart NEW_VALUE _gen_count_inst_restart NOPRINT
SELECT
CASE WHEN COUNT(DISTINCT TO_CHAR(s.startup_time, 'DD/MM/YYYY HH24:MI')) > 1
THEN 6 ELSE 0 END gen_awr_report_inst_restart
FROM dba_hist_snapshot s, v$instance i
WHERE i.instance_number = s.instance_number
AND snap_id BETWEEN TO_NUMBER(&_gen_wr_begin) AND TO_NUMBER(&_gen_wr_end);
HEADING OFF TERMOUT OFF
SELECT
CASE WHEN &_gen_count_inst_restart > 1 THEN '
====== PAY ATENTION: There are restart instance between the snapshot id that you choose ======
' || chr(10) || chr(10) END
FROM DUAL;
EXEC DBMS_LOCK.SLEEP(&_gen_count_inst_restart);
SET TERMOUT OFF
SPOOL RUN_gen_awr_report.sql
DECLARE
l_snap_id NUMBER := 0;
l_report_type VARCHAR2(20) := 'AWR_REPORT_TEXT';
l_gen_awr_name VARCHAR2(50) := '';
l_files_generate_awr VARCHAR2(10000) := '';
PROCEDURE out(p_name in VARCHAR2) is
BEGIN
DBMS_OUTPUT.PUT_LINE(p_name);
END;
BEGIN
out('PROMPT Generation AWR SNAPID ...' || &_gen_wr_begin || ' TO ' || &_gen_wr_end);
out('SET FEEDBACK OFF HEADING OFF');
out('exec DBMS_LOCK.SLEEP(0.5);');
l_snap_id := &_gen_wr_begin;
IF ('&_gen_wr_type' = 'html') THEN
l_report_type := 'AWR_REPORT_HTML' ;
END IF;
WHILE (l_snap_id + 1) <> &_gen_wr_end
LOOP
out('SET PAGES 0 LINES 32767 FEEDBACK OFF HEADING OFF');
l_gen_awr_name :=
'gen_awr_' || '&_gen_awr_inst_name' || '_' || l_snap_id || '_' || (l_snap_id + 1) || '_report.' ||
CASE WHEN '&_gen_wr_type' = 'text' THEN 'txt' ELSE 'html' END;
out('SPOOL ' || l_gen_awr_name );
out('SELECT * FROM TABLE
(DBMS_WORKLOAD_REPOSITORY.' || l_report_type || '(' || &_gen_awr_dbid || ',' ||
&_gen_awr_inst_id || ',' || l_snap_id || ',' || (l_snap_id + 1) || '));');
out('SPOOL off');
l_files_generate_awr := l_files_generate_awr || chr(10) || 'PROMPT AWR begin snap ' || l_snap_id ||
' to end snap ' ||
(l_snap_id + 1) || ' generated, file: ' || l_gen_awr_name;
l_snap_id := l_snap_id + 1;
END LOOP;
out('exec DBMS_LOCK.SLEEP(2);');
out('PROMPT');
out('PROMPT');
out(l_files_generate_awr);
END;
/
SET TERMOUT ON
SPOOL OFF
PROMPT
PROMPT
@@RUN_gen_awr_report.sql
PROMPT
UNDEFINE _gen_wr_days _gen_wr_begin _gen_wr_begin _gen_wr_end _gen_count_inst_restart _gen_wr_type _gen_awr_dbid
@@store_set_saved.sql
host rm -f RUN_gen_awr_report.sql
host rm -f store_set_saved.sql
De esta manera concluimos este artículo. Esperando haya sido de interés y utilidad nos despedimos hasta la próxima.
Joel Pérez es un experto DBA (Oracle ACE Director, OCM Cloud Admin. & OCM11g) con más de 14 años de experiencia real en el mundo de tecnología Oracle, especializado en diseño e implementación de soluciones de: Alta disponibilidad, Recuperación contra desastres, Upgrades, Replicación y toda área relacionada con bases de datos Oracle. Consultor Internacional con trabajos, conferencias y actividades relacionadas en más de 50 países alrededor del mundo. Habitual Orador en eventos Oracle alrededor del mundo tales como: OTN LAD, OTN MENA, OTN APAC y más. Joel se ha caracterizado siempre por ser un pionero en materia de tecnología Oracle desde los inicios de su carrera siendo el primer latinoamericano publicado como experto en Oracle.com como "OTN Expert" en el año 2003, uno de los primeros Oracle ACE del respectivo programa en el año 2004, unos de los primeros OCP Cloud en el mercado global en el año 2013 y como uno de los mayores logros de su carrera, recientemente en el 2014 fue honorificado como uno de los primeros "OCM Database Cloud Administrator" del mundo. En la actualidad Joel Pérez esta radicado en el continente de Asia con base en Beijing, China llevando a cabo operaciones profesionales en alianza con una de las mayores compañías de consultoría Oracle en China "Enmotech".
Flavio Soares es un senior DBA Oracle, solucionador de problemas y Consultor Oracle Certificado como OCP/OCE RAC. Especialista en Exadata, alta disponibilidad y replicación de datos con diversas soluciones Oracle. Flavio ofrece información frecuente para la comunidad de Oracle a través de sus artículos a través de OTN Portugués.
Sebastián D. es un Senior DBA con más de 12 años de experiencia en tecnología Oracle, focalizado principalmente en seguridad de base de datos, soluciones de alta disponibilidad, disaster recovery y virtualización. Actualmente desarrolla su actividad como consultor e instructor de manera independiente
Este artículo ha sido revisado por el equipo de productos Oracle y se encuentra en cumplimiento de las normas y prácticas para el uso de los productos Oracle.