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 :)

Segment Advisor Script v1.0
dezembro 12, 2012

Só nós DBA’s sabemos as inúmeras funções, sintaxes e comandos que precisamos decorar no nosso dia a dia e isso não é uma tarefa fácil, além do que, as soluções sempre precisam ser rapidamente propostas. Por isso que gosto de facilitar as coisas sempre que posso, assim como esse script, que além de simples ele realiza é uma formarápida os passos que nem sempre são lembrados, isso evita aquele tempão gasto procurando na documentação a sintaxe exata de tal procedimento. Qual DBA que nunca passou por isso que atire a primeira pedra!

O objetivo principal do script é facilitar a execução do Oracle Segment Advisor, de uma forma bem simples, apenas três argumentos e nada mais e o melhor de tudo: não é preciso criar qualquer tipo de objeto no banco de dados, todo trabalho é executado através de um bloco anônimo de PLSQL e o único requisito aqui é o bom e velho SQL*Plus.

Você pode conferir a primeira versão (sim, próximas poderão surgir) você pode fazer aqui.

Como usar Segment Advisor Script?

SYNTAX: { @run_sa “Owner Segment” “Segment Name” “Segment Type” }

Ele não precisa nada mais além do que três argumento:

1. “Owner Segment” :  O primeiro representa o nome do owner do segmento.

2. “Segment Name”:  O segundo representa o nome do segmento.

3. “Segment Type”:  O terceiro representa o tipo do segmento. Que pode ser um TABLE, INDEX etc …

Usando o Segment Advisor Script

Para explicar melhor vamos a um teste prático.

Primeiro de tudo, precisamos de uma tabela grande favorável para o teste que vamos fazer, para isso criei a tabela chamada TBIG dentro do schema FSOARES.

FSOARES@dbtst> create table tbig as select * from dba_source;

Table created.

FSOARES@dbtst> insert into tbig (select * from tbig);

633054 rows created.

FSOARES@dbtst> /

1266108 rows created.

FSOARES@dbtst> /

2532216 rows created.
...
FSOARES@dbtst> commit;
FSOARES@dbtst> @size tbig

SEGMENT_NAME                    SEGMENT_TYPE        SIZE_MB
------------------------------ ------------------ ---------------
TBIG                             TABLE              5,244.00

Pronto, temos agora uma tabela de 5G que está perfeita para o nosso teste. Vamos ver quantos registros temos:

FSOARES@dbtst> set timing on
FSOARES@dbtst> select count(*) from tbig;

COUNT(*)
----------
40515456

1 row selected.
Elapsed: 00:04:08.21

Ok, temos cerca de 40515456 registros e levamos cerca de 4 minutos para sabermos isso. Vamos agora apagar uns 98% dessa tabela deixando apenas alguns milhares de registro. Para fazer essa operação mais racional,  quero descobrir  quantos registros tenho por usuário nessa tabela, afim de deixar apenas os menores owners:

FSOARES@dbtst> select count(*), owner from tbig group by owner order by 1;

  COUNT(*) OWNER
---------- ------------------------------
       576 IX
       576 OUTLN
      1088 PM
      2176 FLOWS_FILES
      2176 HR
      2880 SYSTEM
     13696 OE
     14912 ORDPLUGINS
     19392 WMSYS
     70464 EXFSYS
    183808 ORACLE_OCM
    230528 DBSNMP
    231552 ORDSYS
    548288 XDB
    731264 OLAPSYS
   1258816 CTXSYS
   1340416 MDSYS
   2624256 APEX_030200
   9466112 SYS
  23772480 SYSMAN

20 rows selected.

Elapsed: 00:01:14.06
FSOARES@dbtst> delete tbig where owner NOT IN ('IX', 'OUTLN');                         
40514304 rows deleted.

Elapsed: 00:29:57.41
FSOARES@dbtst> commit;

Commit complete.

Elapsed: 00:00:00.01

Aqui, a nossa tabela TBIG está somente com os dados usuário IX e OUTLN, o resto dos outros usuário foi simplesmente apagado. Bom como temos agora apenas alguns registros vamos realizar aquele mesmo count para ver o a quantidade de registro, vamos ver agora o quanto tempo levará. Primeiro é claro, vamos remover a consulta do nosso cache.

FSOARES@dbtst> alter system flush shared_pool;

System altered.

FSOARES@dbtst> alter system flush buffer_cache;

System altered.

FSOARES@dbtst> select count(*) tbig

  COUNT(*)
----------
      1152

1 row selected.

Elapsed: 00:03:53.93

Temos agora cerca de mil registros e levamos quase o mesmo tempo para realizar o count da tabela quando ela estava com mais de 40 milhões de registro!? Como isso pode ser? Tivemos o mesmo tempo para contar de 0 a 1152 e de 0 a 40 milhões?

Bom, sem dúvidas há alguma coisa de errado com nosso segmento de tabela TBIG. É aí que entra o Segment Advisor, que vai nos aconselhar o que fazer com esse segmento.

Veja como é simples:

FSOARES@dbtst> @run_sa fsoares tbig table

---------------------------------------------------------------------------------
-- Segment Adviser Script v1.0 by Flavio Soares ( http://flaviosoares.com )

Running the Segment Advisor for Segment 
Owner   : FSOARES
Segment Name: TBIG
Segment Type: TABLE

Segment Advisor successfuly completed

For delete the task TaskName_FSOARES_cxdnLahXMf run: 
SQL> exec  dbms_advisor.delete_task('TaskName_FSOARES_cxdnLahXMf');

-- Showing the Segment Advice Recommendations for the object "table" "fsoares" "tbig"

 TABLESPACE_NAME   : USERS
 SEGMENT_OWNER     : FSOARES
 SEGMENT_NAME      : TBIG
 SEGMENT_TYPE      : TABLE
 PARTITION_NAME    :
 ALLOC_MB          :    5,244.00
 RECLAIM_MB        :    4,567.54
 USED_MB           :      676.46
 PCT_SAVE          : 87 %
 RECOMMENDATIONS   : Enable row movement of the table FSOARES.TBIG and perform shrink, estimated savings is 4789413285 bytes.
 SOLUTION 1        : alter table "FSOARES"."TBIG" shrink space
 SOLUTION 2        : alter table "FSOARES"."TBIG" shrink space COMPACT
 SOLUTION 3        : alter table "FSOARES"."TBIG" enable row movement

---------------------------------------------------------------------------------

Observe a recomendação, ele sugere realizar um shrink na tabela que ganharemos com isso cerca de 87% de espaço que hoje não está sendo utilizado. Opa!  … é 87% é um bom ganho, então vamos aplicar as recomendações sugeridas.

FSOARES@dbtst> alter table "FSOARES"."TBIG" enable row movement;

Table altered.

FSOARES@dbtst> alter table "FSOARES"."TBIG" shrink space;

Table altered.

Após as recomendações aplicadas, vamos agora executar novamente o count de encontro a tabela TBIG e observar o tempo:

FSOARES@dbtst> alter system flush shared_pool;

System altered.

FSOARES@dbtst> alter system flush buffer_cache;

System altered.

FSOARES@dbtst> select count(*) from tbig;

  COUNT(*)
----------
      1152

1 row selected.

Elapsed: 00:00:00.01

Depois da recomendação aplicada, o tempo simplesmente caiu para 0.01 segundos.

Viu como ficou bem mais simples utilizar o Segment Advisor agora com o run_sa.sql :)

Com apenas três argumentos e já temos nossas recomendações.

FSOARES@dbtst> @run_sa fsoares tbig table

---------------------------------------------------------------------------------
-- Segment Adviser Script v1.0 by Flavio Soares ( http://flaviosoares.com )

Running the Segment Advisor for Segment 
Owner   : FSOARES
Segment Name: TBIG
Segment Type: TABLE

Segment Advisor successfuly completed

For delete the task TaskName_FSOARES_cxdnLahXMf run: 
SQL> exec  dbms_advisor.delete_task('TaskName_FSOARES_cxdnLahXMf');

-- Showing the Segment Advice Recommendations for the object "table" "fsoares" "tbig"

 TABLESPACE_NAME   : USERS
 SEGMENT_OWNER     : FSOARES
 SEGMENT_NAME      : TBIG
 SEGMENT_TYPE      : TABLE
 PARTITION_NAME    :
 ALLOC_MB          :    5,244.00
 RECLAIM_MB        :    4,567.54
 USED_MB           :      676.46
 PCT_SAVE          : 87 %
 RECOMMENDATIONS   : Enable row movement of the table FSOARES.TBIG and perform shrink, estimated savings is 4789413285 bytes.
 SOLUTION 1        : alter table "FSOARES"."TBIG" shrink space
 SOLUTION 2        : alter table "FSOARES"."TBIG" shrink space COMPACT
 SOLUTION 3        : alter table "FSOARES"."TBIG" enable row movement

---------------------------------------------------------------------------------

Dúvidas, melhorias, bugs, recomendações serão muito bem vindas!

Um abraço e 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.

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.

Nome do clusterware
maio 10, 2011

Está atrás de descobrir o nome do clusterware instalado?

Com o utilitário cemutlo fácil descobrir nome e versão do clusterware. Lembrando que esse deve ser executado como root.

$ORA_CRS_HOME/bin/cemutlo [-n] [-w]
-n Informa o nome do cluster
-w Informa a versão do clusterware.

[root@rac1 ~]# $ORA_CRS_HOME/bin/cemutlo -n
crs_vm
[root@rac1 ~]# $ORA_CRS_HOME/bin/cemutlo -w
2:1:
Como GoldenGate trabalha?
maio 10, 2011

GoldenGate

Como mostra a figura acima, esses são os principais processos e arquivos do Oracle GoldenGate, ele mostra também como é todo o processo de replicação realizado, que estarei explicando mais a frente.

O GoldenGate é composto pelos seguientes componentes

  • Manager
  • Extract
  • Data pump
  • Replicat
  • Trails files
  • Checkpoints
  • Collector

Manager
Manager é um processo do GoldenGate que desempenha a função de monitorar e restartar (quando necessário) os processos GoldenGate. Erros de eventos ou problemas de lentidão são reportados por ele e também desempenha a função de manter (período) os arquivos de trail e logs do GoldenGate.

Ele deve estar sendo executado em cada configuração GoldenGate antes dos processos Extract e Replicat serem iniciado.

Extract
Esse processo executa em cima do source database e tem a responsabilidade de caputrar os dados e gravar na forma de “trail files” que depois serão enviados para o target database.

Data Pump
Quando o extract escreve para um trail file no source database, o data pump lê esses trail´s e envia atráves de uma rede para o database de destino.

Replicat
Ao contrário do extract, o replicat executa em cima do target database. Ele lê os arquivos trail files enviados pelo Data Pump e então replica essas alterações seja ela DDL ou DML para o database de destino.

Trails files
Todas as mudanças realizadas pelo source database é registrados e armazenadas na forma de arquivos em series assim o GoldenGate é capaz de armazenar essas mudanças temporariamente no disco,
esses arquivos são chamados de trail. Um arquivo trail pode existir tanto no source como no target database.

Checkpoints
Checkpoints assegura que as mudanças feitas pelo database source sejam de certa forma sincronizados com a extração do processo Extract. Ele previne também a redundancia de execuções em ambientes bi-direcionais.

Através dele é permitido usar multiplos Extract e Replicat processos para ler o mesmo arquivo trail.

Collector
Quando o Data Pump envia as informações dos trails através da NetWork, o primeiro processo a lêr essa informação é o Collector, ele recebe as mudanças do database que são enviadas pelo TCP/IP e as escreves para os trails files. O processo manager é quem gerência o momento do Collector capturar os dados.

Em um próximo artigo sobre GoldenGate, estarei fazendo a instalação de uma replicação simples, de oracle para oracle.

Até mais

Monitorando o uso dos índices
maio 10, 2011

Existem muitos bancos de dados em que índices estão criados mais não são utilizados. Por exemplo, ter criado um índice para uma determinado procedimento, que é executado somente uma vez  e após seu uso não é removido, ou até mesmo o Oracle perceber que leitura por scans completos pode ser mais vantajosos do que utilizar um determinado índice (isso acontece).

Criar índice em uma base, deve ser algo realmente estudado, pois podem ter impacto negativo sobre o desempenho das operações DML. Além de modificar o valor do bloco da data, também é necessário atualizar o bloco do índice.

Por esse motivo que deve-se notar muito bem a utilização de um índice, caso não seja utilizado prejudica o desempenho do banco de dados.

Abaixo está um exemplo para descobrir se um índice está sendo ou não utilizado

Acompanhe …

SQL> create table t
2 (
3 id number,
4 nome varchar2(50)
5 )
6 ;

Table created.

SQL> create index t_id_idx on t (id);

Index created.
SQL> insert into t values (1, 'Flávio');
1 row created.
SQL> commit; SQL> select index_name, table_name, used from v$object_usage; INDEX_NAME                          TABLE_NAME                USE ------------------------------ ------------------------------ --- T_ID_IDX                             T                         NO SQL> alter index t_id_idx monitoring usage; Index altered. SQL> select * from t where id=1; ID           NOME ---------- -------------------------------------------------- 1            Flávio SQL> select index_name, table_name, used from v$object_usage; INDEX_NAME                       TABLE_NAME                   USE ------------------------------ ------------------------------ --- T_ID_IDX                          T                           YES SQL> alter index t_id_idx nomonitoring usage; Index altered.

Veja que a view v$OBJECT_USAGE, terá cada índice do seu esquema cujo uso está sendo monitorando, se por ventura o índice não for usado, pode ser uma ótima opção exclui-lo.

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.
Próxima Página »