Oracle Tuning – SQL: Os Falsos Nulos

Falsos Nulos ocorrem quando ao invés de NULL, é utilizado um valor que nunca seria utilizado normalmente, como “Bruce Wayne” para uma columa NOME_EMPREGADO, “000.000.000.000-00” para uma coluna CPF, ou “31/12/4000” para uma coluna DATA_NASCIMENTO.

É muito comum uma modelagem utilizar Falsos Nulos (propositalmente ou não), para “evitar problemas”. Geralmente estes problemas são comportamentos não esperados – não esperados pelo desenvolvedor, e não pelo Oracle – com valores NULL, e manter a portabilidade da aplicação para outro banco de dados (que de qualquer forma não seria fácil mesmo).

O problema dos Falsos Nulos é que ao computar a seletividade de um SELECT, o Oracle os considera valores normais.

Por exemplo, em uma coluna de data, se é utilizada um Falso Nulo com um valor muito longe da maioria dos outros valores (como “31/12/4000”), o Oracle (mais especificamente, o CBO – Cost Based Optimizer) considera que há valores “de 1998 a 4000”, incluindo nos anos 3999, 3998, e assim por diante.

Vejam a tabela abaixo:

SQL> SELECT COUNT(*) FROM T92;

  COUNT(*)
----------
    182700

SQL> SELECT COUNT(*) FROM T92 WHERE DATA_PEDIDO = TO_DATE('31/12/4000', 'DD/MM/YYYY');

  COUNT(*)
----------
       183

SQL>

Como é mostrado acima, apenas uma pequena parte das linhas possui o Falso Nulo, mas vemos abaixo a consequência – os valores reais estão mais “diluídos”, e o CBO considera que há apenas 4 linhas na consulta dos pedidos de um ano, quando na verdade há 3097.

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT COUNT(*) FROM T92 WHERE DATA_PEDIDO BETWEEN TO_DATE('01/12/2010','DD/MM/YYYY') AND TO_DATE('31/12/2010','DD/MM/YYYY');

  COUNT(*)
----------
      3097


Execution Plan
----------------------------------------------------------
Plan hash value: 103883946

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |    93   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T92  |     4 |    36 |    93   (3)| 00:00:01 |
---------------------------------------------------------------------------

SQL>

Um Histograma corrigiria essa situação, mas colunas de data geralmente tem uma grande quantidade de valores distintos (10 anos são 3650 dias distintos, que nem os histogramas do 12c comportam), o que inviabiliza um bom histograma.

Mas o melhor é fazer o certo: se removermos os falsos nulos, este problema é resolvido.

SQL> SET AUTOTRACE OFF
SQL> DELETE FROM T92 WHERE DATA_PEDIDO = TO_DATE('31-Dec-4000');

183 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT COUNT(*) FROM T92 WHERE DATA_PEDIDO BETWEEN TO_DATE('01/12/2010','DD/MM/YYYY') AND TO_DATE('31/12/2010','DD/MM/YYYY');

  COUNT(*)
----------
      3097


Execution Plan
----------------------------------------------------------
Plan hash value: 103883946

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |    91   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T92  |  3097 | 27873 |    91   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL>

Lendas de Índices: Tabelas Pequenas

Há uma lenda que diz que em tabelas pequenas índices não são utilizados.

Isto não é verdade, como por exemplo quando só é necessária a informação que está indexada – não há acesso a colunas não indexadas da tabela.

SQL> CREATE TABLE T (C1 NUMBER);

Table created.

SQL> CREATE INDEX IDX_T ON T(C1);

Index created.

SQL> INSERT INTO T VALUES (1);

1 row created.

SQL> INSERT INTO T VALUES (2);

1 row created.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT C1 FROM T WHERE C1 = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2296882198

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T |     1 |    13 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL>

Lendas de Índices: Cláusulas de Negação

Há uma lenda de que se você utiliza cláusulas de negação (<>, !=, NOT IN, NOT EXISTS) em um SQL, este não se beneficiará de índices.

Esta afirmação está incorreta, especialmente se o indice evitar o acesso à tabela, como no método de acesso Fast Index Scan. Para testar primeiramente vamos criar uma rápida tabela de teste, com cerca de 90.000 linhas, e um índice.

SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

Table created.

SQL> CREATE INDEX IDX_T ON T(OBJECT_TYPE);

Index created.

SQL> SELECT COUNT(OBJECT_TYPE) FROM T;

COUNT(OBJECT_TYPE)
------------------
             89727

SQL> 

Agora, os planos de execução da seleção de uma quase toda a tabela, de cerca de metade, e até de uma pequena parte, utilizando cláusulas de negação.

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE != 'TABLE';

COUNT(OBJECT_TYPE)
------------------
             87308


Execution Plan
----------------------------------------------------------
Plan hash value: 2371838348

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    13 |    72   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T | 88554 |  1124K|    72   (5)| 00:00:01 |
-------------------------------------------------------------------------------

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE != 'SYNONYM';

COUNT(OBJECT_TYPE)
------------------
             52429


Execution Plan
----------------------------------------------------------
Plan hash value: 2371838348

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    13 |    72   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T | 50496 |   641K|    72   (5)| 00:00:01 |
-------------------------------------------------------------------------------

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE NOT IN ('SYNONYM', 'JAVA CLASS', 'VIEW', 'INDEX');

COUNT(OBJECT_TYPE)
------------------
             11448


Execution Plan
----------------------------------------------------------
Plan hash value: 2371838348

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    13 |    72   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T |  7296 | 94848 |    72   (5)| 00:00:01 |
-------------------------------------------------------------------------------

SQL>

Ou seja, o índice é plenamente utilizado, na operação Index Fast Full Scan (onde não há o acesso à tabela, somente ao índice).

Agora os mesmos SQLs, mas sem o índice. Comparem o custo, de 72 para 416, e a troca de operação para Full Table Scan.

SQL> DROP INDEX IDX_T;

Index dropped.

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE != 'TABLE';

COUNT(OBJECT_TYPE)
------------------
             87308


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   416   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 88554 |  1124K|   416   (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE != 'SYNONYM';

COUNT(OBJECT_TYPE)
------------------
             52429


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   416   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 50496 |   641K|   416   (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE NOT IN ('SYNONYM', 'JAVA CLASS', 'VIEW', 'INDEX');

COUNT(OBJECT_TYPE)
------------------
             11448


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   416   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  7296 | 94848 |   416   (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL>

Memória Manual: SORT_AREA_SIZE

O parâmetro PGA_AGGREGATE_TARGET foi introduzido no 9i para simplificar a utilização das áreas de memória privadas de usuários.
Documentação do 9iR2: http://docs.oracle.com/cd/B10501_01/server.920/a96536/ch1158.htm#REFRN10165

Antes do 9i, as seguintes áreas tinham que ser gerenciadas manualmente:
SORT_AREA_SIZE
HASH_AREA_SIZE
BITMAP_MERGE_AREA_SIZE
CREATE_BITMAP_AREA_SIZE

A memória automática (tanto de SGA quanto PGA) atende muito bem a maioria dos casos (como quase tudo no Oracle Database).

Mas a utilização manual ainda é possível até hoje, no 12.1.0.2, e ainda tem sua função.

Para habilitar as área manuais, é preciso alterar o parâmetro WORKAREA_SIZE_POLICY para MANUAL, e atribuir o valor desejado para a área em questão.

Este parâmetro pode ser alterado em SESSION, o que seria ideal para uma carga específica.

Fiz um teste com o SQL abaixo, que faz um grande HASH JOIN.

SELECT T1.OBJECT_TYPE, COUNT(T1.OBJECT_TYPE) FROM T1, T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID GROUP BY T1.OBJECT_TYPE ORDER BY 2;

Utilizando PGA_AGGREGATE_TARGET de 512M, e apenas uma sessão conectada na instância, este SQL possuia o plano de execução abaixo:

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |     40 |00:06:50.58 |     384K|    429K|  45508 |       |       |      		|        |
|   1 |  SORT ORDER BY       |      |      1 |     40 |     40 |00:06:50.58 |     384K|    429K|  45508 |  2048 |  2048 | 2048   (0)|        |
|   2 |   HASH GROUP BY      |      |      1 |     40 |     40 |00:06:50.58 |     384K|    429K|  45508 |  1200K|  1200K| 1379K  (0)|        |
|*  3 |    HASH JOIN         |      |      1 |   1443M|   1463M|00:04:48.25 |     384K|    429K|  45508 |   465M|    23M|   93M  (1)|    368K|
|   4 |     TABLE ACCESS FULL| T2   |      1 |     11M|     11M|00:00:13.86 |     192K|    192K|      0 |       |       |      		|        |
|   5 |     TABLE ACCESS FULL| T1   |      1 |     11M|     11M|00:00:11.06 |     192K|    192K|      0 |       |       |      		|        |
----------------------------------------------------------------------------------------------------------------------------------------------

Veja que é utilizada TEMP (Used-Tmp), e para isso gravações (Writes).

Os tempos desta execução foram os abaixo (gosto de fazer teste três vezes seguidas, para eliminar os pontos fora da curva):

Elapsed: 00:06:50.60
Elapsed: 00:06:41.43
Elapsed: 00:06:49.06

Em seguida alterei a memória de usuário para MANUAL, e coloquei 256M para a área responsável pelo SORT (ou seja, metade do que já estava disponível pela PGA).

SQL> ALTER SESSION SET workarea_size_policy = MANUAL;
SQL> ALTER SESSION SET sort_area_size = 268435456;

O plano de execução passou então a ser o seguinte:

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |     40 |00:06:20.50 |     384K|    384K|       |       |          |
|   1 |  SORT ORDER BY       |      |      1 |     40 |     40 |00:06:20.50 |     384K|    384K|  3072 |  3072 | 2048  (0)|
|   2 |   HASH GROUP BY      |      |      1 |     40 |     40 |00:06:20.50 |     384K|    384K|  1214K|  1214K| 9972K (0)|
|*  3 |    HASH JOIN         |      |      1 |   1443M|   1463M|00:04:20.75 |     384K|    384K|   465M|    23M|  633M (0)|
|   4 |     TABLE ACCESS FULL| T2   |      1 |     11M|     11M|00:00:11.64 |     192K|    192K|       |       |          |
|   5 |     TABLE ACCESS FULL| T1   |      1 |     11M|     11M|00:00:11.25 |     192K|    192K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Além da eliminação do uso da TEMP, os tempos foram reduzidos em 8%-10%. Parece pouco, mas se o SQL for executado em concorrência, a contenção de I/O causada pelo uso da TEMP será um sério limitador de escalabilidade.

Elapsed: 00:06:20.56
Elapsed: 00:06:17.79
Elapsed: 00:06:20.89

Vídeos de RMAN

Reuni aqui links para todos os vídeos que fiz de RMAN (na verdade, de Recuperação), para ficar mais fácil de quem esteja procurando.

Recuperação da SYSTEM:

Recuperação da UNDO:

Recuperação de todos os CONTROLFILEs:

Recuperação da UNDO sem BACKUP:

Recuperação de DATAFILE da USERS e da SYSTEM:

Restauração de DATAFILE sem BACKUP:

CROSSCHECK e DELETE EXPIRED:

SNAPSHOT CONTROLFILE, CONTROLFILE AUTOBACKUP, e recuperação do CONTROLFILE:

BACKUP OPTIMIZATION:

Recuperação da perda de Redo Log INACTIVE:

Criação de Standby Manual:

Nervix

Nervix-Center

Em 2015, quando a Nerv passou a focar como Consultoria, um dos primeiros passos foi estabelecer a plataforma de monitoração. Eu escolhi o Zabbix, por já conhecer suas capacidades de trabalhos anteriores que eu tive. O Zabbix na Nerv evoluiu para o Nervix, pelos motivos a seguir.

Para quem não conhece, o Zabbix sem customização não monitora bem bancos de dados (mas monitora muito bem sistemas operacionais e rede). Mas ele pode ser naturalmente configurado para monitorar qualquer coisa, através de Templates.

Um dos primeiros problemas foram os Templates livremente disponíveis mais utilizados para Oracle. Todos os que eu testei eram simplesmente inaceitáveis – monitoram o que não precisa, e não monitoram o que precisa. Para ajustar, preferimos fazer do zero.

Aliás, não faz sentido ter um único Template no Zabbix para Oracle: um banco de Produção possui monitorações diferentes de Standby, Homologação, Desenvolvimento. E mesmo bancos de Produção possuem criticidades diferentes.
Existem vários tipos diferentes de Standby, e também existem monitorações distintas para RAC, Oracle Restart, Enterprise, Standard, 9i, 10g, 11g, 12c, PDB… Enfim, um sistema de Monitoração que possui um único template para bancos de dados Oracle já não faz sentido. Atualmente o Nervix possui mais de 20 Templates diferentes para Oracle.

E também não devem ser monitorados apenas Incidentes, mas também Mudanças, Melhores Práticas, Licenciamento. Procuramos cobrir todos os aspectos de Disponibilidade, Integridade, Desempenho, Recuperabilidade e Segurança. Prevenir é melhor do que remediar.

Sobre Tuning, temos especial orgulho do módulo MRD: Monitoramento Real de Desempenho. Não alertamos o cliente que a utilização de CPU está alta, ou a memória livre está baixa, por exemplo. Nós avisamos que há SQLs sofrendo lentidão, quais são eles, o quanto estão lentos, porque estão lentos, e claro, como corrigir.

Passando do mero Monitoramento, logo esbarramos em outras características (eu não chamaria de problemas) do Zabbix: atualização de configurações, visualização de informações, agendamento de rotinas, tratamento de alertas como chamados. Então só o Zabbix já não nos atendia mais, e a plataforma foi expandida.

Depois de 1 ano de trabalho ininterrupto, hoje temos disponível o Nervix: um serviço de Monitoração e Administração de bancos de dados, feito pela Nerv, empresa referência no Brasil em Oracle e MySQL.

O genial logotipo é do Pablo da http://www.blackjoao.com/.

O Nervix pode ser utilizado em sua empresa, através da aquisição dos Pacotes de Consultoria da Nerv Monitoração + Administração ou Monitoração + Suporte a Equipe:

Monitoração + Administração:
Nós monitoramos, administramos o ambiente, e auxiliamos a Equipe do Cliente.
Este Pacote é direcionado para um ambiente que não possui uma equipe dedicada para administração, ou quando esta equipe precisa dedicar mais tempo a outras atividades, e também precisa de suporte de especialistas da Nerv.

Monitoração + Suporte a Equipe:
Nós monitoramos e auxiliamos a Equipe do Cliente que administra o ambiente.
Este Pacote é direcionado para um ambiente que possui uma equipe dedicada para administração, mas esta precisa de um monitoramento mais efetivo do ambiente, além de suporte de especialistas da Nerv.

Suporte a Equipe:
Nós auxiliamos a Equipe do Cliente, de DBAs e/ou Desenvolvedores.
Este Pacote é direcionado para um ambiente que possui uma equipe dedicada para administração e monitoração, mas esta precisa de suporte de especialistas da Nerv.

Maiores informações: http://nervinformatica.com.br/monitoracao.php

Os nos envie um e-mail: contato@nervinfomatica.com.br.

O porquê do novo Logotipo da Nerv

Logotipia-Nerv-Cortado-02

Para refletir o novo momento da Nerv, decidimos reformular toda a marca, e o resultado desse trabalho começa a ser visto pelo Logotipo.

O motivo da escolha do Golfinho, saltando pelo desenho que representa um banco de dados são vários.

O Golfinho é um dos animais mais inteligentes e ágeis que existem. A agilidade é um de nossos diferenciais como Consultoria.

Seus grandes cérebros permitem realizar tarefas de maneira eficaz, além de terem desenvolvido sua própria linguagem, assim como temos nosso sistema próprio de Monitoramento e Administração de bancos de dados.

Por conta do mesmo motivo, é um dos seres com maior capacidade de aprendizado, o que se liga diretamente com nossa parte de Treinamentos.

Além disso, o golfinho é um ser amigável e próximo, o que também é um dos pilares de nosso trabalho como Consultoria.

E claro, o golfinho é o símbolo do MySQL, uma de nossas especialidades.

Em breve, todo o site será atualizado seguindo a nova imagem, graças ao competente trabalho do Pablo (Design) da http://www.blackjoao.com/, e do Fernando (Código) da http://geekinc.com.br/.
.

13 motivos para NÃO ir no GUOB Tech Day 2016

Palestrantes

1o: O evento será em um sábado, e começa bem cedo. E você está atrasado em suas séries.
2o: Você está trabalhando demais, não tem tempo de aprender algo novo.
3o: Você não aguenta mais tanto conhecimento, sua cabeça já está cheia. Afinal, já domina o exp/imp.
4o: O Carlos Sierra vai palestrar sobre SQL Tuning, e você já entendeu tudo a respeito. Não tem segredo.
5o: Rodrigo Jorge e Alexandre Borges irão falar sobre segurança, e seus ambientes já são bem seguros.
6o: Para que assistir as palestras? Depois é só baixar as apresentações, vai dar pra entender tudo.
7o: Estão sempre as mesmas pessoas da comunidade Oracle nesses eventos, você já não aguenta mais ver estes caras.
8o: A comida é muito boa, e você já está um pouco acima do peso.
9o: O ingresso custa R$ R$ 450,00 (R$ 150,00 para estudantes, e 50% de desconto para Alunos Nerv), e você acha que o valor hora do Daniel Morgan, Tim Gorman, Carlos Sierra, JUNTOS, e com café e biscoitos, não vale isso.
10o: O Alex Gorbachev estará lá, e você não quer saber como trabalhar na Pythian.
11o: Você não quer ganhar brindes, nem prêmios de sorteio. Afinal, você já tem coisa demais.
12o: Você finalmente convenceu sua empresa a só utilizar Excel. Afinal, é tão mais fácil de usar! E não tem esses eventos chatos.
13o: Terá muita gente de muitas empresas lá, e assim você corre o risco de arrumar um novo emprego, e isto sempre é muito estressante.

Para não se inscrever, não clique aqui: http://www.guobtechday2016.eventize.com.br/