O relatórios do Automatic Workload Repository (AWR), é com certeza uma das maiores armas que temos para realizar troubleshooting em um ambiente banco de dados Oracle. A análise de AWR, é uma consequência natural do dia a dia de um DBA, eu mesmo (principalmente no início da carreira) já passei incontáveis horas e horas (e mais horas) investigando e tentando entender cada uma das informações fornecidas no relatório.
O gerador de AWR apresentado aqui neste post, foi criado através de uma necessidade que encontrei afim de obter o máximo de informações possíveis contidas nos relatórios. Como consultor, deparo com situações onde a análise de Performance Diagnosticsand Tuning é realizada um dia depois do incidente, assim o AWR pode ser uma grande ajuda nesses casos.
Um grande erro que mascara as informações do AWR, é gerar o relatório com um grande período de dados, como por exemplo gerar um único report com informações contidas entre as 08 da manhã até as 6 da tarde, isso acaba mascarando os dados e muitas vezes escondendo o real problema com informações irrelevantes fazendo você perder muito tempo consultando um relatório e não apresenta nenhum dado util. Assim, nesses casos o recomendado é você gerar vários reports para cada intervalo das snap entre 08 da manhã até as 6 da tarde. É aí que entra o script gerador de AWR, que ao contrário do script fornecido pela Oracle awrrp.sql que gera apenas um único relatório entre dois pontos no tempo, o gerador AWR consegue produzir vários relatório AWR entre o range de snapshot id fornecido, tendo como exemplo o caso do relatório entre as 8 da manhã as 6 da tarde, o script gerador “awr” irá produzir um relatório AWR para as 08 da manhã, um outro relatório para as 9 da manhã, outro para as 10 da manhã e assim por diante … até o snapshot id da 6 horas da tarde.
Apenas 4 parâmetros serão solicitados para a execução do script:
1 parâmetro: O tipo do relatório que você deseja gerar, html ou text.
2 parâmetro: A quantidade de dias que será listado os snapid na tela. Apenas para poder visualizar os snapshotid disponíveis.
3 parâmetro: O snapshot id inicial
4 parâmetro: O snapshot id final
Veja a execução do script gerador_awr.sql:
SQL> @gerador_awr
====> Showing the AWR Settings Inverval/Retention
Minute Minute Days
Snapshot Interval Snapshot Retention Snapshot Retention
----------------- ------------------ ------------------
60 11520 8
====> AWR Report type: [html/text]: text
====> Days of snapshots: 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
====> BEGIN SNAP ID : 2630
====> 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 Redo NoWait %: 100.00
Buffer Hit %: 99.97 In-memorySort %: 100.00
output removido
output removido
...
...
output removido
output removido
.00 0 0 0 0
-----------------------------------------------------------------------
Dynamic Remastering Stats DB/Inst: PREPROD/preprod1 Snaps: 2638-2639
No data exists for thissectionofthe report.
------------------------------------------------------
Endo of Report
AWR beginsnap 2630 to end snap 2631 generated, file: gen_awr_dbtst1_2630_2631_report.txt
AWR beginsnap 2631 to end snap 2632 generated, file: gen_awr_dbtst1_2631_2632_report.txt
AWR beginsnap 2632 to end snap 2633 generated, file: gen_awr_dbtst1_2632_2633_report.txt
AWR beginsnap 2633 to end snap 2634 generated, file: gen_awr_dbtst1_2633_2634_report.txt
AWR beginsnap 2634 to end snap 2635 generated, file: gen_awr_dbtst1_2634_2635_report.txt
AWR beginsnap 2635 to end snap 2636 generated, file: gen_awr_dbtst1_2635_2636_report.txt
AWR beginsnap 2636 to end snap 2637 generated, file: gen_awr_dbtst1_2636_2637_report.txt
AWR beginsnap 2637 to end snap 2638 generated, file: gen_awr_dbtst1_2637_2638_report.txt
AWR beginsnap 2638 to end snap 2639 generated, file: gen_awr_dbtst1_2638_2639_report.txt
SQL>No final é apresentado os 9 relatórios AWR entre o range de snapshot fornecido.
Aqui está todos os reports AWR criados localmente pela execução do 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.txtNotas do script:
– O script foi testado nas versões Oracle 11g/12c.
– O script foi testado em RAC e Single instance.
– É necessário a permissão de leitura e escrita no diretório atual da execução do script
– O script detecta automaticamente se existe um restart de instância entre o range de snapshot id fornecido e avisa sobre o fato.
– Caso você queria executar o script em ambiente Windows, você obterá uma falha na hora de remover os arquivos temporários RUN_gen_awr_report.sql e store_set_saved.sql.
gerador_awr.sql
Code:
-----------------------------------------------------------------------------------------------------------
-- 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:
--
--
-- Usage: SQL> @gerador_awr
--
-- Others:
--
-- Author: Flavio Soares
-- Copyright: (c) Flavio Soares - https://flaviosoares.com - All rights reserved.
-------------------------------------------------------------------------------------------------------
SET FEEDBACK OFF SERVEROUTPUT ON
PROMPT ====> Showing the AWR Settings
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
where dbid = (select dbid from v$database);
PROMPT
PROMPT
ACCEPT _gen_wr_type PROMPT '====> AWR Report Type: [html/text]: ' DEFAULT 'html'
PROMPT
PROMPT
ACCEPT _gen_wr_days PROMPT '====> Specify the number of days of snapshots to choose from : ' 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_startup_time SKIP 1 ON gen_wr_inst_name 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 - &_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 '====> BEGIN SNAP ID : '
PROMPT
ACCEPT _gen_wr_end PROMPT '====> 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(startup_time, 'DD/MM/YYYY HH24:MI')) > 1 THEN 6 ELSE 0 END gen_awr_report_inst_restart
FROM dba_hist_snapshot
WHERE snap_id
BETWEEN TO_NUMBER(&_gen_wr_begin) AND TO_NUMBER(&_gen_wr_end);
SET HEADING OFF
SELECT
CASE WHEN &_gen_count_inst_restart > 1 THEN ' ============ ERROR: 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);
SPOOL RUN_gen_awr_report.sql
SET TERMOUT OFF LINES 5000
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) := '';
--
-- proc for outputting header using '-' delimiter
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
host &_delete RUN_gen_awr_report.sql
UNDEFINE _gen_wr_days _gen_wr_begin _gen_wr_begin _gen_wr_end _gen_count_inst_restart _gen_wr_type _gen_awr_dbidEspero que esse script o ajude muito em seus troubleshooting 🙂