Oracle Database 11g: Script para criação dinâmica de AWR
Por Joel Perez 
& Flávio Soares (OCE),
Postado em Janeiro 2015
Revisado por Marcelo Pivovar - Solution Architect
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
====>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>
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.txt
Notas 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 restate de instância entre o range de snapshot id fornecido e o 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:
--
-- 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
Espero que esse script o ajude muito em seus troubleshooting :)
Joel Perez é um DBA Especialista (Oracle ACE Director, OCM Cloud Admin. & OCM11g ). Com mais de 14 anos de experiência do mundo Oracle Technology, especializado em arquitetura e implementação de soluções como: Cloud, Alta disponibilidade, Disaster/Recovery, Upgrades, replicação e todos as áreas relacionadas com bancos de dados Oracle. Consultor internacional com deveres, conferências e atividades em mais de 50 países e inúmeros clientes em todo o mundo. Palestrante regular nos eventos Oracle em todo o mundo como: OTN LAD, OTN MENA, OTN APAC e muito mais. Joel sempre foi conhecido por ser pioneiro em tecnologia Oracle desde os primeiros dias de sua carreira sendo o primeiro latino-americano premiado como "OTN Expert" no ano de 2003 pela Oracle Corporation, um dos primeiros "ACE Oracle" no Oracle ACE Program no ano de 2004, um dos primeiros OCP Database Cloud Administrator em todo o mundo no ano de 2013 e como um das maiores realizações profissionais em sua carreira, recentemente ele foi homenageado como um dos primeiros "OCM Database Cloud Administrator" do mundo.
Flávio Soares é um Oracle DBA Sênior, Exadata DMA, Troubleshooter e Consultor Oracle, certificado em OCP/OCE RAC. Especialista em Exadata, alta disponibilidade e replicação de dados com soluções Oracle. Flávio disponibiliza frequentes informações para a comunidade Oracle através do seu blog.
Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.