O uso de ferramentas de automação como aliados do DBA

Novo artigo para o site TiEspecialistas.com.br

http://www.tiespecialistas.com.br/2013/04/o-uso-de-ferramentas-de-automacao-como-aliados-do-dba/

Anúncios
Categorias:Oracle Tags:, ,

Simplificando o AWR

15 de abril de 2013 1 comentário

sql_advisor

O AWR (Automatic Workload Repository) a primeira vista não é muito simpático. Isso porque ele traz consigo muitas informações e para quem não esta muito habituado a usá-lo, terá dificuldade de entender o extenso número de informações e mais do que isso, encontrar o que realmente precisa.

Além disso, ao analisar o AWR sempre precisamos converter e formatar algumas informações de Bytes para MB/GB, segundos para minutos, etc. Com o objetivo de facilitar esse trabalho, Tyler Muth escreveu um código em Java Script e disponibilizou através de um plugin chamado AWR Formatter.

Se é útil o plugin? Até o Tio Tom (Tom Kyte) recomenda…

http://tkyte.blogspot.com.br/2011/04/two-things.html

Instalação:

Video de demonstração:

http://dl.dropboxusercontent.com/u/4131944/Screencasts/AWR-Format/AWR-Format1/AWR-Format1.html

Site Tyler Muth:

https://tylermuth.wordpress.com/2011/04/20/awr-formatter/

As 5 Características de comportamento de um bom DBA

Abaixo artigo que escrevi para o site TIEspecialistas.com.br

http://www.tiespecialistas.com.br/2013/04/as-5-caracteristicas-de-comportamento-de-um-bom-dba/

SQL Tuning Advisor

O SQL Tuning Advisor  é uma ferramenta de apoio ao DBA ou Desenvolvedor, que ajuda a obter o máximo desempenho das instruções SQL, minimizando o tempo de análise de instruções complexas.

Atuando nas seguintes análises:

sqltuning
Grants necessários:
SQL> create user scott identified by oracle account unlock;
SQL> grant unlimited tablespace to scott;
SQL> grant connect to scott;
SQL> grant resource to scott;
SQL> grant select_catalog_role to scott;
SQL> grant advisor to scott;
SQL> grant administer any sql tuning set to scott;
SQL> grant create any sql profile to scott;
SQL> grant drop any sql profile to scott;
SQL> grant alter any sql profile to scott;

Para utilizar, o parâmetro CONTROL_MANAGEMENT_PACK_ACCESS deve estar habilitado  (default é DIAGNOSTIC+TUNING)

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
control_management_pack_access       string      DIAGNOSTIC+TUNING

Valores Possíveis para CONTROL_MANAGEMENT_PACK_ACCESS = |DIAGNOSTIC+TUNING|DIAGNOSTIC|NONE|

O SQL Tuning Advisor pode ser acessado da seguinte forma:

  • Scripts direto para a package DMS_SQLTUNE
  • Enterprise Manager (EM) na guia “Advisor Central”
  • SQL Developer 3.0

sqltuning1Criação do SQL Tuning Task:

DECLARE
  v_sql_tune varchar2(4000);
BEGIN
 v_sql_tune:= DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_ID => '1qbxbuu5mh08b', SCOPE =>
 DBMS_SQLTUNE.SCOPE_COMPREHENSIVE, TIME_LIMIT => 300, TASK_NAME => 'MyTuningTask', DESCRIPTION => 'MyTuningTask');
END;
/

Execução do SQL Tuning Task:

BEGIN    
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK('MyTuningTask');
END;
/
Resultado das Recomendações do SQL Tuning Task:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('MyTuningTask') RECOMMENDATION FROM DUAL;

Exemplo de saida do SQL Tuning Task:

SQL statement with SQL_ID "3cpunfv6u92gq" was executed 1456 times and had an average elapsed time of 0.61 seconds.
Schema Name: USERSMSEXT
SQL ID     : 3cpunfv6u92gq

SQL Text   : SELECT * FROM USERSMS.TB_COB_SMS WHERE STATUS = :"SYS_B_0"

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99,9%)
  -----------------------------------------
  - Consider accepting the recommended SQL profile.

execute dbms_sqltune.accept_sql_profile(task_name => 'Tuning Task prof1', replace => TRUE);

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 2973976406

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |   328 |  1076   (6)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| TB_COB_SMS |     1 |   328 |  1076   (6)| 00:00:08 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STATUS"=TO_NUMBER(:SYS_B_0))

2- Using SQL Profile
--------------------
Plan hash value: 758381333

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |   328 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_COB_SMS      |     1 |   328 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_TB_COB_SMS_01 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Temporary Tablespace

27 de agosto de 2012 Deixe um comentário

Abaixo algumas queries para gerenciar temporary tablespaces:

 Checar tamanho dos tempfiles no tablespace temporario:

select tablespace_name,
file_name,
round(bytes / (1024 * 1024)) tamanho_MB,
round(maxbytes / (1024 * 1024)) maximo_MB,
status
from dba_temp_files;

Liberar espaço:

alter tablespace temp shrink space;

Temporary em uso:

select a.tablespace_name,
to_char(nvl(a.used,0) / 1024 / 1024, ‘FM999,990.00’) MB_USED,
to_char(a.total / 1024 / 1024, ‘FM999,990.00’) MB_TOTAL,
to_char(nvl(used,0) * 100 / total, ‘FM990.00’) || ‘%’ perc_used
from (select tablespace_name,
block_size,
(select SUM(v$sort_usage.blocks * block_size)
from v$sort_usage
where v$sort_usage.tablespace = dba_tablespaces.tablespace_name) USED,
(select sum(bytes)
from dba_temp_files
where tablespace_name = dba_tablespaces.tablespace_name) TOTAL
from dba_tablespaces
where contents = ‘TEMPORARY’) a;

 Temporary em uso com o tamanho em MB da Query:

select ses.sid,
ses.serial#,
su.username,
ses.terminal,
su.tablespace,
to_char(sum(ceil((su.blocks * dt.block_size)) / 1048576),’FM999,990.00′) MB_USED,
sq.sql_text,
ses.status
from v$sort_usage su, dba_tablespaces dt, v$session ses, v$sqlarea sq
where su.tablespace = dt.tablespace_name
and su.session_addr = ses.saddr
and su.sql_id = sq.sql_id
group by (su.username, ses.sid,ses.terminal,ses.serial#, su.tablespace, sq.sql_text,ses.status);

Procedimento para Recriar Temporary Tablespace:

1 – criar o novo tablespace temporário:

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
‘/home/oracle/oracle/oradata/orasid/temp1.dbf’ SIZE 50M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 4g,
‘/home/oracle/oracle/oradata/orasid/temp2.dbf’ SIZE 50M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 4g
EXTENT MANAGEMENT LOCAL;

2 – alterar default temporary tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

3 – Remover temporary tablespace antigo: (ATENÇÃO: verificar se existe transações em execução, se tiver esperar ou dar Kill).

— verificar se existe transação usando Temporary Tablespace (antiga):

select su.username,
s.osuser,
su.tablespace,
to_char(s.logon_time, ‘dd/mm/yyyy hh:mi:ss’) logon,
s.sid,
s.serial#,
s.machine,
s.terminal,
s.program
from v$sort_usage su, v$session s
where su.session_addr = s.saddr;

— se não retornar, remover:

DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;

1Z0-052 Aprovado!

22 de agosto de 2012 4 comentários

uhuuuu! enfim minha tão sonhada OCA!

Dia 21/ago tomei coragem, (porque já não aguentava mais estudar) e fiz a prova! Fui aprovado e com isso consegui minha primeira certificação – Oracle Database 11g Administrator Certified Associated e OPN Certified Specialist (já que a empresa em que eu trabalho é parceira Oracle).

Para quem pretende fazer, estudei pelo livro de administração I. Aconselho dividir por tópicos/assuntos e só passe para um novo assim que estiver dominando muito bem.

Nos simulados estava fazendo uma média de 85 a 90%. Então só depois que estava bem seguro e dominando bem todo assunto marquei a prova. Afinal não da para correr o risco de gastar R$350 pila a toa rsrs.

Compressão de Dados

Até pouco tempo não conhecia sobre essa feature de Tuning da Oracle, a compressão de dados!  Mesmo estando disponível desde o Oracle 9ir2.

O algoritmo de compressão consiste em eliminar valor duplicados em um bloco do banco de dados, (mesmo em várias colunas).

Entre os benefícios alcançados com a utilização pode-se destacar:

  • Economia de disco em até 3X – Dependendo dos seus dados (quanto mais repetitivo melhor), a compressão irá resultar em grandes economias de espaço em disco.
  • Redução do Tráfego de Rede – Os blocos de dados são compactados / descompactados somente no Oracle, os pacotes da rede serão potencialmente menores.
  • Operações de Full Scan / Range Scan mais rápidos – Como as tabelas / Indices irão ocupar menos blocos de dados, operações de full table scans e index range scans vão recuperar as linhas com menos operação I/O.
  • Melhor aproveitamento da estruturas de memória (buffer_cache). 
  • Transparência para aplicação e ao usuário.

O Oracle 11g lançou um novo recurso chamado Compressão de Tabela OLTP

Suporte para operações DML convencionais (INSERT UPDATE, DELETE).

Os blocos comprimidos contêm uma estrutura chamada de tabela de símbolos que mantém os metadados da compressão. Quando um bloco é comprimido, os valores duplicados são eliminados, primeiro adicionando uma cópia do valor duplicado à tabela de símbolos.

Cada valor duplicado é então substituído por uma referência curta à entrada apropriada na tabela de símbolos.

  • O novo recurso de Compressão de Tabela OLTP faz parte da opção de Compressão Avançada da Oracle e necessita de uma licença além da licença do Enterprise Edition.

Em uma primeira leitura fica um pouco complicado de entender como tudo isso ai funciona né? Então abaixo tem um exemplo para ficar mais fácil.

Processo de Compressão de Bloco – OLTP

  • Um bloco recém-inicializado permanece descomprimido até que os dados no bloco alcancem um limite controlado internamente.
  • Quando uma transação faz com que os dados no bloco alcancem esse limite, todo o conteúdo do bloco é comprimido. Quando mais dados são adicionados ao bloco e o limite é alcançado novamente, todo o bloco é recomprimido para alcançar o mais alto nível de compressão.
  • Esse processo é repetido até que o Oracle determine que o bloco não vá se beneficiar de mais uma compressão.
  • Somente transações que disparam a compressão do bloco irão sofrer o mínimo overhead de compressão. Logo, a maioria das transações OLTP em blocos comprimidos terão exatamente o mesmo desempenho que teriam com blocos não comprimidos.

No exemplo abaixo, a tabela Employees tem 4 registros, que ainda não estão comprimidos

Supondo que um novo insert na tabela Employees irá disparar a compressão

A tabela local de símbolo é criada

Em seguida o bloco é comprimido, substituindo os dados repetidos pelo símbolo correspondente na tabela local de símbolo

Abaixo a comparação da tabela com e sem a compressão:

OLTP Table Compression Syntax:

CREATE TABLE emp (

emp_id NUMBER

, first_name VARCHAR2(128)

, last_name VARCHAR2(128)

) COMPRESS FOR ALL OPERATIONS;

Direct Load Compression Syntax (default):

 

CREATE TABLE emp (

emp_id NUMBER

, first_name VARCHAR2(128)

, last_name VARCHAR2(128)

) COMPRESS [FOR DIRECT_LOAD OPERATIONS];

Abaixo segue um exemplo de uma aplicação real em um ambiente de produção OLTP, como o cliente não tinha licença para compressão OLTP a compressão default foi utilizada. O Tuning na aplicação correspondeu um ganho de 50% de desempenho, razoável não?

Por questões de sigilo da empresa não vou disponibilizar as imagens do aplicativo, mas consiste em fazer a apuração de cálculos de indicadores, onde várias tabelas são lidas, seus valores alterados e disponibilizado um relatório ao usuário.

Execução do teste sem compressão:

  • Tempo: 00:14:14
  • Registros Processados: 5989

Procedimento para compressão:

  • Comprimir tabelas;
  • Validar Indexes e Comprimir;
  • Atualizar estatísticas das tabelas;
  • Esvaziar Cache Oracle;

Execução do teste com compressão:

  • Tempo: 00:06:11
  • Registros Processados: 5989

Lista comparativa (antes e depois) da compressão das tabelas utilizadas. Notem a redução do tamanho da tabela além do número de blocos utilizados:

Abaixo considerações finais e a sintaxe utilizada para comprimir as tabelas existentes e recriar os indexes.

SQL> alter table TABLE_NAME move compress tablespace TABLESPACE_NAME storage (initial 1m);

storage initial – Ao comprimir uma tabela já existente verificar se o storage initial não vai exceder o tamanho total da tabela, senão sua tabela vai estar comprimida mas o tamanho alocado estará o mesmo.

O Comando Move invalidará os Indexes. Para melhorar o desempenho recriar usando parallel.

SQL>  alter index index_name rebuild parallel degree [compress] tablespace tablespace_name;

Atualizar as estatísticas das tabelas após a compressão.

Espero ter ajudado aumentando seu conhecimento!

Fonte:

http://www.oracle.com/technetwork/database/performance/index.html#oow

Oracle E-Business Suite Performance with Real Application Clusters & Advanced Compression