Blog do Portilho

Um amigo meu, o Cauã, estava me contando que na empresa em que trabalha precisa lidar com algumas aplicações antigas.

Pelo print que ele mandou, ele não estava exagerando. Resolvi mostrar aqui para vocês.

E só para lembrar, até o 10g será Legado a partir de Julho/2010, para quem não tem um contrato de suporte Extended.

O RESULT CACHE é uma das New Features do 11g, e é a que mais gosto, pois deve trazer ganho de desempenho a qualquer aplicação, sem nenhuma alteração em códigos.

Você gosta de Materialized Views? Pois o RESULT CACHE praticamente as torna desnecessárias, pois todos os resultados de SELECTs são armazenados, e continuam lá até que a tabela seja alterada, como em uma MV, mas sem o inconveniente de ter que configura-las, e nem sofrer o Overhead que elas podem causar em tabelas com grande volume de alterações.

Veja o exemplo simples a seguir. Criei uma tabelona, e um SELECT COUNT(*) leva de 10 a 20 segundos para ser executado.

Reparem que o tempo não diminui muito mesmo após executar o SELECT várias vezes, pois esta tabela não cabe toda em meu DB_CACHE_SIZE.

SQL> SET TIMING ON
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
———-
2590224

Decorrido: 00:00:17.07
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
———-
2590224

Decorrido: 00:00:20.79
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
———-
2590224

Decorrido: 00:00:10.58

Agora eu habilito o RESULT_CACHE para esta sessão. Este parâmetro pode ser alterado também para toda a instância.

SQL> ALTER SESSION SET RESULT_CACHE_MODE=FORCE;

SessÒo alterada.

Decorrido: 00:00:00.21

Agora executo novamente o SELECT. Já na segunda execução (quando o resultado já está no RESULT_CACHE), o resultado é praticamente instantâneo.

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
———-
2590224

Decorrido: 00:00:11.82
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
———-
2590224

Decorrido: 00:00:00.09
SQL>

Mas não trate o RESULT_CACHE como uma bala de prata. Leia a documentação, pois vários parâmetros controlam seu comportamento.

É interessante dizer que o MySQL possui esta funcionalidade a quase 10 anos, se bem que me lembro que o algoritmo não funcionava também até a versão 5.0.33. É uma prova de que a diversidade agrega valor para todos: o Oracle só fica melhor a cada dia porque tem concorrentes.

Não adianta fugir. Se você pretende administrar seriamente um banco de dados Oracle, terá que dominar o RMAN.

Já está nas bancas a edição 72 da SQL Magazine, onde demonstro algumas das novas funcionalidades do RMAN do Oracle 11g.

Eu começo do básico (modo ARCHIVELOG, criação do repositório, executar backup), para que o artigo possa ser utilizado por todos os leitores, passo por algumas funcionalidades legais que já existiam (Stored Scripts, paralelismo, backup automático do controlfile), até chegar a algumas funcionalidades do 11g, como a compressão BZIP2 e o uso do Data Recovery Advisor.

Site da SQL Magazine

Dedico este artigo a minha esposa Luiza, que mesmo com todos seus problemas, sempre encontra tempo para me animar a escrever, me mostrando que o principal objetivo deve ser ajudar as pessoas.

Agradeço também a Solvo, empresa onde trabalho, que me permite utilizar (e destruir) os laboratórios também para escrever artigos.

Após 10 anos de trabalho ininterrupto, estou de férias.

Aproveitarei as férias para meditar sobre o Credo do Samurai (pois ele bem poderia se chamar de Credo do DBA), o que não faço desde que os tempos em que fazia Aikido.

Até mais !

Eu não tenho pais, faço do céu e da terra meus país.
Eu não tenho casa, faço do mundo minha casa.
Eu não tenho poder divino, faço da honestidade meu poder divino.
Eu não tenho pretensões, faço da minha disciplina minha pretensão.
Eu não tenho poderes mágicos, faço da personalidade meus poderes mágicos.
Eu não tenho vida ou morte, faço das duas uma, tenho vida e morte.
Eu não tenho visão, faço da luz do trovão a minha visão.
Eu não tenho audição, faço da sensibilidade meus ouvidos.
Eu não tenho língua, faço da prontidão minha língua.
Eu não tenho leis, faço da auto-defesa minha lei.
Eu não tenho estratégia, faço do direito de matar e do direito de salvar vidas minha estratégia.
Eu não tenho projetos, faço do apego às oportunidades meus projetos.
Eu não tenho princípios, faço da adaptação a todas as circunstâncias meu princípio.
Eu não tenho táticas, faço da escassez e da abundância minha tática.
Eu não tenho talentos, faço da minha imaginação meus talentos.
Eu não tenho amigos, faço da minha mente minha única amiga.
Eu não tenho inimigos, faço do descuido meu inimigo.
Eu não tenho armadura, faço da benevolência minha armadura.
Eu não tenho castelo, faço do caráter meu castelo.
Eu não tenho espada, faço da perseverança minha espada.

Os índices BITMAP podem ser úteis sim, em colunas com cardinalidade maior.

Veja no teste abaixo, onde utilizei uma coluna com cardinalidade bem maior (de 28/10936000 para 40998/10936000), e pelo menos para agregações, a performance com BITMAP foi muito melhor (3x) do que o Índice BTREE. O ganho foi muito menor do que na cardinalidade menor, demonstrada no teste anterior, mas mesmo assim um ganho de 300% é muito bom.

SQL> SELECT COUNT(1) FROM T;

COUNT(1)
———-
10936000

Decorrido: 00:00:00.03
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;

COUNT(DISTINCT(OWNER))
———————-
28

Decorrido: 00:00:01.87
SQL> SELECT COUNT(DISTINCT(OBJECT_NAME)) FROM T;

COUNT(DISTINCT(OBJECT_NAME))
—————————-
40998

Decorrido: 00:00:26.03
SQL> CREATE INDEX T_IDX2_BTREE ON T(OBJECT_NAME);

-ndice criado.

Decorrido: 00:03:37.48
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(’SCOTT’, ‘T’, CASCADE=>TRUE);

Procedimento PL/SQL concluÝdo com sucesso.

Decorrido: 00:01:00.10
SQL> SELECT COUNT(DISTINCT(OBJECT_NAME)) FROM T;

COUNT(DISTINCT(OBJECT_NAME))
—————————-
40998

Decorrido: 00:00:09.90
SQL> SELECT COUNT(DISTINCT(OBJECT_NAME)) FROM T;

COUNT(DISTINCT(OBJECT_NAME))
—————————-
40998

Decorrido: 00:00:09.01
SQL> SELECT COUNT(DISTINCT(OBJECT_NAME)) FROM T;

COUNT(DISTINCT(OBJECT_NAME))
—————————-
40998

Decorrido: 00:00:08.95
SQL> DROP INDEX T_IDX2_BTREE;

-ndice eliminado.

Decorrido: 00:00:00.37
SQL> CREATE BITMAP INDEX T_IDX2_BTREE ON T(OBJECT_NAME);

-ndice criado.

Decorrido: 00:00:43.67
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(’SCOTT’, ‘T’, CASCADE=>TRUE);

Procedimento PL/SQL concluÝdo com sucesso.

Decorrido: 00:01:02.87
SQL> SELECT COUNT(DISTINCT(OBJECT_NAME)) FROM T;

COUNT(DISTINCT(OBJECT_NAME))
—————————-
40998

Decorrido: 00:00:03.14
SQL> SELECT COUNT(DISTINCT(OBJECT_NAME)) FROM T;

COUNT(DISTINCT(OBJECT_NAME))
—————————-
40998

Decorrido: 00:00:02.45
SQL> SELECT COUNT(DISTINCT(OBJECT_NAME)) FROM T;

COUNT(DISTINCT(OBJECT_NAME))
—————————-
40998

Decorrido: 00:00:03.15
SQL>

Tempo do COUNT DISTINCT com índice BTREE: 9 segundos, 9 segundos e 8 segundos.

Tempo do COUNT DISTINCT com índice BITMAP: 3 segundos, 2 segundos e 3 segundos.

Então, devemos ter cuidado com o que chamamos de alta cardinalidade. Testar é sempre a melhor opção.

Um bom uso para Índices BITMAP é em tabelas que possuem colunas onde são feitas agregações em que a performance é primordial.

Nestes casos, os Indices BITMAP serão melhores que os BTREE.

Veja o exemplo abaixo, onde criei uma grande tabela a partir de várias cópias da ALL_OBJECTS;

SQL> SELECT COUNT(1) FROM T;
COUNT(1)
———-
10936000

SQL> set timing on;
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;

COUNT(DISTINCT(OWNER))
———————-
28

Decorrido: 00:00:25.95
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;

COUNT(DISTINCT(OWNER))
———————-
28

Decorrido: 00:00:26.51
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;

COUNT(DISTINCT(OWNER))
———————-
28

Decorrido: 00:00:26.75
SQL> CREATE INDEX T_IDX1 ON T(OWNER);

-ndice criado.

Decorrido: 00:04:35.95
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(’SCOTT’, ‘T’, CASCADE=>TRUE);

Procedimento PL/SQL concluÝdo com sucesso.

Decorrido: 00:01:01.14
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;

COUNT(DISTINCT(OWNER))
———————-
28

Decorrido: 00:00:16.06
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;

COUNT(DISTINCT(OWNER))
———————-
28

Decorrido: 00:00:05.57
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;

COUNT(DISTINCT(OWNER))
———————-
28

Decorrido: 00:00:05.29
SQL> DROP INDEX T_IDX1;

-ndice eliminado.

Decorrido: 00:00:00.32
SQL> CREATE BITMAP INDEX T_IDX2 ON T(OWNER);

-ndice criado.

Decorrido: 00:00:30.84
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(’SCOTT’, ‘T’, CASCADE=>TRUE);

Procedimento PL/SQL concluÝdo com sucesso.

Decorrido: 00:01:01.14
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;

COUNT(DISTINCT(OWNER))
———————-
28

Decorrido: 00:00:02.01
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;

COUNT(DISTINCT(OWNER))
———————-
28

Decorrido: 00:00:01.90
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;

COUNT(DISTINCT(OWNER))
———————-
28

Decorrido: 00:00:01.84
SQL>

Tempo do COUNT DISTICT sem índices: 25 segundos, 26 segundos, e 26 segundos.

Tempo do COUNT DISTINCT com índice BTREE: 16 segundos, 5 segundos, 5 segundos.

Tempo do COUNT DISTINCT com índice BITMAP: 2 segundos, 1 segundo, 1 segundo.

É interessante observar que o tempo de criação do índice também é bem menor (4 minutos e 35 segundos X 30 segundos). Este tempo menor é o mesmo para qualquer coluna da tabela, independente da cardinalidade.

O índice BITMAP, ao contrário do B-TREE, contém uma entrada para cada valor indexado, que aponta (por um bitmap) para todos os registros que contém este valor.
Por causa desta arquitetura, durante um INSERT de um registro, seu BITMAP estará bloqueado durante esta operação. Ninguém poderá inserir um valor igual na coluna indexada.

Veja o teste abaixo:

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

SQL>

Deixe esta sessão como está, e em seguida, abra outra sessão, e tente executar o mesmo INSERT:

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

SQL> INSERT INTO T1 VALUES (’Teste’);

Este segundo INSERT irá esperar indefinidamente, até um COMMIT ou ROLLBACK da primeira sessão.
Parabéns, você fez o Oracle tornar-se monousuário para INSERTs.

O Apex - Oracle Application Express - ficou muito mais simples de instalar no 11g.

Na verdade, no 11gR1 ele já vem instalado, pois ele aparece nas opções de criação de banco de dados no DBCA.

Mas falta uma pequena configuração para que ele funcione:

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\RPRONI>set ORACLE_HOME=c:\oracle\product\11.1.0\db_1

C:\Documents and Settings\RPRONI>set ORACLE_SID=ORCL

C:\Documents and Settings\RPRONI>cd %ORACLE_HOME%

C:\oracle\product\11.1.0\db_1>cd apex

C:\oracle\product\11.1.0\db_1\apex>sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on Seg Jan 4 13:35:46 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Informe o nome do usußrio: / AS SYSDBA

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @apxconf

PORT
———-
8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user
Default values are in brackets [ ].
Press Enter to accept the default value.

Enter a password for the ADMIN user              []
Enter a port for the XDB HTTP listener [      8080]
…changing HTTP Port

Procedimento PL/SQL concluÝdo com sucesso.

Procedimento PL/SQL concluÝdo com sucesso.

SessÒo alterada.

…changing password for ADMIN

Procedimento PL/SQL concluÝdo com sucesso.

Commit concluÝdo.

SQL> Alter user ANONYMOUS account unlock;

Usußrio alterado.

SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);

Procedimento PL/SQL concluÝdo com sucesso.

SQL> commit;

Commit concluÝdo.

SQL> exit
Desconectado de Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\oracle\product\11.1.0\db_1\apex>

Depois acesse http://<hostname>:8080/apex/apex_admin, e use o usuário ADMIN e a senha que você forneceu na configuração acima.
Depois, o Apex está pronto para uso em http://<hostname>:8080/apex

Novo Twitter em 2010: sigam-me os bons !

http://twitter.com/rportilhoproni

Adoro testar, pois sou daqueles que só acredita vendo. E acho que os DBAs devem ser assim.

Este teste foi realizado em um servidor IBM novíssimo, rodando AIX 5.3, em um Storage se última linha, e Oracle 9.2.0.8.
O teste foi feito em um ambiente real, pois deve basear a decisão sobre como será a utilização deste ambiente em produção.

Para executar este teste, utilizei esta sequência de comandos em um arquivo .sql.

CREATE TABLE T AS SELECT * FROM DBA_SOURCE;

SET TIMING ON

ALTER SESSION SET EVENTS = ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;
INSERT INTO T SELECT * FROM T;
COMMIT;
SELECT TO_CHAR(SUM(BYTES)) FROM DBA_SEGMENTS WHERE OWNER = ‘SYS’ AND SEGMENT_NAME = ‘T’;
ALTER SESSION SET EVENTS = ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;

ALTER SESSION SET EVENTS = ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;
INSERT INTO T SELECT * FROM T;
COMMIT;
SELECT TO_CHAR(SUM(BYTES)) FROM DBA_SEGMENTS WHERE OWNER = ‘SYS’ AND SEGMENT_NAME = ‘T’;
ALTER SESSION SET EVENTS = ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;

ALTER SESSION SET EVENTS = ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;
INSERT INTO T SELECT * FROM T;
COMMIT;
SELECT TO_CHAR(SUM(BYTES)) FROM DBA_SEGMENTS WHERE OWNER = ‘SYS’ AND SEGMENT_NAME = ‘T’;
ALTER SESSION SET EVENTS = ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;

ALTER SESSION SET EVENTS = ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;
INSERT INTO T SELECT * FROM T;
COMMIT;
SELECT TO_CHAR(SUM(BYTES)) FROM DBA_SEGMENTS WHERE OWNER = ‘SYS’ AND SEGMENT_NAME = ‘T’;
ALTER SESSION SET EVENTS = ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;

ALTER SESSION SET EVENTS = ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;
INSERT INTO T SELECT * FROM T;
COMMIT;
SELECT TO_CHAR(SUM(BYTES)) FROM DBA_SEGMENTS WHERE OWNER = ‘SYS’ AND SEGMENT_NAME = ‘T’;
ALTER SESSION SET EVENTS = ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T;

Reparem no comando ALTER SESSION feito para esvaziar o db_cache_size do Oracle, para que uma sequência de testes não influenciasse positivamente a próxima.

Vejam que executei a sequência se INSERT e SELECTs 5 vezes, de forma que a tabela T sempre crescia, começando com apenas 800MB, e terminando com 7GB. Utilizei um db_cache_size de 512MB, relativamente pequeno, pois o propósito é medir a velocidade dos filesystems.

Executei este arquivos 3 veses: uma com o JFS2 com as opções default (ou seja, com Caché de dados do JFS2), outra com o JFS2 com Direct I/O, e outra com JFS2 com Concurrent I/O - uma evolução do Direct I/O. Adicionalmente, em todos os testes foi utilizado também Asynchronous I/O.
Após uma sequência, eu desligava a instância do Oracle, desmontava o filesystem, e o remontava com a opção do próximo teste.

prd05 /> umount /prddb9i
prd05 /> mount -o cio /prddb9i

Para utilizar estas opções, é necessário deixar o parâmetro do Oracle filesystemio_options em SET_ALL. Não há problemas em deixar este parâmetro com o valor SET_ALL se o DIO ou CIO não estiverem em uso no filesystem, pois o Oracle tentará utilizar as features, e se elas não existirem, não há problemas.

O resultado foi muito interessante.

Em gravações, a análise é fácil: o CIO é, nos tempos individuais e total, 3 vezes mais rápido que o JFS2 com suas configurações Default.
Nas leituras, embora no tempo total o CIO ganhe, ele só ganha quando os dados não estão no Caché do Oracle, ou seja, são lidos diretamente do disco.

Como a maioria das operações do nosso ambiente são de leitura, e seu Buffer Caché Hit Ratio (esse é um dos poucos casos onde o BCHR é útil, este tipo de decisão) é de 90% (parece alto, mas um índice bom é >96%), não vale a pena utilizarmos CIO ou DIO do JFS2 neste ambiente.

Resultado dos INSERTs

Resultado dos SELECTs