Eu preparei recentemente este test case para mostrar o funcionamento da compressão de índices para um de nosso clientes e achei interessante compartilhá-lo, pois eu sei que esta é uma feature que é muito pouco utilizada por aqui e isso se deve de maneira geral pela falta de conhecimento de sua existência ou do seu funcionamento.
Também devemos levar em conta que é muito comum encontrarmos na prática bancos onde a área alocada para índices é igual ou até mesmo maior que a área de dados. Empregando a compressão de índices conseguimos em média uma economia de 20% de área de storage. Se colocar isso dentro de um contexto onde hoje busca-se reduzir custos ao máximo, aumentar a longevidade do hardware, reduzir o TCO, obter uma melhor performance... não existe motivo para não utilizar uma tecnologia que está ali a disposição que pode auxiliar em todos estes quesitos.
Além de mostrar como a compressão funciona, a idéia é criar uma metodologia para automatizar a seleção dos candidatos, pois o próprio Oracle consegue nos dar todas as informações que precisamos com relação ao melhor nível de compressão que podemos obter.
Primeiro vamos comentar algumas caracteristicas básicas:
- A compressão de indices é uma feature padrão do Oracle, não necessita de licenciamento especial, estando inclusive habilitada no Oracle standard.
- Esta compressão se baseia na compressão de repetições dentro da estrutura da arvore binária, portanto é altamente dependente da ordem das colunas
- Em função da característica acima, indices que começam com uma coluna única não conseguem ser comprimidos
Para
estimar a compressão de um indice existe uma metodologia bem prática. Vamos por
partes.
O comando
ANALYZE INDEX ... VALIDATE STRUCTURE popula uma view chamada INDEX_STATS. Esta
view sempre irá conter um indice por vez, ou seja, cada vez que você executar o
VALIDATE STRUCTURE seu conteúdo é apagado e populado com os dados do indice
solicitado.
Estamos
interessados em duas colunas desta view: OPT_CMPR_COUNT e OPT_CMPR_PCTSAVE.
OPT_CMPR_COUNT é o número ideal de colunas que deve ser especificado no momento
da compressão. OPT_CMPR_PCTSAVE é o ganho esperado com a compressão utilizando
o número recomendado de colunas.
Uma vez
determinado o número de colunas, para executar a compressão é bem simples:
ALTER INDEX
... REBUILD COMPRESS n
Onde ‘n’ é
o número de colunas indicado por OPT_CMPR_COUNT.
Considerando
isso que foi dito acima, eu criei uma package que possui duas procedures: uma
para analisar todos os indices do usuário atual e a outra para executar a
compressão de acordo com o indice recomendado. O test case completo está em
anexo (pronto para rodar com um @ do sqlplus), vou comentando abaixo:
Primeiro
vou criar uma tabela com 1.000.000 linhas para servir de massa de dados:
create table t(pedido primary key, produto, cliente,
dt_pedido) as
select rownum
pedido,
mod(rownum,
1000) produto,
mod(rownum,
100) cliente,
sysdate +
mod(rownum, 100) dt_pedido
from
dual connect by level <= 1000000;
Note que
com essa sintaxe eu garanto que todo pedido é unico (numero sequencial) e que
tenho 1000 produtos distintos e 100 clientes.
Em seguida
vou criar uma série de indices, tentando imitar as características de uma
aplicação convencional:
create index t_idx1
on t(pedido, produto, cliente);
create index t_idx2
on t(produto, cliente);
create index t_idx3 on t(produto, cliente, dt_pedido);
create index t_idx4 on t(cliente, produto, pedido);
create index t_idx5
on t(cliente);
Com o
modelo pronto, vou criar agora as ferramentas para trabalhar com a compressão.
Como a view INDEX_STATS é volátil, eu vou criar uma cópia dela chamada
IDX_STATS:
create table idx_stats as
select * from index_stats;
Depois
criamos a package responsável pela estimativa e compressão:
create or replace
package pkg_idx_compress as
procedure
idx_compress_analyze;
procedure
idx_compress_execute(pctsave number default 10);
end
pkg_idx_compress;
/
create or replace
package body pkg_idx_compress as
procedure
idx_compress_analyze as
begin
for r in
(select user as owner,
index_name
from
user_indexes)
loop
execute immediate 'analyze index ' || r.owner || '.' || r.index_name || '
validate structure';
insert into idx_stats select * from index_stats;
end loop;
commit;
end
idx_compress_analyze;
procedure
idx_compress_execute(pctsave number default 10) as
begin
for r in
(select user as owner, name, opt_cmpr_count from idx_stats where
opt_cmpr_pctsave >= pctsave)
loop
execute
immediate 'alter index ' || r.owner || '.' || r.name || ' rebuild compress ' ||
r.opt_cmpr_count;
end loop;
end
idx_compress_execute;
end pkg_idx_compress;
/
Note que a
procedure idx_compress_analyze simplesmente chama a ANALYZE INDEX VALIDATE
STRUCTURE uma vez para cada indice do usuário e guarda uma cópia da informação
na tabela IDX_STATS. A procedure idx_compress_execute irá varrer a tabela
IDX_STATS em busca de indices que tenham uma estimativa de melhoria maior ou
igual a 10%, ou o valor especificado.
Tendo todas
as ferramentas na mão podemos analisar o cenário antes e depois da compressão:
SQL> select
table_name, index_name, bytes / 1024 as kbytes
2 from user_indexes ui,
3 user_segments us
4
where ui.index_name = us.segment_name;
TABLE_NAME
INDEX_NAME KBYTES
------------------------------
--------------- ----------
T
T_IDX4
26624
T
SYS_C0012678 17408
T
T_IDX1
26624
T
T_IDX2
20480
T
T_IDX3
29696
T
T_IDX5
16384
6 linhas selecionadas.
Executando
a estimativa:
SQL> exec pkg_idx_compress.idx_compress_analyze;
Procedimento PL/SQL concluído com sucesso.
SQL> select user
as owner,
2 name as index_name,
3 opt_cmpr_count,
4 opt_cmpr_pctsave
5 from idx_stats;
OWNER
INDEX_NAME OPT_CMPR_COUNT
OPT_CMPR_PCTSAVE
---------- --------------- --------------
----------------
DANI SYS_C0012678
0
0
DANI T_IDX1
0
0
DANI T_IDX2
2
38
DANI T_IDX3
3
57
DANI T_IDX4
2
29
DANI T_IDX5
1
21
6 linhas selecionadas.
Note que tanto
o indice SYS_C0012678 (que é a nossa PK) como o indice T_IDX1 que possui a
coluna pedido como primeira coluna tem estimativa zero. Isso se deve ao fato da
questão da repetição que eu falei. Os demais apresentam bons indices de
compressão esperados em função da grande quantidade de repetição.
Agora,
vendo o resultado na prática:
SQL> exec
pkg_idx_compress.idx_compress_execute;
Procedimento PL/SQL concluído com sucesso.
SQL> select
table_name, index_name, bytes / 1024 as kbytes
2 from user_indexes ui,
3 user_segments us
4
where ui.index_name = us.segment_name;
TABLE_NAME
INDEX_NAME KBYTES
------------------------------
--------------- ----------
T
T_IDX4
18432
T
SYS_C0012678 17408
T
T_IDX1
26624
T
T_IDX2
13312
T
T_IDX3
13312
T
T_IDX5
13312
6 linhas selecionadas.
No fim das
contas houve uma pequena diferença com relação ao estimado, mas dá para ver que
a compressão indicada foi bem eficiente.
Claro que
trata-se de um caso artificial, mas na minha vida de DBA eu já utilizei
estratégias similares com bons resultados. Inclusive esse procedimento pode ser
feito online.
Para
finalizar, segue o trecho do manual que trata de compressão de indices
(extraido de http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_1010.htm):
Specify
COMPRESS
to enable key compression, which eliminates repeated
occurrence of key column values. Use integer to
specify the prefix length (number of prefix columns to compress).- For unique indexes, the range of valid prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.
- For nonunique indexes, the range of valid prefix length values is from 1 to the number of key columns. The default prefix length is number of key columns.
Oracle Database compresses indexes that are
nonunique or unique indexes of at least two columns. If you want to use
compression for a partitioned index, then the index must have compression
enabled at the index level.
Specify
NOCOMPRESS
to disable key compression. This is the default.
Nenhum comentário:
Postar um comentário