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.