Script para criação dinâmica de AWR
dezembro 16, 2014

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 Diagnostics and 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


====> Entering the type of AWR Report: [html/text]: text


====> Entering the number of days of snapshots listed: 1

	DBID INST_ID INSTANCE_NAME   STARTUP_TIME		  SNAP_ID BEGIN_TIME		       END_TIME 		    ERROR_COUNT
------------ ------- --------------- ---------------------------- ------- ---------------------------- ---------------------------- -----------
   665418562	   1 dbtst1	     11/10/2014 19:57		     2626 15/12/2014 00:00	       15/12/2014 01:00 		      0
								     2627 15/12/2014 01:00	       15/12/2014 02:00 		      0
								     2628 15/12/2014 02:00	       15/12/2014 03:00 		      0
								     2629 15/12/2014 03:00	       15/12/2014 04:00 		      0
								     2630 15/12/2014 04:00	       15/12/2014 05:00 		      0
								     2631 15/12/2014 05:00	       15/12/2014 06:00 		      0
								     2632 15/12/2014 06:00	       15/12/2014 07:00 		      0
								     2633 15/12/2014 07:00	       15/12/2014 08:00 		      0
								     2634 15/12/2014 08:00	       15/12/2014 09:00 		      0
								     2635 15/12/2014 09:00	       15/12/2014 10:00 		      0
								     2636 15/12/2014 10:00	       15/12/2014 11:00 		      0
								     2637 15/12/2014 11:00	       15/12/2014 12:00 		      0
								     2638 15/12/2014 12:00	       15/12/2014 13:00 		      0
								     2639 15/12/2014 13:00	       15/12/2014 14:00 		      0
								     2640 15/12/2014 14:00	       15/12/2014 15:00 		      0
								     2641 15/12/2014 15:00	       15/12/2014 16:00 		      0
								     2642 15/12/2014 16:00	       15/12/2014 17:00 		      0
								     2643 15/12/2014 17:00	       15/12/2014 18:00 		      0
								     2644 15/12/2014 18:00	       15/12/2014 19:00 		      0
								     2645 15/12/2014 19:00	       15/12/2014 20:00 		      0
								     2646 15/12/2014 20:00	       15/12/2014 21:00 		      0
								     2647 15/12/2014 21:00	       15/12/2014 22:00 		      0
								     2648 15/12/2014 22:00	       15/12/2014 23:00 		      0
								     2649 15/12/2014 23:00	       16/12/2014 00:00 		      0




====> Entering the BEGIN SNAP ID : 2630

====> Entering the 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	  Sessions Curs/Sess Instances
	    --------- ------------------- -------- --------- ---------
Begin Snap:	 2630 15-Dec-14 05:00:00     2,368	 4.9	     2
  End Snap:	 2631 15-Dec-14 06:00:03     2,368	 4.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
      Redo size (bytes):	     634.1	    95,187.8
  Logical read (blocks):	      30.7	     4,603.3
	  Block changes:	       1.6	       240.5
 Physical read (blocks):	       0.0		 1.3
Physical write (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 blocks received:	       0.2		32.7
   RAC GC blocks served:	       0.1		21.8
	     User calls:	       1.6	       233.1
	   Parses (SQL):	       1.0	       151.2
      Hard parses (SQL):	       0.0		 5.3
     SQL Work Area (MB):	       0.1		16.5
		 Logons:	       0.1		11.0
	 Executes (SQL):	       1.7	       249.7
	      Rollbacks:	       0.0		 0.0
	   Transactions:	       0.0

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	    Buffer Nowait %:  100.00	   Redo NoWait %:  100.00
	    Buffer  Hit   %:   99.97	In-memory Sort %:  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 this section of the report.
			  ------------------------------------------------------

End of Report


AWR begin snap 2630 to end snap 2631 generated, file: gen_awr_dbtst1_2630_2631_report.txt
AWR begin snap 2631 to end snap 2632 generated, file: gen_awr_dbtst1_2631_2632_report.txt
AWR begin snap 2632 to end snap 2633 generated, file: gen_awr_dbtst1_2632_2633_report.txt
AWR begin snap 2633 to end snap 2634 generated, file: gen_awr_dbtst1_2633_2634_report.txt
AWR begin snap 2634 to end snap 2635 generated, file: gen_awr_dbtst1_2634_2635_report.txt
AWR begin snap 2635 to end snap 2636 generated, file: gen_awr_dbtst1_2635_2636_report.txt
AWR begin snap 2636 to end snap 2637 generated, file: gen_awr_dbtst1_2636_2637_report.txt
AWR begin snap 2637 to end snap 2638 generated, file: gen_awr_dbtst1_2637_2638_report.txt
AWR begin snap 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--  1 flaviosoares  staff  113049600 Dec 16 00:10 gen_awr_dbtst1_2630_2631_report.txt
-rw-r--r--  1 flaviosoares  staff  114753536 Dec 16 00:11 gen_awr_dbtst1_2631_2632_report.txt
-rw-r--r--  1 flaviosoares  staff  113672192 Dec 16 00:11 gen_awr_dbtst1_2632_2633_report.txt
-rw-r--r--  1 flaviosoares  staff  113213440 Dec 16 00:11 gen_awr_dbtst1_2633_2634_report.txt
-rw-r--r--  1 flaviosoares  staff  114327552 Dec 16 00:11 gen_awr_dbtst1_2634_2635_report.txt
-rw-r--r--  1 flaviosoares  staff  120291328 Dec 16 00:11 gen_awr_dbtst1_2635_2636_report.txt
-rw-r--r--  1 flaviosoares  staff  125730816 Dec 16 00:11 gen_awr_dbtst1_2636_2637_report.txt
-rw-r--r--  1 flaviosoares  staff  115408896 Dec 16 00:11 gen_awr_dbtst1_2637_2638_report.txt
-rw-r--r--  1 flaviosoares  staff  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.


PARA O DOWNLOAD DO SCRIPT: gerador_awr.sql


Quais quer erros/sugestões/melhorias/bugs e afins por favor me avise, o script ainda está em faze de desenvolvimento …

Espero que esse script o ajude muito em seus troubleshooting :)

Um abraço

Performance Tuning Utilities
janeiro 9, 2013

Já ouviram falar de OSWatcher? LTOM? SQLTXPLAIN e OPDG?

Bom, se vocês nunca ouviram  falar dessas ferramentas chegou a hora de conhece-las.

A nota do metalink 438452.1 mostra essas e diversas outras ferramentas sobre performance tuning.

Bom apetite, aproveitem :)

Instalando Oracle Statspack
outubro 3, 2012

Statspack tem sido utilizado desde a versão do Oracle 8i para análise e monitoramento de performance. Não muito diferente da versão Oracle 11g (mais nova até o momento), ele fornece um relatório rápido de seu ambiente afins de processos de tuning do seu banco de dados Oracle

Para mais informações do Statspack consulte o arquivo no seu ORACLE HOME: $ORACLE_HOME/rdbms/admin/spdoc.txt

É verdade sim, que o AWR fornece um relatório melhor comparado com o Statspack, porém usuário que não possuem licença para utilizar Enterprise Manager, devem continuar utilizando o Statspack.

StatsPack tem sido construido para ser instalado somente com uma conexão SYSDBA. A instalação é simples e trivial, uma vez conectado como SYSDBA no SQLPLUS basta executar o script:

FSOARES@oracle11g> @spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password:
...

Alguns perguntas serão feitas como:

  • Senha do usuário PERSTATS
  • Tablespace Default do usuário PERSTATS
  • Tablespace Temporário do usuário PERFSTATS

Caso queria remover Statspack basta executor o spdrop.sql encontrado no $ORACLE_HOME/rdbms/admin. Qualquer falha na execução do script no momento da criação ou algum cancelamento inadequado, deve-se primeiro remover o StatsPack com o script spdrop.sql depois sim criar com o spcreate.sql.

Na criação do script, um arquivo spckg.list é criado, reveja o arquivo para qualquer possível erro que encontrar.

Rolling Upgrades com Oracle Data Guard 11g
julho 19, 2012

Em Abril deste ano, escrevi um artigo para a Oracle sobre a utilização de Rolling Upgrade com Oracle Data Guard 11g.

A Oracle chama de Rolling Upgrade a possibilidade de você realizar upgrade de versão com o mínimo (quase nada) de down time e nesse caso específico é utilizado o Oracle DataGuard 11g … isso mesmo, utilizando Data Guard. Você pode conferir tudo isso na integra aqui: http://www.oracle.com/technetwork/pt/articles/database-performance/rolling-upgrades-com-data-guard-11g-1576838-ptb.html

Estou publicando a versão em PDF do artigo para quem interessar:

Download Artigo

Abraço a todos !

Exadata e Eu!
dezembro 8, 2011

Olá Pessoal,

Ontem participei do Oracle Open World 2011 Latin America, foi a primeira vez que participei do evento (essa é uma das vantagens de se morar em SP, os eventos são bem mais próximos) e gostei muito.

Fiquei quase que o tempo todo no stand da Discover, o pessoal se divertiu muito lá com games interativos :)

Pude ver de perto o Oracle Exadata e Exalogic e participei de algumas palestras de demonstrações, realmente valeu a pena.

Estimando tempo de rollback
maio 10, 2011

Como estimar tempo de rollback de uma transação?

Em grandes operações em que a sessão é terminada de forma anormal, ou qualquer transação que tenha executado rollback é possível estimar um tempo de termino da operação de recovery transaction.

Primeiro passo é identificar qual sessão está sofrendo rollback.

SQL>  select sid, serial#, status  from v$session where username='SCOTT';

 SID         SERIAL#    STATUS
---------- ---------- --------
137           22        KILLED

Tudo está em identificar a quantidade de blocos usado por essa sessão, relacionando por um tempo.

SQL> SELECT  b.used_urec, b.used_ublk, to_char(sysdate, 'hh24:mi:ss') hora_atual
2  FROM v$session a, v$transaction b
3  WHERE a.saddr = b.ses_addr
4  AND a.sid=137;

USED_UREC   USED_UBLK    HORA_ATUAL
---------- ----------          ----------------
50000        40000        22:08:46

A coluna USED_UREC mostra que existem 50000 registros usados na UNDO e 40000 blocos que ainda faltam processar. O objetivo de todo rollback e ler todos os blocos presentes na UNDO, no caso da sessão 137 o SMON precisa varrer ainda 1000 blocos.

Com a quantidade de bloco que ainda falta para terminar o rollback, temos agora que relacionar isso a um tempo. Após a execução do primeiro select, aguarde um minuto e execute novamente a consulta.

SQL> SELECT  b.used_urec, b.used_ublk, to_char(sysdate, 'hh24:mi:ss') hora_atual
2  FROM v$session a, v$transaction b
3  WHERE a.saddr = b.ses_addr
4  AND a.sid=137;

USED_UREC    USED_UBLK   DATA_ATUAL
---------- ----------          ----------------
30500          30000        22:09:46

Ou seja, depois de um minuto o rollback percorreu 10000 blocos, relacionando a diferença com o tempo, podemos estimar um tempo restante do processo

No meu caso, por exemplo:

30000 (blocos restantes) / 10000 (blocos por minuto) = 3 (Minutos para concluir).

Erro ao gerar AWR
maio 10, 2011

Quando tentar obter um AWR e seguinte mensagem for mostrada:

WARNING (-20023)
ORA-20023: Missing start and end values for time model stat: parse time elapsed

WARNING (-20016)
ORA-20016: Missing value for SGASTAT: free memory

WARNING (-20009)
ORA-20009: Missing System Statistic logons current

Isso pode ser resolvido de duas formas:

Tentar alterar o parâmetro control_management_pack_access para DIAGNOSTIC+TUNING

SQL> show parameter control_management_pack_access

NAME                                     TYPE          VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string

SQL> alter system set control_management_pack_access='DIAGNOSTIC+TUNING';

Sistema alterado.

Espere um ou dois dias e execute novamente o AWR. Se o problema persistir o seu banco pode estar com problema e de acordo com o nota do metalink 1181573.1 será necessário aplicar o Patch 7532789.

Explain Plan
maio 10, 2011

Utilizado quando você precisa saber o CBO (Cost Based Optimizer) de uma instrução SQL. O primeiro passo é criar a tabela PLAN_TABLE.

[oracle@oel510gfs ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@oel510gfs admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Feb 19 17:31:44 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @utlxplan

Table created.

SQL> GRANT ALL ON sys.plan_table TO public;

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

Synonym created.

Com a estrutura criada, que tal agora dar uma olhada nos caminhos percorridos pelo seu SELECT.

SQL> explain plan for
2 select * from dual;

Explained.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.
Tamanho ideal para Shared Pool?
maio 10, 2011

Não sabe? Pergunte ao Oracle …

A shared pool (pool compartilhado) é um elemento obrigatório da SGA e se divide em uma série de estruturas de memória. O DBA não tem controle sobre o tamanho delas, o próprio Oracle que faz essa parte dinamicamente dentro do limite do parâmetro SHARED_POOL_SIZE.

Uma shared pool pequena é muito ruim para o desempenho. E um shared pool excessivamente grande também prejudica o desempenho. Mas qual o tamanho ideal ?

Alguns tentam acertar pela sorte e outros pela tentativa, mais nada melhor do que saber o que o Oracle precisa

sys@ORCL> select shared_pool_size_for_estimate "size",
2 shared_pool_size_factor "factor",
3 estd_lc_time_saved "result"
4 from v$shared_pool_advice;

size         factor    result
---------- ---------- ----------
400          .5        788,794
480          .6        790,444
560          .7        791,191
640          .8        791,589
720          .9        791,973
800         1.0        791,917
880         1.1        792,013
960         1.2        792.079
1.040       1.3        792.106
1.120       1.4        792.122
1.200       1.5        792.139
1.280       1.6        792.156
1.360       1.7        792.171
1.440       1.8        792.181
1.520       1.9        792.187
1.600       2.0        792.199

Esta view mostra a quantidade de tempo de parsing que seria economizado pelo pool compartilhado se tivesse um determinado tamanho.

No exemplo o pool atual tem 800 MB, o que podemos ver claramente que é muito maior que o necessário. Se baixarmos o tamanho dela para 480MB teríamos uma grande economia de memória com uma mínima queda de parsing.