quinta-feira, 27 de junho de 2013

Utilizando a compressão de índices no Oracle

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

key_compression

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.

Restriction on Key Compression You cannot specify COMPRESS for a bitmap index.

Nenhum comentário:

Postar um comentário