AWR – Criação dinâmica dos relatórios

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.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 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_dbid

Espero que esse script o ajude muito em seus troubleshooting 🙂

Deixe um comentário