Estatísticas incrementais para tabelas e índices particionadas

A coleta de estatísticas de forma incremental foi introduzida a partir da versão 11g, para melhorar a performance da coleta de estatística de grandes tabelas particionadas.

Nas tabelas particionadas, geralmente os novos dados são inseridos em uma nova partição. Quando uma nova partição é adicionada e os dados inseridos, as estatísticas da nova partição deve ser coletada e as estatísticas globais também devem ser atualizadas. Esse processo em grandes tabelas particionadas pode ser de grande impacto no ambiente uma vez que quanto mais partições são inseridas maior será o tempo de coleta devido ao full table scan realizado para atualização das estatísticas globais.

Com a coleta incremental ativada (ativação a nível de tabela) e a coleta é realizada com o parâmetro GRANULARITY AUTO, o Oracle realizada a coleta de estatística da nova partição e atualizará a estatística global apenas com as informações das partições modificadas, ao invés de atualizar as informações de todas as partições existentes (realizando um full table scan).

Vamos a prática..

Estarei realizando uma demonstração utilizando uma tabela com 38 partições existentes, e uma tamanho de 41,5 MB.

LAMIMTST1@SYS>  select table_owner, table_name, count(1) from dba_tab_partitions where table_name='TABLE_PART_LAMIM' group by table_name, table_owner;

TABLE_OWNER                    TABLE_NAME                       COUNT(1)
------------------------------ ------------------------------ ----------
LAMIM                          TABLE_PART_LAMIM             38


LAMIMTST1@SYS> select sum(bytes/1024/1024) from dba_segments where segment_name='TABLE_PART_LAMIM';

SUM(BYTES/1024/1024)
--------------------
                41.5

Elapsed: 00:00:00.14

Primeiramente, vamos verificar através do comando dbms_stats.get_prefs se a tabela TABLE_PART_LAMIM está com o parâmetro FALSE (desativado) ou TRUE (ativado) para as coletas incrementais.

LAMIMTST1@SYS> SELECT dbms_stats.get_prefs(ownname=>'LAMIM',pname=>'INCREMENTAL',tabname=>'TABLE_PART_LAMIM') FROM DUAL;

DBMS_STATS.GET_PREFS(OWNNAME=>'LAMIM',PNAME=>'INCREMENTAL',TABNAME=>'TABLE_PART_LAMIM
--------------------------------------------------------------------------------
FALSE

Conforme podemos contatar acima, a coleta desta tabela não executará de forma incremental. Posteriormente, executamos uma coleta de estatística da tabela para mensurar o tempo de execução, que foi de 21,28 segundos.

LAMIMTST1@SYS> begin
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3  ownname => 'LAMIM',
  4  TABNAME =>'TABLE_PART_LAMIM',
  5  estimate_percent => dbms_stats.auto_sample_size,
  6  method_opt => 'FOR ALL COLUMNS  SIZE AUTO',
  7  degree => 5 ,
  8  granularity => 'ALL',
  9  cascade => TRUE,
 10  no_invalidate => FALSE);
 11  end;
 12  /


PL/SQL procedure successfully completed.

Elapsed: 00:00:21.28
LAMIMTST1@SYS> LAMIMTST1@SYS>

Agora vamos definir o parâmetro incremental como TRUE e verificar o tempo de execução para que possamos comparar.

exec dbms_stats.set_table_prefs('LAMIM','TABLE_PART_LAMIM','INCREMENTAL','TRUE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18

LAMIMTST1@SYS> SELECT dbms_stats.get_prefs(ownname=>'LAMIM',pname=>'INCREMENTAL',tabname=>'TABLE_PART_LAMIM') FROM DUAL;

DBMS_STATS.GET_PREFS(OWNNAME=>'DW',PNAME=>'INCREMENTAL',TABNAME=>'FATOCLASSIFICA
--------------------------------------------------------------------------------
TRUE

Agara que definimos a opção INCREMENTAL para nossa tabela particionada, vamos efetuar uma nova coleta de estatísticas para mensurar novo tempo de execução.

LAMIMTST1@SYS> begin
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3  ownname => 'LAMIM',
  4  TABNAME =>'TABLE_PART_LAMIM',
  5  estimate_percent => dbms_stats.auto_sample_size,
  6  method_opt => 'FOR ALL COLUMNS  SIZE AUTO',
  7  degree => 5 ,
  8  granularity => 'ALL',
  9  cascade => TRUE,
 10  no_invalidate => FALSE);
 11  end;
 12  /


PL/SQL procedure successfully completed.

Elapsed: 00:00:6.31
LAMIMTST1@SYS> LAMIMTST1@SYS>

Conforme pode ser validado na execução acima, o ganho no tempo de execução com a coleta incremental é bastante expressivo, representando um ganho de 70,35%.

Vale ressaltar alguns pontos importantes sobre esse processo:

• Para que o processo de coleta execute de modo incremental, o valor do parâmetro estimate_percent deve estar definido como dbms_stats.auto_sample_size.
• Sempre que uma tabela particionada é definida para execução da coleta incremental, sempre que houver uma alteração DML em uma partição ou subpartição a mesma será marcada para que a mesma seja coletada na próxima execução da rotina.Ou seja, não é levado em consideração o valor do stale_percent.
• A partir da versão 12c já é possível utilizar a coleta incremental junto com a definição de uma porcentagem de modificações (stale_percent).

Fonte:

https://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm#i37048
https://blogs.oracle.com/optimizer/efficient-statistics-maintenance-for-partitioned-tables-using-incremental-statistics-part-1